| 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: | |
| 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: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).
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