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:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.42BK, 5.1BK OS:Any
Assigned to: Igor Babaev
Tags: crash

[7 May 2007 6:57] Shane Bester
Description:
I encountered a crash using the debug binary when running a batch of EXPLAIN SELECT ... commands.

The crash was caused by uninitialized pointer being dereferenced (0xa5a5a5a5).
Stack trace:

mysqld-debug.exe!my_strnncollsp_simple
mysqld-debug.exe!sortcmp
mysqld-debug.exe!Arg_comparator::compare_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!_callthreadstart
mysqld-debug.exe!_threadstart

while (a < end)
  {
    if (map[*a++] != map[*b++]) <---------------CRASH
      return ((int) map[a[-1]] - (int) map[b[-1]]);
  }

How to repeat:
Will upload testcase soon.

Suggested fix:
.
[7 May 2007 7:28] Shane Bester
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] Shane Bester
as far back as 5.0.24a still crashes. This isn't a new bug.
[7 May 2007 8:10] Shane Bester
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.