| Bug #20287 | Select using same column twice for order by crashes MySQL server | ||
|---|---|---|---|
| Submitted: | 6 Jun 2006 9:40 | Modified: | 12 Oct 2006 14:13 |
| Reporter: | neil craig | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.20, 4.1.15 | OS: | Linux (Debian 3.3.5-13) |
| Assigned to: | CPU Architecture: | Any | |
[6 Jun 2006 9:40]
neil craig
[6 Jun 2006 9:54]
Valeriy Kravchuk
Thank you for a problem report. Please, send the EXPLAIN select distinct chatroom_label as label, chatroom_id as id from chatroom where chatroom_date_deleted_timestamp is null and chatroom_is_permanent_chatroom=1 order by chatroom_label desc, chatroom.chatroom_label asc\G statement results, SHOW CREATE TABLE and SHOW TABLE STATUS results for that chatroom table. Can you try to upgrade to 4.1.20 and check with it?
[6 Jun 2006 10:14]
neil craig
Hi
mysqldump for the relevant 2 tables shows:
DROP TABLE IF EXISTS `chatroom`;
CREATE TABLE `chatroom` (
`chatroom_id` int(63) unsigned NOT NULL auto_increment,
`lang_id` int(10) unsigned NOT NULL default '0',
`chatroom_label` varchar(255) default NULL,
`chatroom_date_created_timestamp` int(32) unsigned NOT NULL default '0',
`chatroom_is_permanent_chatroom` tinyint(1) NOT NULL default '0',
`chatroom_date_deleted_timestamp` int(32) unsigned default NULL,
`user_id` int(63) unsigned NOT NULL default '0',
PRIMARY KEY (`chatroom_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40000 ALTER TABLE `chatroom` DISABLE KEYS */;
INSERT INTO `chatroom` (`chatroom_id`,`lang_id`,`chatroom_label`,`chatroom_date_created_timestamp`,`chatroom_is_permanent_chatroom`,`chatroom_date_deleted_timestamp`,`user_id`) VALUES
(31,0,'abc',1149526417,0,NULL,1),
(30,0,'test1',1149526397,0,NULL,1),
(26,0,'bean',1148935369,0,NULL,196),
(25,0,'hello',1148501419,0,NULL,276),
(24,0,'hi',1147873771,0,NULL,195),
(27,0,'evening',1148935496,0,NULL,276),
(28,0,'splaindawg',1149011334,0,NULL,1),
(29,0,'fwfwe',1149011389,0,NULL,1);
/*!40000 ALTER TABLE `chatroom` ENABLE KEYS */;
DROP TABLE IF EXISTS `chatroom_allowed_user`;
CREATE TABLE `chatroom_allowed_user` (
`chatroom_allowed_user_id` int(63) unsigned NOT NULL auto_increment,
`chatroom_id` int(63) unsigned NOT NULL default '0',
`user_id` int(63) unsigned NOT NULL default '0',
`chatroom_allowed_user_date_deleted_timestamp` int(32) unsigned default NULL,
PRIMARY KEY (`chatroom_allowed_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40000 ALTER TABLE `chatroom_allowed_user` DISABLE KEYS */;
INSERT INTO `chatroom_allowed_user` (`chatroom_allowed_user_id`,`chatroom_id`,`user_id`,`chatroom_allowed_user_date_deleted_timestamp`) VALUES
(56,24,196,NULL),
(57,25,155,NULL),
(58,26,190,NULL),
(59,27,196,NULL),
(64,31,155,NULL),
(63,30,204,NULL),
(62,29,1,NULL);
/*!40000 ALTER TABLE `chatroom_allowed_user` ENABLE KEYS */;
also, the EXPLAIN you asked for is:
mysql> EXPLAIN select distinct chatroom_label as label, chatroom_id as id
-> from chatroom where chatroom_date_deleted_timestamp is null
-> and chatroom_is_permanent_chatroom=1
-> order by chatroom_label desc, chatroom.chatroom_label asc;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | chatroom | ALL | NULL | NULL | NULL | NULL | 9 | Using where; Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)
i'll attach the status output in just a moment...there are about 85 tables in the database.
Unfortunately I can't upgrade MySQL as it's installed via apt on a live server and there are no apt sources available for 4.1.2 via PHP5 module...
Thanks for your help.
Neil
[27 Jun 2006 4:43]
Levi Cameron
Simple test case to reproduce error on Fedora Core 4 / Mysql 4.1.20 (standard distribution RPMs) Table creation code (3 tables, 3kb): http://www.levi.id.au/misc/mysql20287/createtables.sql Offending query with duplicate ORDER BY fields: http://www.levi.id.au/misc/mysql20287/loseconnection.sql The following will cause a lost connection every time: /etc/init.d/mysqld stop /etc/init.d/mysqld start mysql < createtables.sql mysql < loseconnection.sql Running this on a live server with other simultaneous queries will eventually cause the server to stop responding, although the server daemon process is still running. Note also that if I remove the FK fields from the select, the error doesn't occur.
[1 Jul 2006 11:42]
Valeriy Kravchuk
Sorry, but I was not able to repeat on 4.1.21-BK:
openxs@suse:~/dbs/4.1> bin/mysql -uroot test
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 3 to server version: 4.1.21
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS `chatroom`;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> CREATE TABLE `chatroom` (
-> `chatroom_id` int(63) unsigned NOT NULL auto_increment,
-> `lang_id` int(10) unsigned NOT NULL default '0',
-> `chatroom_label` varchar(255) default NULL,
-> `chatroom_date_created_timestamp` int(32) unsigned NOT NULL default '0',
-> `chatroom_is_permanent_chatroom` tinyint(1) NOT NULL default '0',
-> `chatroom_date_deleted_timestamp` int(32) unsigned default NULL,
-> `user_id` int(63) unsigned NOT NULL default '0',
-> PRIMARY KEY (`chatroom_id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)
mysql> /*!40000 ALTER TABLE `chatroom` DISABLE KEYS */;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `chatroom`
-> (`chatroom_id`,`lang_id`,`chatroom_label`,`chatroom_date_created_timestamp`,`chatroom_is_permanent_chatroom`,`chatroom_date_deleted_timestamp`,`user_id
`)
-> VALUES
-> (31,0,'abc',1149526417,0,NULL,1),
-> (30,0,'test1',1149526397,0,NULL,1),
-> (26,0,'bean',1148935369,0,NULL,196),
-> (25,0,'hello',1148501419,0,NULL,276),
-> (24,0,'hi',1147873771,0,NULL,195),
-> (27,0,'evening',1148935496,0,NULL,276),
-> (28,0,'splaindawg',1149011334,0,NULL,1),
-> (29,0,'fwfwe',1149011389,0,NULL,1);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> /*!40000 ALTER TABLE `chatroom` ENABLE KEYS */;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS `chatroom_allowed_user`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `chatroom_allowed_user` (
-> `chatroom_allowed_user_id` int(63) unsigned NOT NULL auto_increment,
-> `chatroom_id` int(63) unsigned NOT NULL default '0',
-> `user_id` int(63) unsigned NOT NULL default '0',
-> `chatroom_allowed_user_date_deleted_timestamp` int(32) unsigned default
NULL,
-> PRIMARY KEY (`chatroom_allowed_user_id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> /*!40000 ALTER TABLE `chatroom_allowed_user` DISABLE KEYS */;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `chatroom_allowed_user`
-> (`chatroom_allowed_user_id`,`chatroom_id`,`user_id`,`chatroom_allowed_user_date_deleted_timestamp`) VALUES
-> (56,24,196,NULL),
-> (57,25,155,NULL),
-> (58,26,190,NULL),
-> (59,27,196,NULL),
-> (64,31,155,NULL),
-> (63,30,204,NULL),
-> (62,29,1,NULL);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> /*!40000 ALTER TABLE `chatroom_allowed_user` ENABLE KEYS */;
Query OK, 0 rows affected (0.00 sec)
mysql> select distinct chatroom_label as label, chatroom_id as id
-> from chatroom where chatroom_date_deleted_timestamp is null
-> and chatroom_is_permanent_chatroom=1
-> order by chatroom_label desc, chatroom.chatroom_label asc;
Empty set (0.01 sec)
mysql> select distinct chatroom_label as label, chatroom_id as id from chatroo
m where chatroom_date_deleted_timestamp is null order by chatroom_label desc,
chatroom.chatroom_label asc;
+------------+----+
| label | id |
+------------+----+
| abc | 31 |
| test1 | 30 |
| bean | 26 |
| hello | 25 |
| hi | 24 |
| evening | 27 |
| splaindawg | 28 |
| fwfwe | 29 |
+------------+----+
8 rows in set (0.00 sec)
If you have another test case, please, dump tables with all the data needed to repeat. Othervise just wait for 4.1.21 to be released officially.
[3 Jul 2006 4:16]
Levi Cameron
On further investigation, the alternate example that I posted can be duplicated only on 64 bit platforms (repeatedly demonstrated on two Fedora Core 4 machines, default system packages), and not on the 32 bit platforms I tested (Debian & FC2, system packages). Neither of the 64 bit platforms fails on 4.1.21-BK, and I cannot repeat Neil's error (even though is it the same as mine) on any of the four machines above, so it would seem to be very compilation specific.
[10 Jul 2006 7:29]
Levi Cameron
I have found a test case that fails on 32 bit platforms: Table creation code (3 tables, 3kb): (no data is needed to cause the problem) http://www.levi.id.au/misc/mysql20287/createtables.sql Tested will crash 4.1.20 FC4/64bit: http://www.levi.id.au/misc/mysql20287/loseconnection.sql Tested will crash 4.1.16 FC3/32bit and 4.1.15 Debian/32bit: http://www.levi.id.au/misc/mysql20287/loseconnection32.sql I can deterministically repeat the crash with the following /etc/init.d/mysqld stop /etc/init.d/mysqld start mysql < createtables.sql mysql < loseconnection.sql (or mysql < loseconnection32.sql for 32 bit systems) I can't find a test case on 4.1.21BK but if it is a buffer overrun this would explain why different queries crash 32bit/64bit systems; unless the affected code has accidentally been rewritten the bug is probably still present in 4.1.21, I just haven't found a demonstrable case yet.
[12 Sep 2006 14:13]
Valeriy Kravchuk
All reporters: Please, try to repeat with 4.1.21, released officially on July 19, 2006 (see http://dev.mysql.com/doc/refman/4.1/en/news-4-1-21.html), and inform about the results.
[28 Sep 2006 5:50]
Levi Cameron
No longer occurs on FC4(64bit) or FC3(32bit)
[12 Oct 2006 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
