Bug #18816 ALTER/OPTIMIZE TABLE drops triggers on InnoDB
Submitted: 5 Apr 2006 16:41 Modified: 5 Apr 2006 23:11
Reporter: Kolbe Kegel Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.19 OS:Linux (Linux)
Assigned to:

[5 Apr 2006 16:41] Kolbe Kegel
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.
[6 Apr 2006 20:20] Konstantin Osipov
Thank you for your bug report. This bug is a duplicate of Bug#18153 "REPAIR/OPTIMIZE/ALTER on transactional tables corrupt triggers", which is fixed in 5.0.20.