Bug #29461 corruption with character set macce collate macce_bin
Submitted: 30 Jun 2007 22:11 Modified: 23 Jul 2007 20:13
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S1 (Critical)
Version:5.0.46, 5.1.21 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: corruption, myisam

[30 Jun 2007 22:11] Shane Bester
Description:
With compound index, check table reports corruption:

mysql> drop table if exists tbl_105;
Query OK, 0 rows affected (0.03 sec)

mysql> create table tbl_105 (a varchar(2) character set macce collate macce_bin,b varchar(2) character set latin7 collate latin7_general_ci,key(b,a))engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> load data local infile '/tmp/dump.txt' replace into table tbl_105 (@h,@i) set a=UNHEX(@h),b=UNHEX(@h);
Query OK, 10923 rows affected (0.16 sec)
Records: 10923  Deleted: 0  Skipped: 0  Warnings: 0

mysql> check table tbl_105 extended;
+-----------+-------+----------+----------------------------------------------+
| Table     | Op    | Msg_type | Msg_text                                     |
+-----------+-------+----------+----------------------------------------------+
| g.tbl_105 | check | error    | Record at: 360  Can't find key for index:  1 |
| g.tbl_105 | check | error    | Corrupt                                      |
+-----------+-------+----------+----------------------------------------------+
2 rows in set (0.02 sec)

How to repeat:
drop table if exists tbl_105;
create table tbl_105 (a varchar(2) character set macce collate macce_bin,b varchar(2) character set latin7 collate latin7_general_ci,key(b,a))engine=myisam;
load data local infile '/tmp/dump.txt' replace into table tbl_105 (@h,@i) set a=UNHEX(@h),b=UNHEX(@h);
check table tbl_105 extended;

Suggested fix:
please check in case this is duplicate of:

#bug 29333
#bug 29459
#bug 29261
[30 Jun 2007 22:12] Shane Bester
import this with testcase above

Attachment: dump.txt (text/plain), 52.62 KiB.

[30 Jun 2007 23:02] Shane Bester
affects innodb also.  Same thing with this table too:

create table tbl_42 (a varchar(2),b varchar(2) character set cp1250 collate cp1250_general_ci,key(b,a))engine=myisam;
[30 Jun 2007 23:42] Shane Bester
for qa, create a  few dump.txt with numbers. Then run attached sql commands. check no corruptions happen in *any* table.

Attachment: bug29461_proposed_qa_start.sql.bz2 (application/octet-stream, text), 10.69 KiB.

[8 Jul 2007 21:34] 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/30508

ChangeSet@1.2521, 2007-07-09 01:23:33+04:00, evgen@moonbone.local +3 -0
  Bug#29461: Sort order of the collation wasn't used when comparing characters
  with the space character.
  
  When the my_strnncollsp_simple function compares two strings and one is a prefix
  of another then this function compares characters in the rest of longer key
  with the space character to find whether the longer key is greater or less.
  But the sort order of the collation isn't used in this comparison. This may
  lead to a wrong comparison result, wrongly created index or wrong order of the
  result set of a query with the ORDER BY clause.
  
  Now the my_strnncollsp_simple function uses collation sort order to compare
  the characters in the rest of longer key with the space character.
[20 Jul 2007 23:46] Bugs System
Pushed into 5.1.21-beta
[20 Jul 2007 23:49] Bugs System
Pushed into 5.0.48
[23 Jul 2007 20:13] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.

Failure to consider collation when comparing space characters could
lead to incorrect index entry order, making it impossible to find
some index values.