Description:
Triggers on an InnoDB table are dropped when the table is ALTERed. Because OPTIMIZE TABLE maps to ALTER TABLE for InnoDB, OPTIMIZE TABLE also causes triggers to be dropped.
It seems that only the .TRG file is deleted, as all of the .TRN files still exist, which means that the triggers can't be re-created without manually removing the affected .TRN files from the filesystem.
How to repeat:
CREATE TABLE t1 (id INT) ENGINE=InnoDB;
CREATE TRIGGER t1_trig1 AFTER INSERT ON t1 FOR EACH ROW BEGIN END;
SHOW TRIGGERS;
ALTER TABLE t1 ENGINE=InnoDB;
SHOW TRIGGERS;
mysql 5.0.19-max (root) [test]> CREATE TABLE t1 (id INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql 5.0.19-max (root) [test]> CREATE TRIGGER t1_trig1 AFTER INSERT ON t1 FOR EACH ROW BEGIN END;
Query OK, 0 rows affected (0.00 sec)
mysql 5.0.19-max (root) [test]> SHOW TRIGGERS;
+----------+--------+-------+-----------+--------+---------+----------+----------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer |
+----------+--------+-------+-----------+--------+---------+----------+----------------+
| t1_trig1 | INSERT | t1 | BEGIN END | AFTER | | | root@localhost |
+----------+--------+-------+-----------+--------+---------+----------+----------------+
1 row in set (0.00 sec)
mysql 5.0.19-max (root) [test]> ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.0.19-max (root) [test]> SHOW TRIGGERS;
Empty set (0.00 sec)
Suggested fix:
Triggers should not be dropped when ALTERing a table.
