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