13.7.3.5 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, 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.4.3.3, “What to Do If MySQL Keeps Crashing”, and Section 16.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.3.2, “CHECK TABLE Syntax”, for more information.

Important

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 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.
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.

REPAIR TABLE Storage Engine and Partitioning Support

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.9, “ALTER TABLE Syntax”, and Section 23.3.4, “Maintenance of Partitions”.

Inhaltsverzeichnis Haut

REPAIR TABLE Options

NO_WRITE_TO_BINLOG or LOCAL
By default, the server writes REPAIR TABLE statements to the binary log so that they replicate to replication slaves. 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 data dictionary. This kind of repair cannot be done with myisamchk.
Caution

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.
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.

Inhaltsverzeichnis Haut

REPAIR TABLE Output

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 .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.

Inhaltsverzeichnis Haut

Table Repair Considerations

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”.

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-repair-table.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut