Bug #37415 Crash in row_sel_field_store_in_mysql_format on multiple-table update (innodb)
Submitted: 15 Jun 2008 17:56 Modified: 25 Nov 2009 12:55
Reporter: Philip Stoev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0 OS:Any
Assigned to: Olav Sandstå
Tags: engine_condition_pushdown, ICP, index_condition_pushdown, optimizer_switch, v6
Triage: Triaged: D1 (Critical)

[15 Jun 2008 17:56] Philip Stoev
Description:
When executing a single-tread DML workload containing randomly-generated queries, mysqld crashed as follows:

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x0843eda3 in write_core (sig=11) at stacktrace.c:302
#3  0x0829b228 in handle_segfault (sig=11) at mysqld.cc:2626
#4  <signal handler called>
#5  0x08648710 in row_sel_field_store_in_mysql_format (dest=0xa663ff5 "", templ=0xaf09269c, data=0xd8962c1c <Address 0xd8962c1c out of bounds>,
    len=3592049804) at row/row0sel.c:2463
#6  0x08648afb in row_sel_store_mysql_rec (mysql_rec=0xa663ff0 "$", prebuilt=0xaf098a68, rec=0xaeb080a8 "\200", offsets=0xa9e5a95c, start_field_no=0,
    end_field_no=4) at row/row0sel.c:2651
#7  0x0864a91d in row_search_for_mysql (buf=0xa663ff0 "$", mode=2, prebuilt=0xaf098a68, match_mode=1, direction=0) at row/row0sel.c:4170
#8  0x085ce921 in ha_innobase::index_read (this=0xa663e50, buf=0xa663ff0 "$", key_ptr=0xa6aae70 "", key_len=5, find_flag=HA_READ_KEY_EXACT)
    at handler/ha_innodb.cc:4187
#9  0x083cd95a in handler::index_read_map (this=0xa663e50, buf=0xa663ff0 "$", key=0xa6aae70 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
    at ../../sql/handler.h:1657
#10 0x08312cf9 in join_read_always_key (tab=0xa6aacbc) at sql_select.cc:14031
#11 0x08315d89 in sub_select (join=0xa686f38, join_tab=0xa6aacbc, end_of_records=false) at sql_select.cc:13341
#12 0x08315b79 in evaluate_join_record (join=0xa686f38, join_tab=0xa6aab08, error=0) at sql_select.cc:13568
#13 0x08315da5 in sub_select (join=0xa686f38, join_tab=0xa6aab08, end_of_records=false) at sql_select.cc:13342
#14 0x08322d70 in do_select (join=0xa686f38, fields=0xa9e5b3c4, table=0x0, procedure=0x0) at sql_select.cc:13092
#15 0x08335655 in JOIN::exec (this=0xa686f38) at sql_select.cc:2740
#16 0x083308f2 in mysql_select (thd=0xa65d1a0, rref_pointer_array=0xa65e660, tables=0xa69a088, wild_num=0, fields=@0xa9e5b3c4, conds=0xa68fe10, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0xa686ec8, unit=0xa65e2cc, select_lex=0xa65e55c)
    at sql_select.cc:2929
#17 0x0834ef47 in mysql_multi_update (thd=0xa65d1a0, table_list=0xa69a088, fields=0xa65e5f0, values=0xa65e81c, conds=0xa68fe10, options=0,
    handle_duplicates=DUP_ERROR, ignore=false, unit=0xa65e2cc, select_lex=0xa65e55c) at sql_update.cc:1188
#18 0x082ae74e in mysql_execute_command (thd=0xa65d1a0) at sql_parse.cc:2902
#19 0x082b4ec6 in mysql_parse (thd=0xa65d1a0,
    inBuf=0xa699db8 "UPDATE B AS X LEFT JOIN C AS Y USING ( int_key ) SET Y . int_key = Y . int_key WHERE Y . int_nokey > ( SELECT L FROM ( SELECT Y . pk AS K, X . int_nokey AS L , X . pk AS M FROM C AS X LEFT JOIN B AS Y"..., length=297, found_semicolon=0xa9e5c260) at sql_parse.cc:5782
