InnoDB FTSがメモリを食う件その2 tcmallocでいくらかマシになりそう

日々の覚書: InnoDB FULLTEXT KEYがメモリを食う件(未解決) の続き。

何がメモリを使ってるのかを performance_schema.memory_summary_global_by_event_name テーブルで(できないだろうなと思いながら)観測してみる。

mysql80> RESTART;
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_read_write --table_size=1000000 --time=600 --report-interval=1 cleanup
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_read_write --table_size=1000000 --time=600 --report-interval=1 prepare
$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3591371
3591371 mysqld   498060

↑まずはフルテキストインデックスなしの状態でベンチを流した後の memory_summary_global_by_event_name

mysql80 74> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool      |           1 |          0 |                  68620288 |                        0 |              0 |                  1 |               1 |                        0 |                     68620288 |                  68620288 | 65.44 MiB                                                                                         |
| memory/innodb/ut0link_buf       |           2 |          0 |                  25165888 |                        0 |              0 |                  2 |               2 |                        0 |                     25165888 |                  25165888 | 89.44 MiB                                                                                         |
| memory/innodb/log_buffer_memory |           1 |          0 |                  16778224 |                        0 |              0 |                  1 |               1 |                        0 |                     16778224 |                  16778224 | 105.44 MiB                                                                                        |
| memory/mysys/KEY_CACHE          |           3 |          0 |                   8390864 |                        0 |              0 |                  3 |               3 |                        0 |                      8390864 |                   8390864 | 113.44 MiB                                                                                        |
| memory/sql/TABLE                |        6028 |       3948 |                  30576081 |                 22566230 |              0 |               2080 |            2082 |                        0 |                      8009851 |                   8041086 | 121.08 MiB                                                                                        |
| memory/innodb/sync0arr          |           3 |          0 |                   7373032 |                        0 |              0 |                  3 |               3 |                        0 |                      7373032 |                   7373032 | 128.11 MiB                                                                                        |
| memory/sql/log_sink_pfs         |           3 |          2 |                   5292128 |                    49216 |              0 |                  1 |               2 |                        0 |                      5242912 |                   5259328 | 133.11 MiB                                                                                        |
| memory/innodb/lock0lock         |          33 |          0 |                   5086600 |                        0 |              0 |                 33 |              33 |                        0 |                      5086600 |                   5086600 | 137.97 MiB                                                                                        |
| memory/innodb/ut0pool           |           2 |          0 |                   4194488 |                        0 |              0 |                  2 |               2 |                        0 |                      4194488 |                   4194488 | 141.97 MiB                                                                                        |
| memory/temptable/physical_ram   |          73 |         69 |                  76548384 |                 72353952 |              0 |                  4 |               4 |                        0 |                      4194432 |                   4194432 | 145.97 MiB                                                                                        |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql80 74> SELECT SUM(current_number_of_bytes_used) FROM performance_schema.memory_summary_global_by_event_name;
+-----------------------------------+
| SUM(current_number_of_bytes_used) |
+-----------------------------------+
|                         410046116 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql80 74> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
..
| performance_schema | performance_schema.memory                                   | 242396808 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)

トータルではまあまあ合ってそうな気がする( memory/temptable/physical_ram はたぶん監視に使ってる pmm-agent だな…失敗したかも )

次にALTER TABLEでFULLTEXT INDEXを足す。
この時点でメモリ使用量が増えている（これ自体は不思議ではない）けど、p_sには計上されず…（これがぐぬぬ）

mysql80 90> ALTER TABLE sbtest.sbtest1 ADD FULLTEXT KEY (c);
Query OK, 0 rows affected, 1 warning (1 min 38.24 sec)
Records: 0  Duplicates: 0  Warnings: 1

$ ps -Ao pid,fname,rss | grep 3591371
3591371 mysqld   659868

mysql80 107> SELECT SUM(current_number_of_bytes_used) FROM performance_schema.memory_summary_global_by_event_name;
+-----------------------------------+
| SUM(current_number_of_bytes_used) |
+-----------------------------------+
|                         408419366 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql80 107> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool      |           1 |          0 |                  68620288 |                        0 |              0 |                  1 |               1 |                        0 |                     68620288 |                  68620288 | 65.44 MiB                                                                                         |
| memory/innodb/ut0link_buf       |           2 |          0 |                  25165888 |                        0 |              0 |                  2 |               2 |                        0 |                     25165888 |                  25165888 | 89.44 MiB                                                                                         |
| memory/innodb/log_buffer_memory |           1 |          0 |                  16778224 |                        0 |              0 |                  1 |               1 |                        0 |                     16778224 |                  16778224 | 105.44 MiB                                                                                        |
| memory/mysys/KEY_CACHE          |           3 |          0 |                   8390864 |                        0 |              0 |                  3 |               3 |                        0 |                      8390864 |                   8390864 | 113.44 MiB                                                                                        |
| memory/sql/TABLE                |        7177 |       5065 |                  37444758 |                 29329243 |              0 |               2112 |            2113 |                        0 |                      8115515 |                   8130336 | 121.18 MiB                                                                                        |
| memory/innodb/sync0arr          |           3 |          0 |                   7373032 |                        0 |              0 |                  3 |               3 |                        0 |                      7373032 |                   7373032 | 128.22 MiB                                                                                        |
| memory/sql/log_sink_pfs         |           3 |          2 |                   5292128 |                    49216 |              0 |                  1 |               2 |                        0 |                      5242912 |                   5259328 | 133.22 MiB                                                                                        |
| memory/innodb/lock0lock         |          33 |          0 |                   5086600 |                        0 |              0 |                 33 |              33 |                        0 |                      5086600 |                   5086600 | 138.07 MiB                                                                                        |
| memory/innodb/ut0pool           |           2 |          0 |                   4194488 |                        0 |              0 |                  2 |               2 |                        0 |                      4194488 |                   4194488 | 142.07 MiB                                                                                        |
| memory/temptable/physical_ram   |          96 |         92 |                 100666368 |                 96471936 |              0 |                  4 |               4 |                        0 |                      4194432 |                   4194432 | 146.07 MiB                                                                                        |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)

