Friday, December 28, 2012

How to Fix MySQL Database Error: “incorrect key file for table try to repair”

The error message “incorrect key file for table try to repair” occurs when I was trying to select database from the table. I really shocked 'why the error occurred' even I did not perform any action on the database. I was searching the proper steps to fix this error & also ensure that it does not happen in the future again. Finally after a long search over the internet, I found that this error not even occur with myi table but can also occur with temp tables. Here is the full description of two scenarios: 

1. While you are trying to select data from the table:

Error: “Incorrect key file for table '.\XXX.MYI'; try to repair it”
Reason: This is the symptom of myi table corruption!!

Solution: 

1. Stop mysql
2. rename the .myi file  
3. restart mysql
4. REPAIR TABLE -- MySQL will see that the .MYI file is missing and rebuild it. But this time, it will be in a different spot on disk.

2. Whenever you try to fetch records from a table:  error:

Error: “Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it”

Reason: Sometimes this error happens with temp tables, it means that there isn't enough disk space left on the partition where the temporary file would be written. Presumably MySQL has started to write data out to the file but then the disk space runs out, and that's when it errors out., it's likely you've run out of disk space. It's a temporary table, so you can't repair it. 

Solution: The simple solution of this error is to check your disk space, and make sure there should be some enough free space. You can do free some space by compressing and/or delete some old log files and backup files.

In my case, the error message occurs with myi table & get resolved by first method. so I only needed to repair myi table. 

No comments:

Post a Comment