MySQL repair myisam table fails


I play the role of the “MySQL administrator” in some cases without having the appropriate expertise or knowledge, and in some cases, I find myself against some first time seeing situations. One situation like this I had never faced before was to fail the repair table command when a myIsam table is corrupted. For everyone there is a first time. Let’s see what the problem was:

I got connected through mysql client as root to the database server, I selected my database through use command and tried to execute the repair:

mysql -h localhost -u root -p
use database_name;
repair table table_name;

The output was:

Found link that points at 5427775770519881079 outside data file) at 788525176
Found block with too small length at 872968268; Skipped
Found link that points at 5131911463743613025 (outside data file) at 886920912
Found link that points at 5131911463743613025 (outside data file) at 888429508
Found block with too small length at 892989976; Skipped
Found block with too small length at 893943488; Skipped
Found block with too small length at 896942348; Skipped
Not enough memory for blob at 919756416 (need 1416319567)
Operation failed

This error probably means that your variable sort_buffer_size is not high enough to repair the table.
Everytime I tried to repair a table it was a success story! And now what? Don’t get anxious! There is another way to repair your corrupted myIsam table. There is a command line tool, myisamchk that you can use. Now take some care because this tool needs to be the only process that uses the corrupted table. You can choose to shutdown the database server, or there are lot of critical applications running on this database server, you have to stop the services that use the database which has the corrupted table. For example the database in which I had the corrupted table was used by two applications, webmail and sympa mailing list server. The corrupted table was inside the database sympa uses. I took the sympa service offline and then I was sure that this database was not accessed by anything else.

Thus, after reassuring that noone access the table you want to repair, you have to do the following steps:
1. cd to the data dir of the database where table_name.MYI file exists. Lets say mine was at :/var/data/mysql/data/sympa/
cd /var/data/mysql/data/sympa/
2. Check what’s your available free memory using something like top, leave a little memory ballon and decice the value of sort_buffer_size. Let’s use an example, if top gives about 3Gb of total ram free and cached then use sort_buffer_time=2G as soon in the example below:
myisamchk -o -f table_name.MYI -sort_buffer_size=2G
3. If this is successfull you are done! Just check the table status:

mysql -h localhost -u root -p
use database_name;
check table table_name;

else if the operation failed, maybe it’s a good idea to shutdown the mysql server, to free more ram and try again with higher value of sort_buffer_size.

It’s good to see this MySQL article too:

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove me that you are not a robot :) * Time limit is exhausted. Please reload the CAPTCHA.