Pdo Cannot Execute Queries While Other Unbuffered Queries Are Active

Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active

The MySQL client protocol doesn't allow more than one query to be "in progress." That is, you've executed a query and you've fetched some of the results, but not all -- then you try to execute a second query. If the first query still has rows to return, the second query gets an error.

Client libraries get around this by fetching all the rows of the first query implicitly upon first fetch, and then subsequent fetches simply iterate over the internally cached results. This gives them the opportunity to close the cursor (as far as the MySQL server is concerned). This is the "buffered query." This works the same as using fetchAll(), in that both cases must allocate enough memory in the PHP client to hold the full result set.

The difference is that a buffered query holds the result in the MySQL client library, so PHP can't access the rows until you fetch() each row sequentially. Whereas fetchAll() immediately populates a PHP array for all the results, allowing you access any random row.

The chief reason not to use fetchAll() is that a result might be too large to fit in your PHP memory_limit. But it appears your query results have just one row anyway, so that shouldn't be a problem.

You can closeCursor() to "abandon" a result before you've fetched the last row. The MySQL server gets notified that it can discard that result on the server side, and then you can execute another query. You shouldn't closeCursor() until you're done fetching a given result set.

Also: I notice you're executing your $stmt2 over and over inside the loop, but it will return the same result each time. On the principle of moving loop-invariant code out of the loop, you should have executed this once before starting the loop, and saved the result in a PHP variable. So regardless of using buffered queries or fetchAll(), there's no need for you to nest your queries.

So I would recommend writing your code this way:

$sql ='SELECT temp_id FROM temp1';
$stmt2 = db::db()->prepare($sql);
$stmt2->execute();
$rs2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$stmt2->closeCursor();

$sql='SELECT COUNT(*) AS valid FROM cities_has_zipcodes 
      WHERE cities_id=:cities_id AND zipcodes_id=:zipcodes_id';
$stmt1 = db::db()->prepare($sql);

foreach($data AS $row)
{
    try
    {
        $stmt1->execute($row);
        $rs1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
        $stmt1->closeCursor();
        syslog(LOG_INFO,'$rs1: '.print_r($rs1[0],1).' '.rand());
        syslog(LOG_INFO,'$rs2: '.print_r($rs2[0],1).' '.rand());
    }
    catch(PDOException $e){echo(sql_error($e));}            
}

Note I also used named parameters instead of positional parameters, which makes it simpler to pass $row as the array of parameter values. If the keys of the array match the parameter names, you can just pass the array. In older versions of PHP you had to include the : prefix in the array keys, but you don't need that anymore.

You should use mysqlnd anyway. It has more features, it's more memory-efficient, and its license is compatible with PHP.

Different solution to `Cannot execute queries while other unbuffered queries are active`

And here's how my constructor looked:

$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $db . ';port=' . $port, $user, $pass, 
                array(
                  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8;SET SESSION time_zone="system"', 
                  PDO::MYSQL_ATTR_LOCAL_INFILE => true
                ));

I changed it to

$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $db . ';port=' . $port, $user, $pass, 
                array(
                  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8, SESSION time_zone="system"',     
                  PDO::MYSQL_ATTR_LOCAL_INFILE => true
                ));

and it does work now.

I am posting my solution here because all other places have "wrong" ones.

So the difference is instead of having

'SET NAMES utf8;SET SESSION time_zone="system"'

in PDO::MYSQL_ATTR_INIT_COMMAND I have:

'SET NAMES utf8, SESSION time_zone="system"'

and everything's fine now.

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active

Finally I got the answer:

$statement = $connection->prepare($query);

$statement->execute();

$results = $statement->fetchAll();

$statement->closeCursor();

What is causing PDO error Cannot execute queries while other unbuffered queries are active?

Oddly enough, the PHP packages provided by Ubuntu are not compiled with the Mysql native driver, but with the old libmysqlclient instead (tested on Ubuntu 13.10 with default packages):

<?php
echo $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION); // prints "5.5.35", i.e MySQL version
// prints "mysqlnd (...)" when using mysqlnd

Your very test case ("Edit 4", with setAttribute(MYSQL_ATTR_USE_BUFFERED_QUERY, true)) works as expected with PHP 5.5.3 manually compiled with mysqlnd with:

./configure --with-pdo-mysql=mysqlnd # default driver since PHP v5.4

... but fails with:

bash> ./configure --with-pdo-mysql=/usr/bin/mysql_config

It quite odd that it fails only if the first statement is executed twice; this must be a bug in the libmysqlclient driver.

Both drivers fail as expected when MYSQL_ATTR_USE_BUFFERED_QUERY is false. Your Common Sense already demonstrated why this is expected behaviour, regardless of the number of rows in the result set.

Mike found out that the current workaround is installing the php5-mysqlnd package instead of the Canonical-recommended php5-mysql.

PDO General error: 2014 Cannot execute queries while other unbuffered queries are active when trying to LOCK TABLES

You should use exec() instead query(). exec() does not expect any return values, which is exactly what LOCK TABLES needs.

$pdo->exec("LOCK TABLES Inspections WRITE");

Laravel - PDO Prepared Statement - Cannot execute queries while other unbuffered queries are active

The only way I could get it working was to replace the prepared statement with an 'exec' call:

$rowCount = DB::connection()->getpdo()->exec(
            "LOAD DATA LOCAL INFILE '$fileName'
            INTO TABLE $tableName
            CHARACTER SET utf8mb4
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY '\\n'"
        );

I have no idea why it wouldn't work using a prepared statement in Laravel - it definitely does work with a pure PDO prepared statement.

PDO “Uncaught exception 'PDOException' .. Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll().”

You need to fetch until a row fetch attempt fails. I know you may only have one row in the result set and think one fetch is enough, but its not (when you're using unbuffered queries). PDO doesn't know how many rows there are until it reaches the end, where it tries to fetch the next row, but it fails.

You probably have other statements where you didn't fully "fetch until a fetch failed". Yes, I see that you fetch until the fetch failed for one of the statements, but that doesn't mean you did it for all of them.

To clarify -
When you execute a query via execute(), you create a result set that must be fetched from the db into php. PDO can only handle 1 of these "result set in progress of being fetched" at a time (per connection). You need to completely fetch the result set, all the way to the end of it, before you can start fetching a different result set from a different call to execute().

When you "call fetch() until a fetch() fails", the fact that you reached the end of the results is internally noted by PDO when that final call to fetch() fails due to there being no more results. PDO is then satisfied that the results are fully fetched, and it can clean up whatever internal resources between php and the db that were established for that result set, allowing you to make/fetch other queries.

There's other ways to make PDO "call fetch() until a fetch() fails".

1. Just use fetchAll(), which simply fetches all rows, and so it will hit the end of the result set.
2. or just call closeCursor()

*if you look at the source for closeCursor(), the default implementation literally just fetches the rows and discards them until it reaches the end. It's written in c obviously, but it more or less does this:

function closeCursor() {
    while ($row = $stmt->fetch()) {}
    $this->stmtFullyFetched = true;
}

Some db drivers may have a more efficient implementation that doesn't require them to fetch lots of rows that nobody cares about, but that's the default way PDO does it. Anyway...

Normally you don't have these problems when you use buffered queries. The reason is because with buffered queries, right after you execute them, PDO will automatically fully fetch the db results into php memory, so it does the "call fetch() until a fetch() fails" part for you, automatically. When you later call fetch() or fetchAll() yourself, it's fetching results from php memory, not from the db. So basically, the result set is immediately fully fetched when using buffered queries, so there's no opportunity to have more than 1 "result set in progress of being fetched" at the same time (because php is single threaded, so no chance of 2 queries running at the same time).

Given this:

$sql = "select * from test.a limit 1";
$stmt = $dbh->prepare($sql);
$stmt->execute(array());

Ways to fully fetch the result set (assuming you only want the first row):

$row = $stmt->fetch();
$stmt->closeCursor();

or

list($row) = $stmt->fetchAll(); //tricky

or

$row = $stmt->fetch();
while ($stmt->fetch()) {}

---

Related Topics