消息"SQLSTATE [HY000]"的"PDOException":常规错误:2014当其他未缓冲的查询处于活动状态时，无法执行查询

问题描述

我知道这个问题已经问过很多次了，但是普遍接受的答案并没有帮助我.但是我很偶然地偶然发现了一个答案.

I know this question has been asked many times, but the commonly-accepted answers didn't help me. But quite by accident I stumbled on an answer.

这里是设置:我通过连接进行了大量的查询(主要是CREATE TABLE).但是CREATE TRIGGER不断抛出可怕的2014年错误.这与打开的游标无关，因为即使它是程序中的唯一命令，它也发生了.例如，这失败了:

Here's the setup: I had a load of queries (mostly CREATE TABLE) going through a connection. But a CREATE TRIGGER kept throwing up the dreaded 2014 error. This was nothing to do with open cursors, becasue it happened even when it was the only command in the program. This, for example, failed:

<?php
$db = new PDO ($cnstring, $user, $pwd);
$db->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute (PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

$st = $db->query ("CREATE TRIGGER `CirclesClosureSync` AFTER INSERT ON Circles
FOR EACH ROW BEGIN
    INSERT INTO CirclesClosure (ancestor, descendant)
    SELECT ancestor, NEW.ID from CirclesClosure WHERE descendant=NEW.Parent;
    INSERT INTO CirclesClosure (ancestor, descendant) values (NEW.ID, NEW.ID);
END;");

$st->closeCursor();
?>

这似乎与涉及创建存储过程的其他问题相似.

This seemed similar to other problems involving creating stored procedures.

这是php 5.4.5，MySql 5.5，Windows XP(尽管在其他Windows上也失败了)

This is php 5.4.5, MySql 5.5, Windows XP (though it failed on other windows' too)

推荐答案

有点麻烦，但是我发现当我拿出ATTR_EMULATE_PREPARES = false时(默认是模拟)，它起作用了:

Took a bit of fiddling, but I found that when I took the ATTR_EMULATE_PREPARES=false out (the default is to emulate), it worked:

<?php
$db = new PDO ($cnstring, $user, $pwd);
$db->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$db->setAttribute (PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

$st = $db->query ("CREATE TRIGGER `CirclesClosureSync` AFTER INSERT ON Circles
FOR EACH ROW BEGIN
    INSERT INTO CirclesClosure (ancestor, descendant)
    SELECT ancestor, NEW.ID from CirclesClosure WHERE descendant=NEW.Parent;
    INSERT INTO CirclesClosure (ancestor, descendant) values (NEW.ID, NEW.ID);
END;");

$st->closeCursor();
?>

希望这对某人有帮助

这篇关于消息"SQLSTATE [HY000]"的"PDOException":常规错误:2014当其他未缓冲的查询处于活动状态时，无法执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！