REPAIR TABLE - MySQL

Overview

REPAIR TABLE is a MySQL command used to check and repair damaged tables in a database. It is particularly useful when table corruption occurs due to unexpected system shutdowns, hardware failures, or software bugs.

Syntax

REPAIR TABLE [Repair Options] table_name [, table_name] ...

Repair Options:

  • QUICK: Performs a quick check, repairing only obvious errors.
  • EXTENDED: Performs a more thorough check, repairing most types of corruption.
  • USE_FRM: Repairs the table using its .frm file as a reference.

Options/Flags

  • NO_AUTO_REPAIR: Suppresses automatic repairs.
  • NO_CHECK_CONSTRAINTS: Skips checking for constraint violations during repair.
  • LOCAL: Repairs only the local copy of a table in a multi-source replication setup.

Examples

Simple Repair:

REPAIR TABLE my_table QUICK;

Extended Repair:

REPAIR TABLE my_table EXTENDED;

Repair Using .frm File:

REPAIR TABLE my_table USE\_FRM;

Repair Multiple Tables:

REPAIR TABLE table1, table2, table3;

Common Issues

Cannot Repair File:

  • Ensure the table file exists and is not corrupted beyond repair.
  • Check for insufficient permissions or file system issues.

Constraint Violation:

  • If NO_CHECK_CONSTRAINTS is not used, constraints must be satisfied during repair.
  • Consider temporarily dropping constraints before repairing and recreating them afterwards.

No Changes Made:

  • The table may not be corrupted, or the repair options specified are insufficient.
  • Try using more thorough repair options or consider using the CHECK TABLE command for diagnostics.

Integration

With CHECK TABLE:

  • Use CHECK TABLE to diagnose table issues, then use REPAIR TABLE to fix any identified problems.

With mysqldump/mysqlimport:

  • Repair a table before importing data to ensure data integrity.
  • Dump a table, repair it, then import the data to fix corruption.
  • CHECK TABLE: Checks the consistency of a table.
  • OPTIMIZE TABLE: Removes fragmentation and improves performance.
  • ALTER TABLE … REPAIR: Repairs a table while modifying its structure.