Bug #51494 crash with join, explain and 'sounds like' operator
Submitted: 25 Feb 2010 9:33 Modified: 20 Jun 2010 17:52
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.34,5.1.45, 5.5.99-m3 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: crash, explain, explain, regression, sounds like, valgrind
Triage: Triaged: D1 (Critical)

[25 Feb 2010 9:33] Shane Bester
Description:
5.1.44 stack trace:
mysqld.exe!my_mb_wc_bin()[ctype-bin.c:253]                                
mysqld.exe!Item_func_soundex::val_str()[item_strfunc.cc:1949]             
mysqld.exe!Arg_comparator::compare_binary_string()[item_cmpfunc.cc:1300]  
mysqld.exe!Item_func_eq::val_int()[item_cmpfunc.cc:1858]                  
mysqld.exe!Item::val_bool()[item.cc:184]                                  
mysqld.exe!Item_cond_and::val_int()[item_cmpfunc.cc:4388]                 
mysqld.exe!make_join_select()[sql_select.cc:6160]                         
mysqld.exe!JOIN::optimize()[sql_select.cc:1095]                           
mysqld.exe!mysql_select()[sql_select.cc:2445]                             
mysqld.exe!mysql_explain_union()[sql_select.cc:16617]                     
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5031]                     
mysqld.exe!mysql_execute_command()[sql_parse.cc:2248]                     
mysqld.exe!mysql_parse()[sql_parse.cc:5975]                               
mysqld.exe!dispatch_command()[sql_parse.cc:1235]                          
mysqld.exe!do_command()[sql_parse.cc:874]                                 
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()                                            

Maybe not crash, but numerous valgrind errors are seen.

How to repeat:
drop table if exists `t1`,`t2`;
create table `t1` (`a` int) engine=myisam;
insert into `t1` values(1);

create table `t2` (`b` int not null,`c` int,`d` int,`e` blob not null,
key `idx0` (`d`,`c`)) engine=mrg_myisam;

explain select 1 from `t1` natural right join 
`t2` where `b` sounds like `e` and `d`=1;
[25 Feb 2010 9:37] Shane Bester
bug51494_5.1.44_valgrind_errors

Attachment: bug51494_5.1.44_valgrind_errors.txt (text/plain), 17.66 KiB.

[25 Feb 2010 9:42] Shane Bester
5.0 is not affected.
[25 Feb 2010 10:45] Valeriy Kravchuk
Crashed with 5.1.45 under valgrind:

...
==6856==  Address 0xa5a5a5a5 is not stack'd, malloc'd or (recently) free'd
==6856==
100602 17:20:12 - 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=8384512
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 = 337727 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x6791718
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...
stack_bottom = 0x776c430 thread_stack 0x30000
libexec/mysqld(my_print_stacktrace+0x29)[0x86e4310]
libexec/mysqld(handle_segfault+0x253)[0x82939df]
/lib/tls/libpthread.so.0[0x4040778]
libexec/mysqld(_ZN17Item_func_soundex7val_strEP6String+0x153)[0x8221a41]
libexec/mysqld(_ZN14Arg_comparator21compare_binary_stringEv+0x5d)[0x820a917]
libexec/mysqld(_ZN14Arg_comparator7compareEv+0x4a)[0x81eebb4]
libexec/mysqld(_ZN12Item_func_eq7val_intEv+0x41)[0x820c279]
libexec/mysqld(_ZN4Item8val_boolEv+0x43)[0x81c7a0d]
libexec/mysqld(_ZN13Item_cond_and7val_intEv+0x7f)[0x8214203]
libexec/mysqld[0x831a7cd]
libexec/mysqld(_ZN4JOIN8optimizeEv+0x1391)[0x830d08f]
libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x2e3)[0x831193b]
libexec/mysqld(_Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result+0x31c)[0x83343f8]
libexec/mysqld[0x82acf71]
libexec/mysqld(_Z21mysql_execute_commandP3THD+0x792)[0x82a512a]
libexec/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x206)[0x82af254]
libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xa37)[0x82a2f33]
libexec/mysqld(_Z10do_commandP3THD+0x260)[0x82a21c2]
libexec/mysqld(handle_one_connection+0x14e)[0x82a05f8]
/lib/tls/libpthread.so.0[0x403aaa7]
/lib/tls/libc.so.6(__clone+0x5e)[0x4180c2e]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x67975a0 is an invalid pointer
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.
==6856==
==6856== HEAP SUMMARY:
==6856==     in use at exit: 28,322,246 bytes in 32,203 blocks
==6856==   total heap usage: 33,012 allocs, 809 frees, 32,630,395 bytes allocated
==6856==
==6856== LEAK SUMMARY:
==6856==    definitely lost: 0 bytes in 0 blocks
==6856==    indirectly lost: 0 bytes in 0 blocks
==6856==      possibly lost: 28,135,654 bytes in 31,464 blocks
==6856==    still reachable: 186,592 bytes in 739 blocks
==6856==         suppressed: 0 bytes in 0 blocks
==6856== Rerun with --leak-check=full to see details of leaked memory
==6856==
==6856== For counts of detected and suppressed errors, rerun with: -v
==6856== Use --track-origins=yes to see where uninitialised values come from
==6856== ERROR SUMMARY: 15 errors from 13 contexts (suppressed: 23 from 6)
[26 Feb 2010 9:01] Valeriy Kravchuk
Bug #51524 was marked as a duplicate of this one.
[1 Mar 2010 14:46] 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/101892

