Bug #47761 crash when killing a query during subquery execution...
Submitted: 1 Oct 2009 14:03 Modified: 20 Jun 2010 22:30
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.85, 5.1.39, 5.0, 5.1, next bzr OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: crash, crash, KILL, KILL
Triage: Triaged: D1 (Critical)

[1 Oct 2009 14:03] Shane Bester
Description:
got this crash when killing a nasty query during explain:

mysqld-debug.exe!???                                                           
mysqld-debug.exe!sub_select()[sql_select.cc:11134]                             
mysqld-debug.exe!do_select()[sql_select.cc:10891]                              
mysqld-debug.exe!JOIN::exec()[sql_select.cc:2209]                              
mysqld-debug.exe!subselect_single_select_engine::exec()[item_subselect.cc:1965]
mysqld-debug.exe!Item_subselect::exec()[item_subselect.cc:263]                 
mysqld-debug.exe!Item_singlerow_subselect::val_str()[item_subselect.cc:581]    
mysqld-debug.exe!Item::save_in_field()[item.cc:5115]                           
mysqld-debug.exe!Item::save_in_field_no_warnings()[item.cc:989]                
mysqld-debug.exe!get_mm_leaf()[opt_range.cc:5817]                              
mysqld-debug.exe!get_mm_parts()[opt_range.cc:5624]                             
mysqld-debug.exe!get_ne_mm_tree()[opt_range.cc:5068]                           
mysqld-debug.exe!get_func_mm_tree()[opt_range.cc:5101]                         
mysqld-debug.exe!get_full_func_mm_tree()[opt_range.cc:5406]                    
mysqld-debug.exe!get_mm_tree()[opt_range.cc:5592]                              
mysqld-debug.exe!get_mm_tree()[opt_range.cc:5448]                              
mysqld-debug.exe!get_mm_tree()[opt_range.cc:5465]                              
mysqld-debug.exe!SQL_SELECT::test_quick_select()[opt_range.cc:2331]            
mysqld-debug.exe!get_quick_record_count()[sql_select.cc:2446]                  
mysqld-debug.exe!make_join_statistics()[sql_select.cc:2855]                    
mysqld-debug.exe!JOIN::optimize()[sql_select.cc:955]                           
mysqld-debug.exe!mysql_select()[sql_select.cc:2384]                            
mysqld-debug.exe!mysql_explain_union()[sql_select.cc:16421]                    
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:4989]                    
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2206]                    
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5935]                              
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1215]                         
mysqld-debug.exe!do_command()[sql_parse.cc:854]                                
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]                  
mysqld-debug.exe!pthread_start()[my_winthread.c:85]                            
mysqld-debug.exe!_callthreadstart()[thread.c:295]                              
mysqld-debug.exe!_threadstart()[thread.c:277]                                  
kernel32.dll!BaseThreadStart()                                                 

How to repeat:
uploading testcase later.
[1 Oct 2009 15:52] Shane Bester
some further information.

Attachment: bug47761_valgrind_output_query.5.1.39.txt (text/plain), 5.49 KiB.

[2 Oct 2009 5:32] Shane Bester
The bug is repeatable as follows:
#1) create the table

drop table if exists `t1`;
create table `t1` (`c4` tinyint,key `k4` (`c4`)) engine=myisam;
insert into `t1` values (0),(127);

#2) run the following query over and over, and kill it over and over from another thread:

select 1 from t1, t1 as `a_t1_972` where `a_t1_972`.`c4` =1 or `a_t1_972`.`c4` <> ( select length(`a_tbl463_410`.`c4`) from t1,t1 as `a_tbl463_410` where `a_tbl463_410`.`c4` >= @@global.slave_net_timeout )

It's timing related so if a crash doesn't happen in few minutes then
add more threads..
[2 Oct 2009 5:44] Shane Bester
testcase. leaving running a few minutes....

Attachment: bug47761.c (text/x-csrc), 7.24 KiB.

[2 Oct 2009 6:53] Shane Bester
if you have troubles repeating a crash then:

o) use release binary
o) use faster server
o) start mysqld with --no-default --skip-grant-tables --skip-name-resolve
o) use blade12.mysql.com, which can repeat the crash in 3 seconds.
[2 Oct 2009 7:20] Sveta Smirnova
Thank you for the report.

Verified as described.
[9 Mar 2010 12:52] 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/102702

3369 Davi Arnaut	2010-03-09
      Bug#47761: crash when killing a query during subquery execution...
      
      The problem was that killing a query during the optimization
      phase of a subselect would lead to crashes. The root of the
      problem is that the subselect execution engine ignores failures
      (eg: killed) during the optimization phase (JOIN::optimize),
      leading to a crash once the subquery is executed due to
      partially initialized structures (in this case a join tab).
      
      The optimal solution would be to cleanup certain optimizer
      structures if the optimization phase fails, but currently
      there is no infrastructure to properly to track and cleanup
      the structures. To workaround the whole problem one somewhat
      good solution is to avoid executing a subselect if the query
      has been killed. Cutting short any problems caused by failures
      during the optimization phase.
     @ sql/item_subselect.cc
        Do not execute a subselect if the session or query has been killed.
[17 Mar 2010 13:34] Davi Arnaut
Queued to mysql-5.1-bugteam
[26 Mar 2010 8:22] Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:26] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:30] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 7:58] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:davi.arnaut@sun.com-20100309125156-z2c4uyqque49v61k) (merge vers: 5.1.46) (pib:16)
[12 Apr 2010 22:55] Paul Dubois
Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

Killing a query during the optimization phase of a subquery could
cause a server crash.
[17 Jun 2010 11:49] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:26] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:14] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)