MySQL Bugs: #43448: Server crashes on multi table delete with Innodb

Bug #43448	Server crashes on multi table delete with Innodb
Submitted:	6 Mar 2009 10:11	Modified:	26 Feb 2010 9:57
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0.11	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	index_condition_pushdown, optimizer_switch

Description:
As mentioned in How to repeat section.

How to repeat:
create table mt1(id1 int not null auto_increment primary key, t char(12)) engine=innodb;
create table mt2(id2 int not null, t char(12)) engine=innodb;
create table mt3(id3 int not null, t char(12), index(id3)) engine=innodb;
disable_query_log;
let $1 = 100;
while ($1)
{
let $2 = 5;
eval insert into mt1(t) values ('$1');
while ($2)
{
eval insert into mt2(id2,t) values ($1,'$2');
let $3 = 10;
while ($3)
{
eval insert into mt3(id3,t) values ($1,'$2');
dec $3;
}
dec $2;
}
dec $1;
}
enable_query_log;
select count(*) from mt1 where id1 > 95;
select count(*) from mt2 where id2 > 95;
select count(*) from mt3 where id3 > 95;
update mt1,mt2,mt3 set mt1.t="aaa", mt2.t="bbb", mt3.t="cc" where  mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3  and mt1.id1 > 90;
select count(*) from mt1 where t = "aaa";
select count(*) from mt1 where id1 > 90;
select count(*) from mt2 where t = "bbb";
select count(*) from mt2 where id2 > 90;
select count(*) from mt3 where t = "cc";
select count(*) from mt3 where id3 > 90;
delete mt1.*, mt2.*, mt3.*  from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3  and mt1.id1 > 95;
check table mt1, mt2, mt3;
select count(*) from mt1 where id1 > 95;
select count(*) from mt2 where id2 > 95;
select count(*) from mt3 where id3 > 95;
delete mt1, mt2, mt3  from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3  and mt1.id1 > 5;
--->crashes at the above command as below

Thread 1 (process 24662):
#0  0xb8002430 in __kernel_vsyscall ()
#1  0xb7fa8158 in pthread_kill () from /lib/tls/i686/cmov/libpthread.so.0
#2  0x08998eca in my_write_core (sig=11) at stacktrace.c:309
#3  0x0835215f in handle_segfault (sig=11) at mysqld.cc:2689
#4  <signal handler called>
#5  0xb7d48b37 in memcpy () from /lib/tls/i686/cmov/libc.so.6
#6  0x087f322e in row_sel_field_store_in_mysql_format (dest=0xa1b8aa5 ' ' <repeats 12 times>, "��������d", templ=0x9ebdee9c, 
    data=0xb8414f35 <Address 0xb8414f35 out of bounds>, len=3861249640) at row/row0sel.c:2567
#7  0x087f34d4 in row_sel_store_mysql_rec (mysql_rec=0xa1b8aa0 "�", prebuilt=0x9ebe0668, rec=0x9e674d9d "\200", offsets=0x99da0160, start_field_no=0, end_field_no=2)
    at row/row0sel.c:2672
#8  0x087f58d6 in row_search_for_mysql (buf=0xa1b8aa0 "�", mode=2, prebuilt=0x9ebe0668, match_mode=1, direction=0) at row/row0sel.c:4141
#9  0x0878b30c in ha_innobase::index_read (this=0xa1b88f0, buf=0xa1b8aa0 "�", key_ptr=0xa2af850 "_", key_len=4, find_flag=HA_READ_KEY_EXACT) at handler/ha_innodb.cc:4478
#10 0x084a89f0 in handler::index_read_map (this=0xa1b88f0, buf=0xa1b8aa0 "�", key=0xa2af850 "_", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at ../../sql/handler.h:1783
#11 0x083d90cb in join_read_always_key (tab=0xa2af5a8) at sql_select.cc:16902
#12 0x083dc0ec in sub_select (join=0xa2afed8, join_tab=0xa2af5a8, end_of_records=false) at sql_select.cc:16222
#13 0x083dbea2 in evaluate_join_record (join=0xa2afed8, join_tab=0xa2af3f8, error=0) at sql_select.cc:16490
#14 0x083dc13f in sub_select (join=0xa2afed8, join_tab=0xa2af3f8, end_of_records=false) at sql_select.cc:16227
#15 0x083dbea2 in evaluate_join_record (join=0xa2afed8, join_tab=0xa2af248, error=0) at sql_select.cc:16490
#16 0x083dc13f in sub_select (join=0xa2afed8, join_tab=0xa2af248, end_of_records=false) at sql_select.cc:16227
#17 0x083e9327 in do_select (join=0xa2afed8, fields=0xa0d7464, table=0x0, procedure=0x0) at sql_select.cc:15786
#18 0x08400f93 in JOIN::exec (this=0xa2afed8) at sql_select.cc:2881
#19 0x083fb976 in mysql_select (thd=0xa0d6098, rref_pointer_array=0xa0d74d4, tables=0xa173800, wild_num=0, fields=@0xa0d7464, conds=0xa2ae138, og_num=0, order=0x0, group=0x0, 
    having=0x0, proc_param=0x0, select_options=3489942144, result=0xa2ae458, unit=0xa0d7138, select_lex=0xa0d73d0) at sql_select.cc:3062
