While upgrading MySQL in cPanel from 4.1 to 5.1 some tables may error “Incorrect key file for table ‘XXXX’; try to repair it”.
When you run a repair on the table / database the table reports that it is corrupt. Normally a command like
myisamchk --silent --force --fast *.MYI
This can happen with Drupal, WordPress and a lot of other DB driven websites.
Best option is to restore the table from backup but what if the backup is corrupt as well say you did not notice the site was broken before cPanel does its next backup or the table was corrupt before the upgrade.
This is the task that was handed to me.
There is still a chance for restoring the table but there are some requirements and no guarantee this will work for you;
- Table needs to be MyISAM (have not tested this with InnoDB)
- Need a seconds MySQL server running 4.1
MySQL 5.1 Server – with corrupt table
Firstly Backup the whole database which you are going to try and repair.
Shutdown down MySQL and cp the folder for the database to another location.
mkdir /root/mysqlrecovery/
cp -ar /var/lib/mysql/User_dbname /root/mysqlrecovery/
Now tar this up and send it over to the server running MySQL 4.1.
MySQL 4.1 Server
On the MySQL 4.1 server check to make sure there is no database named the one you are going to use
mysql user_dbname
Should give you an error "Unknown database"
untar the database and cp to /var/lib/mysql/
cp -ar user_dbname /var/lib/mysql/
make sure its own by the right user
chown mysql:mysql /var/lib/mysql/user_dbname
Now you can repair the corrupt table and do a mysqldump
mysql user_dbname
show tables;
This should display a all the tables from your database (or use myisamchk).
REPAIR TABLE table_name USE_FRM;
Repeat for all tables that need to be repaired then quit MySQL command prompt
To check that the repair worked you can do a select on the table and it should report.
echo "select * from table_name;" | mysql user_dbname
Time to mysqldump the tables out.
mysqldump user_dbname table_name > user_dbname-table.sql
Repeat for all tables that need to be repaired.
You can now copy this back to the server with the corrupt table and insert it with the data intact.
MySQL 5.1 Server
Check inside the SQL file to make sure the mysqldump has grabbed the data and to see what it will do when you go to insert the table back into your database.
cat user_dbname-table.sql |more
Insert the tables back into the DB.
cat user_dbname-table.sql | mysql user_dbname
What this will do is drop the current table that is corrupted, recreates the table and inserts the data back into the table.
To check that the database and tables are now working ok again.
echo "select * from table_name;" | mysql user_dbname
I would also now recormend doing a full back up of the database, ether use cpanel or do another mysqldump.
mysqldump user_dbname > user_dbname_backup.sql
Notes
It should not matter if mysql old_password style is set in my.cnf or not.
This may also work for the error table has crashed and should be repaired.
drupal tables noticed with this error – node, system, menu_links.
Any feed back or questions please ask.
Hi Mr.Buckykat,
I’m battling with this error right now. I don’t know if I have any MySQL 4 servers I can use for testing this yet. I’ll see if I can find one and try and work my way through your steps. However when I get deep into MySQL I’m like a dog in a chemistry lab, no idea what I’m doing. If you’re up for it I’m interested in hiring you for an hour or two of troubleshooting on this.
Let me know…
My Buckykat saves lives! It worked perfectly thanks
Glad it worked.
Sorry for the slow reply 😉