MySQL Bugs: #28598: mysqld crash when killing a long-running explain query

Bug #28598	mysqld crash when killing a long-running explain query
Submitted:	22 May 2007 15:11	Modified:	10 Jun 2007 18:51
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	5.1.19, 5.0.44	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	crash, explain, greedy, Optimizer

Description:
Killing a long-running explain query caused the server to crash with the following debug assertion:

Version: '5.1.19-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
mysqld: sql_select.cc:4737: void greedy_search(JOIN*, long long unsigned int, unsigned int, unsigned int): Assertion `(pos != __null)' failed.
070522 16:39:18 - mysqld got signal 6;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=4
max_threads=151
threads_connected=4
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 342321 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x907f2c8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x43288eb4, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81f8c08 handle_segfault + 792
0xffffe410 _end + -142310144
0x400edb75 _end + 932412517
0x400e5903 _end + 932379123
0x8277c34 _Z20make_join_statisticsP4JOINP13st_table_listP4ItemP16st_dynamic_array + 11668
0x827abef _ZN4JOIN8optimizeEv + 2175
0x827ec52 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 274
0x827f543 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 451
0x8209206 _Z21execute_sqlcom_selectP3THDP13st_table_list + 502
0x820fc2f _Z21mysql_execute_commandP3THD + 22575
0x8213812 _Z11mysql_parseP3THDPKcjPS2_ + 418
0x82149da _Z16dispatch_command19enum_server_commandP3THDPcj + 4218
0x82155a5 _Z10do_commandP3THD + 421
0x8202a1f handle_one_connection + 271
0x4004daa7 _end + 931756951
0x4017ec2e _end + 933006622
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.

How to repeat:
I will upload a testcase later.

The only thing to note about this case, is that I ran this before running the explain:

set session optimizer_search_depth=0

Suggested fix:
.

testcase:
-----------------------

drop table if exists a01;
drop table if exists a02;
drop table if exists a03;
drop table if exists a04;
drop table if exists a05;
drop table if exists a06;
drop table if exists a07;
drop table if exists a08;
drop table if exists a09;
drop table if exists a10;
drop table if exists a11;
drop table if exists a12;
drop table if exists a13;
drop table if exists a14;
drop table if exists a15;
drop table if exists a16;
drop table if exists a17;
drop table if exists a18;
drop table if exists a19;
drop table if exists a20;
drop table if exists a21;
drop table if exists a22;
drop table if exists a23;
drop table if exists a24;
drop table if exists a25;
drop table if exists a26;
drop table if exists a27;
drop table if exists a28;
drop table if exists a29;
drop table if exists a30;
drop table if exists a31;
drop table if exists a32;
drop table if exists a33;
drop table if exists a34;
drop table if exists a35;
drop table if exists a36;
drop table if exists a37;
drop table if exists a38;
drop table if exists a39;
drop table if exists a40;

create table a01(a01 int,key(a01))engine=myisam;
create table a02(a02 int,key(a02))engine=myisam;
create table a03(a03 int,key(a03))engine=myisam;
create table a04(a04 int,key(a04))engine=myisam;
create table a05(a05 int,key(a05))engine=myisam;
create table a06(a06 int,key(a06))engine=myisam;
create table a07(a07 int,key(a07))engine=myisam;
create table a08(a08 int,key(a08))engine=myisam;
create table a09(a09 int,key(a09))engine=myisam;
create table a10(a10 int,key(a10))engine=myisam;
create table a11(a11 int,key(a11))engine=myisam;
create table a12(a12 int,key(a12))engine=myisam;
create table a13(a13 int,key(a13))engine=myisam;
create table a14(a14 int,key(a14))engine=myisam;
create table a15(a15 int,key(a15))engine=myisam;
create table a16(a16 int,key(a16))engine=myisam;
create table a17(a17 int,key(a17))engine=myisam;
create table a18(a18 int,key(a18))engine=myisam;
create table a19(a19 int,key(a19))engine=myisam;
create table a20(a20 int,key(a20))engine=myisam;
create table a21(a21 int,key(a21))engine=myisam;
create table a22(a22 int,key(a22))engine=myisam;
create table a23(a23 int,key(a23))engine=myisam;
create table a24(a24 int,key(a24))engine=myisam;
create table a25(a25 int,key(a25))engine=myisam;
create table a26(a26 int,key(a26))engine=myisam;
create table a27(a27 int,key(a27))engine=myisam;
create table a28(a28 int,key(a28))engine=myisam;
create table a29(a29 int,key(a29))engine=myisam;
create table a30(a30 int,key(a30))engine=myisam;
create table a31(a31 int,key(a31))engine=myisam;
create table a32(a32 int,key(a32))engine=myisam;
create table a33(a33 int,key(a33))engine=myisam;
create table a34(a34 int,key(a34))engine=myisam;
create table a35(a35 int,key(a35))engine=myisam;
create table a36(a36 int,key(a36))engine=myisam;
create table a37(a37 int,key(a37))engine=myisam;
create table a38(a38 int,key(a38))engine=myisam;
create table a39(a39 int,key(a39))engine=myisam;
create table a40(a40 int,key(a40))engine=myisam;

insert into a01 values (1),(2),(3),(4),(5),(6),(7);
insert into a02 values (1),(2),(3),(4),(5),(6),(7);
insert into a03 values (1),(2),(3),(4),(5),(6),(7);
insert into a04 values (1),(2),(3),(4),(5),(6),(7);
insert into a05 values (1),(2),(3),(4),(5),(6),(7);
insert into a06 values (1),(2),(3),(4),(5),(6),(7);
insert into a07 values (1),(2),(3),(4),(5),(6),(7);
insert into a08 values (1),(2),(3),(4),(5),(6),(7);
insert into a09 values (1),(2),(3),(4),(5),(6),(7);
insert into a10 values (1),(2),(3),(4),(5),(6),(7);
insert into a11 values (1),(2),(3),(4),(5),(6),(7);
insert into a12 values (1),(2),(3),(4),(5),(6),(7);
insert into a13 values (1),(2),(3),(4),(5),(6),(7);
insert into a14 values (1),(2),(3),(4),(5),(6),(7);
insert into a15 values (1),(2),(3),(4),(5),(6),(7);
insert into a16 values (1),(2),(3),(4),(5),(6),(7);
insert into a17 values (1),(2),(3),(4),(5),(6),(7);
insert into a18 values (1),(2),(3),(4),(5),(6),(7);
insert into a19 values (1),(2),(3),(4),(5),(6),(7);
insert into a20 values (1),(2),(3),(4),(5),(6),(7);
insert into a21 values (1),(2),(3),(4),(5),(6),(7);
insert into a22 values (1),(2),(3),(4),(5),(6),(7);
insert into a23 values (1),(2),(3),(4),(5),(6),(7);
insert into a24 values (1),(2),(3),(4),(5),(6),(7);
insert into a25 values (1),(2),(3),(4),(5),(6),(7);
insert into a26 values (1),(2),(3),(4),(5),(6),(7);
insert into a27 values (1),(2),(3),(4),(5),(6),(7);
insert into a28 values (1),(2),(3),(4),(5),(6),(7);
insert into a29 values (1),(2),(3),(4),(5),(6),(7);
insert into a30 values (1),(2),(3),(4),(5),(6),(7);
insert into a31 values (1),(2),(3),(4),(5),(6),(7);
insert into a32 values (1),(2),(3),(4),(5),(6),(7);
insert into a33 values (1),(2),(3),(4),(5),(6),(7);
insert into a34 values (1),(2),(3),(4),(5),(6),(7);
insert into a35 values (1),(2),(3),(4),(5),(6),(7);
insert into a36 values (1),(2),(3),(4),(5),(6),(7);
insert into a37 values (1),(2),(3),(4),(5),(6),(7);
insert into a38 values (1),(2),(3),(4),(5),(6),(7);
insert into a39 values (1),(2),(3),(4),(5),(6),(7);
insert into a40 values (1),(2),(3),(4),(5),(6),(7);

set session optimizer_search_depth=0;

explain select * from 
a01,a02,a03,a04,a05,a06,a07,a08,a09,a10,
a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,
a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,
a31,a32,a33,a34,a35,a36,a37,a38,a39,a40
where
a01=a02 and a02=a03 and a03=a04 and a05=a06 and a06=a07 and a08=a09 and a09=a10 and
a11=a12 and a12=a13 and a13=a14 and a15=a16 and a16=a17 and a18=a19 and a19=a20 and
a21=a22 and a22=a23 and a23=a24 and a25=a26 and a26=a27 and a28=a29 and a29=a30 and
a31=a32 and a32=a33 and a33=a34 and a35=a36 and a36=a37 and a38=a39 and a39=a40;

#now in another connection, kill the above explain.

full stack trace and debug info

Attachment: bug28598_debug_info.txt (text/plain), 3.04 KiB.

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

ChangeSet@1.2504, 2007-05-29 19:47:41+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28598.
  mysqld was crashing when a long-running explain query was killed from
  another connection.
   
  The greedy_search() ignored thd->killed status after a call to the
  best_extension_by_limited_search() function.
  choose_plan(), greedy_search() best_extension_by_limited_search() functions
  has been changed to return TRUE in case of fatal error.

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

ChangeSet@1.2504, 2007-05-29 19:54:10+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28598.
  mysqld was crashing when a long-running explain query was killed from
  another connection.
   
  The greedy_search() function ignored thd->killed status after
  a call to the best_extension_by_limited_search() function.
  choose_plan(), greedy_search() best_extension_by_limited_search()
  functions has been changed to return TRUE in case of fatal error.

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

ChangeSet@1.2504, 2007-05-29 20:22:56+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28598.
  mysqld was crashing when a long-running explain query was killed from
  another connection.
   
  The greedy_search() function ignored thd->killed status after
  a call to the best_extension_by_limited_search() function.
  choose_plan(), greedy_search() best_extension_by_limited_search()
  functions has been changed to return TRUE in case of fatal error.

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

ChangeSet@1.2504, 2007-05-29 21:17:54+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28598.
  mysqld was crashing when a long-running explain query was killed from
  another connection.
   
  The greedy_search() function ignored thd->killed status after
  a call to the best_extension_by_limited_search() function.
  choose_plan(), greedy_search() best_extension_by_limited_search()
  functions has been changed to return TRUE in case of fatal error.

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

ChangeSet@1.2504, 2007-05-30 19:37:37+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28598.
  mysqld crashed when a long-running explain query was killed from
  another connection.
  
  When current thread was marked as killed, the
  best_extension_by_limited_search() function was silently returning
  without initialization of join->best_positions[] array elements.
  However, the greedy_search() function ignored thd->killed status
  after a calls to the best_extension_by_limited_search() function, and
  after several calls the greedy_search() function used an uninitialized
  data from the join->best_positions[idx] to search position in the
  join->best_ref[] array. 
  That search failed, and greedy_search() tried to call swap_variables()
  function with NULL argument - that is a crash.

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

ChangeSet@1.2504, 2007-05-30 19:47:55+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28598.
  mysqld crashed when a long-running explain query was killed from
  another connection.
  
  When current thread was marked as killed, the
  best_extension_by_limited_search() function was silently returning
  without initialization of join->best_positions[] array elements.
  However, the greedy_search() function ignored thd->killed status
  after a calls to the best_extension_by_limited_search() function, and
  after several calls the greedy_search() function used an uninitialized
  data from the join->best_positions[idx] to search position in the
  join->best_ref[] array. 
  That search failed, and greedy_search() tried to call swap_variables()
  function with NULL argument - that is a crash.

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

ChangeSet@1.2504, 2007-05-31 12:10:21+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28598.
  mysqld crashed when a long-running explain query was killed from
  another connection.
  
  When the current thread caught a kill signal executing the function
  best_extension_by_limited_search it just silently returned to  
  the calling function greedy_search without initializing elements of
  the join->best_positions array.
  However, the greedy_search function ignored thd->killed status
  after a calls to the best_extension_by_limited_search function, and
  after several calls the greedy_search function used an uninitialized
  data from the join->best_positions[idx] to search position in the
  join->best_ref array. 
  That search failed, and greedy_search tried to call swap_variables
  function with NULL argument - that caused a crash.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.