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

Question

I have the following code:

$dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbh->prepare("SELECT 1");
$stmt->execute();
$result = $stmt->fetch();

$stmt->execute();
$result = $stmt->fetch();

$stmt = $dbh->prepare("SELECT 1");
$stmt->execute();
$result = $stmt->fetch();

However, for some reason I get the following error when executing the second prepared statement:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 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.'

I know what this error means and how to fix it (either doing unset($stmt); or $stmt->closeCursor();), so I am not looking for a solution of how to get it to work. From what I understand it is usually caused by doing fetch instead of fetchAll and not fetching all the results. However in this case, there is only one result and it is being fetched. Also, if I only execute the first prepared statement once, the error does not occur. It only happens when the first statement is executed twice. It also only happens when PDO::ATTR_EMULATE_PREPARES is false.

So my question is, what is causing the above error to occur in this case? It doesn't appear to be any different than any other query I've ever executed.

I have tested this on two Ubuntu 13.10 servers, Debian and CentOS and all produce the same error using the default packages.

Edit:

To answer Ryan Vincent's comment, I am a complete mysqli noob, but I believe what I have below is roughly equivalent to the above example. Please correct me if I'm wrong. However it produces no errors, so it would appear to be a PDO-only error:

$mysqli = new mysqli($host, $user, $pass, $dbname);
if ($mysqli->connect_errno) {
    die("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
}

if (!($stmt = $mysqli->prepare("SELECT 1"))) {
     die("Prepare 1 failed: (" . $mysqli->errno . ") " . $mysqli->error);
}

if (!$stmt->execute()) {
    die("Execute 1 failed: (" . $stmt->errno . ") " . $stmt->error);
}
$stmt->store_result();
$stmt->bind_result($col1);
$stmt->fetch();

if (!$stmt->execute()) {
    die("Execute 2 failed: (" . $stmt->errno . ") " . $stmt->error);
}
$stmt->store_result();
$stmt->bind_result($col1);
$stmt->fetch();

if (!($stmt = $mysqli->prepare("SELECT 1"))) {
    // The following line is what fails in PDO
    die("Prepare 2 failed: (" . $mysqli->errno . ") " . $mysqli->error);
}

if (!$stmt->execute()) {
    die("Execute 3 failed: (" . $stmt->errno . ") " . $stmt->error);
}
$stmt->store_result();
$stmt->bind_result($col1);
$stmt->fetch();

Answer 1

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.

Answer 2

It seems that you have PDO::MYSQL_ATTR_USE_BUFFERED_QUERY set to FALSE.

And in such a case it is obligatory to make sure that there are no more rows pending for the retrieval. To do so one to run fetch() one extra time, as it seems that fetch() returning false is "releasing" non-buffered resultset somehow. Without such extra call non-buffered resultset remains locked and causing "Commands out of sync" error

Answer 3

This is not necessarily the answer to this question, but this may help somebody in the future.

I came across exactly the same error and it took hours to discover what was wrong. It turned out it was just a extremely minor syntax issue all along. If you're not actually using any buffering, but still have this error, like I did, this could be your issue - so check your code.

I was doing my normal database queries when I came across this error -- not purposely using any buffering techniques -- so I highly doubted it had anything to do with buffering. I read every SO question about it and looked deeper in to it.

This was my STUPID syntax issue:

$SQL = "UPDATE articles SET
            topicID = :topic;    <-------- semicolon - woops!
            heading = :heading,
            subheading = :subheading,
            keywords = :keywords,
            rawContent = :rawContent,
            content = :content,
            ...
            ...

This resulted in me getting this buffering error. I fixed the code and it went away. What was most annoying, was the fact the PDO error was pointing at another query, the next query, but that query was in a function elsewhere in the code, and that through me well off course for a while!

Answer 4

Just to complete the list of possible mistakes causing this issue ... because I was loosing my hair on this I want to share my solution/finding with you.

In my case I've tried to sent several statements to the database with PDO::exec

e.g.

self::$objDatabase->exec( "SELECT id from testtable; UPDATE testtable SET name = 'example';" );

Only 1 SQL Statement in 1 PDO::exec is allowed and save.