php - 'PDOException' 消息 'SQLSTATE[HY000] : General error: 2014 Cannot execute queries while other unbuffered queries are active

Question

我知道这个问题已被问过很多次，但普遍接受的答案对我没有帮助。但我偶然发现了一个答案。

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

<?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();
?>

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

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

Answer 1

花了一点时间，但我发现当我把 ATTR_EMULATE_PREPARES=false 去掉(默认是模拟)时，它起作用了:

<?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();
?>

希望对大家有帮助