REPAIR TABLE
Syntax
REPAIR TABLE
repairs a possibly corrupted table, for certain storage engines only. By default, it has the same effect as myisamchk --recover tbl_name
.Note
REPAIR TABLE
only applies to MyISAM
, ARCHIVE
, and CSV
tables. See , "The MyISAM
Storage Engine", and , "The ARCHIVE
Storage Engine", and , "The CSV
Storage Engine"
This statement requires SELECT
and INSERT
privileges for the table.
REPAIR TABLE
is supported for partitioned tables. However, the USE_FRM
option cannot be used with this statement on a partitioned table.
You can use ALTER TABLE ... REPAIR PARTITION
to repair one or more partitions; for more information, see , "ALTER TABLE
Syntax", and , "Maintenance of Partitions".
Although normally you should never have to run REPAIR TABLE
, if disaster strikes, this statement is very likely to get back all your data from a MyISAM
table. If your tables become corrupted often, try to find the reason for it, to eliminate the need to use REPAIR TABLE
. See "What to Do If MariaDB Keeps Crashing", and , "MyISAM
Table Problems".Caution
Make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors. See , Backup and Recovery.Warning
If the server crashes during a REPAIR TABLE
operation, it is essential after restarting it that you immediately execute another REPAIR TABLE
statement for the table before performing any other operations on it. In the worst case, you might have a new clean index file without information about the data file, and then the next operation you perform could overwrite the data file. This is an unlikely but possible scenario that underscores the value of making a backup first.
REPAIR TABLE
returns a result set with the following columns.
Column | Value |
---|---|
Table
| The table name |
Op
| Always repair
|
Msg_type
| status , error , info , note , or warning
|
Msg_text
| An informational message |
The REPAIR TABLE
statement might produce many rows of information for each repaired table. The last row has a Msg_type
value of status
and Msg_test
normally should be OK
. If you do not get OK
for a MyISAM
table, you should try repairing it with myisamchk --safe-recover. (REPAIR TABLE
does not implement all the options of myisamchk.) With myisamchk --safe-recover, you can also use options that REPAIR TABLE
does not support, such as --max-record-length
.
If you use the QUICK
option, REPAIR TABLE
tries to repair only the index file, and not the data file. This type of repair is like that done by myisamchk --recover --quick.
If you use the EXTENDED
option, MariaDB creates the index row by row instead of creating one index at a time with sorting. This type of repair is like that done by myisamchk --safe-recover.
The USE_FRM
option is available for use if the .MYI
index file is missing or if its header is corrupted. This option tells MariaDB not to trust the information in the .MYI
file header and to re-create it using information from the .frm
file. This kind of repair cannot be done with myisamchk.Note
Use the USE_FRM
option only if you cannot use regular REPAIR
modes! Telling the server to ignore the .MYI
file makes important table metadata stored in the .MYI
unavailable to the repair process, which can have deleterious consequences:
- The current
AUTO_INCREMENT
value is lost. - The link to deleted records in the table is lost, which means that free space for deleted records will remain unoccupied thereafter.
- The
.MYI
header indicates whether the table is compressed. If the server ignores this information, it cannot tell that a table is compressed and repair can cause change or loss of table contents. This means thatUSE_FRM
should not be used with compressed tables. That should not be necessary, anyway: Compressed tables are read only, so they should not become corrupt.
If you use USE_FRM
for a table that was created by a different version of the MariaDB server than the one you are currently running, REPAIR TABLE
will not attempt to repair the table. In this case, the result set returned by REPAIR TABLE
contains a line with a Msg_type
value of error
and a Msg_text
value of Failed repairing incompatible .FRM file
.
If USE_FRM
is not used, REPAIR TABLE
checks the table to see whether an upgrade is required. If so, it performs the upgrade, following the same rules as CHECK TABLE ... FOR UPGRADE
. See , "CHECK TABLE
Syntax", for more information. REPAIR TABLE
without USE_FRM
upgrades the .frm
file to the current version.
By default, REPAIR TABLE
statements are written to the binary log so that they will be replicated to replication slaves. Logging can be suppressed with the optional NO_WRITE_TO_BINLOG
keyword or its alias LOCAL
.Important
In the event that a table on the master becomes corrupted and you run REPAIR TABLE
on it, any resulting changes to the original table are not propagated to slaves.
You may be able to increase REPAIR TABLE
performance by setting certain system variables. See , "Speed of REPAIR TABLE
Statements".