Bug #47896 5.1 cannot repair tables created in 4.1 with prefixed varchar indexes
Submitted: 7 Oct 2009 12:57 Modified: 9 Oct 2009 0:09
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.1.39 OS:Any
Assigned to: CPU Architecture:Any

[7 Oct 2009 12:57] Shane Bester
Description:
For tables that were created originally in 4.1, it is expected that
5.1 will be able to repair the table at least since 5.0 can repair such tables.
But 5.1.39 says this:

mysql> check table t1 for upgrade\G
*************************** 1. row ***************************
   Table: test.t1
      Op: check
Msg_type: Error
Msg_text: Incorrect key file for table 't1'; try to repair it
*************************** 2. row ***************************
   Table: test.t1
      Op: check
Msg_type: error
Msg_text: Corrupt
2 rows in set (0.23 sec)

mysql> repair table t1\G
*************************** 1. row ***************************
   Table: test.t1
      Op: repair
Msg_type: Error
Msg_text: Incorrect key file for table 't1'; try to repair it
*************************** 2. row ***************************
   Table: test.t1
      Op: repair
Msg_type: error
Msg_text: Corrupt
2 rows in set (0.01 sec)

The problem is shown in this message in check_definition():

>check_definition                                              
| error: Key segment 0 (key 0) has different definition        
| error: t1_type=15, t1_language=8, t1_null_bit=0, t1_length=10
| error: t2_type=1, t2_language=8, t2_null_bit=1, t2_length=10 
<check_definition                                              

How to repeat:
in 4.1.25 create a table:

drop table if exists t1;
create table t1(a varchar(200) default null,key(a(10)))engine=myisam default charset latin1;
insert into t1 values ('test');

Now copy the t1.frm, t1.MYD, t1.MYI to 5.1.x and try repair table.

Suggested fix:
since many folks didn't dump/reload from 4.1->5.0, their tables are now entering 5.1 with version 9.  At least we need to be able to repair the tables, since 5.0 could do it I see no reason 5.1 cannot too?
[9 Oct 2009 0:09] Omer Barnir
A direct upgrade from 4.1 to 5.1 is not supported
[28 Oct 2009 15:22] Paul Dubois
The problem is now noted here:

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
http://dev.mysql.com/doc/refman/5.1/en/checking-table-incompatibilities.html