php - 一个查询调用中的 PDO 多个语句以 "unbuffered queries"错误结束

Question

我正在尝试在 MySQL 的 PHP/PDO 中实现一些嵌套的集合操作。进行插入操作后(如此处所述:http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)由于错误 HY000/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.

产生错误的代码(错误在代码底部的最后一个“SELECT * FROM tree”处抛出):

// insert a node into a nested set as child
$queryParts = array(
    'LOCK TABLE tree WRITE',
    'SELECT @myLeft:= lft FROM tree WHERE id = :parentuid',
    'UPDATE tree SET rgt = rgt + 2 WHERE rgt > @myLeft',
    'UPDATE tree SET lft = lft + 2 WHERE lft > @myLeft',
    'UPDATE tree SET lft = @myLeft + 1, rgt = @myLeft + 2 WHERE id = :childuid',
    'UNLOCK TABLES',
);

$parentuid = $parent->getUid();
$childuid = $node->getUid();

$stmt = self::$connection->prepare(join(";\n", $queryParts) . ';') or die (print_r(self::$connection->errorCode(),1));
$stmt->bindParam(':parentuid', $parentuid);
$stmt->bindParam(':childuid', $childuid);

$res = $stmt->execute() or die (print_r($stmt->errorCode(),1));

// that does not help :(
if ($res) {
    do {
        $stmt->fetchAll(); // empty array
        $stmt->closeCursor();
    } while($stmt->nextRowset());
}

// is not executed
$rset = self::$connection->query('SELECT * FROM tree')
     or die (print_r(self::$connection->errorInfo(),1)); // that kills the code
while($row = $rset->fetchObject()) {
    var_dump($row);
}

在我看来，没有公开的查询。 如何解决这个错误？目前我被卡住了，所以非常感谢任何帮助:)

Answer 1

如果查询包含多个由分号分隔的语句并在非缓冲模式下执行，则会出现错误“无法执行查询而其他非缓冲查询处于事件状态”。

解决方案是:

• 分别执行每个查询部分，每个部分应该是没有分号“;”的单个查询
• 或切换到缓冲模式。