Bug #42116 Mysql crash on specific query
Submitted: 14 Jan 2009 15:35 Modified: 18 Dec 2009 20:40
Reporter: castagno florian Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.68 entreprise/5.1 OS:Linux (RHEL 5 x86_64)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: regression

[14 Jan 2009 15:35] castagno florian
Description:
Using this  query :
SELECT   IFNULL( T6.`name` , 'No host group' ) AS C1 FROM  ((`Exa_Alert` T0 LEFT OUTER JOIN `Exa_ElemEvent` T1 ON (T0.`alert_id` = T1.`aggrevent_id`))  LEFT OUTER JOIN (`Exa_Target` T2 LEFT OUTER JOIN (`Exa_Node` T3 LEFT OUTER JOIN (`Exa_Lnk_Host_Node` T4 LEFT OUTER JOIN (`Exa_Host` T5 LEFT OUTER JOIN `Exa_Host_Group` T6 ON (T5.`host_group_id` = T6.`host_group_id`))  ON (T4.`host_id` = T5.`host_id`))  ON (T3.`node_id` = T4.`node_id`))  ON (T2.`node_id` = T3.`node_id`))  ON (T0.`alert_id` = T2.`alert_id`))  WHERE (((((((((T0.`cat_actiondetail_id`=50) OR (T0.`cat_actiondetail_id`=63)) AND (T0.`cat_eventtype_id`=3)) AND (T0.`cat_action_id`=1))))) AND (((T1.`classification_text`<>''))))) GROUP BY   IFNULL( T6.`name` , 'No host group' ) ORDER BY C1  ASC;

result in an immediate crash of Mysql server :

090114 15:13:36 - mysqld got signal 11 ;
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=67108864
read_buffer_size=131072
max_used_connections=1
max_connections=500
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 385536 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x15e5c770
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=0x15e5c770, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x15e5c770, stack_bottom=0x407c0000, thread_stack=131072, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x15e9a730 = SELECT   IFNULL( T6.`name` , 'No host group' ) AS C1 FROM  ((`Exa_Alert` T0 LEFT OUTER JOIN `Exa_ElemEvent` T1 ON (T0.`alert_id` = T1.`aggrevent_id`))  LEFT OUTER JOIN (`Exa_Target` T2 LEFT OUTER JOIN (`Exa_Node` T3 LEFT OUTER JOIN (`Exa_Lnk_Host_Node` T4 LEFT OUTER JOIN (`Exa_Host` T5 LEFT OUTER JOIN `Exa_Host_Group` T6 ON (T5.`host_group_id` = T6.`host_group_id`))  ON (T4.`host_id` = T5.`host_id`))  ON (T3.`node_id` = T4.`node_id`))  ON (T2.`node_id` = T3.`node_id`))  ON (T0.`alert_id` = T2.`alert_id`))  WHERE (((((((((T0.`cat_actiondetail_id`=50) OR (T0.`cat_actiondetail_id`=63)) AND (T0.`cat_eventtype_id`=3)) AND (T0.`cat_action_id`=1))))) AND (((T1.`classification_text`<>''))))) GROUP BY   IFNULL( T6.`name` , 'No host group' ) ORDER BY C1  ASC
thd->thread_id=2
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0

How to repeat:
using the provided query do the same on 5.0.68 and 5.0.74 pro enterprise mysql
[14 Jan 2009 16:04] MySQL Verification Team
Thank you for the bug report. It is possible you provide a dump file with create table + insert data to run the offended query on our side?. Thanks in advance.
[15 Jan 2009 16:43] castagno florian
Please find files with dump and config file my.cnf
[15 Jan 2009 16:57] MySQL Verification Team
Thank you for the feedback.

