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:
REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]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:
CREATE TABLE vehicle (
vehicle_no VARCHAR(18) PRIMARY KEY,
model_name VARCHAR(45),
cost_price DECIMAL(10,2 ),
sell_price DECIMAL(10,2)
);Next, we will insert some data into this table with the below statement:
mysql> INSERT INTO vehicle (vehicle_no, model_name, cost_price, sell_price)
VALUES('S2001', 'Scorpio', 950000, 1000000),
('M3000', 'Mercedes', 2500000, 3000000),
('R0001', 'Rolls Royas', 75000000, 85000000);
Next, execute the below statement to verify the data:
mysql> SELECT * FROM vehicle; We should get the below result:
Next, we will execute the below statement to check the storage engine of the vehicle table:
mysql> SELECT table_name, engine
FROM information_schema.tables
WHERE table_name = 'vehicle';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:
mysql> REPAIR TABLE vehicle; 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.
mysql> ALTER TABLE vehicle ENGINE = 'MyISAM';
//Now, use the repair table query
mysql> REPAIR TABLE vehicle;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:
| SN | Column Name | Descriptions |
|---|---|---|
| 1. | Table | This column indicates the name of the table. |
| 2. | Op | This column always contains repair word whether the storage engine supports or not with the statement. |
| 3. | Msg_type | This column can be either status, error, info, note, or warning. |
| 4. | Msg_text | This column consists of the informational message. |
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.
CREATE TABLE memberships (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(55) NOT NULL,
email VARCHAR(55) NOT NULL,
plan VARCHAR(45) NOT NULL,
validity_date DATE NOT NULL
) ENGINE = MyISAM;We will insert some data into this table with the below statement:
mysql> INSERT INTO memberships (name, email, plan, validity_date)
VALUES('Stephen', '[email protected]', 'Gold', '2020-06-13'),
('Jenifer', '[email protected]', 'Platinum', '2020-06-10'),
('david', '[email protected]', 'Silver', '2020-06-15');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:
mysql> REPAIR TABLE memberships QUICK EXTENDED; 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:
mysql> REPAIR TABLE service_memberships QUICK EXTENDED; 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.
Leave a Reply