MySQL :: MySQL 5.0 Reference Manual :: 12.5.2.6 REPAIR TABLE

╰☆惢棂擱淺

Recommended Servers for MySQL
The world's most popular open source database
Contact a MySQL Representative
Login | Register


MySQL.com
Downloads
Developer Zone
Partners & Solutions
Customer Login
DevZone
Documentation
Librarian
Articles
Forums
Bugs
Forge
Planet MySQL
Labs
   

Documentation Library
Table of Contents
MySQL 5.4 Reference Manual
MySQL 5.1 Reference Manual
MySQL 5.0 Reference Manual
MySQL 3.23/4.0/4.1 Manual
Search manual:
   


Additional languages
French
Spanish






MySQL 5.0 Reference Manual :: 12 SQL Statement Syntax :: 12.5 Database Administration Statements :: 12.5.2 Table Maintenance Statements :: 12.5.2.6 REPAIR TABLE Syntax
? 12.5.2.5 OPTIMIZE TABLE Syntax


12.5.2.7 RESTORE TABLE Syntax ?
Section Navigation      [Toggle]
12.5.2 Table Maintenance Statements
12.5.2.1 ANALYZE TABLE Syntax
12.5.2.2 BACKUP TABLE Syntax
12.5.2.3 CHECK TABLE Syntax
12.5.2.4 CHECKSUM TABLE Syntax
12.5.2.5 OPTIMIZE TABLE Syntax
12.5.2.6 REPAIR TABLE Syntax
12.5.2.7 RESTORE TABLE Syntax
12.5.2.6.聽REPAIR TABLE Syntax
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_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. See Section聽13.1, 鈥淭he MyISAM Storage Engine鈥?/a>, and Section聽13.8, 鈥淭he ARCHIVE Storage Engine鈥?/a>.
This statement requires SELECT and INSERT privileges for the table.
Normally, you should never have to run this statement. However, if disaster strikes, REPAIR TABLE 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.1.4.2, 鈥淲hat to Do If MySQL Keeps Crashing鈥?/a>, and Section聽13.1.4, 鈥?code class="literal">MyISAM Table Problems鈥?/a>.
Caution
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.
Warning
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.
ColumnValue
TableThe table name
OpAlways repair
Msg_typestatus, error, info, or warning
Msg_textAn 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 tree. 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.
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 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.
Caution
As of MySQL 5.0.62, 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.0.62, 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`" 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.5.2.3, 鈥?code class="literal">CHECK TABLE Syntax鈥?/a>, for more information. As of MySQL 5.0.62, 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.
Previous / Next / Up / Table of Contents


User Comments
Posted by shimon doodkin on September 14 2009 1:50am[Delete] [Edit]

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.
Top / Previous / Next / Up / Table of Contents
? 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Online Shop
Site Map
About MySQL
Legal
Privacy Policy
Contact Us
Job Opportunities

hailar

有时候，不是对方不在乎你，而是你把对方看的太重。