How do we repair tables?

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.

Syntax:

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

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.

1
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.
2
If we specify the EXTENDED clause, MySQL not only repairs the table but also rebuilds the index and optimizes the table structure.
3
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

Difficulty: 6/10

Topics: InnoDB Recovery, Table Corruption, Database Administration

Scenario Questions

0-2 years experience

1
We have a legacy MyISAM table that suddenly stopped accepting writes, and the error logs say it is marked as crashed. Walk me through how you would check its status and safely get it back online.
2
Imagine you run REPAIR TABLE on an InnoDB table and MySQL returns an error saying the engine doesn't support it. What does this mean, and how would you actually go about rebuilding an InnoDB table that has minor index corruption?

2-5 years experience

1
During a peak traffic window, a critical InnoDB table becomes corrupted due to an abrupt VM restart. We cannot afford to take the entire database offline. How do you diagnose the extent of the corruption, and what are your options to rebuild or repair this table with minimal impact on active users?
2
You are debugging an application error where certain queries on a specific table consistently crash the MySQL thread, but other queries work fine. How do you isolate whether this is a corrupted index, and how do you resolve it without losing data?

5-8 years experience

1
A 2TB InnoDB table has suffered page corruption due to a faulty RAID controller. The MySQL server won't start up normally because it crashes during crash recovery. Walk me through how you would use innodb_force_recovery to safely extract the data, and how you decide which recovery level (1 through 6) to use.
2
We are running a high-throughput MySQL cluster and suspect silent data corruption on one of our replicas. How would you design a proactive verification process to detect page-level corruption before it causes a hard crash, and how do you repair a replica without rebuilding it from scratch?

8+ years experience

1
After a datacenter power event, several critical databases across our fleet are reporting varying degrees of table corruption. How would you structure the incident response, prioritize recovery efforts, and what architectural patterns would you introduce to ensure we can achieve a near-zero Recovery Time Objective (RTO) for corrupted tables in the future?
2
We are migrating a legacy fleet of MySQL instances with mixed MyISAM and InnoDB tables to a cloud-native managed database environment. How do you address the operational risks of table corruption during this migration, and how do you design the target architecture to eliminate manual 'repair' operations entirely?

Follow-up Questions

❓What are the risks of running innodb_force_recovery with a value greater than 4?
❓How does physical page corruption differ from logical corruption in MySQL?
❓How do you verify table integrity on a replica without locking the primary database?