| Bug #74877 | Assertion `false' failed in JOIN::decide_subquery_strategy | ||
|---|---|---|---|
| Submitted: | 15 Nov 2014 17:00 | Modified: | 5 Dec 2016 5:50 |
| Reporter: | Ramesh Sivaraman | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S6 (Debug Builds) |
| Version: | 5.6.21 | OS: | Linux (CentOS 7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | debug | ||
[16 Nov 2014 1:09]
MySQL Verification Team
On Windows optimized version statement stays sleeping: c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.23 Source distribution revno 6246 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > SHOW PROCESSLIST; +----+------+-----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+-------+------------------+ | 1 | root | localhost:49318 | test | Sleep | 84 | | NULL | | 2 | root | localhost:49347 | NULL | Query | 0 | init | SHOW PROCESSLIST | +----+------+-----------------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)
[16 Nov 2014 2:25]
MySQL Verification Team
Thank you for the bug report.
[miguel@centosvb bin]$ mysql -uroot --socket=/tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-debug Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
mysql> create table t2 (id int primary key,val varchar(20));
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE t1 (a SET('x') NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> create view v1 as select * from t1 where a in (select * from t2);
Query OK, 0 rows affected (0.01 sec)
mysql> EXPLAIN INSERT INTO v1 VALUES (10);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[5 Dec 2016 5:32]
Roel Van de Paar
If the statement stays sleeping on optimized, then this is a serious bug.
[5 Dec 2016 5:50]
Ramesh Sivaraman
As explain statement goes into sleep mode on optimized build marking it as s1.
Description: mysqld: /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:9558: bool JOIN::decide_subquery_strategy(): Assertion `false' failed. 16:50:15 UTC - mysqld got signal 6 ; When we execute the testcase with optimized build "EXPLAIN INSERT INTO v1 VALUES (10);" statement is not responding. In processlist the transaction is in sleep mode. mysql> DROP DATABASE test;CREATE DATABASE test;USE test; Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Database changed mysql> create table t2 (id int primary key,val varchar(20)); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE t1 (a SET('x') NOT NULL); Query OK, 0 rows affected (0.04 sec) mysql> create view v1 as select * from t1 where a in (select * from t2); Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN INSERT INTO v1 VALUES (10); mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 1 | root | localhost | test | Sleep | 361 | | NULL | | 3 | root | localhost | test | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec) mysql> #0 0x00007f0c01a6f771 in pthread_kill () from /lib64/libpthread.so.0 #1 0x0000000000a954ea in my_write_core (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/mysys/stacktrace.c:422 #2 0x0000000000726494 in handle_fatal_signal (sig=6) at /ssd/ramesh/mysql-server/mysql-5.6/sql/signal_handler.cc:230 #3 <signal handler called> #4 0x00007f0c0087b5c9 in raise () from /lib64/libc.so.6 #5 0x00007f0c0087ccd8 in abort () from /lib64/libc.so.6 #6 0x00007f0c00874536 in __assert_fail_base () from /lib64/libc.so.6 #7 0x00007f0c008745e2 in __assert_fail () from /lib64/libc.so.6 #8 0x00000000009b9ea5 in JOIN::decide_subquery_strategy (this=0x7f0b64d50668) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:9558 #9 0x00000000009ab5c3 in make_join_statistics (join=0x7f0b64d50668, tables_arg=0x7f0b64c7e158, conds=0x0, keyuse_array=0x7f0b64d50890, first_optimization=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:3772 #10 0x00000000009a25fb in JOIN::optimize (this=0x7f0b64d50668) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_optimizer.cc:363 #11 0x0000000000800f52 in mysql_execute_select (thd=0x7f0b6e75d000, select_lex=0x7f0b64c7d570, free_join=true) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_select.cc:1086 #12 0x00000000008012a3 in mysql_select (thd=0x7f0b6e75d000, tables=0x7f0b64c7e158, wild_num=0, fields=..., conds=0x0, order=0x7f0b64c7d738, group=0x7f0b64c7d670, having=0x0, select_options=2147748612, result=0x7f0b64c7eb78, unit=0x7f0b64c7d870, select_lex=0x7f0b64c7d570) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_select.cc:1221 #13 0x000000000093e71d in mysql_explain_unit (thd=0x7f0b6e75d000, unit=0x7f0b64c7d870, result=0x7f0b64c7eb78) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:2132 #14 0x0000000000939634 in Explain::explain_subqueries (this=0x7f0c02023970, result=0x7f0b64c7eb78) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:602 #15 0x0000000000939d0d in Explain::send (this=0x7f0c02023970) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:713 #16 0x000000000093de03 in explain_no_table (thd=0x7f0b6e75d000, message=0xef8f6a "No tables used", rows=18446744073709551615) at /ssd/ramesh/mysql-server/mysql-5.6/sql/opt_explain.cc:1886 #17 0x00000000007b1a4e in mysql_insert (thd=0x7f0b6e75d000, table_list=0x7f0b64c1f280, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_insert.cc:881 #18 0x00000000007d43b7 in mysql_execute_command (thd=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:3443 #19 0x00000000007dbdbc in mysql_parse (thd=0x7f0b6e75d000, rawbuf=0x7f0b64c1f010 "EXPLAIN INSERT INTO v1 VALUES (10)", length=34, parser_state=0x7f0c02024e70) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:6245 #20 0x00000000007cf36f in dispatch_command (command=COM_QUERY, thd=0x7f0b6e75d000, packet=0x7f0b63266001 "", packet_length=34) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1332 #21 0x00000000007ce45e in do_command (thd=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_parse.cc:1034 #22 0x0000000000796879 in do_handle_one_connection (thd_arg=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:982 #23 0x0000000000796362 in handle_one_connection (arg=0x7f0b6e75d000) at /ssd/ramesh/mysql-server/mysql-5.6/sql/sql_connect.cc:898 #24 0x0000000000d71288 in pfs_spawn_thread (arg=0x7f0bfdffea60) at /ssd/ramesh/mysql-server/mysql-5.6/storage/perfschema/pfs.cc:1860 #25 0x00007f0c01a6adf3 in start_thread () from /lib64/libpthread.so.0 #26 0x00007f0c0093c01d in clone () from /lib64/libc.so.6 How to repeat: DROP DATABASE test;CREATE DATABASE test;USE test; create table t2 (id int primary key,val varchar(20)); CREATE TABLE t1 (a SET('x') NOT NULL); create view v1 as select * from t1 where a in (select * from t2); EXPLAIN INSERT INTO v1 VALUES (10);