4.4.5 REPAIR TABLE Syntax

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE works only on MyISAM tables and is the same as running myisamchk -r table_name on the table.

Normally you should never have to run this command, but if disaster strikes you are very likely to get back all your data from a MyISAM table with REPAIR TABLE. If your tables get corrupted a lot, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE. See section A.4.1 What To Do If MySQL Keeps Crashing. See section 7.1.3 MyISAM Table Problems.

REPAIR TABLE repairs a possibly corrupted table. The command returns a table with the following columns:

Note that the statement may produce many rows of information for each repaired table. The last one row will be of Msg_type status and should normally be OK. If you don't get OK, you should try repairing the table with myisamchk --safe-recover, as REPAIR TABLE does not yet implement all the options of myisamchk. In the near future, we will make it more flexible.

If QUICK is given, REPAIR TABLE tries to repair only the index tree.

If you use EXTENDED, MySQL will create the index row by row instead of creating one index at a time with sorting; this may be better than sorting on fixed-length keys if you have long CHAR keys that compress very well. This type of repair is like that done by myisamchk --safe-recover.

As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR. Use it if the `.MYI' file is missing or if its header is corrupted. In this mode MySQL will recreate the table, using information from the `.frm' file. This kind of repair cannot be done with myisamchk.

Warning: If mysqld dies during a REPAIR TABLE, it's essential that you do at once another REPAIR on the table before executing any other commands on it. (It's of course always good to start with a backup). In the worst case you can have a new clean index file without information about the datafile and when the next command you do may overwrite the datafile. This is not a likely, but possible scenario.

Strictly before MySQL 4.1.1, REPAIR commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

User Comments

MySQL Berkley tables cannot be repaired using
REPAIR TABLE. However, after a BIG hardware
crash, I was able to recover data that I thought
had been lost by;
1) Shutting down all clients
2) Reloading the database (using mysqladmin
reload) then 3) Shutting down and restarting the
mySQL server.

I am truly amazed. :)) Well done guys!

I recently had a table crash. All the data (400K of carefully picked up data like monument descriptions, timetables, addresses, phones and so on) vanished in the air.

Of course I should have made regular backups. Sometimes I do. Generally I make a routine to do it for me while I sleep. But I didn't this once.

I was advised to drop the crashed table and reload it again. However, I thought that could be a little too radical to start with.

So I came to mysql.com, and the only suggestion only was: use REPAIR TABLE <tablename>

So I did. I wrote a small php script:

mysql_connect(your data)

$query="repair table monuments";
$res=mysql_query($query);
echo $res;

I ran the script and got an OK page.

At first I thought it was too quick, too easy, but then I went to see and yes, the table was there, in all its glory There's probably a corrupt record somewhere, but I didn't find it yet.

Hope this helps someone...

No it did not help anyone. You did not say anything that has not already been said.

Add your own comment.