导致PDO错误的原因其他未缓冲的查询处于活动状态时无法执行查询? [英] What is causing PDO error Cannot execute queries while other unbuffered queries are active?

问题描述

我有以下代码:

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

但是，由于某些原因，当执行 second 准备好的语句时，会出现以下错误:

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

致命错误:带有消息的未捕获异常'PDOException' 'SQLSTATE [HY000]:一般错误:2014年在执行以下操作时无法执行查询 其他未缓冲的查询处于活动状态.考虑使用 PDOStatement :: fetchAll().或者，如果您的代码只是 要针对mysql运行，您可以通过设置启用查询缓冲 PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性.'

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.'

我知道此错误意味着的含义以及如何解决该错误(执行unset($stmt);或$stmt->closeCursor();)，因此我不希望找到如何使其正常工作的解决方案.据我了解，这通常是由于执行fetch而不是fetchAll并且未获取所有结果引起的.但是，在这种情况下，只有一个结果，并且正在获取该结果.同样，如果我只执行第一个准备好的语句一次，则不会发生该错误.仅在第一个语句执行两次 时才会发生.它也只会在PDO::ATTR_EMULATE_PREPARES是false时发生.

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.

我已经在两台Ubuntu 13.10服务器Debian和CentOS上对此进行了测试，并且都使用默认软件包产生了相同的错误.

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

要回答Ryan Vincent的评论，我是一个完整的mysqli noob，但是我相信下面的内容与上面的示例大致相同.如果我错了，请纠正我.但是，它不会产生任何错误，因此它似乎是仅PDO的错误:

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

推荐答案

奇怪的是，Ubuntu提供的PHP软件包未使用

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

您的测试用例("Edit 4"，使用setAttribute(MYSQL_ATTR_USE_BUFFERED_QUERY, true))在使用mysqlnd手动编译的PHP 5.5.3中可以按预期工作:

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

...但是失败:

bash> ./configure --with-pdo-mysql=/usr/bin/mysql_config

奇怪的是，只有在第一个语句执行两次时它才会失败；这一定是 libmysqlclient 驱动程序中的错误.

It quite odd that it fails only if the first statement is executed twice; this must be a bug in the libmysqlclient driver.

当MYSQL_ATTR_USE_BUFFERED_QUERY为false时，两个驱动程序均按预期方式失败. 您的常识已经证明，无论结果集中的行数如何，为什么这都是预期的行为.

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发现当前的解决方法是安装php5-mysqlnd软件包，而不是Canonical建议的php5-mysql.

Mike found out that the current workaround is installing the php5-mysqlnd package instead of the Canonical-recommended php5-mysql.

这篇关于导致PDO错误的原因其他未缓冲的查询处于活动状态时无法执行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！