MySQL Bugs: #5894: Triggers with altered tables cause corrupt databases

Bug #5894	Triggers with altered tables cause corrupt databases
Submitted:	5 Oct 2004 1:18	Modified:	10 Jun 2005 19:14
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
If I have a trigger that depends on a table t, and I alter  t, and then execute a statement 
which activates the trigger, I get a crash. But more interestingly: if I restart the server after 
the crash, and then attempt to "USE" the database, I get another crash. 
 

How to repeat:
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.29 sec) 
 
mysql> create trigger t before insert on t for each row set new.s1 = 5; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> alter table t change s1 s2 char; 
Query OK, 0 rows affected (0.52 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
mysql> insert into t values (0); 
ERROR 2013 (HY000): Lost connection to MySQL server during query

Verified with  5.0.2-alpha-debug-log.
Back trace:

(gdb) bt
#0  0x0842169f in my_vsnprintf (to=0x87944ad "  Warnings: 0set new.s1 = 5", n=512,
    fmt=0x8682016 "Unknown column '%-.64s' in '%-.64s'", ap=0xbe5fcee4) at my_vsnprintf.c:76
#1  0x081573e7 in net_printf (thd=0x879c9a8, errcode=1054) at protocol.cc:225
#2  0x0818ee31 in yyparse (yythd=0x879c9a8) at sql_yacc.yy:7104
#3  0x08266a77 in Table_triggers_list::check_n_load (thd=0x879c9a8, db=0x876bcb0 "test",
    table_name=0x87a9888 "t", table=0x87af818) at sql_trigger.cc:379
#4  0x08194009 in open_unireg_entry (thd=0x879c9a8, entry=0x87af818, db=0x876bcb0 "test",
    name=0x87a9888 "t", alias=0x87a98a8 "t", table_desc=0x87a98b0, mem_root=0xbe5ff0ec)
    at sql_base.cc:1531
#5  0x08192db5 in open_table (thd=0x879c9a8, table_list=0x87a98b0, mem_root=0xbe5ff0ec,
    refresh=0xbe5ff0db) at sql_base.cc:1009
#6  0x081942b8 in open_tables (thd=0x879c9a8, start=0x87a98b0, counter=0xbe5ff138)
    at sql_base.cc:1623
#7  0x081947ca in open_and_lock_tables (thd=0x879c9a8, tables=0x87a98b0) at sql_base.cc:1831
#8  0x081b6b4d in mysql_insert (thd=0x879c9a8, table_list=0x87a98b0, fields=@0x879cde0,
    values_list=@0x879ce00, update_fields=@0x879cbfc, update_values=@0x879cdf0, duplic=DUP_ERROR)
    at sql_insert.cc:201
#9  0x0817380e in mysql_execute_command (thd=0x879c9a8) at sql_parse.cc:2782
#10 0x08177759 in mysql_parse (thd=0x879c9a8, inBuf=0x87a9850 "insert into t values (0)",
    length=24) at sql_parse.cc:4551
#11 0x08170842 in dispatch_command (command=COM_QUERY, thd=0x879c9a8,
    packet=0x8794489 "insert into Unknown column 's1' in '  Warnings: 0set new.s1 = 5",
    packet_length=25) at sql_parse.cc:1517
#12 0x08170127 in do_command (thd=0x879c9a8) at sql_parse.cc:1297
#13 0x0816f68c in handle_one_connection (arg=0x879c9a8) at sql_parse.cc:1033
#14 0x4017414b in pthread_start_thread () from /lib/libpthread.so.0
#15 0x401741df in pthread_start_thread_event () from /lib/libpthread.so.0
#16 0x402df50a in clone () from /lib/libc.so.6

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/25384

Hi, Peter!

Thanks to recent (and non-recent) bugfixes this bug is no longer repetable in current tree.
Now MySQL is able to open table with such "broken" trigger without crash. It successfully executes operations which does not involve "insert" triggers on it. INSERTs to this table (or other statements that invoke "before insert" triggers, e.g. LOAD DATA) fail with proper error message. So I am closing this bug as fixed in 5.0.7.

For documentation purproses:
- Crash was fixed by patch for bug #5888 "Triggers with nonexistent columns cause packets out of order".
- Fix for bug #5860 "Multi-table UPDATE does not activate update triggers" cleaned up handling of errors in triggers.
- ChangeSet 1.1928 05/05/30 18:55:56 dlenev@brandersnatch.localdomain +3 -0
  Added test for bug #5894 "Triggers with altered tables cause corrupt databases"
  Fixed last glitch related to this bug (trigger failure which happened during execution of of multi-delete stalled connection).

Noted in 5.0.7 changelog.