Php – causing PDO error Cannot execute queries while other unbuffered queries are active

MySQLpdoPHPprepared-statementunbuffered-queries

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();

Best Answer

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.

Related Topic