MySQL - Repair Tables

MySQL Repair Table Statement

There can be scenarios where tables in databases can become corrupted due to various reasons such as hardware failures, software bugs, or unexpected server crashes. When this situation happens, we cannot be able to access or manipulate the data in those tables because of data inconsistencies or errors.

In such situations, to repair those currupted tables, we use the MySQL REPAIR TABLE statement. This statement works for only certain engines such as MyISAM, etc.

Syntax

Following is the syntax of MySQL REPAIR TABLE Statement −

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

Example

Let us start by creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, we are inserting 7 records into the above created table using the below INSERT statement −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Assume the above created table is corrupted and we are using the REPAIR TABLE statement to repair it.

REPAIR TABLE CUSTOMERS;

The above query displays an error as: "The storage engine for the table doesn't support repair" because the REPAIR TABLE statement won't work with the default InnoDB engine.

To repair the table, We need to change the table's engine to MyISAM because it supports the REPAIR TABLE statement.

ALTER TABLE CUSTOMERS ENGINE = MyISAM;

Now, to repair the CUSTOMERS table, execute the following query −

REPAIR TABLE CUSTOMERS;

Output

We can see in the output below, it says OK which indicates that the table CUSTOMERS is in good condition, and there are no issues or corruption.

Repairing multiple tables

In MySQL, we can also repair multiple tables and get the results using the REPAIR TABLE Statement. To do this, we just need to list the names of the tables we want to repair, separating them with commas.

Example

Let us create three different tables with the names Test1, Test2, and Test3 using the following CREATE TABLE statements −

CREATE TABLE Test1(ID INT, Name VARCHAR(255));
CREATE TABLE Test2(ID INT, Name VARCHAR(255));
CREATE TABLE Test3(ID INT, Name VARCHAR(255));

Assume the above three tables are corrupted. Change the engine of these tables to MyISAM to repair them with REPAIR TABLE statement −

ALTER TABLE Test1 ENGINE = MyISAM;
ALTER TABLE Test2 ENGINE = MyISAM;
ALTER TABLE Test3 ENGINE = MyISAM;

Now, to repair these tables, execute the following query −

REPAIR TABLE Test1, Test2, Test3;

As we can see in the output below, all three tables are in good condition, and there are no issues or corruption.

Repair Table Options

We have various optional clauses to use with REPAIR TABLE such as QUICK, EXTENDED, and, USE_FRM clause. Let us discuss them one by one with suitable examples.

QUICK Clause

The QUICK clause is the is the default and it is most commonly used with REPAIR TABLE. If you specify the QUICK clause, MySQL will repair the table without re-creating it. −

In the following example, we are using the QUICK clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS QUICK;

Executing the query above will produce the following output −

EXTENDED Clause

If we specify the EXTENDED clause, MySQL not only repairs the table but also rebuilds the index and optimizes the table structure.

Note: The EXTENDED clause is a more time-consuming compared to QUICK clause.

In the following example, we are using the EXTENDED clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS EXTENDED;

Executing the query above will produce the following output −

USE_FRM clause

We can use the USE_FRM clause, in case the MYI index file is missing. If you provide this clause the .NYI file will be recreated using information from the data dictionary −

Here, we are using the USE_FRM clause with the REPAIR TABLE statement to repair the CUSTOMERS table.

REPAIR TABLE CUSTOMERS USE_FRM;

Executing the query above will produce the following output −

Repairing table Using a Client Program

Besides repairing a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPAIR TABLE operation.

Syntax

Following are the syntaxes to repair a table in various programming languages −

$sql="Repair TABLE table_names";
$mysqli->query($sql);

sql=" REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
   [QUICK] [EXTENDED] [USE_FRM]";
con.query(sql);

String sql="Repair TABLE table_names";
statement.executeUpdate(sql);

sql="REPAIR TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = " REPAIR TABLE SalesSummary ";
if ($mysqli->query($sql)) {
    printf(" Table repair successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be repaired .
", $mysqli->error);
}
$mysqli->close();

Table repair successfully.

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "Create Database TUTORIALS"
  con.query(sql);

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE sales(ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "insert into sales values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad'),(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam'),(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada'),(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai'),(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');"
  con.query(sql);

  sql = "ALTER TABLE Sales ENGINE = MyISAM;"
  con.query(sql);

  sql = "REPAIR TABLE Sales;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Table: 'tutorials.sales',
    Op: 'repair',
    Msg_type: 'status',
    Msg_text: 'OK'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class RepairTable{
        public static void main(String[] args){
            String url = "jdbc:mysql://localhost:3306/TUTORIALS";
            String username = "root";
            String password = "password";
            try{
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection connection = DriverManager.getConnection(url, username, password);
                Statement statement = connection.createStatement();
                System.out.println("Connected successfully...!");

                //Repair tables...!
                String sql = "REPAIR TABLE customers";
                statement.executeUpdate(sql);
                System.out.println("Table repaired successfully...!");

                connection.close();
            }
            catch(Exception e){
                System.out.println(e);
            }
        }
}

Connected successfully...!
Table repaired successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl_temp'
#Creating a cursor object 
cursorObj = connection.cursor()
repair_table_query = f"REPAIR TABLE {table_name}"
cursorObj.execute(repair_table_query)
print(f"Table '{table_name}' is repaired successfully.")
# Fetch and consume any remaining results from the cursor
# ensuring that there are no unread results before closing the cursor.
for _ in cursorObj:
    pass
cursorObj.close()
connection.close()

Table 'tutorials_tbl_temp' is repaired successfully.