そもそもこの構文破壊されたCREATE TRIGGER文は、DELIMITERの変更とバージョン指定コメント構文が揃わないと起こらないので、おそらくmysqldumpで引っこ抜いたトリガー定義を間違っていじったりコピペし間違えた時くらいしか起こらない気がする。
が、一度この状態になるとそれ以降のmysqldumpで発火する。
How to repeat
- MySQL 5.7とそれ以前に対して以下を実行すると、CREATE TRIGGERは成功するけどSyntax Errorが返ってくる。
CREATE DATABASE d1;
CREATE TABLE d1.t1 (num INT);
DELIMITER ;;
/*!50003 CREATE TRIGGER d1.test BEFORE INSERT ON d1.t1 FOR EACH ROW INSERT INTO d1.t1 VALUES (1); */;;
DELIMITER ;
- こんな風に。
mysql57 5> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
mysql57 5> CREATE TABLE d1.t1 (num INT);
Query OK, 0 rows affected (0.01 sec)
mysql57 5> DELIMITER ;;
mysql57 5> /*!50003 CREATE TRIGGER d1.test BEFORE INSERT ON d1.t1 FOR EACH ROW INSERT INTO d1.t1 VALUES (1); */;;
Query OK, 0 rows affected (0.00 sec)
ERROR 1064 (42000): 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
mysql57 5> DELIMITER ;
バージョン指定コメント構文をパースする時にDELIMITERの指定が効いてなくて、コメントの中の ; でクエリが終端したと思って実行、その後残った */;; がDELIMITERが効いた状態で解釈されてシンタックスエラーになってるんじゃないかと思う。
ジェネラルログで見ると割と微妙で、コメントの開きは記録されてるけど閉じが記録されてない（5.7の時代ではデフォルトFALSEだった —comments はちゃんと有効にしてある）
2026-04-02T06:07:29.232360-00:00 6 Connect root@localhost on using Socket
2026-04-02T06:07:29.232486-00:00 6 Query select @@version_comment limit 1
2026-04-02T06:07:31.155143-00:00 6 Query DROP DATABASE d1
2026-04-02T06:07:31.158391-00:00 6 Query SELECT DATABASE()
2026-04-02T06:07:33.678277-00:00 6 Query CREATE DATABASE d1
2026-04-02T06:07:33.678793-00:00 6 Query CREATE TABLE d1.t1 (num INT)
2026-04-02T06:07:33.686280-00:00 6 Query /*!50003 CREATE TRIGGER d1.test BEFORE INSERT ON d1.t1 FOR EACH ROW INSERT INTO d1.t1 VALUES (1);
で、この状態で作ったデータベースをmysqldumpすると(ワーニングはこの件に直接関係ないので無視していい)
$ mysqldump57 d1 --triggers > d1.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.
↓のようにまたこれが再生産されるので
$ grep TRIGGER d1.sql
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER d1.test BEFORE INSERT ON d1.t1 FOR EACH ROW INSERT INTO d1.t1 VALUES (1); */;;
このダンプを食わせようとするとエラって止まる。
mysql57 11> DROP DATABASE d1;
Query OK, 1 row affected (0.00 sec)
mysql57 13> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
$ mysql57 -v d1 < d1.sql
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------
..
--------------
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER d1.test BEFORE INSERT ON d1.t1 FOR EACH ROW INSERT INTO d1.t1 VALUES (1); */
--------------
ERROR 1064 (42000) at line 49: 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
そのくせちゃんとトリガーはできているという曲者。。
mysql57 15> SHOW TRIGGERS\G
*************************** 1. row ***************************
Trigger: test
Event: INSERT
Table: t1
Statement: INSERT INTO d1.t1 VALUES (1);
Timing: BEFORE
Created: 2026-04-02 06:14:44.43
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
mysql57 15> SHOW CREATE TRIGGER d1.test\G
*************************** 1. row ***************************
Trigger: test
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER d1.test BEFORE INSERT ON d1.t1 FOR EACH ROW INSERT INTO d1.t1 VALUES (1);
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8mb4_general_ci
Created: 2026-04-02 06:14:44.43
1 row in set (0.00 sec)
この時点で SQL Original Statement にセミコロンが含まれてしまっている。
シングルステートメントで正しく作った場合はセミコロンは含まれない。
mysql57 15> CREATE TRIGGER d1.test2 BEFORE UPDATE ON d1.t1 FOR EACH ROW DELETE FROM d1.t1 WHERE num = NEW.num;
Query OK, 0 rows affected (0.11 sec)
mysql57 15> DELIMITER ;;
mysql57 15> CREATE TRIGGER d1.test3 BEFORE UPDATE ON d1.t1 FOR EACH ROW DELETE FROM d1.t1 WHERE num = NEW.num;;
Query OK, 0 rows affected (0.00 sec)
mysql57 15> DELIMITER ;
mysql57 15> SHOW TRIGGERS\G
*************************** 1. row ***************************
Trigger: test
Event: INSERT
Table: t1
Statement: INSERT INTO d1.t1 VALUES (1);
Timing: BEFORE
Created: 2026-04-02 06:14:44.43
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8mb4_general_ci
*************************** 2. row ***************************
Trigger: test2
Event: UPDATE
Table: t1
Statement: DELETE FROM d1.t1 WHERE num = NEW.num
Timing: BEFORE
Created: 2026-04-02 06:16:52.01
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8mb4_general_ci
*************************** 3. row ***************************
Trigger: test3
Event: UPDATE
Table: t1
Statement: DELETE FROM d1.t1 WHERE num = NEW.num
Timing: BEFORE
Created: 2026-04-02 06:17:29.12
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8mb4_general_ci
3 rows in set (0.00 sec)
そもそもこの ; */;; が現れるのがmysqldump由来だろうから最初に間違わなければいいんだけれど、当たってしまったので（そして5.7はもうEOLでバグレポートしても相手にしてもらえないので）供養のために記録しておく。
エラるのが確実にこのCREATE TRIGGER由来だと断言できるなら mysql -f で無視させるもよし、多少自由がが効くなら取ったmysqldumpファイルをエディタで編集して ; */;; の ; の方を削ってやるもよし。
ちなみに8.0とそれ以降だと、綺麗にシンタックスエラーになってトリガーがそもそも出来上がらないのでリストアできないmysqldumpファイルも出来上がらない。
【202/04/02 16:09】
対象のトリガーがあるかどうかを調べるSQLはたぶんこれでいいはず（「ステートメントがセミコロンで終わっているもの」、実際見つかった）
SELECT trigger_schema, trigger_name, action_statement FROM information_schema.triggers WHERE action_statement LIKE '%;';
対象の
0 件のコメント :
コメントを投稿