MySQL Bugs: #29261: repeatable innodb and myisam corruption

Bug #29261	repeatable innodb and myisam corruption
Submitted:	21 Jun 2007 8:55	Modified:	3 Jul 2007 19:21
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S1 (Critical)
Version:	4.1.23, 5.0.44BK, 5.1.20BK	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	corruption, innodb, myisam

Description:
Found a table which is persistently corrupted:

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

mysql> repair table tbl_char;
+------------+--------+----------+----------+
| Table      | Op     | Msg_type | Msg_text |
+------------+--------+----------+----------+
| g.tbl_char | repair | status   | OK       |
+------------+--------+----------+----------+
1 row in set (2 min 5.52 sec)

mysql> check table tbl_char extended;
+------------+-------+----------+-------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                        |
+------------+-------+----------+-------------------------------------------------+
| g.tbl_char | check | error    | Record at: 739240  Can't find key for index: 13 |
| g.tbl_char | check | error    | Corrupt                                         |
+------------+-------+----------+-------------------------------------------------+

How to repeat:
Will make a testcase later.

Suggested fix:
.

some info

Attachment: bug29261_some_info.txt (text/plain), 18.47 KiB.

TESTCASE:
------------------------

drop table if exists t1;
create table t1(`col_12` varchar(255) character set latin2 collate latin2_hungarian_ci default NULL,key(`col_12`))engine=myisam;
insert into t1 set col_12=0x3F3F9DC73FCE3F00C1B00E4C6F1A174E6B3F3F;
insert into t1 set col_12=0x3F3F1E563F9D3F3FA4D73FC73F705218433F3F7A10255B3F3FB43F3F3F223F3F3F3FE23F3F3F3F25333F3F3F3F90353F3F3F3F3F3FD63F3F3F3F67363F3F3F3FB03F3FBEE43F3F413F3F3FCE3FDD3FE93F3F41E13F8F3F3F3F09363FA0AD4F1649786816ED3F3F;
insert into t1 set col_12=0x3F3F;
check table t1 extended;

testcase in a file so bugs system doesn't wrap it.

Attachment: testcase_bug29261.sql (application/octet-stream, text), 517 bytes.

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/29939

ChangeSet@1.2504, 2007-06-29 15:35:27+04:00, evgen@moonbone.local +3 -0
  Bug#29261: Sort order of the collation wasn't used when comparing trailing
  spaces.
  
  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 to find whether the longer key is greater or lesser.
  But the sort order of the collation isn't used in this comparison. This may
  lead to wrong comparison result.
  
  Now the my_strnncollsp_simple function uses collation sort order to compare
  the characters in the rest of longer key with the space.

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/29974

ChangeSet@1.2504, 2007-06-29 22:01:49+04:00, evgen@moonbone.local +3 -0
  Bug#29261: Sort order of the collation wasn't used when comparing trailing
  spaces.
  
  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 a 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 a space character.

InnoDB reports corruptions in the error log also:

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

mysql> create table t1 (a varchar(2) character set latin2 collate latin2_hungarian_ci,primary key(a))engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> load data local infile '/tmp/dump.txt' replace into table t1 (@h) set a=UNHEX(@h);
ERROR 1062 (23000): Duplicate entry 'É£' for key 1
mysql>
mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | error    | Corrupt  |
+---------+-------+----------+----------+
1 row in set (3.95 sec)

dump file for innodb corruption

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

updated category and synopsis.

The bug occurs only on collations where a character with the code greater than the code of the space character is mapped by the sort_order table to a character with the code less than the code of the space character.
In the given example the code of the such character is 0x9D.
I checked other collations, it seems that only latin2_hungarian_ci is affected.

Pushed into 5.1.21-beta

Pushed into 5.0.46

Noted in 5.0.46, 5.1.21 changelogs.

Sort order of the collation wasn't used when comparing trailing
spaces. This could lead to incorrect comparison results, incorrectly
created indexes, or incorrect result set order for queries that
include an ORDER BY clause.