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 44 45 |
[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 15 16 |
[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 25 26 |
[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:
https://bugs.mysql.com/bug.php?id=31331
Venu, my dear friend,
This is not necessarily a bug. This could be just a consequence of how 4.1 and 5.0 are treating end spaces. As our manual says loud and clear, please dump / restore when upgrading from 4.1 to 5.0. You can’t upgrade any other way.
Thanks Sinisa 🙂 Good to catch you..you never change 🙂
[…] months back we noticed the incompatibility change related to MyISAM that was introduced in recent 4.1.23 and 5.0 and 5.1 which breaks when one wants to upgrade to […]
Hi Venu, I have similar problem. I should transfer my databases from old to new data server. Its on FreeBSD and I installed in from th ports. On my old data server I have server version: 4.1.18 and on new mysql-server-5.1.24_1. Whenever I try to transfer db from one to another, after I do myisamchk –safe-recover of all my tables in all my databases, I still get
ERROR 1034 (HY000): Incorrect key file for table ‘radacct’; try to repair it
mysql> repair table radacct;
+————–+——–+———-+———————————————————-+
| Table | Op | Msg_type | Msg_text |
+————–+——–+———-+———————————————————-+
| rudb.radacct | repair | Error | Incorrect key file for table ‘radacct’; try to repair it |
| rudb.radacct | repair | error | Corrupt |
+————–+——–+———-+———————————————————-+
2 rows in set (0.00 sec)
What should I do?