MySQL 错误 2014 的原因无法执行查询而其他无缓冲查询处于活动状态 [英] Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
问题描述
我的服务器运行 CentOS 6.4，MySQL 5.1.69 安装使用 yum 和 CentOS 的存储库，PHP 5.4.16 安装使用 yum 和 ius 的存储库.Edit3 升级到 MySQL Server 版本:5.5.31 由 IUS 社区项目分发，错误仍然存在.然后将库更改为mysqlnd，似乎消除了错误.尽管如此，来回反复，需要知道为什么这个错误只是有时会出现.
My server runs CentOS 6.4 with MySQL 5.1.69 installed using yum with CentOS's repos, and PHP 5.4.16 installed using yum with ius's repos. Edit3 Upgraded to MySQL Server version: 5.5.31 Distributed by The IUS Community Project, and error still exists. Then changed library to mysqlnd, and seems to eliminate the error. Still, with this back and forth, need to know why this error only sometimes manifests.
在使用 PDO 并使用 PDO::ATTR_EMULATE_PREPARES=>false 创建 PDO 对象时，我有时会收到以下错误:
When using PDO and creating the PDO object using PDO::ATTR_EMULATE_PREPARES=>false, I sometimes get the following error:
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 行是最后一行:
Line 547 is the last line of:
$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 中遇到了.
I had a similar problem several years ago, but upgraded from PHP 5.1 to PHP 5.3 (and MySQL probably was updated as well), and the problem magically went away, and now I have it with PHP 5.5.
为什么它只在 PDO::ATTR_EMULATE_PREPARES=>false 时才出现，并且只有 PHP 的交替版本?
Why does it only manifest itself when PDO::ATTR_EMULATE_PREPARES=>false, and with only alternating version of PHPs?
我还发现 closeCursor() 也可以修复错误.这是否应该总是在每个 SELECT 查询之后完成，其中 fetchAll() 没有被使用?请注意，即使查询类似于 SELECT COUNT(col2) 仅返回一个值，该错误仍然会发生.
I've also found that closeCursor() will also fix the error. Should this always be done after every SELECT query where fetchAll() is not used? Note that the error still occurs even if the query is something like SELECT COUNT(col2) which only returns one value.
编辑顺便说一下，这就是我创建连接的方式.我最近才添加了 MYSQL_ATTR_USE_BUFFERED_QUERY=>true，但是，它并不能解决错误.此外，可以按原样使用以下脚本来创建错误.
Edit By the way, this is how I create my connection. I've only recently added MYSQL_ATTR_USE_BUFFERED_QUERY=>true, however, it doesn't cure the error. Also, the following script could be used as is to create the error.
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');
推荐答案
MySQL 客户端协议不允许正在进行"多个查询.也就是说，您已经执行了一个查询并且您已经获取了一些结果，但不是全部——然后您尝试执行第二个查询.如果第一个查询仍有要返回的行，则第二个查询会出错.
The MySQL client protocol doesn't allow more than one query to be "in progress." That is, you've executed a query and you've fetched some of the results, but not all -- then you try to execute a second query. If the first query still has rows to return, the second query gets an error.
客户端库通过在第一次获取时隐式获取所有第一个查询的行来解决这个问题，然后后续获取简单地迭代内部缓存的结果.这使他们有机会关闭游标(就 MySQL 服务器而言).这就是缓冲查询".这与使用 fetchAll() 的工作原理相同，因为这两种情况都必须在 PHP 客户端中分配足够的内存来保存完整的结果集.
Client libraries get around this by fetching all the rows of the first query implicitly upon first fetch, and then subsequent fetches simply iterate over the internally cached results. This gives them the opportunity to close the cursor (as far as the MySQL server is concerned). This is the "buffered query." This works the same as using fetchAll(), in that both cases must allocate enough memory in the PHP client to hold the full result set.
不同之处在于缓冲查询将结果保存在 MySQL 客户端库中，因此 PHP 无法访问行，直到您按顺序 fetch() 每一行.而 fetchAll() 会立即为所有结果填充一个 PHP 数组，允许您访问任何随机行.
The difference is that a buffered query holds the result in the MySQL client library, so PHP can't access the rows until you fetch() each row sequentially. Whereas fetchAll() immediately populates a PHP array for all the results, allowing you access any random row.
不使用 fetchAll() 的主要原因是结果可能太大而无法容纳您的 PHP memory_limit.但看起来您的查询结果无论如何都只有一行，所以这应该不是问题.
The chief reason not to use fetchAll() is that a result might be too large to fit in your PHP memory_limit. But it appears your query results have just one row anyway, so that shouldn't be a problem.
在获取最后一行之前，您可以 closeCursor() 来放弃"结果.MySQL 服务器得到通知，它可以在服务器端丢弃该结果，然后您可以执行另一个查询.在完成获取给定的结果集之前，您不应关闭 Cursor().
You can closeCursor() to "abandon" a result before you've fetched the last row. The MySQL server gets notified that it can discard that result on the server side, and then you can execute another query. You shouldn't closeCursor() until you're done fetching a given result set.
另外:我注意到你在循环中一遍又一遍地执行你的 $stmt2，但它每次都会返回相同的结果.根据将循环不变代码移出循环的原则，您应该在开始循环之前执行一次，并将结果保存在 PHP 变量中.因此，无论使用缓冲查询还是 fetchAll()，您都无需嵌套查询.
Also: I notice you're executing your $stmt2 over and over inside the loop, but it will return the same result each time. On the principle of moving loop-invariant code out of the loop, you should have executed this once before starting the loop, and saved the result in a PHP variable. So regardless of using buffered queries or fetchAll(), there's no need for you to nest your queries.
所以我建议你这样写代码:
So I would recommend writing your code this way:
$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 中，您必须在数组键中包含 : 前缀，但您不再需要它了.
Note I also used named parameters instead of positional parameters, which makes it simpler to pass $row as the array of parameter values. If the keys of the array match the parameter names, you can just pass the array. In older versions of PHP you had to include the : prefix in the array keys, but you don't need that anymore.
无论如何你应该使用mysqlnd.它具有更多功能，更节省内存，并且其许可证与 PHP 兼容.
You should use mysqlnd anyway. It has more features, it's more memory-efficient, and its license is compatible with PHP.
这篇关于MySQL 错误 2014 的原因无法执行查询而其他无缓冲查询处于活动状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
