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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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);