MySQL Bugs: #15696: DROP TRIGGER corrupts the index file

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:	Windows (WinXP Pro)
Assigned to:	Aleksey Kishkin	CPU Architecture:	Any

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;

Changed severity to S2 as per guidelines. REPAIR TABLE does repair the table with no data loss.

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>

If you have any ideas how to reproduce it, please let us know.

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.

but I tested it with delay_key_write. Still cannot reproduce it.

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.

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.