| 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 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)
Description: 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. main.information_schema: 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 FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 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.