| Bug #52717 | Assert in EXPLAIN SELECT with very particular combination with ANY and index | ||
|---|---|---|---|
| Submitted: | 9 Apr 2010 11:33 | Modified: | 12 Apr 2010 11:09 |
| Reporter: | Bjørn Munch | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.99-M4 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | explain any index, regression | ||
[9 Apr 2010 11:43]
Valeriy Kravchuk
Does not crash with 5.5.4-m3 (mysql-trunk) tree.
[9 Apr 2010 11:46]
Bjørn Munch
I think this only happens with debug builds.
[9 Apr 2010 11:50]
Valeriy Kravchuk
Crashes on 32-bit Ubuntu 8.04: openxs@ubuntu:/home2/openxs/dbs/next-mr$ bin/mysql --no-defaults -uroot test Reading 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 2 Server version: 5.6.99-m4-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE t1 ( a int, b int ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES (1,1),(2,2),(3,3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t1 ADD INDEX (a); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2); ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> 100409 14:48:12 mysqld_safe Number of processes running now: 0 100409 14:48:12 mysqld_safe mysqld restarted mysql> exit Bye openxs@ubuntu:/home2/openxs/dbs/next-mr$ tail -100 var/ubuntu.err100409 ... 100409 14:47:47 InnoDB: mbminlen=4, mbmaxlen=4, type->mbminlen=0, type->mbmaxlen=4 mysqld: field.cc:3694: virtual int Field_long::store(longlong, bool): Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field_index))' failed. 100409 14:48:11 - mysqld got signal 6 ; 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=8388608 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 = 337841 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x93ef208 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 = 0xa88583b0 thread_stack 0x30000 /home2/openxs/dbs/next-mr/libexec/mysqld(my_print_stacktrace+0x26)[0x8781d15] /home2/openxs/dbs/next-mr/libexec/mysqld(handle_segfault+0x2ee)[0x82c884e] [0xb779e420] /lib/tls/i686/cmov/libc.so.6(abort+0x101)[0xb75e0a01] /lib/tls/i686/cmov/libc.so.6(__assert_fail+0xee)[0xb75d810e] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN10Field_long5storeExb+0x7c)[0x82914ec] /home2/openxs/dbs/next-mr/libexec/mysqld[0x81faaab] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN8Item_int13save_in_fieldEP5Fieldb+0x59)[0x81fab0d] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN20store_key_const_item10copy_innerEv+0x48)[0x837743e] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN9store_key4copyEv+0x80)[0x827b218] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN31subselect_uniquesubquery_engine12copy_ref_keyEv+0x51)[0x82750a9] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN30subselect_indexsubquery_engine4execEv+0x91)[0x82754f3] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN14Item_subselect4execEv+0x8c)[0x827849a] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN14Item_subselect4execEv+0xb5)[0x82784c3] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN17Item_in_subselect8val_boolEv+0x50)[0x8274486] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN4Item15val_bool_resultEv+0x18)[0x82066b6] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN17Item_in_optimizer7val_intEv+0x231)[0x8238681] /home2/openxs/dbs/next-mr/libexec/mysqld[0x8360fda] /home2/openxs/dbs/next-mr/libexec/mysqld(_ZN4JOIN8optimizeEv+0x138b)[0x836e075] /home2/openxs/dbs/next-mr/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x283)[0x83713a9] /home2/openxs/dbs/next-mr/libexec/mysqld(_Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result+0x362)[0x83718c4] /home2/openxs/dbs/next-mr/libexec/mysqld[0x82d9f37] /home2/openxs/dbs/next-mr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x9c1)[0x82dbf01] /home2/openxs/dbs/next-mr/libexec/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x229)[0x82e4151] /home2/openxs/dbs/next-mr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x9e0)[0x82e4cc6] /home2/openxs/dbs/next-mr/libexec/mysqld(_Z10do_commandP3THD+0x241)[0x82e6225] /home2/openxs/dbs/next-mr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x15b)[0x82d3401] /home2/openxs/dbs/next-mr/libexec/mysqld(handle_one_connection+0x25)[0x82d34bf] /lib/tls/i686/cmov/libpthread.so.0[0xb777c4fb] /lib/tls/i686/cmov/libc.so.6(clone+0x5e)[0xb768ae5e] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x9466890 = EXPLAIN SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2) thd->thread_id=2 thd->killed=NOT_KILLED ...
[9 Apr 2010 11:51]
MySQL Verification Team
Thank you for the bug report. C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.99-m4-Win X64-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.5 >use test Database changed mysql 5.5 >CREATE TABLE t1 ( a int, b int ); Query OK, 0 rows affected (0.04 sec) mysql 5.5 >INSERT INTO t1 VALUES (1,1),(2,2),(3,3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql 5.5 >ALTER TABLE t1 ADD INDEX (a); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql 5.5 >EXPLAIN SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2); ERROR 2013 (HY000): Lost connection to MySQL server during query 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... 00000001405A9DA5 mysqld.exe!my_sigabrt_handler()[my_thr_init.c:519] 00000001406ADF71 mysqld.exe!raise()[winsig.c:597] 00000001406B7503 mysqld.exe!abort()[abort.c:78] 00000001406B104A mysqld.exe!_wassert()[assert.c:395] 0000000140230549 mysqld.exe!Field_long::store()[field.cc:3694] 000000014018C4DE mysqld.exe!save_int_value_in_field()[item.cc:5437] 000000014018C577 mysqld.exe!Item_int::save_in_field()[item.cc:5443] 0000000140291311 mysqld.exe!store_key_const_item::copy_inner()[sql_select.h:779] 0000000140290966 mysqld.exe!store_key::copy()[sql_select.h:674] 00000001402FF338 mysqld.exe!subselect_uniquesubquery_engine::copy_ref_key()[item_subselect.cc:2102] 00000001402FF880 mysqld.exe!subselect_indexsubquery_engine::exec()[item_subselect.cc:2309] 00000001402F865B mysqld.exe!Item_subselect::exec()[item_subselect.cc:265] 00000001402F868E mysqld.exe!Item_subselect::exec()[item_subselect.cc:270] 00000001402FA748 mysqld.exe!Item_in_subselect::val_bool()[item_subselect.cc:865] 000000014003E646 mysqld.exe!Item::val_bool_result()[item.h:832] 000000014005E0E1 mysqld.exe!Item_in_optimizer::val_int()[item_cmpfunc.cc:1835] 000000014026E3EE mysqld.exe!make_join_select()[sql_select.cc:6328] 000000014025E007 mysqld.exe!JOIN::optimize()[sql_select.cc:1147] 000000014026391C mysqld.exe!mysql_select()[sql_select.cc:2517] 000000014028DF10 mysqld.exe!mysql_explain_union()[sql_select.cc:16958] 00000001401AC531 mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4681] 00000001401A4048 mysqld.exe!mysql_execute_command()[sql_parse.cc:2191] 00000001401AEDC7 mysqld.exe!mysql_parse()[sql_parse.cc:5735] 00000001401A184E mysqld.exe!dispatch_command()[sql_parse.cc:1026] 00000001401A0DF8 mysqld.exe!do_command()[sql_parse.cc:710] 0000000140084D8B mysqld.exe!do_handle_one_connection()[sql_connect.cc:1174] 0000000140084B87 mysqld.exe!handle_one_connection()[sql_connect.cc:1114] 00000001405A8C4B mysqld.exe!pthread_start()[my_winthread.c:62] 00000001406CAFE5 mysqld.exe!_callthreadstartex()[threadex.c:348] 00000001406CAFB4 mysqld.exe!_threadstartex()[threadex.c:331] 0000000077A9BE3D kernel32.dll!BaseThreadInitThunk() 0000000077CA6A51 ntdll.dll!RtlUserThreadStart() Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 000000000ADB42F0=EXPLAIN SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2) thd->thread_id=2 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.
[9 Apr 2010 12:01]
Bjørn Munch
Confirmed: could not reproduce on mysql-trunk in exact same environment and build flags
[9 Apr 2010 12:47]
Manyi Lu
Could this be a duplicate of BUG#52317?
[12 Apr 2010 6:01]
Tor Didriksen
bzrfind says # Revision jorgen.loland@sun.com-20100311102910-tsbh5nal1qz73fjd: test FAILED # Regression source: jorgen.loland@sun.com-20100311102910-tsbh5nal1qz73fjd jorgen.loland@sun.com-20100311102910-tsbh5nal1qz73fjd So, most likely a duplicate of Bug #52317 Assertion failing in Field_varstring::store () at field.cc:6833
Description: Experimenting for WL #4357, test main.subselect causes a server crash when EXPLAIN is prepended to all SELECTs. From core dump: ----- ---- called from signal handler with signal 6 (SIGABRT) ------ [8] __lwp_kill(0x4, 0x6, 0xffffffff8a97d7c0, 0x5, 0xfefefefffffffeff, 0x0), at 0xfffffd7fff12316a [9] _thr_kill(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff11e193 [10] raise(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff0ccd89 [11] abort(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff0ac88e [12] __assert(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff0acb4e [13] Field_long::store(this = 0x5869738, nr = 1LL, unsigned_val = false), line 3694 in "field.cc" [14] save_int_value_in_field(field = 0x5869738, nr = 1LL, null_value = false, unsigned_flag = false), line 5436 in "item.cc" [15] Item_int::save_in_field(this = 0x5868cb8, field = 0x5869738, no_conversions = true), line 5442 in "item.cc" [16] store_key_const_item::copy_inner(this = 0x5869700), line 779 in "sql_select.h" [17] store_key::copy(this = 0x5869700), line 674 in "partition_element.h" [18] subselect_uniquesubquery_engine::copy_ref_key(this = 0x586afb8), line 2102 in "item_subselect.cc" [19] subselect_indexsubquery_engine::exec(this = 0x586afb8), line 2309 in "item_subselect.cc" [20] Item_subselect::exec(this = 0x588d2b0), line 265 in "item_subselect.cc" [21] Item_subselect::exec(this = 0x588d2b0), line 270 in "item_subselect.cc" [22] Item_in_subselect::val_bool(this = 0x588d2b0), line 865 in "item_subselect.cc" [23] Item::val_bool_result(this = 0x588d2b0), line 832 in "types.h" [24] Item_in_optimizer::val_int(this = 0x585acd8), line 1835 in "item_cmpfunc.cc" [25] make_join_select(join = 0x58593c8, select = 0x5868630, cond = 0x585acd8), line 6328 in "sql_select.cc" [26] JOIN::optimize(this = 0x58593c8), line 1147 in "sql_select.cc" [27] mysql_select(thd = 0x5812bc0, rref_pointer_array = 0x5814c18, tables = 0x588b910, wild_num = 0, fields = CLASS, conds = 0x588d2b0, og_num = 0, order = (nil), group = (nil), having = (nil), proc_param = (nil), select_options = 2147748612ULL, result = 0x588d4a0, unit = 0x5814448, select_lex = 0x5814a48), line 2517 in "sql_select.cc" [28] mysql_explain_union(thd = 0x5812bc0, unit = 0x5814448, result = 0x588d4a0), line 16947 in "sql_select.cc" [29] execute_sqlcom_select(thd = 0x5812bc0, all_tables = 0x588b910), line 4681 in "sql_parse.cc" [30] mysql_execute_command(thd = 0x5812bc0), line 2191 in "sql_parse.cc" [31] mysql_parse(thd = 0x5812bc0, inBuf = 0x588b6d0 "EXPLAIN SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2)", length = 75U, found_semicolon = 0xfffffd7ffed17bb0), line 5735 in "sql_parse.cc" ----- The line in question from fileds.cc: --- int Field_long::store(longlong nr, bool unsigned_val) { ASSERT_COLUMN_MARKED_FOR_WRITE; --- Seen in next-mr on Solaris x86, not in 5.1 How to repeat: This extract from subsectlt.test with added EXPLAIN triggers the crash: --- CREATE TABLE t1 ( a int, b int ); INSERT INTO t1 VALUES (1,1),(2,2),(3,3); ALTER TABLE t1 ADD INDEX (a); EXPLAIN SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2); DROP TABLE t1; exit; --- Test main.subselect includes a long list of similar statements with ANY or ALL, but only this exact combination fails with EXPLAIN. And only if we have created an index on a.