Bug #24776 MySQL4 Server crashes on complicated autojoin query
Submitted: 2 Dec 2006 14:33 Modified: 1 Feb 2007 1:45
Reporter: Jochen Tuchbreiter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.22/4.1BK OS:Linux (Linux)
Assigned to: Igor Babaev CPU Architecture:Any
Tags: autojoin, querycrash

[2 Dec 2006 14:33] Jochen Tuchbreiter
Description:
4.1.22-debug-log crashes with a query involving autojoins on a certain table. 

Kernel: 2.4.29-grsec
Distribution: Gentoo

The server binary is the statically compiled version directory downloaded from your site.

$ md5sum /usr/sbin/mysqld
2cd21c65a3139db38fbc78e123893dfd  /usr/sbin/mysqld

I can reproduce this on two different machines running the same software.

Instructions on how to repeat, stack trace and debug log of crash is attached.

If anything is missing let me know, I am not quite sure what you need.

How to repeat:
Create a fresh database. Connect to MySQL-Server and select the database.

Execute the following commands:

CREATE TABLE `oxcategories` (
  `OXID` varchar(32) collate latin1_german2_ci NOT NULL default '',
  `OXPARENTID` varchar(32) collate latin1_german2_ci NOT NULL default 'oxrootid',
  `OXLEFT` int(11) NOT NULL default '0',
  `OXRIGHT` int(11) NOT NULL default '0',
  `OXROOTID` varchar(32) collate latin1_german2_ci NOT NULL default '',
  PRIMARY KEY  (`OXID`),
  KEY `OXNID` (`OXID`),
  KEY `OXLEFT` (`OXLEFT`),
  KEY `OXRIGHT` (`OXRIGHT`),
  KEY `OXROOTID` (`OXROOTID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO `oxcategories` VALUES ('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,'d8c4177d09f8b11f5.52725521'),('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,'d8c4177d09f8b11f5.52725521'),('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,'d8c4177d09f8b11f5.52725521'),('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,'d8c4177d09f8b11f5.52725521'),('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,'d8c4177d09f8b11f5.52725521');

Now execute the following select:

SELECT s.oxid FROM oxcategories v, oxcategories s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' and v.oxrootid='d8c4177d09f8b11f5.52725521' and s.oxleft > v.oxleft AND s.oxleft < v.oxright;

MySQL-Server will crash:
ERROR 2013 (HY000): Lost connection to MySQL server during query

--------------
Log of my session:

mysql> CREATE TABLE `oxcategories` (
    ->   `OXID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   `OXPARENTID` varchar(32) collate latin1_german2_ci NOT NULL default 'oxrootid',
    ->   `OXLEFT` int(11) NOT NULL default '0',
    ->   `OXRIGHT` int(11) NOT NULL default '0',
    ->   `OXROOTID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   PRIMARY KEY  (`OXID`),
    ->   KEY `OXNID` (`OXID`),
    ->   KEY `OXLEFT` (`OXLEFT`),
    ->   KEY `OXRIGHT` (`OXRIGHT`),
    ->   KEY `OXROOTID` (`OXROOTID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `oxcategories` VALUES ('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,'d8c4177d09f8b11f5.52725521'),('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,'d8c4177d09f8b11f5.52725521'),('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,'d8c4177d09f8b11f5.52725521'),('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,'d8c4177d09f8b11f5.52725521'),('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,'d8c4177d09f8b11f5.52725521');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT s.oxid FROM oxcategories v, oxcategories s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' and v.oxrootid='d8c4177d09f8b11f5.52725521' and s.oxleft > v.oxleft AND s.oxleft < v.oxright;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> Aborted
[2 Dec 2006 14:34] Jochen Tuchbreiter
Stack trace as found in error-log after crash, processed on server to get symbols

Attachment: mysqld.stacktrace (application/octet-stream, text), 1.21 KiB.

[2 Dec 2006 15:22] MySQL Verification Team
Thank you for the bug report. I was able to repeat on today source server
4.1 (5.0 tree isn't affected) and most probably this bug was introduced
after 4.1.21 because a Windows server 4.1.21 not crashes.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `oxcategories` (
    ->   `OXID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   `OXPARENTID` varchar(32) collate latin1_german2_ci NOT NULL default
    -> 'oxrootid',
    ->   `OXLEFT` int(11) NOT NULL default '0',
    ->   `OXRIGHT` int(11) NOT NULL default '0',
    ->   `OXROOTID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   PRIMARY KEY  (`OXID`),
    ->   KEY `OXNID` (`OXID`),
    ->   KEY `OXLEFT` (`OXLEFT`),
    ->   KEY `OXRIGHT` (`OXRIGHT`),
    ->   KEY `OXROOTID` (`OXROOTID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `oxcategories` VALUES
    -> ('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),('d8c4177d151affab2.81582770',
    -> 'd8c4177d09f8b11f5.52725521',2,3,'d8c4177d09f8b11f5.52725521'),('d8c4177d206a333d2.74422679',
    -> 'd8c4177d09f8b11f5.52725521',4,5,'d8c4177d09f8b11f5.52725521'),('d8c4177d225791924.30714720',
    -> 'd8c4177d09f8b11f5.52725521',6,7,'d8c4177d09f8b11f5.52725521'),('d8c4177d2380fc201.39666693',
    -> 'd8c4177d09f8b11f5.52725521',8,9,'d8c4177d09f8b11f5.52725521'),('d8c4177d24ccef970.14957924',
    -> 'd8c4177d09f8b11f5.52725521',10,11,'d8c4177d09f8b11f5.52725521');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT s.oxid FROM oxcategories v, oxcategories s WHERE s.oxrootid =
    -> 'd8c4177d09f8b11f5.52725521' and v.oxrootid='d8c4177d09f8b11f5.52725521' and
    -> s.oxleft > v.oxleft AND s.oxleft < v.oxright;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

C:\dbs\4.1\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.21-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE `oxcategories` (
    ->   `OXID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   `OXPARENTID` varchar(32) collate latin1_german2_ci NOT NULL default
    -> 'oxrootid',
    ->   `OXLEFT` int(11) NOT NULL default '0',
    ->   `OXRIGHT` int(11) NOT NULL default '0',
    ->   `OXROOTID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   PRIMARY KEY  (`OXID`),
    ->   KEY `OXNID` (`OXID`),
    ->   KEY `OXLEFT` (`OXLEFT`),
    ->   KEY `OXRIGHT` (`OXRIGHT`),
    ->   KEY `OXROOTID` (`OXROOTID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO `oxcategories` VALUES
    -> ('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),('d8c4177d151affab2.81582770',
    -> 'd8c4177d09f8b11f5.52725521',2,3,'d8c4177d09f8b11f5.52725521'),('d8c4177d206a333d2.74422679',
    -> 'd8c4177d09f8b11f5.52725521',4,5,'d8c4177d09f8b11f5.52725521'),('d8c4177d225791924.30714720',
    -> 'd8c4177d09f8b11f5.52725521',6,7,'d8c4177d09f8b11f5.52725521'),('d8c4177d2380fc201.39666693',
    -> 'd8c4177d09f8b11f5.52725521',8,9,'d8c4177d09f8b11f5.52725521'),('d8c4177d24ccef970.14957924',
    -> 'd8c4177d09f8b11f5.52725521',10,11,'d8c4177d09f8b11f5.52725521');
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT s.oxid FROM oxcategories v, oxcategories s WHERE s.oxrootid =
    -> 'd8c4177d09f8b11f5.52725521' and v.oxrootid='d8c4177d09f8b11f5.52725521' and
    -> s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+----------------------------+
| oxid                       |
+----------------------------+
| d8c4177d151affab2.81582770 |
| d8c4177d206a333d2.74422679 |
| d8c4177d225791924.30714720 |
| d8c4177d2380fc201.39666693 |
| d8c4177d24ccef970.14957924 |
+----------------------------+
5 rows in set (0.06 sec)

mysql>

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create dbq
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbq
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `oxcategories` (
    ->   `OXID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   `OXPARENTID` varchar(32) collate latin1_german2_ci NOT NULL default
    -> 'oxrootid',
    ->   `OXLEFT` int(11) NOT NULL default '0',
    ->   `OXRIGHT` int(11) NOT NULL default '0',
    ->   `OXROOTID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->   PRIMARY KEY  (`OXID`),
    ->   KEY `OXNID` (`OXID`),
    ->   KEY `OXLEFT` (`OXLEFT`),
    ->   KEY `OXRIGHT` (`OXRIGHT`),
    ->   KEY `OXROOTID` (`OXROOTID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `oxcategories` VALUES
    -> ('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),('d8c4177d151affab2.81582770',
    -> 'd8c4177d09f8b11f5.52725521',2,3,'d8c4177d09f8b11f5.52725521'),('d8c4177d206a333d2.74422679',
    -> 'd8c4177d09f8b11f5.52725521',4,5,'d8c4177d09f8b11f5.52725521'),('d8c4177d225791924.30714720',
    -> 'd8c4177d09f8b11f5.52725521',6,7,'d8c4177d09f8b11f5.52725521'),('d8c4177d2380fc201.39666693',
    -> 'd8c4177d09f8b11f5.52725521',8,9,'d8c4177d09f8b11f5.52725521'),('d8c4177d24ccef970.14957924',
    -> 'd8c4177d09f8b11f5.52725521',10,11,'d8c4177d09f8b11f5.52725521');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT s.oxid FROM oxcategories v, oxcategories s WHERE s.oxrootid =
    -> 'd8c4177d09f8b11f5.52725521' and v.oxrootid='d8c4177d09f8b11f5.52725521' and
    -> s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+----------------------------+
| oxid                       |
+----------------------------+
| d8c4177d151affab2.81582770 | 
| d8c4177d206a333d2.74422679 | 
| d8c4177d225791924.30714720 | 
| d8c4177d2380fc201.39666693 | 
| d8c4177d24ccef970.14957924 | 
+----------------------------+
5 rows in set (0.01 sec)

mysql>
[6 Dec 2006 14:24] Mads Martin Joergensen
Is this only the debug server?
[11 Dec 2006 11:25] Jochen Tuchbreiter
This does *NOT* occur on 4.1.22-standard-log. Looks to me like only debug might be affected

----------
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+----------------------+
| Tables_in_db67070_73 |
+----------------------+
| oxcategories         |
+----------------------+
1 row in set (0.00 sec)

mysql> drop tables oxcategories;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `oxcategories` ( `OXID` varchar(32) collate 
latin1_german2_ci NOT NULL default '',  `OXPARENTID` varchar(32) collate 
latin1_german2_ci NOT NULL default 'oxrootid', `OXLEFT` int(11) NOT NULL 
default '0',   `OXRIGHT` int(11) NOT NULL default '0',  `OXROOTID` 
varchar(32) collate latin1_german2_ci NOT NULL default '',  PRIMARY KEY  
(`OXID`), KEY `OXNID` (`OXID`), KEY `OXLEFT` (`OXLEFT`), KEY `OXRIGHT` 
(`OXRIGHT`), KEY `OXROOTID` (`OXROOTID`) ) ENGINE=MyISAM DEFAULT 
CHARSET=latin1 COLLATE=latin1_german2_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `oxcategories` VALUES 
('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,'d8c4177d09f8b11f5.52725521'),('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,'d8c4177d09f8b11f5.52725521'),('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,'d8c4177d09f8b11f5.52725521'),('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,'d8c4177d09f8b11f5.52725521'),('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,'d8c4177d09f8b11f5.52725521');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT s.oxid FROM oxcategories v, oxcategories s WHERE 
s.oxrootid ='d8c4177d09f8b11f5.52725521' and 
v.oxrootid='d8c4177d09f8b11f5.52725521' and s.oxleft > v.oxleft AND 
s.oxleft < v.oxright;
+----------------------------+
| oxid                       |
+----------------------------+
| d8c4177d151affab2.81582770 |
| d8c4177d206a333d2.74422679 |
| d8c4177d225791924.30714720 |
| d8c4177d2380fc201.39666693 |
| d8c4177d24ccef970.14957924 |
+----------------------------+
5 rows in set (0.00 sec)

----------
[19 Dec 2006 15:45] MySQL Verification Team
Backtrace on Suse 10.0 32-bit

Attachment: backtrace-24776.txt (text/plain), 9.41 KiB.

[19 Dec 2006 15:49] MySQL Verification Team
I still was able to repeat the crash with latest source server:

miguel@hegel:~/dbs/mysql-4.1> bk changes | head
ChangeSet@1.2581.1.2, 2006-12-14 22:30:32+01:00, joerg@trift2. +1 -0
  support-files/mysql.spec.sh
      The Docs team has two new manpages for the "server" RPM: "my_print_defaults" and "mysql_tzinfo_to_sql".

ChangeSet@1.2542.3.22, 2006-12-07 16:06:29+01:00, df@kahlann.erinye.com +1 -0
  do not autorelease build ids when a child of mysql-test-run.pl dies

ChangeSet@1.2542.3.21, 2006-12-07 15:02:32+01:00, df@kahlann.erinye.com +3 -0
  BUG#24780 use --sysconfdir in scripts

Server compiled with script:

BUILD/compile-pentium-debug-max --prefix=/home/miguel/dbs/4.1

miguel@hegel:~/dbs/4.1> bin/mysqladmin -uroot shutdown
miguel@hegel:~/dbs/4.1> bin/mysql -uroot dhx
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `oxcategories` (
    -> `OXID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    -> `OXPARENTID` varchar(32) collate latin1_german2_ci NOT NULL default
    -> 'oxrootid',
    -> `OXLEFT` int(11) NOT NULL default '0',
    -> `OXRIGHT` int(11) NOT NULL default '0',
    -> `OXROOTID` varchar(32) collate latin1_german2_ci NOT NULL default '',
    ->  PRIMARY KEY  (`OXID`),
    ->  KEY `OXNID` (`OXID`),
    -> KEY `OXLEFT` (`OXLEFT`),
    -> KEY `OXRIGHT` (`OXRIGHT`),
    ->  KEY `OXROOTID` (`OXROOTID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT INTO `oxcategories` VALUES
    -> ('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
    -> ('d8c4177d151affab2.81582770',
    -> 'd8c4177d09f8b11f5.52725521',2,3,'d8c4177d09f8b11f5.52725521'),
    -> ('d8c4177d206a333d2.74422679',
    -> 'd8c4177d09f8b11f5.52725521',4,5,'d8c4177d09f8b11f5.52725521'),
    -> ('d8c4177d225791924.30714720',
    -> 'd8c4177d09f8b11f5.52725521',6,7,'d8c4177d09f8b11f5.52725521'),
    -> ('d8c4177d2380fc201.39666693',
    -> 'd8c4177d09f8b11f5.52725521',8,9,'d8c4177d09f8b11f5.52725521'),
    -> ('d8c4177d24ccef970.14957924',
    -> 'd8c4177d09f8b11f5.52725521',10,11,'d8c4177d09f8b11f5.52725521');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT s.oxid FROM oxcategories v, oxcategories s WHERE s.oxrootid =
    -> 'd8c4177d09f8b11f5.52725521' and v.oxrootid='d8c4177d09f8b11f5.52725521'
    -> and
    ->  s.oxleft > v.oxleft AND s.oxleft < v.oxright;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

I attached a backtrace file.
[13 Jan 2007 7:19] Igor Babaev
The problem is due to the assertion abort in handler::ha_index_init(uint).
It happens when 'Range checked for each record' is used in the query
execution plan, like in one we have for the reported query:

mysql> EXPLAIN
    -> SELECT s.oxid FROM oxcategories v, oxcategories s 
    ->   WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
    ->         v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
    ->         s.oxleft > v.oxleft AND s.oxleft < v.oxright;
+----+-------------+-------+------+-------------------------+----------+---------+-------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys           | key      | key_len | ref   | rows | Extra                                          |
+----+-------------+-------+------+-------------------------+----------+---------+-------+------+------------------------------------------------+
|  1 | SIMPLE      | v     | ref  | OXLEFT,OXRIGHT,OXROOTID | OXROOTID |      32 | const |    5 | Using where                                    |
|  1 | SIMPLE      | s     | ALL  | OXLEFT                  | NULL     |    NULL | NULL  |    5 | Range checked for each record (index map: 0x4) |
+----+-------------+-------+------+-------------------------+----------+---------+-------+------+------------------------------------------------+

The problem is already fixed in the 5.0 code.
[13 Jan 2007 18:29] Igor Babaev
In 5.0 the problem was fixed by Evgeny's patch for bug #12291 on 2005/09/16.
[13 Jan 2007 18:47] 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/18092

ChangeSet@1.2597, 2007-01-13 10:49:26-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #24776: an assertion abort in handler::ha_index_init
  for queries using 'range checked for each record'.
  The problem was fixed in 5.0 by the patch for bug 12291.
  This patch down-ported the corresponding code from 5.0 into 
  QUICK_SELECT::init() and added a new test case.
[28 Jan 2007 2:02] Igor Babaev
Pushed to 4.1.23, 5.0.36, 5.1.16-beta main trees.
[1 Feb 2007 1:45] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.23, 5.0.14, and 5.1.16 changelogs (5.0.14 rather than 5.0.36 for the 5.0 fix per previous notes and Bug #12291).