When I have the following MyISAM table in MySQL 4.1 with no rows in the table, it works as expected. But the same table fails with an error “ERROR 1034 (HY000) at line 1: Incorrect key file for table ’statSite’; try to repair it” when it was upgraded to 5.1 server using the same old 4.1 data. If you re-create the table in 5.1, everything starts working back (ofcourse). This is really a bad incompatibility and can cause lot of problems for many properties here at Yahoo! as most of the data exists from 4.1 server and people tend to migrade directly instead of export and import. This is a similar case for Merge Tables as well that we noticed earlier.

When the server is 4.1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

[vanugant@escapereply:v1 ~]$ mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.23-5b-Yahoo-SMP-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE TABLE `statSite` (
    ->   `siteID` varchar(16) NOT NULL default '',
    ->   `siteName` tinytext,
    ->   `siteDesc` mediumtext,
    ->   `username` tinytext,
    ->   `password` tinytext,
    ->   `acctype` int(1) default NULL,
    ->   `name` tinytext,
    ->   `addr` tinytext,
    ->   `addr2` tinytext,
    ->   `city` tinytext,
    ->   `state` tinytext,
    ->   `zip` tinytext,
    ->   `payDue` tinytext,
    ->   `billingType` char(1) default NULL,
    ->   `lastStatsView` varchar(12) default NULL,
    ->   `trackServerID` varchar(16) NOT NULL default '',
    ->   `refID` varchar(16) default NULL,
    ->   `refStatus` int(1) NOT NULL default '0',
    ->   `refAmount` float default NULL,
    ->   `refDate` varchar(8) NOT NULL default '',
    ->   `isAfl` int(1) default '0',
    ->   `country` tinytext,
    ->   `ppEmail` tinytext,
    ->   `returntourl` tinytext,
    ->   `adultContent` char(1) default NULL,
    ->   `dbServerID` varchar(10) default NULL,
    ->   `db_group` varchar(20) default NULL,
    ->   PRIMARY KEY  (`siteID`),
    ->   KEY `username_idx` (`username`(20))
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from  statSite;
Empty set (0.00 sec)
 
mysql> exit

After upgraded to 5.0 from 4.1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

[vanugant@escapereply:v1 ~]$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37-2-Yahoo-SMP-log Yahoo SMP (i386)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> select * from statSite;
Empty set (0.00 sec)
 
mysql> exit

After the server has been updated to 5.1 from 5.0:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

[vanugant@escapereply:v1 ~]$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.21-beta-Yahoo-SMP-log  (RHEL i386)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> select * from statSite;
ERROR 1034 (HY000): Incorrect key file for table 'statSite'; try to repair it
mysql> repair table statSite;
+---------------+--------+----------+-----------------------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                                  |
+---------------+--------+----------+-----------------------------------------------------------+
| test.statSite | repair | Error    | Incorrect key file for table 'statSite'; try to repair it |
| test.statSite | repair | error    | Corrupt                                                   |
+---------------+--------+----------+-----------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> select * from statSite;
ERROR 1034 (HY000): Incorrect key file for table 'statSite'; try to repair it
mysql>

This happens even when there is an upgrade from 4.1 to 5.1 directly, but not when you create it with 5.0 or directly with 5.1. As the error indicates, even the repair could not fix the problem. Currently I patched for Yahoo! internal builds by by-passing the validation of key checks (not a functionality change); but need to report the bug to MySQL team and see what the outcome is.

Here is the actual bug report to MySQL team:
http://bugs.mysql.com/bug.php?id=31331