Bug #15696 DROP TRIGGER corrupts the index file
Submitted: 13 Dec 2005 1:07 Modified: 19 Dec 2005 14:06
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15 OS:Microsoft Windows (WinXP Pro)
Assigned to: Aleksey Kishkin CPU Architecture:Any

[13 Dec 2005 1:07] [ name withheld ]
Description:
I don't really know what this bug is tied to, but it seems that dropping a trigger may result in a corruption of the index file may lead to an index corruption. After running the test case, the only way to repair the table is to use the USE_FRM option of REPAIR TABLE. Since I couldn't quite put my finger of what was causing this bug, I included the shortest reproducible test case I could produce.

And if that matters, here is a snip of my.cnf:
delay_key_write=ALL
myisam_data_pointer_size=4

On my machine, the code below produces this

+------------------+-------+----------+---------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                          |
+------------------+-------+----------+---------------------------------------------------+
| dev_default.test | check | error    | Size of indexfile is: 1024        Should be: 2048 |
| dev_default.test | check | error    | Corrupt                                           |
+------------------+-------+----------+---------------------------------------------------+

How to repeat:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
  id int(11) NOT NULL,
  PRIMARY KEY  (id)
);
DELIMITER woot
CREATE TRIGGER test_bu BEFORE UPDATE ON test
FOR EACH ROW BEGIN
END
woot
DELIMITER ;
INSERT INTO test (id) VALUES (1);
DROP TRIGGER test_bu;
CHECK TABLE test;
[13 Dec 2005 1:09] [ name withheld ]
Changed severity to S2 as per guidelines. REPAIR TABLE does repair the table with no data loss.
[14 Dec 2005 10:47] Aleksey Kishkin
Wasn't able to reproduce it with default my.cnf and with delay_key_write=ALL
myisam_data_pointer_size=4 also:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CREATE TABLE test (
    ->   id int(11) NOT NULL,
    ->   PRIMARY KEY  (id)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> DELIMITER woot
mysql> CREATE TRIGGER test_bu BEFORE UPDATE ON test
    -> FOR EACH ROW BEGIN
    -> END
    -> woot
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> INSERT INTO test (id) VALUES (1);
Query OK, 1 row affected (0.09 sec)

mysql> DROP TRIGGER test_bu;
Query OK, 0 rows affected (0.02 sec)

mysql> CHECK TABLE test;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (0.03 sec)

mysql>
[14 Dec 2005 10:50] Aleksey Kishkin
If you have any ideas how to reproduce it, please let us know.
[15 Dec 2005 23:25] [ name withheld ]
I have replaced my config file with my-small.cnf and reexecuted the test case successfully. It appears that index corruption only occurs when delay_key_write is activated (either "globally" via the .cnf or as an option during table creation) and, of course, if the engine is MyISAM.

I have investigated further and found that if tables are flushed ("FLUSH TABLES;") before the trigger is dropped then no corruption occurs. HTH.
[19 Dec 2005 14:06] Aleksey Kishkin
but I tested it with delay_key_write. Still cannot reproduce it.
[4 Apr 2006 18:45] Shane Bester
repeated this on 5.0.15, but couldn't repeat it in 5.0.16 and higher.  I'm not sure  by looking at the changelogs when this was fixed.
[4 Apr 2006 21:10] Dmitry Lenev
Hi, Shane!

Probably, this problem was solved by the fix for bug #12739 "Deadlock in multithread env during creating/droping trigger on innodb table", which changed the way in which tables are locked before during CREATE/DROP TRIGGER operations.