#20 0x082b590f in dispatch_command (command=COM_QUERY, thd=0xa65d1a0,
    packet=0xa6b7ee1 "UPDATE B AS X LEFT JOIN C AS Y USING ( int_key ) SET Y . int_key = Y . int_key WHERE Y . int_nokey > ( SELECT L FROM ( SELECT Y . pk AS K, X . int_nokey AS L , X . pk AS M FROM C AS X LEFT JOIN B AS Y"..., packet_length=297) at sql_parse.cc:1059
#21 0x082b6b75 in do_command (thd=0xa65d1a0) at sql_parse.cc:732
#22 0x082a4385 in handle_one_connection (arg=0xa65d1a0) at sql_connect.cc:1134
#23 0x0057d32f in start_thread () from /lib/libpthread.so.0
#24 0x0049a27e in clone () from /lib/libc.so.6

The crash is here:

(gdb) frame 5
#5  0x08648710 in row_sel_field_store_in_mysql_format (dest=0xa663ff5 "", templ=0xaf09269c, data=0xd8962c1c <Address 0xd8962c1c out of bounds>,
    len=3592049804) at row/row0sel.c:2463
(gdb) list
2458
2459                    ptr = dest + len;
2460
2461                    for (;;) {
2462                            ptr--;
2463                            *ptr = *data;
2464                            if (ptr == dest) {
2465                                    break;
2466                            }
2467                            data++;
(gdb) print ptr
$1 = (unsigned char *) 0xe0809480 <Address 0xe0809480 out of bounds>
(gdb) print *ptr
Cannot access memory at address 0xe0809480
(gdb) print data
$2 = (unsigned char *) 0xd8962c1c <Address 0xd8962c1c out of bounds>
(gdb) print *data
Cannot access memory at address 0xd8962c1c

How to repeat:
A simplifed test case will hopefully follow shortly.
[15 Jun 2008 18:49] Philip Stoev
Test case for bug 37415

Attachment: bug37415.pl (text/plain), 933 bytes.

[15 Jun 2008 18:59] Philip Stoev
Please find attached a test case for this bug. Some observations:

* The script creates/populates more tables than are actually used. If only the required tables are created/populated, the bug will not show up.

* The crashing query is in the form:

UPDATE B AS X LEFT JOIN C AS Y USING ( int_key )
SET Y . int_key = 10
WHERE Y . int_nokey > (SELECT 1 FROM
	(SELECT 1 FROM C AS X LEFT JOIN B AS Y USING ( pk ) )
AS Q LIMIT 1);

* Both the join in the outer update and in the inner subquery are required

* Both joins need to have the USING clause

* The subquery is doubly-nested, otherwise update complains about trying to select and update from the same table.
[16 Jun 2008 17:43] Philip Stoev
Multiple-table delete is also affected. The query is of the form:

DELETE view1, view2 FROM view1 INNER JOIN view2
ON view1.t1_uuid = view2.t1_uuid
WHERE view1.t1_uuid = @uuid;

Where view1 and view2 are views containing joins.
[17 Jun 2008 17:31] Heikki Tuuri
This is yet another serious bug in MySQL/InnoDB-6.0. Assigning this to Calvin.
[9 Jul 2008 4:28] Calvin Sun
Philip - could you please try it again with DS-MRR and index condition pushdown off by using:

set engine_condition_pushdown=off;
set optimizer_use_mrr='disable'; 

If the server does not crash with both off, please change the category to Optimizer. The crash looks similar to the one in bug#36981.

Thanks,
Calvin
[9 Jul 2008 9:05] Philip Stoev
After setting

set global engine_condition_pushdown=off;
set global optimizer_use_mrr='disable';

the crash is _still_ observed.
[12 Aug 2008 20:45] Calvin Sun
With 6.0.7-alpha, I tried three times and the test completed when both engine_condition_pushdown and optimizer_use_mrr are off/disable; but the engine crashed every time when both setting are on/force.

I think this is a MRR bug.
[13 Aug 2008 22:37] Sergey Petrunya
> After setting
> 
> set global engine_condition_pushdown=off;
> set global optimizer_use_mrr='disable';
> 
> the crash is _still_ observed.

SET GLOBAL changes apply only to new sessions. If you do not start new session to run the UPDATE statement, then SET GLOBAL statements have no effect.
[13 Aug 2008 22:39] Sergey Petrunya
Calvin, 

Agree with your conclusions, re-assigning to myself.
[30 Sep 2008 13:54] Sanjay Manwani
Reassigning lead to Manyi since Sergey is aware of the issue and assigned to this
[6 Mar 2009 10:54] Sveta Smirnova
Bug #43448 was marked as duplicate of this one. It contains easier test case.
[27 Mar 2009 22:06] Miguel Solorzano
See bug: http://bugs.mysql.com/bug.php?id=41367.
[14 Aug 2009 10:14] Sveta Smirnova
Tested with case from bug #43448, not repeatable for me also.
[13 Nov 2009 10:16] Olav Sandstå
I am no longer able to reproduce this crash using the mysql-6.0-codebase-bugfixing source. 

The crash seems to have been fixed by the following large merge into the 6.0-bugteam tree:

revno: 2943 [merge]
revision-id: joro@sun.com-20090106103847-5a4189bunn8opg6n
parent: gshchepa@mysql.com-20081231115801-qgbwd2fy5aib72n1
parent: joerg@mysql.com-20081231141015-zruy7wn1un1yn8n2
committer: Georgi Kodinov <joro@sun.com>
branch nick: fix-6.0-bugteam
timestamp: Tue 2009-01-06 12:38:47 +0200
message:
  merged 6.0-main -> 6.0-bugteam

Closing this bug as can't repeat.
[13 Nov 2009 10:22] Philip Stoev
Olav, for every bug that has a MTR test case, please push the test case before you close the bug as Can't repeat. For this bug, the test case is located at

http://bugs.mysql.com/bug.php?id=43448

Thank you!
[13 Nov 2009 11:25] Olav Sandstå
Hi Philip. Thanks for the reminder - and I do agree with you about remember to include MTR test cases. 

But in this case I do not think the test case located in Bug#43448 is triggering the same bug as the original perl test case in this bug report. I looked at this when I originally looked at the bug a few weeks ago and concluded that the test cases was different (one have a subselect while the other do normal joins) and that they likely triggered a different code path. 

Further indication that the bug in Bug#43448 is a different but is that running with Bug#43448 test case I am still able to reproduce the crash. Thus I think Bug#433448 is wrongly classified as a duplicate of this bug and should instead have been classified as a duplicate of Bug#43360. I will update the status in Bug43448 and then considering include the MTR test case after Bug#43360 (and possibly some of the other InnoDB ICP memory corruption issues) has been fixed.

I did an attempt on converting the perl test case for this bug report to MTR by having a test script doing the exact same sql as the perl test case. With this  MTR script I was never able to trigger any crash. I do not understand why but expect it to be timing related. I also had to run the perl script multiple times in some cases in order to reproduce the crash. One possibility could be to include the perl script in the MTR test case?

So my conclusion is: This bug has been fixed some time in before January (while Bug#43448 is still not fixed). I have not been able to find the exact patch that fixed it (using bzrfind) - the closest is a large merge containing 1000+ change sets that was merged into the mysql-6.0-codebase-bugfixing tree in January. And unfortunately I do not have any MTR test case that reproduces this crash.
[25 Nov 2009 12:55] Olav Sandstå
Closing this as "Can not repeat". As mentioned in one of the previous comments 
this bug has been fixed by another patch.

Was unsuccessful in converting the perl test to MTR.