090115 14:54:12 [Note] Event Scheduler: Loaded 0 events
090115 14:54:12 [Note] g:\share\dbs\5.1\bin\mysqld: ready for connections.
Version: '5.1.31-nt-log'  socket: ''  port: 3510  Source distribution
090115 14:55:35 - mysqld got exception 0xc0000005 ;
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=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338112 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3be9790
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...
0000000140199B99    mysqld.exe!make_join_select()[sql_select.cc:6322]
000000014019F284    mysqld.exe!JOIN::optimize()[sql_select.cc:1046]
00000001401A1C69    mysqld.exe!mysql_select()[sql_select.cc:2357]
00000001401A20B6    mysqld.exe!handle_select()[sql_select.cc:269]
0000000140160708    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4891]
00000001401620F8    mysqld.exe!mysql_execute_command()[sql_parse.cc:2184]
00000001401675B9    mysqld.exe!mysql_parse()[sql_parse.cc:5793]
000000014016813A    mysqld.exe!dispatch_command()[sql_parse.cc:1202]
0000000140168EF7    mysqld.exe!do_command()[sql_parse.cc:857]
00000001401F9A87    mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
000000014026DA15    mysqld.exe!pthread_start()
00000001403B2EB7    mysqld.exe!_callthreadstart()[thread.c:295]
00000001403B2F85    mysqld.exe!_threadstart()[thread.c:275]
0000000076EF495D    kernel32.dll!BaseThreadInitThunk()
00000000770F8791    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000003C42860=SELECT   IFNULL( T6.`name` , 'No host group' ) AS C1 FROM  ((`Exa_Alert` T0 LEFT OUTER
JOIN `Exa_ElemEvent` T1 ON (T0.`alert_id` = T1.`aggrevent_id`))  LEFT OUTER JOIN
(`Exa_Target` T2 LEFT OUTER JOIN (`Exa_Node` T3 LEFT OUTER JOIN (`Exa_Lnk_Host_Node` T4
LEFT OUTER JOIN (`Exa_Host` T5 LEFT OUTER JOIN `Exa_Host_Group` T6 ON (T5.`host_group_id`
= T6.`host_group_id`))  ON (T4.`host_id` = T5.`host_id`))  ON (T3.`node_id` =
T4.`node_id`))  ON (T2.`node_id` = T3.`node_id`))  ON (T0.`alert_id` = T2.`alert_id`))
WHERE (((((((((T0.`cat_actiondetail_id`=50) OR (T0.`cat_actiondetail_id`=63)) AND
(T0.`cat_eventtype_id`=3)) AND (T0.`cat_action_id`=1))))) AND
(((T1.`classification_text`<>''))))) GROUP BY   IFNULL( T6.`name` , 'No host group' )
ORDER BY C1  ASC
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

g:\share\dbs>
[15 Jan 2009 17:57] MySQL Verification Team
Thank you for the bug report. Verified as described.

Not repeatable on: 5.0.41 released version and 6.0 bzr source
Repeatable on    : 5.0.51b released version and 5.0/5.1 brz tree.
[25 Feb 2009 8:34] Gleb Shchepa
Simplified test case:

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
CREATE TABLE t3 (a INT, INDEX (a));
CREATE TABLE t4 (a INT);
CREATE TABLE t5 (a INT);
CREATE TABLE t6 (a INT);

INSERT INTO t1 VALUES (1), (1), (1);

INSERT INTO t2 VALUES
(2), (2), (2), (2), (2), (2), (2), (2), (2), (2),
(2), (2), (2), (2), (2);

INSERT INTO t3 VALUES
(3), (3), (3), (3), (3), (3), (3), (3), (3), (3),
(3), (3), (3), (3), (3), (3), (3), (3), (3), (3),
(3), (3), (3);

SELECT *
FROM
  t1 LEFT OUTER JOIN t2 ON t1.a = t2.a
     LEFT OUTER JOIN t3
        LEFT OUTER JOIN t4 ON t3.a = t4.a
        LEFT OUTER JOIN t5
          LEFT OUTER JOIN t6 ON t5.a = t6.a
        ON t4.a = t5.a
     ON t1.a = t3.a
WHERE t2.a=0;

DROP TABLE IF EXISTS t1, t2, t3, t4, t5, t6;
[15 Apr 2009 14:38] Georgi Kodinov
The problem to me is that the greedy_search() function is selecting an impossible join order: 
BEST_POSITIONS: t1 t3 t4 t2 t5 t6 

Looking further.
[30 Apr 2009 12:58] Bugs System
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/73149

2724 Georgi Kodinov	2009-04-30
      Bug #42116 : Mysql crash on specific query
      
      The optimizer uses bitmaps of nested JOINs to determine
      if certain table can be placed at a certain place in the
      JOIN order.
      It does maintain a bitmap describing in which JOINs 
      last placed table is nested.
      When it puts a table it makes sure the bit of every JOIN that
      contains the table in question is set (because JOINs can be nested).
      It does that by recursively setting the bit for the next enclosing
      JOIN when this is the first table in the JOIN and recursively 
      resetting the bit if it's the last table in the JOIN.
      When it removes a table from the join order it should do the
      opposite : recursively unsets the bit if it's the only remaining 
      table in this join and and recursively sets the bit if it's removing
      the last table of a JOIN.
      There was an error in how the bits was set for the upper levels :
      when removing a table it was removing the bit for all the enclosing 
      nested JOINs even if there were more tables left in the current JOIN
      (which practically means that the upper nested JOINs were not affected).
      Fixed by stopping the recursion at the relevant level.
     @ mysql-test/r/join.result
        Bug #42116: test case
     @ mysql-test/t/join.test
        Bug #42116: test case
     @ sql/sql_select.cc
        Bug #41116: don't go up and reset the bits if more tables in
        at the current JOIN level
