Bug #77856 CREATE TRIGGER incorrect delimiter parsing inside of comments
Submitted: 28 Jul 2015 6:33 Modified: 28 Jul 2015 7:57
Reporter: Tyler Mitchell Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.6.26, 5.7.9, 5.5.46 OS:Any
Assigned to: CPU Architecture:Any
Tags: trigger semicolon parsing mysqldump

[28 Jul 2015 6:33] Tyler Mitchell
Description:
When a trigger that is created inside of a comment block terminates with a semicolon, the semicolon will be included in the trigger's definition Statement:

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: tg1
               Event: INSERT
               Table: t1
           Statement: set NEW.a=CURDATE();
              Timing: BEFORE
             Created: NULL
            sql_mode: NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

mysqldump will correctly dump this trigger with the semicolon included which will cause an error on a subsequent import attempt.

How to repeat:
use test
CREATE TABLE t1 (a DATE);
DELIMITER ;;
/*!50003 CREATE TRIGGER `tg1` BEFORE INSERT ON `t1` FOR EACH ROW set NEW.a=CURDATE();*/
;;

This will generate an error on execution but will create the trigger successfully. It will cause subsequent mysqldump imports to fail:

# mysql < trigger.sql
ERROR 1064 (42000) at line 4: 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 '*/' at line 1

# mysqldump test | mysql test
ERROR 1064 (42000) at line 47: 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 '*/' at line 1

Suggested fix:
Discard terminating semicolons in trigger definitions inside of comment blocks.
[28 Jul 2015 7:57] Umesh Shastry
Hello Tyler Mitchell,

Thank you for the report and test case.
Observed this with 5.6.26, and 5.7.9 builds.

Thanks,
Umesh
[3 Mar 2016 19:23] cindy .
This can be cleaned after the fact with:  sed -i "s|END; \*\/|END \*\/|g"  dumpfile.sql