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:
- 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 - There are more than two querys with one statement like mentioned here: PDO Cannot execute queries while other unbuffered queries are active
- 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.
Best Answer
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 withphp -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:You call
closeCursor
on$stmt
, that's true, but you've not closed all cursors that have been created by you:But what about the first statement you assigned to
$stmt
(the stored procedure call)? That cursor isn't closed anywhereNow for the major difference between
PDO::query
andPDO::exec
. Again, quoting the manual:Whereas: