Bug #28272 | server crash during EXPLAIN SELECT .. | ||
---|---|---|---|
Submitted: | 7 May 2007 6:57 | Modified: | 27 May 2007 18:26 |
Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.42BK, 5.1BK | OS: | Any |
Assigned to: | Igor Babaev | CPU Architecture: | Any |
Tags: | crash |
[7 May 2007 6:57]
Shane Bester
[7 May 2007 7:28]
MySQL Verification Team
If release binary doesn't crash, try using debug binary. Testcase: DROP TABLE IF EXISTS `table10`; CREATE TABLE `table10` ( `col0` float default NULL, `col1` double default NULL, `col2` varchar(98) default NULL, `col3` datetime default NULL, `col4` text, `col5` tinyblob, `col6` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `col7` decimal(10,0) default NULL, `col8` double default NULL, `col9` tinyblob, `col10` time default NULL, `col11` blob, `col12` time default NULL, `col13` blob, `col14` double default NULL, `col15` double default NULL, `col16` datetime default NULL, `col17` blob, `col18` tinyint(4) default NULL, `col19` blob, `col20` tinyblob, `col21` int(11) default NULL, `col22` tinytext, `col23` tinyblob, `col24` tinyblob, `col25` timestamp NOT NULL default '0000-00-00 00:00:00', `col26` smallint(6) default NULL, `col27` tinyblob, `col28` time default NULL, `col29` int(11) default NULL, `col30` char(175) default NULL, `col31` smallint(6) default NULL, `col32` set('test1','test2','test3') default NULL, `col33` decimal(10,0) default NULL, `col34` set('test1','test2','test3') default NULL, `col35` date default NULL, `col36` text, `col37` tinytext, `col38` year(4) default NULL, `col39` decimal(10,0) default NULL, `col40` text, `col41` bigint(20) default NULL, `col42` float default NULL, `col43` decimal(10,0) default NULL, `col44` timestamp NOT NULL default '0000-00-00 00:00:00', `col45` tinytext, `col46` float default NULL, `col47` tinyblob, `col48` varchar(168) default NULL, `col49` char(178) default NULL, `col50` tinyblob, `col51` decimal(10,0) default NULL, `col52` decimal(10,0) default NULL, KEY `idx0` (`col29`,`col13`(143),`col52`,`col45`(47)), KEY `idx1` (`col21`), KEY `idx2` (`col20`(224),`col31`,`col15`), KEY `idx3` (`col31`,`col51`,`col13`(194)), KEY `idx4` (`col11`(242),`col33`,`col0`), KEY `idx5` (`col19`(222),`col35`), KEY `idx6` (`col1`,`col52`,`col24`(34)), KEY `idx7` (`col37`(15),`col2`,`col6`,`col10`), KEY `idx8` (`col37`(254),`col7`), KEY `idx9` (`col30`,`col31`), KEY `idx10` (`col15`), KEY `idx11` (`col29`,`col18`,`col45`(76),`col13`(59)), KEY `idx12` (`col37`(223)), KEY `idx13` (`col33`), KEY `idx14` (`col3`), KEY `idx15` (`col19`(213)), KEY `idx16` (`col12`,`col50`(2)), KEY `idx17` (`col13`(86),`col34`,`col32`,`col24`(26)), KEY `idx18` (`col27`(197),`col44`), KEY `idx19` (`col33`), KEY `idx20` (`col18`,`col5`(61),`col6`), KEY `idx21` (`col6`,`col17`(147),`col50`(118),`col32`), KEY `idx22` (`col44`,`col47`(239),`col30`), KEY `idx23` (`col5`(204)), KEY `idx24` (`col6`), KEY `idx25` (`col5`(78),`col9`(25)), KEY `idx26` (`col49`,`col36`(79)), KEY `idx27` (`col16`,`col48`), KEY `idx28` (`col25`), KEY `idx29` (`col10`), KEY `idx30` (`col15`), KEY `idx31` (`col52`), KEY `idx32` (`col20`(242),`col48`,`col14`,`col30`), KEY `idx33` (`col26`,`col33`,`col44`,`col31`), KEY `idx34` (`col47`(95)), KEY `idx35` (`col44`), KEY `idx36` (`col33`,`col30`,`col38`), KEY `idx37` (`col31`,`col6`), KEY `idx38` (`col8`,`col9`(198)), KEY `idx39` (`col49`,`col35`,`col47`(75),`col42`), KEY `idx40` (`col26`,`col42`), KEY `idx41` (`col14`,`col3`,`col50`(65)), KEY `idx42` (`col29`,`col25`,`col37`(21),`col5`(83)), KEY `idx43` (`col10`,`col35`,`col44`), KEY `idx44` (`col12`,`col2`), KEY `idx45` (`col37`(30),`col0`,`col9`(32),`col5`(34)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='generated'; EXPLAIN SELECT `col14`,`col11`,`col52`,`col13`,`col29` FROM `table10` WHERE `col31`=1069597179 AND `col44`=NULL AND `col13`='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS' LIMIT 3;
[7 May 2007 8:02]
MySQL Verification Team
as far back as 5.0.24a still crashes. This isn't a new bug.
[7 May 2007 8:10]
MySQL Verification Team
I have many tables and this crash happens on a few others too. Depending on column types, the stack traces may be slightly different at the crash point: mysqld-debug.exe!memcmp mysqld-debug.exe!Arg_comparator::compare_binary_string mysqld-debug.exe!Arg_comparator::compare mysqld-debug.exe!Item_func_eq::val_int mysqld-debug.exe!Item::val_bool mysqld-debug.exe!Item_cond_and::val_int mysqld-debug.exe!make_join_select mysqld-debug.exe!JOIN::optimize mysqld-debug.exe!mysql_select mysqld-debug.exe!mysql_explain_union mysqld-debug.exe!mysql_execute_command mysqld-debug.exe!mysql_parse mysqld-debug.exe!dispatch_command mysqld-debug.exe!do_command mysqld-debug.exe!handle_one_connection mysqld-debug.exe!pthread_start mysqld-debug.exe!_threadstart
[14 May 2007 2:23]
Igor Babaev
I minimized the test case causing the crash: mysql> SELECT VERSION(); +--------------+ | VERSION() | +--------------+ | 5.0.42-debug | +--------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE t1 ( -> a1 decimal(10,0) DEFAULT NULL, -> a2 blob, -> a3 time DEFAULT NULL, -> a4 blob, -> a5 char(175) DEFAULT NULL, -> a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', -> a7 tinyblob, -> INDEX idx (a6,a7(239),a5) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.08 sec) mysql> EXPLAIN SELECT a4 FROM t1 WHERE -> a6=NULL AND -> a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; ERROR 2013 (HY000): Lost connection to MySQL server during query At this moment at the server side I had: Version: '5.0.42-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution [New Thread -1256490080 (LWP 10602)] Program received signal SIGSEGV, Segmentation fault. The SIGSEGV happened in the following code: int Arg_comparator::compare_binary_string() { String *res1,*res2; if ((res1= (*a)->val_str(&owner->tmp_value1))) { if ((res2= (*b)->val_str(&owner->tmp_value2))) { owner->null_value= 0; uint res1_length= res1->length(); uint res2_length= res2->length(); --> int cmp= memcmp(res1->ptr(), res2->ptr(), min(res1_length,res2_length)); return cmp ? cmp : (int) (res1_length - res2_length); } } owner->null_value= 1; return -1; } The same crash and in the same code could be observed on Windows.
[14 May 2007 3:18]
Igor Babaev
For 5.0 we also have the following results: mysql> EXPLAIN SELECT a4 FROM t1 WHERE -> a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) Yet, as the table t1 is empty, the expected output would be: mysql> EXPLAIN SELECT a4 FROM t1 WHERE -> a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ For the last query we can observe the same behaviour on 4.1 as on 5.0 though the query that causes the crash on 5.0 works returns the correct result on 4.1.
[14 May 2007 3:29]
Igor Babaev
We have the specified problems with the above queries because currently the function make_join_select erroneously tries to evaluate WHERE condition for empty tables. This attempt can be observed both in 5.0 and 4.1
[14 May 2007 4:14]
Igor Babaev
A correction to my previous comments: For the query EXPLAIN SELECT a4 FROM t1 WHERE a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; the expected result is: +----+-------------+-------+------+---------------+------+---------+------+----- -+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+----- -+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+------+----- -+-------------+
[15 May 2007 6:53]
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/26665 ChangeSet@1.2656, 2007-05-14 23:55:18-07:00, igor@olga.mysql.com +3 -0 Fixed bug #28272: crash that occurs when running an EXPLAIN command for a query over an empty table right after its creation. The crash is the result of an attempt made by JOIN::optimize to evaluate the WHERE condition when no records have been actually read. The added test case can reproduce the crash only with InnoDB tables and only with 5.0.x.
[20 May 2007 17:10]
Bugs System
Pushed into 4.1.23
[20 May 2007 17:11]
Bugs System
Pushed into 5.0.44
[20 May 2007 17:12]
Bugs System
Pushed into 5.1.19-beta
[27 May 2007 18:26]
Paul DuBois
Noted in 4.1.23, 5.0.44, 5.1.19 changelogs. EXPLAIN for a query on an empty table immediately after its creation could result in a server crash.