[22 May 2009 13:29] Georgi Kodinov
Bug #44633 is fixed by the fix for this bug.
[19 Jun 2009 13:06] MySQL Verification Team
Joro: does your patch fix this crash too:

set session optimizer_search_depth=1;
drop table if exists `t1`;
create table `t1`(`a` int) engine=myisam;
insert into `t1` values (1);
explain 
select 1 from
    `t1` as `c1`
natural right join  
    `t1` as `c2`
natural right join  
    `t1` as `c3`
natural right join  
(
   select 1 from `t1` as `c4`
) as `d1`
natural left join  
    `t1` as `c5`;

Stack trace from 5.1.35:

mysqld.exe!get_best_combination()[sql_select.cc:5463]
mysqld.exe!make_join_statistics()[sql_select.cc:2889]
mysqld.exe!JOIN::optimize()[sql_select.cc:955]
mysqld.exe!mysql_select()[sql_select.cc:2364]
mysqld.exe!mysql_explain_union()[sql_select.cc:16382]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4987]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2211]
mysqld.exe!mysql_parse()[sql_parse.cc:5929]
mysqld.exe!dispatch_command()[sql_parse.cc:1216]
mysqld.exe!do_command()[sql_parse.cc:857]
mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:293]
mysqld.exe!_threadstart()[thread.c:277]
[23 Jun 2009 5:52] Georgi Kodinov
Shane,

Yes it does : 
+set session optimizer_search_depth=1;
+drop table if exists `t1`;
+Warnings:
+Note   1051    Unknown table 't1'
+create table `t1`(`a` int) engine=myisam;
+insert into `t1` values (1);
+explain 
+select 1 from
+`t1` as `c1`
+natural right join  
+`t1` as `c2`
+natural right join  
+`t1` as `c3`
+natural right join  
+(
+select 1 from `t1` as `c4`
+) as `d1`
+natural left join  
+`t1` as `c5`;
+id     select_type     table   type    possible_keys   key     key_len ref    rows     Extra
+1      PRIMARY <derived2>      system  NULL    NULL    NULL    NULL    1
+1      PRIMARY c3      ALL     NULL    NULL    NULL    NULL    1
+1      PRIMARY c2      ALL     NULL    NULL    NULL    NULL    1
+1      PRIMARY c1      ALL     NULL    NULL    NULL    NULL    1
+1      PRIMARY c5      ALL     NULL    NULL    NULL    NULL    1
+2      DERIVED c4      system  NULL    NULL    NULL    NULL    1
[1 Sep 2009 15:22] castagno florian
When this patch will be included in MySQL release ?
[2 Sep 2009 15:31] Georgi Kodinov
It needs to pass through an internal code review first. we're working on it.
[29 Oct 2009 15:15] Bugs System
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/88618

2724 Georgi Kodinov	2009-10-29
      Bug #42116 : Mysql crash on specific query
      
      Queries with nested outer/innter joins may lead to crashes or 
      bad results because an internal data structure is not handled
      correctly.
      The optimizer uses bitmaps of nested JOINs to determine
      if certain table can be placed at a certain place in the
      JOIN order.
      It does maintain a bitmap describing in which JOINs 
      last placed table is nested.
      When it puts a table it makes sure the bit of every JOIN that
      contains the table in question is set (because JOINs can be nested).
      It does that by recursively setting the bit for the next enclosing
      JOIN when this is the first table in the JOIN and recursively 
      resetting the bit if it's the last table in the JOIN.
      When it removes a table from the join order it should do the
      opposite : recursively unset the bit if it's the only remaining 
      table in this join and and recursively set the bit if it's removing
      the last table of a JOIN.
      There was an error in how the bits was set for the upper levels :
      when removing a table it was setting the bit for all the enclosing 
      nested JOINs even if there were more tables left in the current JOIN
      (which practically means that the upper nested JOINs were not affected).
      Fixed by stopping the recursion at the relevant level.
     @ mysql-test/r/join.result
        Bug #42116: test case
     @ mysql-test/t/join.test
        Bug #42116: test case
     @ sql/sql_select.cc
        Bug #41116: don't go up and set the bits if more tables in
        at the current JOIN level
