i2tutorials

MySQL –  Repair Table

MySQL –  Repair Table

 

MySQL Repair Table allows us to repair or fix corrupted tables. MySQL provides support for only a limited number of storage engines, not for all of them. To use this statement, we must have a few privileges such as SELECT and INSERT. Generally, we should not use the repair table unless something disastrous happens to the table. In most cases, this statement does not retrieve all the data from the MyISAM table. To eliminate the use of this statement, we must determine why our table is corrupted.

As soon as we execute the REPAIR TABLE statement, it checks to see if the table that we are going to repair requires an upgrade or not. It will perform the upgrade according to the same rules as the CHECK TABLE … FOR UPGRADE statement works. When performing the “table repair” option, it is always a good idea to keep a backup of our table.

Syntax

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]  
   TABLE tbl_name [, tbl_name] ...  
   [QUICK] [EXTENDED] [USE_FRM] 

The use of each option will be discussed in more detail below.

NO_WRITE_TO_BINLOG or LOCAL:  In In this section, the server is responsible for writing the REPAIR TABLE statements on behalf of the replication slaves. To suppress logging, we can optionally specify NO_WRITE_TO_BINLOG/LOCAL.

QUICK: With the quick option, only the index file can be repaired by using the REPAIR TABLE statement. It is not possible to repair the data file. The result of this type of repair is the same as that of myisamchk –recover -quick.

EXTENDED: This option allows MySQL to create one index at a time with sorting instead of row by row. Myisamchk –safe-recover gives the same results as this type of repair.

USE_FRM: The .MYI index file cannot be found or its header is corrupted when this option is selected. USE-FRM instructs MySQL not to trust the information within this file header and to re-create it using the information provided by the data dictionary. With the myisamchk command, this type of repair is not possible.

Example

Here is an example of how the repair table statement works in MySQL. The first step is to create a new table named cust in the selected database as follows:

CREATE TABLE cust (
cust_no VARCHAR(18) PRIMARY KEY,
cust_name VARCHAR(45),
cust_price DECIMAL(10,2 ),
sell_price DECIMAL(10,2)
);

The following statement will be used to insert some data into this table:

INSERT INTO cust (cust_no, cust_name, cust_price, sell_price)
VALUES('S2001', 'Scorpio', 950000, 1000000),
('M3000', 'Mercedes', 2500000, 3000000),
('R0001', 'Rolls Royas', 75000000, 85000000);

To verify the data, execute the following statement:

SELECT * FROM cust;  

r1

To check the storage engine of the vehicle table, we will run the following statement:

SELECT table_name, engine
FROM information_schema.tables
WHERE table_name = 'cust';

The following output should be obtained after executing the statement:

The storage engine used by the cust table is InnoDB. As a result, MySQL issued an error when we attempted to create the repair table using the below query for this storage engine:

REPAIR TABLE cust; 

See the below output:

 

Exit mobile version