MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLEtbl_name
[,tbl_name
] ... [QUICK] [EXTENDED] [USE_FRM]
REPAIR TABLE
repairs a possibly
corrupted table, for certain storage engines only.
This statement requires SELECT
and INSERT
privileges for the
table.
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
Section B.3.3.3, “What to Do If MySQL Keeps Crashing”, and
Section 15.2.4, “MyISAM Table Problems”.
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 Section 13.7.2.2, “CHECK TABLE Statement”, for more
information.
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 Chapter 7, Backup and Recovery.
If the server exits 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.
In the event that a table on the source becomes corrupted
and you run REPAIR TABLE
on
it, any resulting changes to the original table are
not propagated to replicas.
REPAIR TABLE
works for
MyISAM
,
ARCHIVE
, and
CSV
tables. For
MyISAM
tables, it has the same
effect as myisamchk --recover
tbl_name
by default. This
statement does not work with views.
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 Section 13.1.8, “ALTER TABLE Statement”, and
Section 22.3.4, “Maintenance of Partitions”.
NO_WRITE_TO_BINLOG
or
LOCAL
By default, the server writes REPAIR
TABLE
statements to the binary log so that they
replicate to replicas. To suppress logging, specify the
optional NO_WRITE_TO_BINLOG
keyword or
its alias LOCAL
.
QUICK
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.
EXTENDED
If you use the EXTENDED
option, MySQL
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.
USE_FRM
The USE_FRM
option is available for use
if the .MYI
index file is missing or
if its header is corrupted. This option tells MySQL 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.
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 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 that
USE_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 MySQL server
than the one you are currently running,
REPAIR TABLE
does 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 used,
REPAIR TABLE
does not
check the table to see whether an upgrade is required.
REPAIR TABLE
returns a result
set with the columns shown in the following table.
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
. For a
MyISAM
table, if you do not get
OK
, 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
.)
REPAIR TABLE
table catches and
throws any errors that occur while copying table statistics
from the old corrupted file to the newly created file. For
example. if the user ID of the owner of the
.frm
, .MYD
, or
.MYI
file is different from the user ID
of the mysqld process,
REPAIR TABLE
generates a
"cannot change ownership of the file" error unless
mysqld is started by the
root
user.
REPAIR TABLE
upgrades a table
if it contains old temporal columns in pre-5.6.4 format
(TIME
,
DATETIME
, and
TIMESTAMP
columns without
support for fractional seconds precision) and the
avoid_temporal_upgrade
system
variable is disabled. If
avoid_temporal_upgrade
is
enabled, REPAIR TABLE
ignores
the old temporal columns present in the table and does not
upgrade them.
To upgrade tables that contain such temporal columns, disable
avoid_temporal_upgrade
before
executing REPAIR TABLE
.
You may be able to increase REPAIR
TABLE
performance by setting certain system
variables. See Section 8.6.3, “Optimizing REPAIR TABLE Statements”.