| Bug #33794 | MySQL crashes executing specific query on specific dump | ||
|---|---|---|---|
| Submitted: | 10 Jan 2008 12:10 | Modified: | 24 Jan 2008 20:46 |
| Reporter: | Sveta Smirnova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0, 5.1, 6.0 | OS: | Any (Linux) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[10 Jan 2008 12:10]
Sveta Smirnova
[10 Jan 2008 13:23]
Sveta Smirnova
Simplified test case: --source include/have_innodb.inc --disable_query_log DROP TABLE IF EXISTS `t4`; CREATE TABLE `t4` ( `f7` varchar(32) collate utf8_bin NOT NULL default '', `f10` varchar(32) collate utf8_bin default NULL, PRIMARY KEY (`f7`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; LOCK TABLES `t4` WRITE; INSERT INTO `t4` VALUES(1,1), (2,null); UNLOCK TABLES; DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `f4` varchar(32) collate utf8_bin NOT NULL default '', `f2` varchar(50) collate utf8_bin default NULL, `f3` varchar(10) collate utf8_bin default NULL, PRIMARY KEY (`f4`), UNIQUE KEY `uk1` (`f2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; LOCK TABLES `t2` WRITE; INSERT INTO `t2` VALUES(1,1,null), (2,2,null); UNLOCK TABLES; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `f8` varchar(32) collate utf8_bin NOT NULL default '', `f1` varchar(10) collate utf8_bin default NULL, `f9` varchar(32) collate utf8_bin default NULL, PRIMARY KEY (`f8`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; LOCK TABLES `t1` WRITE; INSERT INTO `t1` VALUES (1,'P',1), (2,'P',1), (3,'R',2); UNLOCK TABLES; DROP TABLE IF EXISTS `t3`; CREATE TABLE `t3` ( `f6` varchar(32) collate utf8_bin NOT NULL default '', `f5` varchar(50) collate utf8_bin default NULL, PRIMARY KEY (`f6`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; LOCK TABLES `t3` WRITE; INSERT INTO `t3` VALUES (1,null), (2,null); UNLOCK TABLES; --enable_query_log SELECT IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4, IF(t1.f1 = 'R' , a1.f3, t2.f3) AS f3, SUM( IF ( (SELECT VPC.f2 FROM t2 VPC, t4 a2, t2 a3 WHERE VPC.f4 = a2.f10 AND a3.f2 = a4 LIMIT 1 ) IS NULL, 0, t3.f5)) AS a6 FROM t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4 GROUP BY a4 ;
[11 Jan 2008 8:10]
MySQL Verification Team
stack trace
Attachment: bug33794_stacktrace.txt (text/plain), 4.29 KiB.
[11 Jan 2008 8:21]
Marko Mäkelä
I get a similar stack trace on GNU/Linux. There are no InnoDB functions in the stack trace, but the crash in MySQL could be due to an anomaly of the special UTF-8 handling in InnoDB. I will investigate this further.
[11 Jan 2008 8:48]
Marko Mäkelä
I get a similar stack trace on GNU/Linux. There are no InnoDB functions in the stack trace, but the crash in MySQL could be due to an anomaly of the special UTF-8 handling in InnoDB. I will investigate this further.
[11 Jan 2008 9:52]
Marko Mäkelä
Sorry, this is not an InnoDB bug. Proof: replace "InnoDB" with "MyISAM" in the simplified test case from [10 Jan 14:23], and it crashes with a similar stack trace. Someone please assign this bug to a MySQL developer.
[12 Jan 2008 8:10]
Valeriy Kravchuk
Yes, this bug is NOT InnoDB-specific. Verified with 5.0.54 on Windows, for example:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.0.54-enterprise-gpl-nt MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `t4` (
-> `f7` varchar(32) collate utf8_bin NOT NULL default '',
-> `f10` varchar(32) collate utf8_bin default NULL,
-> PRIMARY KEY (`f7`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)
mysql> LOCK TABLES `t4` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `t4` VALUES(1,1), (2,null);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS `t2`;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `t2` (
-> `f4` varchar(32) collate utf8_bin NOT NULL default '',
-> `f2` varchar(50) collate utf8_bin default NULL,
-> `f3` varchar(10) collate utf8_bin default NULL,
-> PRIMARY KEY (`f4`),
-> UNIQUE KEY `uk1` (`f2`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> LOCK TABLES `t2` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `t2` VALUES(1,1,null), (2,2,null);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `t1` (
-> `f8` varchar(32) collate utf8_bin NOT NULL default '',
-> `f1` varchar(10) collate utf8_bin default NULL,
-> `f9` varchar(32) collate utf8_bin default NULL,
-> PRIMARY KEY (`f8`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> LOCK TABLES `t1` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `t1` VALUES (1,'P',1), (2,'P',1), (3,'R',2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DROP TABLE IF EXISTS `t3`;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `t3` (
-> `f6` varchar(32) collate utf8_bin NOT NULL default '',
-> `f5` varchar(50) collate utf8_bin default NULL,
-> PRIMARY KEY (`f6`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> LOCK TABLES `t3` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `t3` VALUES (1,null), (2,null);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4,
-> IF(t1.f1 = 'R' , a1.f3, t2.f3) AS f3,
-> SUM( IF ( (SELECT VPC.f2
-> FROM t2 VPC, t4 a2, t2 a3
-> WHERE
-> VPC.f4 = a2.f10
-> AND a3.f2 = a4
-> LIMIT 1 ) IS NULL, 0, t3.f5)) AS a6
->
-> FROM t2, t3, t1
-> JOIN t2 a1 ON t1.f9 = a1.f4
->
-> GROUP BY a4
-> ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
From this stack trace:
mysqld-nt.exe!Copy_field::set(Field * to=0x01ab4a28, Field * from=0x00000000, int save=0) Line 567 C++
mysqld-nt.exe!store_key_field::store_key_field(THD * thd=0x0450e868, Field * to_field_arg=0x01ab1000, char * ptr=0x01ab4871, char * null_ptr_arg=0x01ab4870, unsigned int length=150, Field * from_field=0x00000000, const char * name_arg=0x01aaefe8) Line 588 + 0xb bytes C++
mysqld-nt.exe!get_store_key(THD * thd=0x0450e868, keyuse_t * keyuse=0x00000000, unsigned __int64 used_tables=28002760, st_key_part_info * key_part=0x01ab0e30, char * key_buff=0x00000000, unsigned int maybe_null=0) Line 5383 + 0x4e bytes C++
mysqld-nt.exe!create_ref_for_key(JOIN * join=0x01ab1488, st_join_table * j=0x00000000, keyuse_t * org_keyuse=0x01ab4870, unsigned __int64 used_tables=4611686018427387908) Line 5323 + 0x22 bytes C++
mysqld-nt.exe!get_best_combination(JOIN * join=0x054aeca4) Line 5202 + 0x13 bytes C++
mysqld-nt.exe!make_join_statistics(JOIN * join=0x01ab1488, TABLE_LIST * tables=0x00000000, Item * conds=0x01aaf178, st_dynamic_array * keyuse_array=0x01ab21ec) Line 2794 + 0x19 bytes C++
mysqld-nt.exe!JOIN::optimize() Line 926 + 0x21 bytes C++
mysqld-nt.exe!subselect_single_select_engine::exec() Line 1776 + 0x8 bytes C++
mysqld-nt.exe!Item_subselect::exec() Line 214 C++
mysqld-nt.exe!Item_singlerow_subselect::val_int() Line 506 + 0x8 bytes C++
mysqld-nt.exe!Item_subselect::is_null() Line 94 C++
mysqld-nt.exe!Item_func_isnull::val_int() Line 4115 + 0xd bytes C++
mysqld-nt.exe!Item::val_bool() Line 175 + 0x7 bytes C++
mysqld-nt.exe!Item_func_if::val_real() Line 2294 + 0xa bytes C++
mysqld-nt.exe!Item_sum_sum::reset_field() Line 1917 + 0xa bytes C++
mysqld-nt.exe!end_update(JOIN * join=0x04536c90, st_join_table * join_tab=0x01ab33c8, int end_of_records=0) Line 11784 + 0x1e bytes C++
mysqld-nt.exe!evaluate_join_record(JOIN * join=0x00000001, st_join_table * join_tab=0x00000000, int error=0, char * report_error=0x0450eb30) Line 10783 + 0xd bytes C++
mysqld-nt.exe!sub_select(JOIN * join=0x04536c90, st_join_table * join_tab=0x01ab3230, int end_of_records=0) Line 10667 + 0x9 bytes C++
mysqld-nt.exe!flush_cached_records(JOIN * join=0x04536c90, st_join_table * join_tab=0x00000000, int skip_last=0) Line 10931 + 0x11 bytes C++
mysqld-nt.exe!sub_select_cache(JOIN * join=0x04536c90, st_join_table * join_tab=0x01ab3098, int end_of_records=1) Line 10492 + 0x12 bytes C++
mysqld-nt.exe!sub_select(JOIN * join=0x04536c90, st_join_table * join_tab=0x01ab2f00, int end_of_records=1) Line 10631 + 0x10 bytes C++
mysqld-nt.exe!sub_select_cache(JOIN * join=0x04536c90, st_join_table * join_tab=0x01ab2f00, int end_of_records=1) Line 10494 + 0x8 bytes C++
mysqld-nt.exe!do_select(JOIN * join=0x00000000, List<Item> * fields=0x00000000, st_table * table=0x00000000, Procedure * procedure=0x00000000) Line 10432 + 0x16 bytes C++
mysqld-nt.exe!JOIN::exec() Line 1735 + 0x1a bytes C++
mysqld-nt.exe!mysql_select(THD * thd=0x0450e868, Item * * * rref_pointer_array=0x0450f8e8, TABLE_LIST * tables=0x04536018, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x00000000, unsigned int og_num=1, st_order * order=0x00000000, st_order * group=0x04536bc8, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned __int64 select_options=2156153344, select_result * result=0x04536c80, st_select_lex_unit * unit=0x0450f528, st_select_lex * select_lex=0x0450f7b8) Line 2298 C++
mysqld-nt.exe!handle_select(THD * thd=0x0450e868, st_lex * lex=0x0450f4c8, select_result * result=0x04536c80, unsigned long setup_tables_done_option=0) Line 257 + 0x79 bytes C++
mysqld-nt.exe!mysql_execute_command(THD * thd=0x0450e868) Line 2675 + 0xa bytes C++
mysqld-nt.exe!mysql_parse(THD * thd=0x0450e868, const char * inBuf=0x01aad880, unsigned int length=285, const char * * found_semicolon=0x054afb64) Line 6114 C++
mysqld-nt.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x0450e868, char * packet=0x01aa9861, unsigned int packet_length=286) Line 1823 C++
mysqld-nt.exe!do_command(THD * thd=0x00000000) Line 1595 + 0xe bytes C++
mysqld-nt.exe!handle_one_connection(void * arg=0x0450e868) Line 1201 + 0x9 bytes C++
mysqld-nt.exe!pthread_start(void * param=0x00ebfff0) Line 85 + 0x3 bytes C
> mysqld-nt.exe!_threadstart(void * ptd=0x01ab86d0) Line 196 + 0x6 bytes C
kernel32.dll!7c80b683()
[Frames below may be incorrect and/or missing, no symbols loaded for kernel32.dll]
mswsock.dll!71a55ccc()
I'd say it is a bug in Optimizer.
[18 Jan 2008 19:01]
Sergey Petrunya
The problem occurs with referring the named select list entry out of the subquery that is also in the select list. For the provided simplified case, the workaround is: SELECT IF(t1.f1 = 'R', a1.f2, t2.f2) AS a4, IF(t1.f1 = 'R' , a1.f3, t2.f3) AS f3, SUM( IF ( (SELECT VPC.f2 FROM t2 VPC, t4 a2, t2 a3 WHERE VPC.f4 = a2.f10 AND a3.f2 = IF(t1.f1 = 'R', a1.f2, t2.f2) LIMIT 1 ) IS NULL, 0, t3.f5)) AS a6 FROM t2, t3, t1 JOIN t2 a1 ON t1.f9 = a1.f4 GROUP BY a4 ;
[18 Jan 2008 19:51]
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/41066 ChangeSet@1.2590, 2008-01-18 22:50:36+03:00, sergefp@mysql.com +3 -0 BUG#33794 "MySQL crashes executing specific query": The problem occurred when one had a subquery that had an equality X=Y where Y referred to a named select list expression from the parent select. MySQL crashed when trying to use the X=Y equality for ref-based access. Fixed by allowing non-Item_field items in the described case.
[24 Jan 2008 11:56]
Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 12:00]
Bugs System
Pushed into 5.1.24-rc
[24 Jan 2008 12:02]
Bugs System
Pushed into 5.0.56
[24 Jan 2008 20:46]
Paul DuBois
Noted in 5.0.56, 5.1.24, 6.0.5 changelogs.
[6 Mar 2008 15:52]
Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[2 Apr 2008 17:02]
Jon Stephens
Also noted in the 5.1.23-ndb-6.3.11 changelog.
