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

[21 Jun 2007 8:55] Shane Bester
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:
.
[21 Jun 2007 9:14] MySQL Verification Team
some info

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

[21 Jun 2007 9:38] MySQL Verification Team
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;
[21 Jun 2007 9:40] MySQL Verification Team
testcase in a file so bugs system doesn't wrap it.

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

[29 Jun 2007 11:38] 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/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.
[29 Jun 2007 18:05] 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/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.
[30 Jun 2007 18:02] MySQL Verification Team
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)
[30 Jun 2007 18:03] MySQL Verification Team
dump file for innodb corruption

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

[30 Jun 2007 18:04] MySQL Verification Team
updated category and synopsis.
[1 Jul 2007 14:22] Evgeny Potemkin
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.
[1 Jul 2007 19:58] Bugs System
Pushed into 5.1.21-beta
[1 Jul 2007 20:02] Bugs System
Pushed into 5.0.46
[3 Jul 2007 19:21] Paul Dubois
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.