3

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)

Jules May
  • 753
  • 2
  • 10
  • 18

2 Answers2

6

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

Jules May
  • 753
  • 2
  • 10
  • 18
2

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;");
Sergii Shymko
  • 116
  • 4