CODEWITHSUNDEEP
phpmysqlpdo
Jules May
Jules May

Reputation: 805

'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active

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.

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.

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

Upvotes: 3

Views: 3408

Answers (2)

Sergii Shymko
Sergii Shymko

Reputation: 124

Trigger can be created via PDO::exec() even when PDO::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);

$db->exec("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;");

Upvotes: 2

Jules May
Jules May

Reputation: 805

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

Hope this helps someone

Upvotes: 6

Related Questions