MySQL错误2014的原因其他未缓冲的查询处于活动状态时无法执行查询 [英] Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
问题描述
我的服务器运行的CentOS 6.4带有使用yum和CentOS的回购协议安装的MySQL 5.1.69，以及PHP 5.4.16带有yum和ius的回购协议一起安装的PHP 5.4.16. Edit3 升级到MySQL服务器版本: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()也可以解决该错误.是否应该始终在不使用fetchAll()的每个SELECT查询之后执行此操作?请注意，即使查询是类似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.
客户端库通过在第一次获取时隐式获取 all 的第一个查询的行来解决此问题，然后后续的获取仅对内部缓存的结果进行迭代.这给他们提供了关闭游标的机会(就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不能访问行，直到您依次提取()提取每一行.而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()的主要原因 not 是因为结果可能太大而无法容纳在您的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服务器收到通知，可以在服务器端放弃该结果，然后可以执行另一个查询.在完成获取给定结果集之前，不应该关闭closeCursor().
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屋！