3358 Sergey Glukhov	2010-03-01
      Bug#51494 crash with join, explain and 'sounds like' operator
      In case of a merge table without UNION clause the table is
      considered as a const table but table_flags() does not return
      HA_STATS_RECORDS_IS_EXACT. This flag is used in
      make_join_statistics for const table detection and
      later for const elements substitution in condition.
      The substitution does not happen and it leads to incorrect
      calculation of const condition.
      The fix is to return HA_STATS_RECORDS_IS_EXACT flag
      for mrg tables without UNION clause.
     @ mysql-test/r/merge.result
        test case
     @ mysql-test/t/merge.test
        test case
     @ storage/myisammrg/ha_myisammrg.h
        return HA_STATS_RECORDS_IS_EXACT for
        mrg tables without UNION clause.
[4 Mar 2010 15:23] 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/102348

3358 Sergey Glukhov	2010-03-04
      Bug#51494 crash with join, explain and 'sounds like' operator
      The crash happens because of discrepancy between values of
      conts_tables and join->const_table_map(make_join_statisctics).
      Calculation of conts_tables used condition with
      HA_STATS_RECORDS_IS_EXACT flag check. Calculation of
      join->const_table_map does not use this flag check.
      In case of MERGE table without union with index
      the table does not become const table and
      thus join_read_const_table() is not called
      for the table. join->const_table_map supposes
      this table is const and later in make_join_select
      this table is used for making&calculation const
      condition. As table record buffer is not populated
      it leads to crash.
      The fix is adding a check if an engine supports
      HA_STATS_RECORDS_IS_EXACT flag before updating
      join->const_table_map.
     @ mysql-test/r/merge.result
        test case
     @ mysql-test/t/merge.test
        test case
     @ sql/sql_select.cc
        adding a check if an engine supports
        HA_STATS_RECORDS_IS_EXACT flag before updating
        join->const_table_map.
[19 Mar 2010 6:00] 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/103755

3408 Sergey Glukhov	2010-03-19
      Bug#51494 crash with join, explain and 'sounds like' operator
      The crash happens because of discrepancy between values of
      conts_tables and join->const_table_map(make_join_statisctics).
      Calculation of conts_tables used condition with
      HA_STATS_RECORDS_IS_EXACT flag check. Calculation of
      join->const_table_map does not use this flag check.
      In case of MERGE table without union with index
      the table does not become const table and
      thus join_read_const_table() is not called
      for the table. join->const_table_map supposes
      this table is const and later in make_join_select
      this table is used for making&calculation const
      condition. As table record buffer is not populated
      it leads to crash.
      The fix is adding a check if an engine supports
      HA_STATS_RECORDS_IS_EXACT flag before updating
      join->const_table_map.
     @ mysql-test/r/merge.result
        test result
     @ mysql-test/t/merge.test
        test case
     @ sql/sql_select.cc
        adding a check if an engine supports
        HA_STATS_RECORDS_IS_EXACT flag before updating
        join->const_table_map.
[26 Mar 2010 8:21] 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:25] 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 8:00] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:john.embretsen@sun.com-20100322090656-w4ixy7p67fb3vr29) (merge vers: 5.1.46) (pib:16)
[16 Apr 2010 17:02] Paul Dubois
Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

The server crashed when the optimizer attempted to determine
constant tables but a table storage engine did not support
exact record count.
[17 Jun 2010 12:00] 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:41] 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:27] 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)