#20 0x083689ff in mysql_execute_command (thd=0xa0d6098) at sql_parse.cc:3300
#21 0x0836dde8 in mysql_parse (thd=0xa0d6098, inBuf=0xa172770 "delete mt1, mt2, mt3  from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3  and mt1.id1 > 5", 
    length=101, found_semicolon=0x99da1be8) at sql_parse.cc:5752
#22 0x0836e96a in dispatch_command (command=COM_QUERY, thd=0xa0d6098, 
    packet=0xa18f031 "delete mt1, mt2, mt3  from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3  and mt1.id1 > 5", packet_length=101) at sql_parse.cc:1009
#23 0x0836ff45 in do_command (thd=0xa0d6098) at sql_parse.cc:691
#24 0x0835bfd3 in handle_one_connection (arg=0xa0d6098) at sql_connect.cc:1146
#25 0xb7fa350f in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#26 0xb7db17ee in clone () from /lib/tls/i686/cmov/libc.so.6

Result produced by mtr before crash--->

select count(*) from mt1 where id1 > 95;
count(*)
5
select count(*) from mt2 where id2 > 95;
count(*)
25
select count(*) from mt3 where id3 > 95;
count(*)
250
update mt1,mt2,mt3 set mt1.t="aaa", mt2.t="bbb", mt3.t="cc" where  mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3  and mt1.id1 > 90;
select count(*) from mt1 where t = "aaa";
count(*)
10
select count(*) from mt1 where id1 > 90;
count(*)
10
select count(*) from mt2 where t = "bbb";
count(*)
50
select count(*) from mt2 where id2 > 90;
count(*)
50
select count(*) from mt3 where t = "cc";
count(*)
500
select count(*) from mt3 where id3 > 90;
count(*)
500
delete mt1.*, mt2.*, mt3.*  from mt1,mt2,mt3 where mt1.id1 = mt2.id2 and mt2.id2 = mt3.id3  and mt1.id1 > 95;
check table mt1, mt2, mt3;
Table   Op      Msg_type        Msg_text
test.mt1        check   status  OK
test.mt2        check   status  OK
test.mt3        check   status  OK
select count(*) from mt1 where id1 > 95;
count(*)
0
select count(*) from mt2 where id2 > 95;
count(*)
0
select count(*) from mt3 where id3 > 95;
count(*)
0

Thank you for the report.

I believe this is duplicate of bug #37415 as backtrace is same.

This bug is wrongly classified as a duplicate of Bug#37415. Instead it is likely a duplicate of Bug#43360. The crash is still reproducible when ICP is enabled for InnoDB.

I am setting this back to verified in order to remember to consider if we should include the MTR test case in the test suite after Bug#43360 has been fixed.

Valgrind reports the following memory corruption when ICP is enabled in InnoDB (the same as in Bug#43360):

==664== Invalid write of size 8
==664==    at 0x8F020C: build_template(row_prebuilt_struct*, THD*, TABLE*, ha_innobase*, unsigned) (ha_innodb.cc:3437)
==664==    by 0x8F3323: ha_innobase::change_active_index(unsigned) (ha_innodb.cc:4637)
==664==    by 0x8F3492: ha_innobase::index_init(unsigned, bool) (ha_innodb.cc:4309)
==664==    by 0x5812B3: handler::ha_index_init(unsigned, bool) (handler.h:1559)
==664==    by 0x702FD7: join_read_always_key(st_join_table*) (sql_select.cc:16982)
==664==    by 0x70653C: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:16311)
==664==    by 0x7062BB: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:16581)
==664==    by 0x70658C: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:16316)
==664==    by 0x7062BB: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:16581)
==664==    by 0x70658C: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:16316)
==664==    by 0x71421A: do_select(JOIN*, List<Item>*, TABLE*, Procedure*) (sql_select.cc:15874)
==664==    by 0x731B69: JOIN::exec() (sql_select.cc:2929)
==664==    by 0x72C183: mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3120)
==664==    by 0x68AC9E: mysql_execute_command(THD*) (sql_parse.cc:3427)
==664==    by 0x68FB0D: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:5979)
==664==    by 0x690FEA: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1076)

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

3709 Olav Sandstaa	2009-11-13
      Test case for Bug#43448 Server crashes on multi table delete with Innodb
      
      Simplified test case based on the test case from the bug report. Adding this
      to the optimizer_unfixed_bugs suite until this bug has been fixed.
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug43448.result
        Result file for Bug#43448 Server crashes on multi table delete with Innodb
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug43448.test
        Test case for Bug#43448 Server crashes on multi table delete with Innodb

Test case pushed to the optimizer_unfixed_bug suite in mysql-6.0-codebase-bugfizing branch.

Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:olav@sun.com-20091113144107-wha5qk9d78mx50tl) (merge vers: 6.0.14-alpha) (pib:13)

This bug is a duplicate of Bug#43360 and/or Bug#36981. I have verified that this crash does no longer occur after applying the fixes for these two bugs.

Test case for this bug is committed here:

  http://lists.mysql.com/commits/97367

Patch containing updated version of test case:

http://lists.mysql.com/commits/99022

Patch containing test case pushed to mysql-6.0-codebase-bugfixing with revision-id: olav@sun.com-20100226091930-qxvakxmcp6463t5w .

Closing this as duplicate of Bug#36981.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100306102742-yw9zzgw9ac5r65m5) (version source revid:bar@mysql.com-20100305074327-h09o5lw290s04lcf) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)