[29 Oct 2009 15:24] Bugs System
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/88622

2724 Georgi Kodinov	2009-10-29
      Bug #42116 : Mysql crash on specific query
      
      Queries with nested outer joins may lead to crashes or 
      bad results because an internal data structure is not handled
      correctly.
      The optimizer uses bitmaps of nested JOINs to determine
      if certain table can be placed at a certain place in the
      JOIN order.
      It does maintain a bitmap describing in which JOINs 
      last placed table is nested.
      When it puts a table it makes sure the bit of every JOIN that
      contains the table in question is set (because JOINs can be nested).
      It does that by recursively setting the bit for the next enclosing
      JOIN when this is the first table in the JOIN and recursively 
      resetting the bit if it's the last table in the JOIN.
      When it removes a table from the join order it should do the
      opposite : recursively unset the bit if it's the only remaining 
      table in this join and and recursively set the bit if it's removing
      the last table of a JOIN.
      There was an error in how the bits was set for the upper levels :
      when removing a table it was setting the bit for all the enclosing 
      nested JOINs even if there were more tables left in the current JOIN
      (which practically means that the upper nested JOINs were not affected).
      Fixed by stopping the recursion at the relevant level.
     @ mysql-test/r/join.result
        Bug #42116: test case
     @ mysql-test/t/join.test
        Bug #42116: test case
     @ sql/sql_select.cc
        Bug #41116: don't go up and set the bits if more tables in
        at the current JOIN level
[30 Oct 2009 7:30] Bugs System
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/88680

2829 Georgi Kodinov	2009-10-29
      Bug #42116 : Mysql crash on specific query
      
      Queries with nested outer joins may lead to crashes or 
      bad results because an internal data structure is not handled
      correctly.
      The optimizer uses bitmaps of nested JOINs to determine
      if certain table can be placed at a certain place in the
      JOIN order.
      It does maintain a bitmap describing in which JOINs 
      last placed table is nested.
      When it puts a table it makes sure the bit of every JOIN that
      contains the table in question is set (because JOINs can be nested).
      It does that by recursively setting the bit for the next enclosing
      JOIN when this is the first table in the JOIN and recursively 
      resetting the bit if it's the last table in the JOIN.
      When it removes a table from the join order it should do the
      opposite : recursively unset the bit if it's the only remaining 
      table in this join and and recursively set the bit if it's removing
      the last table of a JOIN.
      There was an error in how the bits was set for the upper levels :
      when removing a table it was setting the bit for all the enclosing 
      nested JOINs even if there were more tables left in the current JOIN
      (which practically means that the upper nested JOINs were not affected).
      Fixed by stopping the recursion at the relevant level.
     @ mysql-test/r/join.result
        Bug #42116: test case
     @ mysql-test/t/join.test
        Bug #42116: test case
     @ sql/sql_select.cc
        Bug #41116: don't go up and set the bits if more tables in
        at the current JOIN level
[2 Nov 2009 18:14] MySQL Verification Team
for search purposes, I just wanted to note joro's 25 feb simplified testcase gives this stack trace on 5.1.40 (different to original one)

mysqld.exe!add_found_match_trig_cond()[sql_select.cc:5942]
mysqld.exe!make_join_select()[sql_select.cc:6391]
mysqld.exe!JOIN::optimize()[sql_select.cc:1045]
mysqld.exe!mysql_select()[sql_select.cc:2380]
mysqld.exe!handle_select()[sql_select.cc:268]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5044]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2238]
mysqld.exe!mysql_parse()[sql_parse.cc:5967]
mysqld.exe!dispatch_command()[sql_parse.cc:1226]
mysqld.exe!do_command()[sql_parse.cc:865]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()
[4 Nov 2009 9:16] Bugs System
Pushed into 5.0.88 (revid:joro@sun.com-20091104091355-hpz6dwgkrfmokj3k) (version source revid:joro@sun.com-20091029152429-ks55fhrp4lhknyij) (merge vers: 5.0.88) (pib:13)
[4 Nov 2009 9:27] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[11 Nov 2009 6:54] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 7:03] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092355-jzukkw6wnd7hmgrj) (merge vers: 5.5.0-beta) (pib:13)
[22 Nov 2009 0:52] Paul Dubois
Noted in 5.0.88, 5.1.41, 5.5.0, 6.0.14 changelogs.

Some queries with nested outer joins could lead to crashes or
incorrect results because an internal data structure was handled 
improperly.
[18 Dec 2009 10:28] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:44] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:00] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:14] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)