Introduction MySQL stores table definitions in `.frm` files (MySQL 5.7 and earlier) or in the data dictionary (MySQL 8.0+). After a system crash or disk error, the .frm file can become corrupted, making the table inaccessible even though the InnoDB data pages may be intact.

Symptoms - `ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist` - `ERROR 1017 (HY000): Can't find file: './mydb/mytable.frm' (errno: 22)` - `SHOW CREATE TABLE mytable` fails with file corruption error - `SELECT * FROM mytable` returns table not found despite .ibd file existing - MySQL error log shows `InnoDB: cannot find .frm file for table`

Common Causes - Unclean shutdown or power failure during .frm file write - Disk I/O error corrupting the .frm file - Filesystem corruption affecting the table definition file - Incompatible MySQL version change without proper upgrade - Manual file manipulation in the data directory

Step-by-Step Fix 1. **Verify the .ibd file is intact": ```bash ls -la /var/lib/mysql/mydb/mytable.* # Check if .ibd exists but .frm is missing or corrupted

# Check file size - a zero-byte .ibd means data is lost stat /var/lib/mysql/mydb/mytable.ibd ```

  1. 1.**Recover the table structure from a backup or another server":
  2. 2.```bash
  3. 3.# If you have the CREATE TABLE statement from backup:
  4. 4.mysql mydb -e "
  5. 5.CREATE TABLE mytable (
  6. 6.id INT NOT NULL AUTO_INCREMENT,
  7. 7.name VARCHAR(255),
  8. 8.created_at DATETIME,
  9. 9.PRIMARY KEY (id)
  10. 10.) ENGINE=InnoDB;
  11. 11."

# Or from another server with the same schema: mysqldump -h other-server --no-data mydb mytable > table_structure.sql ```

  1. 1.**Discard the existing tablespace and import the .ibd file":
  2. 2.```sql
  3. 3.-- Create the table with the recovered structure
  4. 4.CREATE TABLE mytable (...);

-- Discard the newly created tablespace ALTER TABLE mytable DISCARD TABLESPACE;

-- Copy the original .ibd file back cp /backup/mytable.ibd /var/lib/mysql/mydb/mytable.ibd chown mysql:mysql /var/lib/mysql/mydb/mytable.ibd

-- Import the tablespace ALTER TABLE mytable IMPORT TABLESPACE; ```

  1. 1.**Use InnoDB recovery mode for severe corruption":
  2. 2.```ini
  3. 3.# /etc/mysql/my.cnf
  4. 4.[mysqld]
  5. 5.innodb_force_recovery = 1 # Start with 1, increase up to 6 if needed

# Levels: # 1 - SRV_FORCE_IGNORE_CORRUPT # 2 - SRV_FORCE_NO_BACKGROUND # 3 - SRV_FORCE_NO_TRX_UNDO # 4 - SRV_FORCE_NO_IBUF_MERGE # 5 - SRV_FORCE_NO_UNDO_LOG_SCAN # 6 - SRV_FORCE_NO_LOG_REDO ```

  1. 1.**Dump the data and rebuild the table":
  2. 2.```bash
  3. 3.# With innodb_force_recovery set, dump the data
  4. 4.mysqldump mydb mytable > mytable_recovered.sql

# Then rebuild mysql -e "DROP DATABASE mydb; CREATE DATABASE mydb;" mysql mydb < mytable_recovered.sql

# Remove innodb_force_recovery and restart normally ```

Prevention - Use MySQL 8.0+ which stores metadata in the data dictionary (no .frm files) - Implement proper UPS shutdown procedures to ensure clean MySQL shutdown - Use RAID or redundant storage to protect against disk failures - Maintain regular schema backups (mysqldump --no-data) - Monitor disk health with SMART and filesystem checks - Test disaster recovery procedures regularly - Use `innodb_flush_method = O_DIRECT` for reliable I/O