| Bug #52711 | Segfault when doing EXPLAIN SELECT with union...order by (select... where...) | ||
|---|---|---|---|
| Submitted: | 9 Apr 2010 9:08 | Modified: | 14 Apr 2011 13:16 |
| Reporter: | Bjørn Munch | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.91, 5.1.37, 5.1.47 | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | explain union | ||
[9 Apr 2010 9:14]
Valeriy Kravchuk
Verified just as described with recent 5.1.47 from bzr on Mac OS X:
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 = 0xb026af64 thread_stack 0x30000
0 mysqld 0x0057ff2e my_print_stacktrace + 44
1 mysqld 0x0010197c handle_segfault + 836
2 libSystem.B.dylib 0x940472bb _sigtramp + 43
3 ??? 0xffffffff 0x0 + 4294967295
4 mysqld 0x000e4b3a _ZN26select_singlerow_subselect9send_dataER4ListI4ItemE + 372
5 mysqld 0x001aa9d9 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 11085
6 mysqld 0x001ab749 _ZN4JOIN4execEv + 2459
7 mysqld 0x000a8b16 _ZN30subselect_single_select_engine4execEv + 1358
8 mysqld 0x000add73 _ZN14Item_subselect4execEv + 129
9 mysqld 0x000a617e _ZN24Item_singlerow_subselect7val_strEP6String + 26
10 mysqld 0x00194697 _Z16create_tmp_tableP3THDP15TMP_TABLE_PARAMR4ListI4ItemEP8st_orderbbyyPc + 10999
11 mysqld 0x001a480e _ZN4JOIN8optimizeEv + 5678
12 mysqld 0x001a7c8d _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 741
13 mysqld 0x002da371 _ZN18st_select_lex_unit4execEv + 2771
14 mysqld 0x001a8112 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 646
15 mysqld 0x001132b2 _Z15update_precheckP3THDP10TABLE_LIST + 690
16 mysqld 0x00115ab6 _Z21mysql_execute_commandP3THD + 2936
17 mysqld 0x0011f477 _Z11mysql_parseP3THDPKcjPS2_ + 625
18 mysqld 0x0012023b _Z16dispatch_command19enum_server_commandP3THDPcj + 3061
19 mysqld 0x001215dc _Z10do_commandP3THD + 666
20 mysqld 0x0010c0a4 handle_one_connection + 372
21 libSystem.B.dylib 0x9400c095 _pthread_start + 321
22 libSystem.B.dylib 0x9400bf52 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1075828 = EXPLAIN SELECT * FROM t2 UNION SELECT * FROM t2
ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE))
thd->thread_id=2
[9 Apr 2010 18:00]
Valeriy Kravchuk
Both 5.0.91 and 5.1.37 crashed even with the first EXPLAIN: 77-52-4-109:mysql-5.1.37-osx10.5-x86_64 openxs$ sudo tail -100 data/77-52-46-29.dialup.umc.net.ua.err 100409 20:56:04 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.1.37-osx10.5-x86_64/data 100409 20:56:05 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-5.1.37-osx10.5-x86_64/data/ is case insensitive 100409 20:56:05 [Note] Plugin 'FEDERATED' is disabled. 100409 20:56:05 InnoDB: Started; log sequence number 0 57901 100409 20:56:05 [Note] Event Scheduler: Loaded 0 events 100409 20:56:05 [Note] /usr/local/mysql-5.1.37-osx10.5-x86_64/bin/mysqld: ready for connections. Version: '5.1.37' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) 100409 20:56:28 - 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 = 338286 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x101035600 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 = 0x102f52ed0 thread_stack 0x40000 0 mysqld 0x00000000002c7f9a my_print_stacktrace + 57 1 mysqld 0x00000000000918b2 handle_segfault + 548 2 libSystem.B.dylib 0x00000000805ed3aa _sigtramp + 26 3 ??? 0x0000000000000000 0x0 + 0 4 mysqld 0x00000000000e30fd _ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit + 907 5 mysqld 0x00000000000ed6e8 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 392 6 mysqld 0x00000000000eda06 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 496 7 mysqld 0x00000000000ef201 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 6635 8 mysqld 0x00000000000ebcdc _ZN4JOIN4execEv + 1346 9 mysqld 0x000000000017bb21 _ZN18st_select_lex_unit4execEv + 531 10 mysqld 0x00000000000ed945 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 303 11 mysqld 0x00000000000996ee _ZN13st_select_lex18convert_right_joinEv + 374 12 mysqld 0x000000000009a1f8 _Z21mysql_execute_commandP3THD + 1888 13 mysqld 0x000000000009f5bf _Z11mysql_parseP3THDPKcjPS2_ + 295 14 mysqld 0x00000000000a0322 _Z16dispatch_command19enum_server_commandP3THDPcj + 3270 15 mysqld 0x00000000000a0da6 _Z10do_commandP3THD + 266 16 mysqld 0x000000000009554b handle_one_connection + 2397 17 libSystem.B.dylib 0x00000000805b5dcb _pthread_start + 316 18 libSystem.B.dylib 0x00000000805b5c8d thread_start + 13 Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x101081a10 = EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY (SELECT a FROM t2 WHERE b = 12)
[9 Apr 2010 18:02]
Valeriy Kravchuk
Also when first EXPLAIN crashes FULLTEXT index does NOT matter: 77-52-4-109:mysql-5.1.37-osx10.5-x86_64 openxs$ bin/mysql -uroot testReading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.37 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter table t1 drop key a; Query OK, 2 rows affected (0.40 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY (SELECT a FROM t2 WHERE b = 12); ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> 100409 21:00:50 mysqld_safe mysqld restarted
[13 Apr 2010 17:42]
MySQL Verification Team
The error is in row[1], for this test case, but row[1] is not NULL, it is just not initialized.
[15 Apr 2010 14:04]
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/105750 3454 Georgi Kodinov 2010-04-15 Bug #52711: Segfault when doing EXPLAIN SELECT with union...order by (select... where...) The problem is mysql is trying to materialize and cache the scalar sub-queries at JOIN::optimize even for EXPLAIN where the number of columns is totally different from what's expected. Fixed by not executing the scalar subqueries for EXPLAIN.
[19 Jul 2010 14:34]
Bugs System
Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:georgi.kodinov@oracle.com-20100625132022-tjvtf6x0fl99w66d) (merge vers: 5.1.48) (pib:16)
[21 Jul 2010 14:52]
Paul DuBois
Noted in 5.1.49 changelog. Using EXPLAIN with queries of the form SELECT ... UNION ... ORDER BY (SELECT ... WHERE ...) could cause a server crash. Setting report to Need Merge pending further pushes.
[23 Jul 2010 12:24]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:31]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[26 Jul 2010 18:32]
Paul DuBois
Noted in 5.5.6 changelog.
[14 Oct 2010 8:30]
Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:45]
Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 9:00]
Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[3 Nov 2010 15:41]
Paul DuBois
CVE-2010-3682
[8 Nov 2010 15:03]
Paul DuBois
Noted in 5.0.92 changelog.
[15 Dec 2010 5:50]
Bugs System
Pushed into mysql-5.1 5.1.55 (revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (version source revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)
[16 Dec 2010 21:46]
Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101216181820-7afubgk2fmuv9qsb) (version source revid:alexander.nozdrin@oracle.com-20101216173826-ze3y5h450sksotrh) (merge vers: 5.6.1) (pib:23)
[16 Dec 2010 22:27]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[8 Feb 2011 13:47]
Bugs System
Pushed into mysql-5.0 5.0.93 (revid:jonathan.perkin@oracle.com-20110208134327-62swunlfs2s88jo2) (version source revid:jonathan.perkin@oracle.com-20110208134327-62swunlfs2s88jo2) (merge vers: 5.0.93) (pib:24)
Description: While doing experimental coding for WL #4357, test main.union cause a segfault on the very last statement when EXPLAIN was added to it. The call stack from the debugger includes: --- ---- called from signal handler with signal 11 (SIGSEGV) ------ [8] 0x0(0x12a0fc0, 0x12afff0, 0x12afff0, 0x33, 0x0, 0x12a0f00), at 0x0 [9] Item_singlerow_subselect::store(this = 0x12a0f00, i = 1U, item = 0x12afff0), line 482 in "item_subselect.cc" [10] select_singlerow_subselect::send_data(this = 0x12a0fd0, items = CLASS), line 2232 in "sql_class.cc" [11] select_describe(join = 0x12ad1f8, need_tmp_table = false, need_order = false, distinct = false, message = (nil)), line 16627 in "sql_select.cc" [12] JOIN::exec(this = 0x12ad1f8), line 1844 in "sql_select.cc" [13] subselect_single_select_engine::exec(this = 0x12a0ff8), line 1968 in "item_subselect.cc" [14] Item_subselect::exec(this = 0x12a0f00), line 261 in "item_subselect.cc" [15] Item_singlerow_subselect::val_str(this = 0x12a0f00, str = 0x12a0f10), line 585 in "item_subselect.cc" [16] remove_const(join = 0x12aba48, first_order = 0x12a1040, cond = (nil), change_list = true, simple_order = 0x12acffa), line 7183 in "sql_select.cc" [17] JOIN::optimize(this = 0x12aba48), line 1127 in "sql_select.cc" [18] mysql_select(thd = 0x1285790, rref_pointer_array = 0x129fb48, tables = 0x12871c8, wild_num = 0, fields = CLASS, conds = (nil), og_num = 1U, order = 0x12a1040, group = (nil), having = (nil), proc_param = (nil), select_options = 268435460ULL, result = 0x12a10a0, unit = 0x1287178, select_lex = 0x129f978), line 2471 in "sql_select.cc" [19] st_select_lex_unit::exec(this = 0x1287178), line 596 in "sql_union.cc" [20] mysql_explain_union(thd = 0x1285790, unit = 0x1287178, result = 0x12a10a0), line 16676 in "sql_select.cc" [21] execute_sqlcom_select(thd = 0x1285790, all_tables = 0x129f378), line 5030 in "sql_parse.cc" [22] mysql_execute_command(thd = 0x1285790), line 2248 in "sql_parse.cc" [23] mysql_parse(thd = 0x1285790, inBuf = 0x129f0e0 "EXPLAIN SELECT * FROM t2 UNION SELECT * FROM t2\nORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE))", length = 123U, found_semicolon = 0xfffffd7ffec67b90), line 5971 in "sql_parse.cc" ---- The relevant code is: void Item_singlerow_subselect::store(uint i, Item *item) { row[i]->store(item); row[i]->cache_value(); } Apparently, row[i] is a null pointer. Discovered on next-mr, reproduced on 5.1 (5.1-mtr), on Solaris x86. How to repeat: This extract from union.test with added EXPLAIN triggere the failure (tested on Solaris x86): ----- CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(2); EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY (SELECT a FROM t2 WHERE b = 12); EXPLAIN SELECT * FROM t2 UNION SELECT * FROM t2 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); DROP TABLE t1,t2; exit; ----- The first EXPLAIN works, the second crashes the server. Note that both work *without* EXPLAIN.