Bug #50052 --optimizer-search-depth=63 makes mysql server crash
Submitted: 4 Jan 2010 14:27 Modified: 2 Mar 2010 9:26
Reporter: Guilhem Bichot Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Linux
Assigned to: Roy Lyseng CPU Architecture:Any
Tags: regression

[4 Jan 2010 14:27] Guilhem Bichot
I see some crashes of mysqld when run with --optimizer-search-depth=63 which is an option advising to not use the greedy join optimizer but the old 4.1 join optimizer.
Using 6.0-codebase-bugfixing revision-id:oystein.grovlen@sun.com-20091229132523-qv9gud3wyrb1ps4a.

Running this:
./mtr  --retry-failure=0 --mem --parallel=2 --force --max-test-fail=0 --mysqld=--optimizer-search-depth=63 --mysqld=--binlog-format=mixed
leads to the crashes below.

mysqltest: At line 322: query 'select s1 from t1 where s1 in (select version from
information_schema.tables) union select version from
information_schema.tables' failed: 2013: Lost connection to MySQL server during query
#2  0x00000000006f27c8 in handle_segfault (sig=11) at mysqld.cc:2606
#3  <signal handler called>
#4  0x00000000007a1612 in optimize_semijoin_nests (join=0x7fea400278c8, all_table_map=3) at sql_select.cc:4655
#5  0x00000000007a3937 in make_join_statistics (join=0x7fea400278c8, tables_arg=0x7fea40063fe8, conds=0x7fea40061dc8, keyuse_array=0x7fea4002d488) at sql_select.cc:4521
#6  0x00000000007a6b76 in JOIN::optimize (this=0x7fea400278c8) at sql_select.cc:1621
#7  0x00000000008e30bc in st_select_lex_unit::exec (this=0x2ab3ab0) at sql_union.cc:503
#8  0x00000000008e4aba in mysql_union (thd=0x2ab21b0, lex=0x2ab3a10, result=0x7fea4006e8d8, unit=0x2ab3ab0, setup_tables_done_option=0) at sql_union.cc:34
#9  0x00000000007b0f12 in handle_select (thd=0x2ab21b0, lex=0x2ab3a10, result=0x7fea4006e8d8, setup_tables_done_option=0) at sql_select.cc:282

main.partition main.group_by main.insert_select main.subselect main.subselect3 main.subselect3_jcl6 main.subselect_sj main.subselect_sj_jcl6 main.subselect_sj2 main.subselect_sj2_jcl6 crash in the same line as information_schema (sql_select.cc:4655). I have not checked if the upper stack frames are the same though; maybe those are multiple bugs.

main.subselect4 crashes with a different backtrace:
mysqltest: At line 215: query 'EXPLAIN SELECT EMPNAME
WHERE PTYPE = 'Design'))' failed: 2013: Lost connection to MySQL server during query
#2  0x00000000006f27c8 in handle_segfault (sig=11) at mysqld.cc:2606
#3  <signal handler called>
#4  0x000000000079725a in setup_sj_materialization (tab=0x34c96c8) at sql_select.cc:9843
#5  0x00000000007a597e in make_join_readinfo (join=0x34b2d18, options=4, no_jbuf_after=3) at sql_select.cc:10377
#6  0x00000000007a80a6 in JOIN::optimize (this=0x34b2d18) at sql_select.cc:1960
#7  0x00000000007ab583 in mysql_select (thd=0x32f5150, rref_pointer_array=0x32f7308, tables=0x333d818, wild_num=0, fields=@0x32f7200, conds=0x34ae438, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147748612, result=0x3376c88, unit=0x32f6a50, select_lex=0x32f70f0) at sql_select.cc:3123
#8  0x00000000007abafe in mysql_explain_union (thd=0x32f5150, unit=0x32f6a50, result=0x3376c88) at sql_select.cc:22229

Same failures in row-based and ps-protocol mode.
I ran the failing tests above against 5.1 and next-mr-bugfixing, and didn't see any problem, so it looks 6.0-specific.

How to repeat:
./mtr  --retry-failure=0 --mem --parallel=2 --force --max-test-fail=0 --mysqld=--optimizer-search-depth=63 --mysqld=--binlog-format=mixed

Suggested fix:
--optimizer-search-depth=63 forces the use of the find_best() join optimizer which has this comment in code:
  - TODO: this function is here only temporarily until 'greedy_search' is
  tested and accepted.
Greedy search was introduced in 5.0 and is default since then. So we could call it tested and accepted, and just remove find_best(), if we think no user depends on that.
[4 Jan 2010 16:25] Valeriy Kravchuk
With current mysql-6.0-codebase on Mac OS X I've got the following tests failed when run as explained here:

... main.subselect main.subselect3 main.subselect3_jcl6 main.subselect4 main.subselect_sj main.subselect_sj_jcl6 sys_vars.join_buffer_size_basic_32 sys_vars.optimizer_search_depth_basic sys_vars.query_cache_size_basic_32 sys_vars.thread_stack_basic sys_vars.transaction_prealloc_size_basic_32 main.bug48315 main.subselect_sj2 main.subselect_sj2_jcl6 main.implicit_commit

The most common stack trace looks a bit different though:

stack_bottom = 0xb0594f64 thread_stack 0x30c00
0   mysqld                              0x005be77b my_print_stacktrace + 45
1   mysqld                              0x00101322 handle_segfault + 918
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x001b70ed _Z23is_indexed_agg_distinctP4JOINP4ListI10Item_fieldE + 8785
5   mysqld                              0x001b7c87 _ZN4JOIN8optimizeEv + 2559
6   mysqld                              0x001bffe0 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 674
7   mysqld                              0x001ba690 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 928
8   mysqld                              0x00112e95 _Z20prepare_schema_tableP3THDP3LEXP11Table_ident18enum_schema_tables + 1555
9   mysqld                              0x00119550 _Z21mysql_execute_commandP3THD + 3158
10  mysqld                              0x00122f74 _Z11mysql_parseP3THDPKcjPS2_ + 748
11  mysqld                              0x00123ba8 _Z16dispatch_command19enum_server_commandP3THDPcj + 2672
12  mysqld                              0x00125071 _Z10do_commandP3THD + 621
13  mysqld                              0x0011078f handle_one_connection + 443
14  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
15  libSystem.B.dylib                   0x9400bf52 thread_start + 34

But still we obviously have a problem with optimizer in 6.0.14-bzr now.
[7 Jan 2010 13:48] Guilhem Bichot
I ran the full test suite against next-mr-bugfixing, no bug there. It's 6.0-specific.
[28 Jan 2010 8:46] Roy Lyseng
Created WL#5252 to deprecate this feature in a later version. If deprecation is successful, this bug will be closed as "will not fix".
[2 Mar 2010 9:26] Roy Lyseng
Deprecation of feature pushed into mysql-next-mr-bugfixing as WL#5252.
Removal of feature will be followed up through WL#5265.
Hence, there is no longer a need to fix this problem.
[26 Apr 2010 14:46] Guilhem Bichot
another testcase with alfranio.correia@sun.com-20100426092226-bnkzpjs8uqkl7l16 :

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

# Multi-column sj-materialization with lookups
create table t1 (a int, b int);
insert into t1 select a,a from t0;
create table t2 (a int, b int); 
insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B;

set @@optimizer_search_depth=63;
explain select * from t1 where (a,b) in (select a,b from t2);

drop table t0, t1, t2;
(inspired from subselect3.test)