MySQL Bugs: #29261: repeatable innodb and myisam corruption

Bug #29261	repeatable innodb and myisam corruption
Submitted:	21 Jun 2007 10:55	Modified:	3 Jul 2007 21:21
Reporter:	Shane Bester
Status:	Closed
Category:	Server: Charsets	Severity:	S1 (Critical)
Version:	4.1.23, 5.0.44BK, 5.1.20BK	OS:	Any
Assigned to:	Evgeny Potemkin	Target Version:
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=0x3F3F1E563F9D3F3FA4D73FC73F705218433F3F7A10255B3F3FB43F3F3F223F3F3F3FE23F3F3F3F253
33F3F3F3F90353F3F3F3F3F3FD63F3F3F3F67363F3F3F3FB03F3FBEE43F3F413F3F3FCE3FDD3FE93F3F41E13F8
F3F3F3F09363FA0AD4F1649786816ED3F3F;
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.