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
[11 Apr 19:25] Peter Mc Aulay
Still present in 5.7.22-enterprise-commercial-advanced:

mysql> show create trigger ss_insert_phpunit_appraisal \G
*************************** 1. row ***************************
               Trigger: ss_insert_phpunit_appraisal
              sql_mode: ANSI_QUOTES,STRICT_ALL_TABLES
SQL Original Statement: CREATE DEFINER="apache"@"xxxx.xxx" TRIGGER ss_insert_phpunit_appraisal AFTER INSERT ON phpunit_appraisal FOR EACH ROW
                       UPDATE ss_tables_phpunit_ SET modifications = 1 WHERE tablename = 'phpunit_appraisal' AND modifications = 0
;
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_unicode_ci
               Created: 2018-11-07 16:07:50.80
1 row in set (0.00 sec)

When dumped, the result is this:

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER="apache"@"xxxx.xxx"*/ /*!50003 TRIGGER ss_insert_phpunit_appraisal AFTER INSERT ON phpunit_appraisal FOR EACH ROW
                       UPDATE ss_tables_phpunit_ SET modifications = 1 WHERE tablename = 'phpunit_appraisal' AND modifications = 0
; */;;

Which is invalid and needs to be fed to sed 's/^\;//g' before it will load.