MySQL - Repair Tables

MySQL - Repairing Tables

Critical Advisory: REPAIR TABLE and Storage Engines

IMPORTANT: The REPAIR TABLE statement is designed almost exclusively for the MyISAM storage engine. Modern MySQL installations use InnoDB as the default storage engine, which is a transactional, crash-safe engine with its own automatic recovery mechanisms. Running REPAIR TABLE on an InnoDB table will result in an error stating the storage engine does not support repair. This tutorial focuses on the use of REPAIR TABLE in its intended context: maintaining legacy MyISAM tables.

Before attempting a repair, always check your table’s storage engine:

SHOW CREATE TABLE your_table_name;

Look for the ENGINE= value in the output. If it says InnoDB, do not use REPAIR TABLE. Instead, rely on MySQL’s crash recovery or restore from a backup.

Understanding Table Corruption

MyISAM tables, unlike InnoDB, are not transactional and are more susceptible to corruption from events like unexpected server shutdowns, hardware failures, or OS crashes. Corruption can manifest as errors when trying to read from a table, incorrect data being returned, or a complete inability to access the table.

The REPAIR TABLE statement attempts to fix these issues by inspecting the table’s data and index files (.MYD and .MYI) and correcting any inconsistencies it finds.

The REPAIR TABLE Statement for MyISAM

Syntax

REPAIR [TABLE] table_name [, table_name] ... [QUICK] [EXTENDED] [USE_FRM];

Example

Let’s create a table explicitly using the MyISAM engine to demonstrate.

CREATE TABLE LegacyLogs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255) NOT NULL,
    log_time TIMESTAMP
) ENGINE=MyISAM; -- Explicitly set engine to MyISAM

INSERT INTO LegacyLogs (message, log_time) VALUES ('System start', NOW());

Now, let’s assume this table has become corrupted. To repair it, you would run:

REPAIR TABLE LegacyLogs;

Output

The output is a result set that indicates the status of the repair operation. A successful repair on a non-corrupted table will look like this:

Table	Op	Msg_type	Msg_text
your_database.legacylogs	repair	status	OK

If the table was corrupted, the Msg_type and Msg_text would show info, warning, or error messages detailing the problems found and fixed.

Repairing Multiple Tables

You can repair several tables in a single command by providing a comma-separated list.

CREATE TABLE OldUsers (id INT) ENGINE=MyISAM;
CREATE TABLE OldSettings (id INT) ENGINE=MyISAM;

-- Repair both tables at once
REPAIR TABLE OldUsers, OldSettings;

The result set will contain one row for each table processed.

REPAIR TABLE Options (QUICK, EXTENDED, USE_FRM)

QUICK

The QUICK option performs a faster repair that only checks the index file (.MYI) and does not modify the data file (.MYD). This is useful for fixing minor index corruption.

REPAIR TABLE LegacyLogs QUICK;

EXTENDED

The EXTENDED option performs the most thorough repair. It goes through the data file row-by-row and re-creates the indexes from scratch. This is much slower but can fix corruption that other methods cannot.

REPAIR TABLE LegacyLogs EXTENDED;

USE_FRM

The USE_FRM option is a last-resort measure used when the .MYI index file is missing or its header is corrupted. It tells MySQL to rebuild the index file based on the information in the table’s definition file (.frm) and data file (.MYD).

REPAIR TABLE LegacyLogs USE_FRM;

Repairing MyISAM Tables Using a Client Program

You can execute REPAIR TABLE from any client program. The key is to correctly parse the multi-row result set to check the status of each repaired table.

Node.js (mysql2)
Python (mysql-connector-python)
Java (JDBC)
PHP (mysqli)


This Node.js example repairs multiple tables and logs the result for each one.

```javascript
// Requires: npm install mysql2
const mysql = require('mysql2/promise');

async function repairLegacyTables() {
  let connection;
  try {
    connection = await mysql.createConnection({
        host: 'localhost', user: 'root', password: 'your_password', database: 'your_database'
    });
    console.log('Connected. Attempting to repair tables...');

    const [results] = await connection.query('REPAIR TABLE LegacyLogs, OldUsers, OldSettings');

    console.log('Repair operation finished. Results:');
    results.forEach(result => {
      console.log(`- Table: ${result.Table}, Operation: ${result.Op}, Status: ${result.Msg_type}, Message: ${result.Msg_text}`);
    });

  } catch (error) {
    console.error(`An error occurred: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

repairLegacyTables();

Python’s mysql-connector-python can be used to execute the command and iterate over the results.

# Requires: pip install mysql-connector-python
import mysql.connector

def repair_legacy_tables():
    try:
        with mysql.connector.connect(user='root', password='your_password', database='your_database') as conn:
            with conn.cursor(dictionary=True) as cursor:
                print('Connected. Attempting to repair tables...')
                cursor.execute('REPAIR TABLE LegacyLogs, OldUsers, OldSettings')

                print('Repair operation finished. Results:')
                for result in cursor.fetchall():
                    print(f"- Table: {result['Table']}, Op: {result['Op']}, Status: {result['Msg_type']}, Message: {result['Msg_text']}")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")

repair_legacy_tables()

In Java, you execute the query and loop through the ResultSet to get the status for each table.

import java.sql.*;

public class RepairTableExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String sql = "REPAIR TABLE LegacyLogs, OldUsers, OldSettings";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("Repair operation finished. Results:");
            while (rs.next()) {
                System.out.printf("- Table: %s, Op: %s, Status: %s, Message: %s%n",
                    rs.getString("Table"),
                    rs.getString("Op"),
                    rs.getString("Msg_type"),
                    rs.getString("Msg_text"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PHP’s mysqli extension can run the REPAIR TABLE query and fetch the results.

<?php
$mysqli = new mysqli('localhost', 'root', 'your_password', 'your_database');
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "REPAIR TABLE LegacyLogs, OldUsers, OldSettings";

if ($result = $mysqli->query($sql)) {
    echo "Repair operation finished. Results:\n";
    while ($row = $result->fetch_assoc()) {
        printf("- Table: %s, Op: %s, Status: %s, Message: %s\n",
            $row['Table'],
            $row['Op'],
            $row['Msg_type'],
            $row['Msg_text']);
    }
    $result->free();
} else {
    echo "Error repairing tables: " . $mysqli->error;
}

$mysqli->close();
?>