一度リスタートして再度600秒ベンチ。

mysql80 107> RESTART;
Query OK, 0 rows affected (0.00 sec)

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3592261
3592261 mysqld   567808

mysql80 48> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool      |           1 |          0 |                  68620288 |                        0 |              0 |                  1 |               1 |                        0 |                     68620288 |                  68620288 | 65.44 MiB                                                                                         |
| memory/innodb/ut0link_buf       |           2 |          0 |                  25165888 |                        0 |              0 |                  2 |               2 |                        0 |                     25165888 |                  25165888 | 89.44 MiB                                                                                         |
| memory/innodb/log_buffer_memory |           1 |          0 |                  16778224 |                        0 |              0 |                  1 |               1 |                        0 |                     16778224 |                  16778224 | 105.44 MiB                                                                                        |
| memory/mysys/KEY_CACHE          |           3 |          0 |                   8390864 |                        0 |              0 |                  3 |               3 |                        0 |                      8390864 |                   8390864 | 113.44 MiB                                                                                        |
| memory/sql/TABLE                |        4204 |       2239 |                  19897956 |                 12217500 |              0 |               1965 |             895 |                        0 |                      7680456 |                   7680456 | 120.77 MiB                                                                                        |
| memory/innodb/sync0arr          |           3 |          0 |                   7373032 |                        0 |              0 |                  3 |               3 |                        0 |                      7373032 |                   7373032 | 127.80 MiB                                                                                        |
| memory/sql/log_sink_pfs         |           3 |          2 |                   5292128 |                    49216 |              0 |                  1 |               2 |                        0 |                      5242912 |                   5259328 | 132.80 MiB                                                                                        |
| memory/innodb/lock0lock         |          33 |          0 |                   5086600 |                        0 |              0 |                 33 |              33 |                        0 |                      5086600 |                   5086600 | 137.65 MiB                                                                                        |
| memory/innodb/ut0pool           |           2 |          0 |                   4194488 |                        0 |              0 |                  2 |               2 |                        0 |                      4194488 |                   4194488 | 141.65 MiB                                                                                        |
| memory/temptable/physical_ram   |          37 |         33 |                  38798496 |                 34604064 |              0 |                  4 |               4 |                        0 |                      4194432 |                   4194432 | 145.65 MiB                                                                                        |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.02 sec)

mysql80 48> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

..
| performance_schema | performance_schema.memory                                   | 240987688 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)

やっぱりperformance_schemaから調べるのは難しそう。
取り敢えずtcmalloc使ったら変わるかどうかの切り分けだけやっておく。

$ grep malloc /etc/my.cnf
malloc-lib=/usr/lib64/libtcmalloc.so

$ lsof -p 3639083 | grep mall
mysqld  3639083 yoku0825  mem    REG              252,0      334544  68185695 /usr/lib64/libtcmalloc.so.4.5.3

$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_common --table_size=1000000 cleanup

$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_common --table_size=1000000 prepare

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3639083
3639083 mysqld   518920

フルテキストインデックス無しのケースは大して変わらず（スコアは気持ち上がったけど複数回測ってないので確かではない）

mysql80 165> ALTER TABLE sbtest.sbtest1 ADD FULLTEXT KEY (c);
Query OK, 0 rows affected, 1 warning (1 min 37.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

$ ps -Ao pid,fname,rss | grep 3639083
3639083 mysqld   704996

mysql80 184> RESTART;
Query OK, 0 rows affected (0.00 sec)

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3640646
3640646 mysqld   588272

おー、だいぶ穏やかになった。tcmallocに変えて改善するということはメモリのフラグメンテーション関連かな…。

​

MySQL 5.7とそれ以前で変な作り方をしたトリガーがmysqldumpで流し込んだ時にSyntax Errorでエラーになる

そもそもこの構文破壊された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 '%;';

対象の​

たｔ