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..."
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..."