Description:
When you create a trigger without specifying the BEGIN/END mysqldump will not dump correctly.
How to repeat:
Step 1
CREATE TRIGGER ins_trans BEFORE INSERT ON transactions FOR EACH ROW SET NEW.CREATED=NOW();
Step 2
mysqldump.exe -hlocalhost -uroot --hex-blob somedb >db_dump.sql
the dump will look like that
/*!40000 ALTER TABLE `transactions` ENABLE KEYS */;
/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE TRIGGER `ins_trans` BEFORE INSERT ON `transactions` FOR EACH ROW SET
NEW.CREATED=NOW() */;;
DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
Step 3
mysql.exe -hlocalhost -uroot -Dsomedb < db_dump.sql
Suggested fix:
the workaround here is to create the trigger like this :
DELIMITER |
CREATE TRIGGER ins_trans BEFORE INSERT ON transactions
FOR EACH ROW
BEGIN
SET NEW.CREATED=NOW();
END;
|
More information, Please refer to MySQL Bug 16878
留言列表
Banana-Pi, 樹梅派應用 
