| 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: | |
| 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 7:57]
MySQL Verification Team
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 2019 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.
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.