REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLEtbl_name
[,tbl_name
] ... [QUICK] [EXTENDED] [USE_FRM]
REPAIR TABLE
repairs a possibly
corrupted table. By default, it has the same effect as
myisamchk --recover
tbl_name
.
REPAIR TABLE
works for
MyISAM
and for ARCHIVE
tables. Starting with MySQL 5.1.9, REPAIR
is
also valid for CSV
tables. See
Section 13.5, “The MyISAM
Storage Engine”, and
Section 13.12, “The ARCHIVE
Storage Engine”, and
Section 13.13, “The CSV
Storage Engine”
This statement requires SELECT
and INSERT
privileges for the
table.
Beginning with MySQL 5.1.27, REPAIR
TABLE
is also supported for partitioned tables.
However, the USE_FRM
option cannot be used
with this statement on a partitioned table.
Also beginning with MySQL 5.1.27, you can use ALTER
TABLE ... REPAIR PARTITION
to repair one or more
partitions; for more information, see
Section 12.1.7, “ALTER TABLE
Syntax”, and
Section 18.3.3, “Maintenance of Partitions”.
Normally, you should never have to run
REPAIR TABLE
. However, if
disaster strikes, this statement is very likely to get back all
your data from a MyISAM
table. If your tables
become corrupted often, you should try to find the reason for
it, to eliminate the need to use REPAIR
TABLE
. See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”, and
Section 13.5.4, “MyISAM
Table Problems”.
It is best to 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.
If the server dies 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 , 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, 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.
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 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 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.
As of MySQL 5.1.25, 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
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
.
Prior to MySQL 5.1.25, do not use
USE_FRM
if your table was created by a
different version of the MySQL server. Doing so risks the loss
of all rows in the table. It is particularly dangerous to use
USE_FRM
after the server returns this
message:
Table upgrade required. Please do
"REPAIR TABLE `tbl_name
`"
or dump/reload to fix it!
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 Section 12.4.2.3, “CHECK TABLE
Syntax”, for more
information. As of MySQL 5.1.25, 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
.
User Comments
false positive case after upgrade
the character sets dir configuration was missing and no tables repair was needed.
i had a strange issue.
i had upgraded mysql from public repository with apt-get
and after then had disk full error and had to restart.
after repairing the disk full error i have discovered the data selected from tables is gibberish.
in phpmyadmin the type of all tables was VIEW
and they all ware corrupt even if i repair them or optimize or check... and when i repair it with myisamchk it does nothing.
just shows :
myisamchk -eron emaillist, the errors:
- recovering (with sort) MyISAM-table 'emaillist'
Data records: 4255
- Fixing index 1
Found link that points at -1735598930481103523 (outside data file) at 27888
Found block that points outside data file at 268252
Found block that points outside data file at 268572
Found block that points outside data file at 268844
Found block that points outside data file at 268916
and nothing was changed after the repair.
in phpmyadmin when i click on a table it selected SHOW FULL COLUMNS and it showd an error similar to: the table is corrupt unknown COLLATIONS #16 error #1273
i have started to search, where are those collation numbers came from, and found that in mysql schema database there is a collations table
and my number 16 was missing
and i saw that the list is suspiciously small.
when i ran 'mysql --help' there was no charset directory
the solution was to set the in my.cnf
[mysqld]
character-sets-dir=/usr/share/mysql/charsets
and it worked like a charm
Add your own comment.