MySQL REPAIR TABLE

How to Fix a Corrupted Table in MySQL?

MySQL Repair Table allows us to repair or fix the corrupted table. The repair table in MySQL provides support only for selected storage engines, not for all. It is to ensure that we have a few privileges like SELECT and INSERT to use this statement. Normally, we should never use the repair table until disastrous things happen with the table. This statement rarely gets all data from the MyISAM table. Therefore, we need to find why our table is corrupted to eliminate the use of this statement.

When we execute the REPAIR TABLE statement, it first checks the table that we are going to repair is required an upgradation or not. If required, it will perform upgradation with the same rules as CHECK TABLE ... FOR UPGRADE statement works. It is always good to keep our table's backup before performing the "table repair" option because it might cause a loss of our data.

Syntax

The following is the syntax to repair a corrupted table in MySQL:

Let us discuss the use of each option in detail.

NO_WRITE_TO_BINLOG or LOCAL: It's a place where the server is responsible for writing the REPAIR TABLE statements for the replication slaves. We can optionally specify the optional NO_WRITE_TO_BINLOG/LOCAL keyword to suppress the logging.

QUICK: The quick option allows the REPAIR TABLE statement for repairing only the index file. It does not allow to repair of the data file. This type of repair gives the same result as the myisamchk --recover -quick command works.

EXTENDED: Instead of creating the index row by row, this option allows MySQL to create one index at a time with sorting. This type of repair gives the same result as the myisamchk --safe-recover command works.

USE_FRM: This option is used when the .MYI index file is not found or if its header is corrupted. The USE-FRM option informs MySQL to do not trust the information present in this file header and re-create it by using the information provided from the data dictionary. This type of repair cannot work with the myisamchk command.

Storage Engine and Partitioning Support with Repair Table

We have mentioned earlier that the repair table does not work for all storage engines. It supports only MyISAM, ARCHIVE, and CSV tables. The repair table statement does not support views.

We can also use the repair table statement for partitioned tables. But, here, we cannot use the USE_FRM option with this statement. If we want to repair multiple partitions, we can use the ALTER TABLE ... REPAIR PARTITION statement.

MySQL REPAIR TABLE Example

Let us understand the working of the repair table statement in MySQL through example. First, we need to create a new table named vehicle in the selected database as follows:

Next, we will insert some data into this table with the below statement:

Next, execute the below statement to verify the data:

We should get the below result:

Next, we will execute the below statement to check the storage engine of the vehicle table:

After executing the statement, we should get the below output:

Here we can see that the storage engine of the vehicle table is InnoDB. Therefore, if we create the repair table using the below query for this storage engine, MySQL issued an error:

See the below output:

To remove this error, we first need to alter the table storage engine to MyISAM with the following query and then used the repair table statement.

We will get the below output:

In this output, we can see that the REPAIR TABLE statement contains the following columns in the result set:

Let us see another example to use a repair table statement with any QUICK, EXTENDED or USE_FRM options. Thus, we will first create another table named memberships and stored this table in the "MyISAM" storage engine instead of the default one InnoDB.

We will insert some data into this table with the below statement:

Next, execute the SELECT statement to verify the data. We will get the below result:

Since we have created the MyISAM storage engine table, the repair table statement does not issue any error. See the below statement:

We should get the output as follows:

If we use the REPAIR TABLE statement with the table that does not exist in our selected database, MySQL gives an error message. See the below statement:

After execution, we will get the following output:

In this article, we have learned how to repair the corrupted table in MySQL using the Repair Table statement. This statement works only for certain storage engines. Thus, before using this query, we first check the table storage engine supports it or not. If it is not supported, we need to change it into MyISAM, ARCHIVE, or CSV. It is always good to keep our table's backup before performing the "table repair" query because it might cause a loss of our data.