Bug #34949 Passing invalid parameter to CHAR() in an ORDER BY causes MySQL to hang
Submitted: 29 Feb 2008 3:23 Modified: 30 May 2008 19:14
Reporter: Lindsey Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:4.1.22 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: char hang
Triage: D1 (Critical)

[29 Feb 2008 3:23] Lindsey Smith
Description:
Some legacy code of ours was using CHAR() to approximate natural language ordering, and we recently stumbled across a bug when updating a client that caused the MySQL server to basically crash in some situations. 

I have tested this on Linux and Windows, and it doesn't seem to be OS specific. 

I believe it only happens when forcing (via connection or via "using UTF8") the UTF8 character set. 

I realise that our application should not have been using CHAR() in this manner, but MySQL should probably throw an error when it encounters this issue, instead of hanging the query thread.

How to repeat:
CREATE DATABASE `crashtest` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

use crashtest;

CREATE TABLE `crashtest` (
  `crash` char(10) NOT NULL default ''
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `crashtest` VALUES ('35');
INSERT INTO `crashtest` VALUES ('36');
INSERT INTO `crashtest` VALUES ('37');

// This query will work fine
select * from crashtest order by char(crash using utf8);

TRUNCATE TABLE `crashtest`;

INSERT INTO `crashtest` VALUES ('-1000');

// This query will hang the query thread
select * from crashtest order by char(crash using utf8);

Suggested fix:
For natural ordering use:
select * from crashtest order by crash + 0;
[29 Feb 2008 4:24] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.

Bug is not repeatable with 5.x.y:

mysql> select * from crashtest order by char(crash using utf8);
+-------+
| crash |
+-------+
| -1000 |
+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1300
Message: Invalid utf8 character string: 'FFFFFC'
1 row in set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.56-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)
[7 Mar 2008 12:02] Georgi Kodinov
Tested on Linux (FC8) using the 4.1.22 official "mysql-standard-4.1.22-pc-linux-gnu-i686-icc-glibc23.tar.gz" from MySQL's site.
I've got :
+ select @@version;
+ @@version
+ 4.1.22-standard-log
+ set character set utf8;
+ show variables like 'char%';
+ Variable_name Value
+ character_set_client  utf8
+ character_set_connection      utf8
+ character_set_database        utf8
+ character_set_results utf8
+ character_set_server  utf8
+ character_set_system  utf8
+ CREATE DATABASE `crashtest` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
+ use crashtest;
+ CREATE TABLE `crashtest` (
+ `crash` char(10) NOT NULL default ''
+ ) ENGINE=INNODB DEFAULT CHARSET=utf8;
+ INSERT INTO `crashtest` VALUES ('35');
+ INSERT INTO `crashtest` VALUES ('36');
+ INSERT INTO `crashtest` VALUES ('37');
+ select * from crashtest order by char(crash using utf8);
+ crash
+ 35
+ 36
+ 37
+ TRUNCATE TABLE `crashtest`;
+ INSERT INTO `crashtest` VALUES ('-1000');
+ select * from crashtest order by char(crash using utf8);
+ crash
+ -1000

and no crashes or hangs. 

Going to test on Win32 next
[7 Mar 2008 13:47] Georgi Kodinov
I've got the hang on windows xp.
This looks like windows specific bug.
[21 Mar 2008 14:04] 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/44332

ChangeSet@1.2709, 2008-03-21 16:03:42+02:00, gkodinov@magare.gmz +4 -0
  Bug#34949: Passing invalid parameter to CHAR() in an 
   ORDER BY causes MySQL to hang.
  
  The function my_strnxfrm_utf8() was not checking for
  certain error codes returned by my_utf8_uni().
  Back-ported the correct check from 5.0.
[7 Apr 2008 12:35] 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/44990

ChangeSet@1.2709, 2008-04-07 15:34:42+03:00, gkodinov@magare.gmz +3 -0
  Bug#34949: Passing invalid parameter to CHAR() in an 
   ORDER BY causes MySQL to hang.
  
  The function my_strnxfrm_utf8() was not checking for
  certain error codes returned by my_utf8_uni().
  Back-ported the correct check from 5.0.
[7 Apr 2008 12:50] Alexander Barkov
Joro, the patch http://lists.mysql.com/commits/44990 is ok to push.
Thanks for taking into account my previous suggestions!
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[30 May 2008 19:14] Paul Dubois
Noted in 6.0.6 changelog.

Passing an invalid parameter to CHAR() in an ORDER BY clause caused
the server to hang.