MySQL 错误 2014 的原因 Cannot execute queries while other unbuffered queries are active

Question

我的服务器运行 CentOS 6.4，使用 yum 和 CentOS 的 repos 安装 MySQL 5.1.69，并使用 yum 和 ius 的 repos 安装 PHP 5.4.16。 Edit3 升级到MySQL Server version: 5.5.31 由IUS Community Project发布，错误依旧。然后将库更改为 mysqlnd，似乎消除了错误。仍然，来回如此，需要知道为什么这个错误只是有时出现。

使用 PDO 并使用 PDO::ATTR_EMULATE_PREPARES=>false 创建 PDO 对象时，有时会出现以下错误：

 Table Name - zipcodes
Error in query:
SELECT id FROM cities WHERE name=? AND states_id=?
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.
File Name: /var/www/initial_install/build_database.php
Line: 547
Time of Error: Tuesday July 2, 2013, 5:52:48 PDT

第 547 行是以下内容的最后一行：

 $stmt_check_county->execute(array($data[5],$data[4]));
if(!$county_id=$stmt_check_county->fetchColumn())
{
    $stmt_counties->execute(array($data[5]));
    $county_id=db::db()->lastInsertId();
}
//$stmt_check_county->closeCursor(); //This will fix the error
$stmt_check_city->execute(array($data[3],$data[4]));

几年前我遇到过类似的问题，但是从 PHP 5.1 升级到 PHP 5.3（MySQL 可能也更新了），问题神奇地消失了，现在我有了 PHP 5.5。

为什么它只在 PDO::ATTR_EMULATE_PREPARES=>false 时出现，并且只有交替版本的 PHP？

我还发现 closeCursor() 也会修复错误。这应该总是在每次 SELECT 查询之后完成，其中 fetchAll() 没有被使用吗？请注意，即使查询类似于 SELECT COUNT(col2) 之类的查询，该错误仍然会发生，它只返回一个值。

顺便 说一句，这就是我创建连接的方式。我最近才添加 MYSQL_ATTR_USE_BUFFERED_QUERY=>true ，但是，它并不能解决错误。 此外，可以按原样使用以下脚本来创建错误。

 function sql_error($e,$sql=NULL){return('<h1>Error in query:</h1><p>'.$sql.'</p><p>'.$e->getMessage().'</p><p>File Name: '.$e->getFile().' Line: '.$e->getLine().'</p>');}

class db {
    private static $instance = NULL;
    private function __construct() {}   //Make private
    private function __clone(){}   //Make private
    public static function db() //Get instance of DB
    {
        if (!self::$instance)
        {
            //try{self::$instance = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
            try{self::$instance = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
            //try{self::$instance = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
            catch(PDOException $e){echo(sql_error($e));}
        }
        return self::$instance;
    }
}

$row=array(
    'zipcodes_id'=>'55555',
    'cities_id'=>123
);
$data=array($row,$row,$row,$row);

$sql = 'CREATE TEMPORARY TABLE temp1(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (temp_id) )';
db::db()->exec($sql);

$sql='SELECT COUNT(*) AS valid FROM cities_has_zipcodes WHERE cities_id=? AND zipcodes_id=?';
$stmt1 = db::db()->prepare($sql);

$sql ='SELECT temp_id FROM temp1';
$stmt2 = db::db()->prepare($sql);

foreach($data AS $row)
{
    try
    {
        $stmt1->execute(array($row['zipcodes_id'],$row['cities_id']));
        $rs1 = $stmt1->fetch(PDO::FETCH_ASSOC);
        //$stmt1->closeCursor();
        syslog(LOG_INFO,'$rs1: '.print_r($rs1,1).' '.rand());
        $stmt2->execute();
        $rs2 = $stmt2->fetch(PDO::FETCH_ASSOC);
        syslog(LOG_INFO,'$rs2: '.print_r($rs2,1).' '.rand());
    }
    catch(PDOException $e){echo(sql_error($e));}
}
echo('done');

原文由 user1032531 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

MySQL 客户端协议不允许多个查询“进行中”。也就是说，您执行了一个查询并获取了部分结果，但不是全部——然后您尝试执行第二个查询。如果第一个查询仍有要返回的行，则第二个查询会出错。

客户端库通过在第一次获取时隐式获取第一个查询的 所有 行来解决这个问题，然后后续获取只是迭代内部缓存的结果。这使他们有机会关闭游标（就 MySQL 服务器而言）。这就是“缓冲查询”。这与使用 fetchAll() 的工作方式相同，因为这两种情况都必须在 PHP 客户端中分配足够的内存来保存完整的结果集。

不同之处在于缓冲查询将结果保存在 MySQL 客户端库中，因此 PHP 无法访问这些行，直到您按顺序 fetch() 每行。而 fetchAll() 立即为所有结果填充一个 PHP 数组，允许您访问任何随机行。

不 使用 fetchAll() 的主要原因是结果可能太大而无法满足您的 PHP memory_limit。但是看起来您的查询结果无论如何都只有一行，所以这应该不是问题。

您可以 closeCursor() 在获取最后一行之前“放弃”结果。 MySQL 服务器收到通知，它可以在服务器端丢弃该结果，然后您可以执行另一个查询。你不应该 closeCursor() 直到你完成获取给定的结果集。

另外：我注意到你在循环中一遍又一遍地执行你的 $stmt2 ，但它每次都会返回相同的结果。根据将循环不变代码移出循环的原则，您应该在开始循环之前执行一次，并将结果保存在 PHP 变量中。因此，无论使用缓冲查询还是 fetchAll()，都不需要嵌套查询。

因此，我建议您以这种方式编写代码：

 $sql ='SELECT temp_id FROM temp1';
$stmt2 = db::db()->prepare($sql);
$stmt2->execute();
$rs2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$stmt2->closeCursor();

$sql='SELECT COUNT(*) AS valid FROM cities_has_zipcodes
      WHERE cities_id=:cities_id AND zipcodes_id=:zipcodes_id';
$stmt1 = db::db()->prepare($sql);

foreach($data AS $row)
{
    try
    {
        $stmt1->execute($row);
        $rs1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
        $stmt1->closeCursor();
        syslog(LOG_INFO,'$rs1: '.print_r($rs1[0],1).' '.rand());
        syslog(LOG_INFO,'$rs2: '.print_r($rs2[0],1).' '.rand());
    }
    catch(PDOException $e){echo(sql_error($e));}
}

请注意，我还使用了命名参数而不是位置参数，这使得将 $row 作为参数值数组传递变得更简单。如果数组的键与参数名称匹配，则可以只传递数组。在旧版本的 PHP 中，您必须在数组键中包含 : 前缀，但您不再需要它了。

无论如何你应该使用 mysqlnd。它有更多的特性，更节省内存，并且它的许可证与 PHP 兼容。

原文由 Bill Karwin 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

我希望得到比以下更好的答案。虽然其中一些解决方案可能会“解决”问题，但它们不会回答有关导致此错误的原因的原始问题。

1. 设置 PDO::ATTR_EMULATE_PREPARES=>true （我不想这样做）
2. 设置 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY （对我不起作用）
3. 使用 PDOStatement::fetchAll() （并不总是可取的）
4. 在每个 --- $stmt->closeCursor() $stmt->fetch() （这大部分都有效，但是，我仍然有几个没有成功的案例）
5. 将 PHP MySQL 库从 php-mysql 更改为 php-mysqlnd（如果没有更好的答案，我可能会这样做）

原文由 user1032531 发布，翻译遵循 CC BY-SA 3.0 许可协议