MySQL Corruption: Error 1034 (HY000) : Incorrect key file for table ‘xyz’ ; try to repair it

Scenario: We upgraded from MySQL 5.1 to 5.5 (It was 4.1 in the past and possibly even 3.x before my time!). There seems to be some sort of corruption in a particular DB. The normal myisamchk and REPAIR TABLE functions didnt cut the mustard. Below are my steps to fix the issue. It appears that the problem is related to upgrading the DB. The move from 5.1 to 5.5 seemingly corrupted it.

 

The table "phpbb_phrase" was totally inaccessible. Even a DESCRIBE or SELECT statement brought up the following error:

As mentioned  a repair doesnt work either:

Interestingly enough the error message above noted an Incorrect key file and that the table is corrupt. Further ways of remedying this including stopping the MySQL server and running myisamchk didnt resolve it.

 

The stages to fix this issue I used were:

1. Find the DBDIR via the CLI of the MySQL server and cd into the directory of the desired DB
2. Copy the .MYD file (The MYD file is the data. They MYI file is the pointer) to another directory (eg /home/jordansphere/)
3. Delete all the data from the affected table and do a repair.

mysql -u root -p dbname
SET autocommit=1
TRUNCATE TABLE table_name
quit

4. The .MYD file should now be 0 bytes. Copy the .MYD file back from the other directory (eg /home/jordansphere)
5. Run the REPAIR TABLE command from the MySQL CLI after copying the data file back

That fixed it for me. You can run a "select *" in the table again to make sure the data is back.

Further Note: .frm  is the table definition file