| Bug #46744 | Crash in optimize_semijoin_nests on empty view with limit and procedure | ||
|---|---|---|---|
| Submitted: | 16 Aug 2009 2:26 | Modified: | 22 Nov 2010 1:24 |
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 6.0 | OS: | Any (Windows 2003, Linux) |
| Assigned to: | Tor Didriksen | CPU Architecture: | Any |
| Tags: | optimizer_switch, semijoin, SP, subquery, VIEW | ||
[16 Aug 2009 4:49]
MySQL Verification Team
Thank you for the bug report. Verified as described: Microsoft Windows [Version 6.0.6002] Copyright (c) 2006 Microsoft Corporation. All rights reserved. c:\dbs>54 c:\dbs>c:\dbs\5.4\bin\mysqld --defaults-file=c:\dbs\5.4\my.ini --standalone --console 090816 1:43:24 [Note] Plugin 'FEDERATED' is disabled. 090816 1:43:25 InnoDB: Started; log sequence number 0 46409 090816 1:43:26 [Note] Event Scheduler: Loaded 0 events 090816 1:43:26 [Note] c:\dbs\5.4\bin\mysqld: ready for connections. Version: '5.4.4-alpha-Win X64' socket: '' port: 3540 Source distribution 090816 1:44:23 - 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=8384512 read_buffer_size=131072 max_used_connections=1 max_threads=151 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338189 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x37add70 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... 000000014018D6AE mysqld.exe!optimize_semijoin_nests()[sql_select.cc:4575] 0000000140190AEB mysqld.exe!make_join_statistics()[sql_select.cc:4459] 00000001401910E9 mysqld.exe!JOIN::optimize()[sql_select.cc:1610] 00000001401926E9 mysqld.exe!mysql_select()[sql_select.cc:3077] 0000000140192B56 mysqld.exe!handle_select()[sql_select.cc:306] 0000000140099FD6 mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4928] 000000014009DA56 mysqld.exe!mysql_execute_command()[sql_parse.cc:2113] 00000001401AEF68 mysqld.exe!sp_instr_stmt::exec_core()[sp_head.cc:2931] 00000001401B19C5 mysqld.exe!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:2754] 00000001401B3116 mysqld.exe!sp_instr_stmt::execute()[sp_head.cc:2869] 00000001401B38B6 mysqld.exe!sp_head::execute()[sp_head.cc:1251] 00000001401B4A3C mysqld.exe!sp_head::execute_procedure()[sp_head.cc:1991] 00000001400A2120 mysqld.exe!mysql_execute_command()[sql_parse.cc:4389] 00000001400A2FE5 mysqld.exe!mysql_parse()[sql_parse.cc:5947] 00000001400A3AF3 mysqld.exe!dispatch_command()[sql_parse.cc:1064] 00000001400A487C mysqld.exe!do_command()[sql_parse.cc:744] 00000001400C6D22 mysqld.exe!handle_one_connection()[sql_connect.cc:1158] 00000001403BA90E mysqld.exe!pthread_start()[my_winthread.c:63] 000000014045EFC7 mysqld.exe!_callthreadstartex()[threadex.c:348] 000000014045F09F mysqld.exe!_threadstartex()[threadex.c:326] 0000000076C6C3BD kernel32.dll!BaseThreadInitThunk() 0000000077364581 ntdll.dll!RtlUserThreadStart() Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 00000000037D94D8=select COUNT(*) FROM v_tb1 WHERE f1 IN (SELECT f1 FROM tb2 WHERE f1 = ANY (SELECT f1 FROM v_tb1)) 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. c:\dbs>
[30 Sep 2009 13:57]
Roy Lyseng
Brief check on mysql-6.0-codebase-bugfixing: This problem is not reproducible on my OpenSolaris machine.
[2 Nov 2009 10:25]
Roy Lyseng
Previous analysis was too fast: the problem still appears. It may be a platform issue, as it does not appear on 64 bit Solaris, but it does on 32 bit Linux. The issue must be investigated further.
[13 Nov 2009 12:16]
Øystein Grøvlen
This isssue is not the same as Bug#46692 and its duplicates (e.g., Bug#45933). It is NOT fixed by the fix to Bug#46692. Note also that the problem here happens when semijoin optimization is turned off.
[19 Nov 2009 13:10]
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/90960 3718 Tor Didriksen 2009-11-19 Bug #46744 Crash in optimize_semijoin_nests on empty view with limit and procedure. Skip optimize_semijoin_nests() if optimizer_switch = 'semijoin=off'; @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/r/subselect_sj_jcl6.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc Skip optimize_semijoin_nests() if OPTIMIZER_SWITCH_SEMIJOIN == false.
[20 Nov 2009 14:11]
Roy Lyseng
The bug fix solves the current problem, but while reviewing I encountered another problem: If optimizer_switch is set back to 'semijoin=on', a subsequent execution will again cause a core dump. To me, this indicates that changing optimizer switches after a statement has been prepared should not have any effect on that statement, only on new statements. The functionality is questionable (did the user mean the setting that existed when the statement was prepared or when it was executed?), and keeping track of all the combinations of optimizer switches makes the risk for bugs too high.
[20 Nov 2009 14:11]
Roy Lyseng
The bug fix solves the current problem, but while reviewing I encountered another problem: If optimizer_switch is set back to 'semijoin=on', a subsequent execution will again cause a core dump. To me, this indicates that changing optimizer switches after a statement has been prepared should not have any effect on that statement, only on new statements. The functionality is questionable (did the user mean the setting that existed when the statement was prepared or when it was executed?), and keeping track of all the combinations of optimizer switches makes the risk for bugs too high.
[23 Nov 2009 10:27]
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/91275 3722 Tor Didriksen 2009-11-23 Bug #46744 Crash in optimize_semijoin_nests on empty view with limit and procedure. Skip optimize_semijoin_nests() if optimizer_switch = 'semijoin=off'; @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/r/subselect_sj_jcl6.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc Skip optimize_semijoin_nests() if OPTIMIZER_SWITCH_SEMIJOIN == false.
[23 Nov 2009 11:22]
Tor Didriksen
pushed to bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/ revision-id: tor.didriksen@sun.com-20091123102734-nlord11m6o0rw2vu
[11 Dec 2009 6:02]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[18 Dec 2009 8:23]
Tor Didriksen
Roy's comment on Nov 10 describes the real problem: we do not support toggling of optimizer switches between different invokations of the same stored procedure.
[14 Apr 2010 12:03]
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/105636 3073 Tor Didriksen 2010-04-14 Bug #46744 Crash in optimize_semijoin_nests on empty view with limit and procedure. Backport of tor.didriksen@sun.com-20091123102734-nlord11m6o0rw2vu Skip optimize_semijoin_nests() if optimizer_switch = 'semijoin=off'; @ mysql-test/r/subselect_sj.result Add test case. @ mysql-test/t/subselect_sj.test Add test case. @ sql/sql_select.cc Skip optimize_semijoin_nests() if OPTIMIZER_SWITCH_SEMIJOIN == false.
[16 Aug 2010 6:36]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[2 Oct 2010 18:16]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:23]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:24]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.
Description: Stack trace on Windows: mysqld.exe!optimize_semijoin_nests()[sql_select.cc:4575] mysqld.exe!make_join_statistics()[sql_select.cc:4459] mysqld.exe!JOIN::optimize()[sql_select.cc:1610] mysqld.exe!mysql_select()[sql_select.cc:3077] mysqld.exe!handle_select()[sql_select.cc:306] mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4927] mysqld.exe!mysql_execute_command()[sql_parse.cc:2112] mysqld.exe!sp_instr_stmt::exec_core()[sp_head.cc:2931] mysqld.exe!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:2754] mysqld.exe!sp_instr_stmt::execute()[sp_head.cc:2869] mysqld.exe!sp_head::execute()[sp_head.cc:1249] mysqld.exe!sp_head::execute_procedure()[sp_head.cc:1991] mysqld.exe!mysql_execute_command()[sql_parse.cc:4387] mysqld.exe!mysql_parse()[sql_parse.cc:5942] mysqld.exe!dispatch_command()[sql_parse.cc:1064] mysqld.exe!do_command()[sql_parse.cc:744] mysqld.exe!handle_one_connection()[sql_connect.cc:1158] mysqld.exe!pthread_start()[my_winthread.c:61] mysqld.exe!_callthreadstartex()[threadex.c:348] mysqld.exe!_threadstartex()[threadex.c:326] on Linux: #0 0x00110416 in __kernel_vsyscall () #1 0x00d39c88 in pthread_kill () from /lib/libpthread.so.0 #2 0x0854270c in my_write_core (sig=11) at stacktrace.c:309 #3 0x082212ca in handle_segfault (sig=11) at mysqld.cc:2734 #4 <signal handler called> #5 0x082a6fe6 in make_join_statistics (join=0x98eb728, tables_arg=0x98d0530, conds=0x98cc770, keyuse_array=0x98f0454) at sql_select.cc:4575 #6 0x082a8d1c in JOIN::optimize (this=0x98ccd48) at sql_select.cc:1609 #7 0x082ac5e2 in mysql_select (thd=0x98b0630, rref_pointer_array=0x98cfe9c, tables=0x98d0530, wild_num=0, fields=@0x98cfe2c, conds=0x98cbb70, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147765760, result=0x98cc740, unit=0x98cfafc, select_lex=0x98cfd98) at sql_select.cc:3077 #8 0x082b263e in handle_select (thd=0x98b0630, lex=0x98cfaa0, result=0x98cc740, setup_tables_done_option=0) at sql_select.cc:306 #9 0x0822e96a in execute_sqlcom_select (thd=0x98b0630, all_tables=0x98d0530) at sql_parse.cc:4927 #10 0x08234183 in mysql_execute_command (thd=0x98b0630) at sql_parse.cc:2112 #11 0x083a3361 in sp_instr_stmt::exec_core (this=0x98d84d0, thd=0x98b0630, nextp=0xb2bde920) at sp_head.cc:2929 #12 0x083a794e in sp_lex_keeper::reset_lex_and_exec_core (this=0x98d84f4, thd=0x98b0630, nextp=0xb2bde920, open_tables=false, instr=0x98d84d0) at sp_head.cc:2754 #13 0x083a858a in sp_instr_stmt::execute (this=0x98d84d0, thd=0x98b0630, nextp=0xb2bde920) at sp_head.cc:2867 #14 0x083a5c72 in sp_head::execute (this=0x98cf5a0, thd=0x98b0630) at sp_head.cc:1249 #15 0x083a64be in sp_head::execute_procedure (this=0x98cf5a0, thd=0x98b0630, args=0x98b1adc) at sp_head.cc:1991 #16 0x08234b21 in mysql_execute_command (thd=0x98b0630) at sql_parse.cc:4385 #17 0x08239277 in mysql_parse (thd=0x98b0630, inBuf=0x98b2100 "CALL run_main_query()", length=21, found_semicolon=0xb2bdff0c) at sql_parse.cc:5942 #18 0x0823a3bf in dispatch_command (command=COM_QUERY, thd=0x98b0630, packet=0x98b8661 "CALL run_main_query()", packet_length=21) at sql_parse.cc:1062 #19 0x0823b193 in do_command (thd=0x98b0630) at sql_parse.cc:744 #20 0x0822bc86 in handle_one_connection (arg=0x98b0630) at sql_connect.cc:1158 #21 0x00d3532f in start_thread () from /lib/libpthread.so.0 #22 0x05ba720e in clone () from /lib/libc.so.6 In the provided test case, I could not get rid of the procedure (or function), the view, the limit clause in the view, and the first call of the procedure (with semijoin ON). Also, it only worked if the table on which the view is based was empty. There is also bug#45933 about a crash in optimize_semijoin_nests, but the stack trace looks somewhat different, so I'm not sure if this one is a duplicate. How to repeat: drop database if exists systest; create database systest; use systest; CREATE TABLE `tb1` ( `f1` int ); CREATE TABLE `tb2` ( `f1` int ); insert into tb2 values ( 5 ),( 7 ); CREATE OR REPLACE VIEW v_tb1 AS SELECT * FROM tb1 LIMIT 2; create procedure run_main_query() select COUNT(*) FROM v_tb1 WHERE f1 IN (SELECT f1 FROM tb2 WHERE f1 = ANY (SELECT f1 FROM v_tb1)); SET SESSION optimizer_switch = 'semijoin=on'; CALL run_main_query(); SET SESSION optimizer_switch = 'semijoin=off'; CALL run_main_query(); # ERROR 2013 (HY000): Lost connection to MySQL server during query