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


    全站熱搜

    Frank 發表在 痞客邦 留言(0) 人氣()