MySQL Bugs: #29461: corruption with character set macce collate macce

Bug #29461	corruption with character set macce collate macce_bin
Submitted:	1 Jul 2007 0:11	Modified:	23 Jul 2007 22:13
Reporter:	Shane Bester
Status:	Closed
Category:	Server: Charsets	Severity:	S1 (Critical)
Version:	5.0.46, 5.1.21	OS:	Any
Assigned to:	Evgeny Potemkin	Target Version:
Tags:	myisam, corruption

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

import this with testcase above

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

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;

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.

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.

Pushed into 5.1.21-beta

Pushed into 5.0.48

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.