MySQL Bugs: #18333: trigger lost on alter table

Bug #18333	trigger lost on alter table
Submitted:	18 Mar 2006 18:39	Modified:	20 Mar 2006 9:47
Reporter:	Christian Rabe	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.19	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
If you alter a table, triggers on that table get lost.
There are still the TRN and/or TRG files in the database directory.
BUT you can't drop them (Trigger does not exist) nor recreate them (Trigger already exists).

Only way to fix this is to delete the TRN/TRG files manualy.

How to repeat:
create a table
add one or more triggers
alter this table (change some columns or such)
try looking for your triggers

I was not able to reproduce this with current 5.0 code and the statements below,
could you please post the exact statements you were using?

-- test statements, no problem found
DROP TABLE IF EXISTS t1;

CREATE t1(i INT PRIMARY KEY) ENGINE=myisam;

CREATE TRIGGER tr1 BEFORE INSERT ON t1
  FOR EACH ROW BEGIN
  END;

ALTER TABLE t1 ADD COLUMN j INT;

SHOW TRIGGERS;

ok, with myisam its ok. try it with innodb and it fails.

DROP TABLE IF EXISTS t1;

CREATE table t1(i INT PRIMARY KEY) ENGINE=innodb;

CREATE TRIGGER tr1 BEFORE INSERT ON t1
  FOR EACH ROW BEGIN
  END;

ALTER TABLE t1 ADD COLUMN j INT;

SHOW TRIGGERS;

---------------------------------
RESULT
---------------------------------
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0,00 sec)

mysql>
mysql> CREATE table t1(i INT PRIMARY KEY) ENGINE=innodb;
Query OK, 0 rows affected (0,02 sec)

mysql>
mysql> CREATE TRIGGER tr1 BEFORE INSERT ON t1
    ->   FOR EACH ROW BEGIN
    ->   END;
Query OK, 0 rows affected (0,01 sec)

mysql>
mysql> ALTER TABLE t1 ADD COLUMN j INT;
Query OK, 0 rows affected (0,02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> SHOW TRIGGERS;
Empty set (0,00 sec)

mysql>
mysql> CREATE TRIGGER tr1 BEFORE INSERT ON t1
    ->   FOR EACH ROW BEGIN
    ->   END;
ERROR 1359 (HY000): Trigger already exists
mysql> DROP TRIGGER tr1;
ERROR 1360 (HY000): Trigger does not exist
mysql>

test case

Attachment: bug18333.test (application/octet-stream, text), 266 bytes.

expected test resut

Attachment: bug18333.result (application/octet-stream, text), 252 bytes.

works ok with myisam, csv, archive, blackhole,
fails with innodb, bdb, ndb

test case for innodb attached

Hi, Christian!

Thank you for spotting this!

Initial investigation showed that this bug has the same cause as bug #18153 "Optimize corrupts triggers, and triggers are lost" on which I have already started work. So I will mark this bug as duplicate. Please use bug #18153 to track progress of work on this problem.