MySQL Bugs: #13627: MySQL crashes when invoking DROP TRIGGER in a trigger

Bug #13627	MySQL crashes when invoking DROP TRIGGER in a trigger
Submitted:	30 Sep 2005 0:28	Modified:	20 Nov 2005 3:07
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.14-rc	OS:	Linux (linux)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
Hi,

MySQL could crash when a trigger invoke the DROP of another trigger.
BTW the error displayed in my testcase seems to be weird ?

Regards,
  Jocelyn

How to repeat:
delimiter ;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
delimiter //
CREATE TRIGGER test BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END //
CREATE TRIGGER test1 BEFORE INSERT ON t2 FOR EACH ROW BEGIN DROP TRIGGER test; END //
delimiter ;
INSERT INTO t1 VALUES (1);//
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
INSERT INTO t1 VALUES (1);//
ERROR 2013 (HY000): Lost connection to MySQL server during query

(the crash could occur after several try of this command - I assume there's a memory corruption somewhere)

Stack trace :

0x814d7cc handle_segfault + 356
0x4004f825 _end + 934398613
0x81abf01 reinit_stmt_before_use(THD*, st_lex*) + 369
0x823ac9f sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) + 151
0x823ae2c sp_instr_stmt::execute(THD*, unsigned int*) + 168
0x8238a4e sp_head::execute(THD*) + 634
0x823902b sp_head::execute_function(THD*, Item**, unsigned int, Item**) + 491
0x8242f6e Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool) + 98
0x8184296 fill_record_n_invoke_before_triggers(THD*, Field**, List<Item>&, bool, Table_triggers_list*, trg_event_type) + 62
0x81a7609 mysql_insert(THD*, st_table_list*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool) + 2981
0x8162e51 mysql_execute_command(THD*) + 8461
0x8167c22 mysql_parse(THD*, char*, unsigned int) + 294
0x815f82e dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1186
0x815f351 do_command(THD*) + 129
0x815e84e handle_one_connection + 550
0x40049e51 _end + 934375617
0x401d06ea _end + 935975258

thd->query at 0x86c5028 = DROP TRIGGER test1

Suggested fix:
MySQL should not crash :)

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

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

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

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t2 (a int);
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter //
mysql> CREATE TRIGGER test BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER test1 BEFORE INSERT ON t2 FOR EACH ROW BEGIN DROP TRIGGER test;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> INSERT INTO t1 VALUES (1);
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
mysql> INSERT INTO t1 VALUES (1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31143

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32181

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32266

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32278

Thank you for bugreport.
The bugfix is pushed to 5.0.17

Noted in 5.0.17 changelog.