I'm trying to migrate a mysql server to new hardware. The old server runs mysql 4.1.12 on RHEL 4; the new server is mysql 5.1.41 on Ubuntu 10.04. I've been having some strange issues trying to migrate one of the databases. I tried using mysqldump, but the file won't import properly into the new server because it complains about duplicate keys on a particular table, even though I've checked and am pretty sure there are in fact no duplicate keys.
Since I spent several days fighting with the mysqldump approach without finding a solution, I decided instead to copy the entire /var/lib/mysql directory from the old server to the new one. This seemed to work well, with mysql starting up without complaining and all of the data seemingly present. However, a Web application that uses one of the databases fails to work, complaining, "Incorrect key file for table 'activities'; try to repair it, line194" (interestingly, the table that I'm having trouble with is NOT the one that mysqldump was unable to import due to duplicate key errors, although they are both on the same database).
I've run REPAIR TABLE on this table from within the mysql shell, but it fails with the message "Corrupt."
I also tried myisamchk with the --recover and --safe-recover flags for this table. In both cases, it says it fixed errors, but the Web application still complains about an incorrect key file.
What confuses me most is that (while mysqld is shut down) I've run an md5sum on the MYI file for the table in question, and it's identical on both the old server and the new server. If the table files are identical on the two machines, I'm puzzled as to why the key file is incorrect on one but not the other. I almost suspect this is a bug with mysql, but I'm not positive.
I'd be really grateful for any help or suggestions, as I'm out of ideas on how to solve this. I'm no mysql guru, so maybe I'm missing something obvious.
