MySQL Bugs: #34643: TRUNCATE crash if trigger and foreign key

Bug #34643	TRUNCATE crash if trigger and foreign key
Submitted:	18 Feb 2008 19:13	Modified:	2 Apr 2008 20:04
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.24-rc-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
I create an InnoDB table with a DELETE
trigger and a foreign key reference.
I insert a row.
I truncate the table.
Crash.

See also Bug#27935, which (I guess) doesn't crash.

How to repeat:
create table t3 (s1 int primary key) engine=innodb;
create trigger t3_bd before delete on t3 for each row insert into t4 values (0);
create table t5 (s1 int, foreign key (s1) references t3 (s1)) engine=innodb;
insert into t3 values (0);
truncate table t3;

Example Run:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.24-rc-debug Source distribution

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

mysql> use test
Database changed
mysql> create table t3 (s1 int primary key) engine=innodb;
Query OK, 0 rows affected (0.12 sec)

mysql> create trigger t3_bd before delete on t3 for each row insert into t4 values (0);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t5 (s1 int, foreign key (s1) references t3 (s1)) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t3 values (0);
Query OK, 1 row affected (0.02 sec)

mysql> truncate table t3;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for a bug report. Verified with -debug binaries on Windows XP also. Stack trace is:

>	mysqld-debug.exe!_NMSG_WRITE(int rterrnum=10)  Line 195	C
 	mysqld-debug.exe!abort()  Line 44 + 0x7 bytes	C
 	mysqld-debug.exe!_assert(const char * expr=0x00ad5d28, const char * filename=0x00ad5d14, unsigned int lineno=1959)  Line 306	C
 	mysqld-debug.exe!Table_triggers_list::process_triggers(THD * thd=0x01fcdbe8, trg_event_type event=TRG_EVENT_DELETE, trg_action_time_type time_type=TRG_ACTION_BEFORE, int old_row_is_record1=0)  Line 1959 + 0x32 bytes	C++
 	mysqld-debug.exe!mysql_delete(THD * thd=0x01fcdbe8, TABLE_LIST * table_list=0x02018268, Item * conds=0x00000000, st_sql_list * order=0x00000000, unsigned __int64 limit=18446744073709551615, unsigned __int64 options=0, int reset_auto_increment=1)  Line 271 + 0x1e bytes	C++
 	mysqld-debug.exe!mysql_truncate(THD * thd=0x01fcdbe8, TABLE_LIST * table_list=0x02018268, int dont_send_ok=0)  Line 1056 + 0x1b bytes	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x01fcdbe8)  Line 2986 + 0xf bytes	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x01fcdbe8, const char * inBuf=0x020181c8, unsigned int length=17, const char * * found_semicolon=0x0577fd04)  Line 5565 + 0x9 bytes	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x01fcdbe8, char * packet=0x02010119, unsigned int packet_length=17)  Line 1090 + 0x1e bytes	C++
 	mysqld-debug.exe!do_command(THD * thd=0x01fcdbe8)  Line 764 + 0x34 bytes	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x01fcdbe8)  Line 1120 + 0x9 bytes	C++
 	mysqld-debug.exe!pthread_start(void * param=0x01fcfd18)  Line 85 + 0x9 bytes	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x01fcd828)  Line 196 + 0xd bytes	C
 	kernel32.dll!7c80b683() 	
 	[Frames below may be incorrect and/or missing, no symbols loaded for kernel32.dll]

Assertion failed: trigger_table->pos_in_table_list->trg_event_map & static_cast<uint>(1 << static_cast<int>(event)), file .\sql_trigger.cc, line 1959

based on e/r values setting target to 5.1+

Bug#27935 was marked as a duplicate of this bug.

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/commits/43833

ChangeSet@1.2596, 2008-03-12 16:13:33+03:00, anozdrin@quad. +3 -0
  A fix for Bug#34643: TRUNCATE crash if trigger and foreign key.
    
  In cases when TRUNCATE was executed by invoking mysql_delete() rather
  than by table recreation (for example, when TRUNCATE was issued on
  InnoDB table with is referenced by foreign key) triggers were invoked.
  In debug builds this also led to crash because of an assertion, which
  assumes that some preliminary actions take place before trigger 
  invocation, which doesn't happen in case of TRUNCATE.
  
  The fix is not to execute triggers in mysql_delete() when this
  function is used by TRUNCATE.

Pushed into 5.0-rt.

Pushed into 5.1.24-rc

Pushed into 5.0.60

Pushed into 6.0.5-alpha

Documented bugfix in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        Executing a TRUNCATE statement on a table having both a foreign key
        reference and a DELETE trigger crashed the server.