| Problem | Recommended Action | Primary Command |
|---|---|---|
| Minor Corruption | Check table integrity | CHECK TABLE table_name; |
| MyISAM Table Failure | Standard Repair | REPAIR TABLE table_name; |
| InnoDB Table Failure | Rebuild Engine | ALTER TABLE table_name ENGINE=InnoDB; |
| Server-side Lock | Kill Process | KILL [process_id]; |
What is a MySQL Table Not Responding?
A “MySQL table not responding” error occurs when the database engine cannot complete a read or write request. This usually results in application timeouts or a “hanging” state in your database manager.
This issue is commonly caused by table corruption, often triggered by a sudden server power loss or a full disk. It can also happen if a query is stuck in a “Waiting for table metadata lock” state.
In some cases, the table might be physically damaged on the disk. For MyISAM tables, this involves .MYI or .MYD files. For InnoDB, it often involves the tablespace or the redo logs.
Step-by-Step Solutions
Step 1: Identify the Hanging Process
Before repairing, check if the table is actually corrupted or just locked by another process. Run the following command to see active queries:
SHOW FULL PROCESSLIST;If you see a query that has been running for thousands of seconds, you may need to terminate it using the KILL command followed by the ID.
Step 2: Check the Table for Errors
Use the CHECK TABLE command to verify the integrity of your data. This is a non-destructive way to confirm if a repair is actually needed.
CHECK TABLE my_table_name;If the status returned is “OK,” the issue is likely related to server resources or locks. If it returns “Table is marked as crashed,” proceed to the next step.
Step 3: Repairing MyISAM Tables
The REPAIR TABLE command works specifically for MyISAM, ARCHIVE, and CSV tables. It is the fastest way to fix index corruption.
REPAIR TABLE my_table_name;If the standard repair fails, you can try the “extended” version, though it takes longer as it recreates the index row by row.
REPAIR TABLE my_table_name QUICK;Step 4: Fixing InnoDB Tables
The REPAIR TABLE command does not work for InnoDB. To “repair” an InnoDB table, you must force a rebuild of the table and its indexes.
ALTER TABLE my_table_name ENGINE=InnoDB;This command effectively copies all data to a new table structure, fixing internal fragmentation and minor corruption in the process.
Step 5: Using the mysqlcheck Utility
If you cannot access the MySQL console or need to repair all tables at once, use the mysqlcheck command-line tool from your terminal.
mysqlcheck -u root -p --auto-repair --all-databasesThis utility is highly efficient for bulk maintenance and ensures that all tables across your server are checked and fixed automatically.