MySQL REPAIR TABLE Statement

Summary: in this tutorial, you will learn how to use the MySQL REPAIR TABLE statement to repair corrupted tables.

Introduction to MySQL REPAIR TABLE statement

During operation, your tables may be corrupted due to various reasons, such as hardware failures, unexpected shutdowns, or software bugs.

To repair the possibly corrupted tables, you use the REPAIR TABLE statement. The REPAIR TABLE statement can repair only tables that use MyISAM, ARCHIVE, or CSV storage engines.

Here’s the syntax of the REPAIR TABLE statement:

REPAIR TABLE table_name [, table_name] ... 
[QUICK] [EXTENDED] [USE_FRM];Code language: SQL (Structured Query Language) (sql)

In this syntax:

table_name: The name of the table you want to repair. The statement allows you to repair multiple tables at once.
QUICK: This is the default option that allows you to perform a quick repair. It is suitable for most cases.
EXTENDED: This option allows you to perform an extended repair. It may take longer however can fix more complex issues.
USE_FRM: This option re-creates the .frm file. It’ll help when the table definition file is corrupted.

MySQL REPAIR TABLE statement examples

Let’s take some examples of using the REPAIR TABLE statement.

The following command performs a quick repair on the sample_table table:

REPAIR TABLE sample_table;Code language: SQL (Structured Query Language) (sql)

It’s equivalent to the following statement that uses the QUICK option explicitly:

REPAIR TABLE sample_table QUICK;Code language: SQL (Structured Query Language) (sql)

The following command performs an extended repair on the sample_table table:

REPAIR TABLE sample_table EXTENDED;Code language: SQL (Structured Query Language) (sql)

When you find that the table definition file (.frm) is suspected to be corrupted, you can use the USE_FRM option to recreate it:

REPAIR TABLE sample_table USE_FRM;Code language: SQL (Structured Query Language) (sql)

Important notes on using the REPAIR TABLE statement

When using the REPAIR TABLE statement, you should consider the following important notes:

Making a backup before repairing tables

It’s important to make a backup of a table before you repair it. In some cases, the REPAIR TABLE statement may cause data loss.

Table lock

The REPAIR TABLE statement requires a table lock during the repair process. If you issue queries to the table, they will blocked until the repair is complete.

Storage Engines

The REPAIR TABLE statement only works with MyISAM, CSV, and ARCHIVE tables. It doesn’t support tables of other storage engines.

Replication

If they run the REPAIR TABLE for the original tables, the fixes will not propagate to replicas.

Summary

Use the REPAIR TABLE statement to repair possibly corrupted tables.

Was this tutorial helpful?