PDO::query() run into "Cannot execute queries while other unbuffered queries are active."

Question

Maybee some other have the same problem than me. I run over the error:

Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

on PDO. As in many threads mentioned the error can at be at least one of the following problems:

1. The query cursor was not closed with closeCursor() as mentioned here; Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
2. There are more than two querys with one statement like mentioned here: PDO Cannot execute queries while other unbuffered queries are active
3. A bug in mysql-driver as mentioned here: What is causing PDO error Cannot execute queries while other unbuffered queries are active?

In my case all above did not help and it took some time till i solved the problem. this was my code (pseudo-code):

$stmt->startTransaction();
$stmt = db::getInstance()->prepare("CALL phones(:phone)");
$stmt->prepare('SELECT * FROM database');
$stmt->execute();
$aData = $stmt->fetchAll();
$stmt->closeCursor();

$stmt->query("USE sometable;");

After I changed it to:

$stmt->startTransaction();
$stmt = db::getInstance()->prepare("CALL phones(:phone)");
$stmt->prepare('SELECT * FROM database');
$stmt->execute();
$aData = $stmt->fetchAll();
$stmt->closeCursor();

$stmt->exec("USE sometable;");

It worked for my. What is the difference between query and exec?

PDO::exec() - "Execute an SQL statement and return the number of affected rows"
PDO::query() - "Executes an SQL statement, returning a result set as a PDOStatement object"

Why in this case PDO::query() does not work? The cursor IS closed, when called.

Answer 1

While it could conceivably be true that you've encountered the mysql driver bug here, we can't be sure of that because you've not given us that information (what version of PHP are you using? Does it use mysqlnd => check with php -i | grep mysqlnd? What does the rest of your code look like?).
There are many other possible explanations for your problem. I suspect the issue is actually your failing to close all the cursors, and/or fetch all the results, because $stmt is being reused heavily:

Quoted directly from the PDO::query manual page:

If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

You call closeCursor on $stmt, that's true, but you've not closed all cursors that have been created by you:

//<-- what is $stmt here?
$stmt->startTransaction();
//no matter, you've reassigned it a PDOStatement instance
$stmt = db::getInstance()->prepare("CALL phones(:phone)");
//Huh? You're preparing yet another query on an instance of PDOStatement?
$stmt->prepare('SELECT * FROM database');
//you're executing this one, though
$stmt->execute();
//and fetching all data
$aData = $stmt->fetchAll();
//and closing this last statement
$stmt->closeCursor();

But what about the first statement you assigned to $stmt (the stored procedure call)? That cursor isn't closed anywhere

Now for the major difference between PDO::query and PDO::exec. Again, quoting the manual:

PDO::exec() does not return results from a SELECT statement.

Whereas:

PDO::query() executes an SQL statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object.

Answer 2

I came across this problem too. It is likely to be a bug. If we take the following code, then you will see how it fails with the message 'General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll().'

$pdo = new \PDO("mysql:host=localhost", "root", "");
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

$pdo->query("USE test");

If you change $pdo->query("USE test"); to $pdo->exec("USE test"); it will work. If you change $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false); to $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true); it will also work. I haven't been able to find a proper solution yet though.

Answer 3

I solve the issue with steps:

After from performed: $stmt = db::getInstance()->prepare("CALL phones(:phone)");

I close the: $stmt->startTransaction();

And after that, I open again the transaction to use the query below: $stmt->prepare('SELECT * FROM database');

My solve it is: One statement to call the procedure "CALL phones(:phone)" and another to execute the query wtih "SELECT * FROM database".

That is it.

Answer 4

Be careful, This can also happen if you are trying to fetch a non SELECT query (Eg - UPDATE/INSERT/ALTER/CREATE)