Bug #20798 mysqlbinlog does not generate DELIMITER for CREATE TRIGGER statements
Submitted: 30 Jun 2006 20:59 Modified: 1 Jul 2006 8:51
Reporter: Brandon Fosdick Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.0.22 OS:Any (All)
Assigned to: CPU Architecture:Any

[30 Jun 2006 20:59] Brandon Fosdick
Description:
This report details a way to cause mysqlbinlog to output invalid SQL from a valid binary log. The mysqli extension of PHP is used as an example, but it will probably work with any client that uses the binary server protocol.

It's possible, using mysqli, to successfully create a multi-statement trigger without changing DELIMITER. The resulting trigger functions properly and is written to the binary log. However, mysqlbinlog outputs the logged CREATE TRIGGER w/o changing the delimiter, thereby creating invalid SQL that can't be played back to mysql.

How to repeat:
Using these tables...

CREATE TABLE `A` (`ID` INT NOT NULL) ENGINE = MYISAM ;
CREATE TABLE `B` (`ID` INT NOT NULL) ENGINE = MYISAM ;
CREATE TABLE `C` (`ID` INT NOT NULL) ENGINE = MYISAM ;

and this PHP code...

$db = new mysqli(...);
$query = 
"CREATE TRIGGER bd_A BEFORE DELETE ON A FOR EACH ROW
BEGIN
	DELETE FROM B WHERE ID=OLD.ID;
	DELETE FROM C WHERE ID=OLD.ID;
END";
$db->query($query);

The trigger is created successfully and functions as intended. mysqlbinlog outputs the following:

SET TIMESTAMP=1151035271;
CREATE DEFINER=`root`@`localhost` TRIGGER bd_A BEFORE DELETE ON A FOR EACH ROW
                BEGIN
                        DELETE FROM B WHERE ID=OLD.ID;
                        DELETE FROM C WHERE ID=OLD.ID;
                END;

Which when piped into mysql like so

mysqlbinglog log | mysql

generates the error message

ERROR 1064 (42000) at line 122: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM B WHERE ID=OLD.ID' at line 3

Suggested fix:
Either mysqlbinlog should output delimiter statements as needed or mysql should be made more forgiving.

BTW, I tried the obvious workaround...

$query = 
"DELIMITER //
CREATE TRIGGER bd_A BEFORE DELETE ON A FOR EACH ROW
BEGIN
	DELETE FROM B WHERE ID=OLD.ID;
	DELETE FROM C WHERE ID=OLD.ID;
END;
//
DELIMITER ;";

However, this doesn't work and returns the syntax error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER // CREATE TRIGGER..."
[1 Jul 2006 5:30] Brandon Fosdick
After reading Bug #11312 (I forgot to check closed bugs before submitting) it seems that this bug is very similar, except for triggers instead of procedures/functions. The comments for #11312 indicate that it was fixed in 5.0.19, but apparently only for procedures and functions.

-Changed the synopsis accordingly
[1 Jul 2006 8:51] Valeriy Kravchuk
Thank you for a problem report. I think, it is a duplicate of Bug #11312, really. The fix to that bug is either "disappered" from both 5.022 and 5.0.23-BK, or not properly documented. I'll reopen that bug report instead.