php MySQL 错误 2014 的原因无法执行查询而其他无缓冲查询处于活动状态

Question

提问by user1032531

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

我的服务器运行 CentOS 6.4，MySQL 5.1.69 安装使用 yum 和 CentOS 的存储库，PHP 5.4.16 安装使用 yum 和 ius 的存储库。Edit3升级到 MySQL Server 版本：5.5.31 由 IUS 社区项目分发，错误仍然存在。然后将库更改为mysqlnd，似乎消除了错误。尽管如此，通过这种来回，需要知道为什么这个错误只是有时会出现。

When using PDO and creating the PDO object using PDO::ATTR_EMULATE_PREPARES=>false, I sometimes get the following error:

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

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

Line 547 is the last line of:

第 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]));

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.

几年前我遇到了类似的问题，但从 PHP 5.1 升级到 PHP 5.3（并且 MySQL 可能也更新了），问题神奇地消失了，现在我用 PHP 5.5 解决了这个问题。

Why does it only manifest itself when PDO::ATTR_EMULATE_PREPARES=>false, and with only alternating version of PHPs?

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

I've also found that closeCursor()will also fix the error. Should this always be done after every SELECTquery 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.

我还发现这closeCursor()也将修复错误。是否应该在每次未使用的SELECT查询之后执行此操作fetchAll()？请注意，即使查询类似于SELECT COUNT(col2)只返回一个值，该错误仍然会发生。

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

编辑顺便说一句，这就是我创建连接的方式。我最近才添加了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');

Answer 1

回答by Bill Karwin

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 客户端协议不允许多个查询“进行中”。也就是说，您已经执行了一个查询并且您已经获取了一些结果，但不是全部——然后您尝试执行第二个查询。如果第一个查询仍有要返回的行，则第二个查询会出错。

Client libraries get around this by fetching allthe 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 服务器而言）。这就是“缓冲查询”。这与使用 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.

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

The chief reason notto 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.

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

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.

在获取最后一行之前，您可以使用 closeCursor() 来“放弃”结果。MySQL 服务器得到通知，它可以在服务器端丢弃该结果，然后您可以执行另一个查询。在完成获取给定的结果集之前，您不应关闭光标（）。

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.

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

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

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.

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

You should use mysqlnd anyway. It has more features, it's more memory-efficient, and its license is compatible with PHP.

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

Answer 2

回答by user1032531

I am hoping for a better answer than the following. While some of these solutions might "fix" the problem, they don't answer the original question regarding what causes this error.

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

Set PDO::ATTR_EMULATE_PREPARES=>true(I don't wish to do this)
Set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY(didn't work for me)
Use PDOStatement::fetchAll()(not always desirable)
Use $stmt->closeCursor()after each $stmt->fetch()(this mostly worked, however, I still had several cases where it didn't)
Change PHP MySQL library from php-mysql to php-mysqlnd (probably what I will do if no better answer)

设置PDO::ATTR_EMULATE_PREPARES=>true（我不想这样做）
设置PDO::MYSQL_ATTR_USE_BUFFERED_QUERY（对我不起作用）
使用PDOStatement::fetchAll()（并不总是可取的）
$stmt->closeCursor()在每个之后使用$stmt->fetch()（这主要是有效的，但是，我仍然有几个没有的情况）
将 PHP MySQL 库从 php-mysql 更改为 php-mysqlnd（如果没有更好的答案，我可能会怎么做）

Answer 3

回答by oleg

I have almost same problem. My first query after connection to db return empty result and drop this error. Enabling buffer doesn't help.

我有几乎同样的问题。我连接到 db 后的第一个查询返回空结果并删除此错误。启用缓冲区没有帮助。

My connection code was:

我的连接代码是：

try { 
    $DBH = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password, 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8; SET NAMES utf8",
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM));
} 
catch(PDOException $e) { echo $e->getMessage(); }

Solution in my way was to remove initial command:

我的解决方案是删除初始命令：

PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8; SET NAMES utf8"

Here is a correct code:

这是一个正确的代码：

try { 
    $DBH = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password, 
    array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM));
} 
catch(PDOException $e) { echo $e->getMessage(); }

And MYSQL_ATTR_USE_BUFFERED_QUERY is not forced to true. It's set as default.

并且 MYSQL_ATTR_USE_BUFFERED_QUERY 不会被强制为真。它被设置为默认值。

Answer 4

回答by tand3m

I had the same problem, I was sending results to another function mid loop. Quick fix was, save all results in an array (like Bill stated, if it's too large you have other issues to worry about), after collecting the data, I ran a separate loop to call the function one at a time.

我遇到了同样的问题，我将结果发送到另一个函数中间循环。快速解决方法是，将所有结果保存在一个数组中（如 Bill 所说，如果它太大，您还有其他问题需要担心），在收集数据后，我运行了一个单独的循环来一次调用一个函数。

Also, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY did not work for me.

另外， PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 对我不起作用。

Answer 5

回答by Barbora Schramková

It could be a simple mistake in SQL string. I had mistakenly two SQL queries in one php variable. And the error "other unbuffered queries are active" was appearing only on the next DB query, so it was hard to find.

这可能是 SQL 字符串中的一个简单错误。我错误地在一个 php 变量中使用了两个 SQL 查询。并且错误“其他未缓冲的查询处于活动状态”仅出现在下一个数据库查询中，因此很难找到。

php MySQL 错误 2014 的原因无法执行查询而其他无缓冲查询处于活动状态

提问by user1032531

回答by Bill Karwin

回答by user1032531

回答by oleg

回答by tand3m

回答by Barbora Schramková

相关推荐

最近更新

标签

php MySQL 错误 2014 的原因无法执行查询而其他无缓冲查询处于活动状态

提问by user1032531

回答by Bill Karwin

回答by user1032531

回答by oleg

回答by tand3m

回答by Barbora Schramková

相关推荐

php Laravel 4 查询生成器：LEFT JOIN ... AND ... 查询

PHP：静态和非静态函数和对象

在 PHP 中连接文件

仅在调用时刷新 PHP 页面一次

相关推荐

最近更新

标签