Introduction MySQL 8.0 replaced .frm files with a transactional data dictionary stored in InnoDB tables. When the data dictionary becomes corrupted—due to crashes, disk errors, or upgrade failures—table definitions become inaccessible even though the underlying .ibd data files may be intact.

Symptoms - `ERROR 1146 (42S02): Table doesn't exist` despite .ibd file being present - `SHOW TABLES` lists the table but `DESCRIBE table_name` fails - MySQL fails to start with `InnoDB: Data dictionary initialization failed` - `SELECT FROM information_schema.tables` shows inconsistent results - Error log shows `DD::Table::validate failed` or similar dictionary errors

Common Causes - Crash during data dictionary transaction commit - Upgrade from MySQL 5.7 to 8.0 interrupted during dictionary migration - Disk corruption affecting the `mysql.ibd` data dictionary tablespace - Manual deletion of data dictionary files - Incompatible `innodb_data_file_path` changes

Step-by-Step Fix 1. **Check data dictionary integrity": ```sql -- Try to access the data dictionary directly SELECT * FROM mysql.tables WHERE name = 'mytable' AND schema_id = ( SELECT id FROM mysql.schemata WHERE name = 'mydb' ); ```

  1. 1.**Check the .ibd file exists and has data":
  2. 2.```bash
  3. 3.ls -la /var/lib/mysql/mydb/mytable.ibd
  4. 4.# If the file exists and has a non-zero size, data may be recoverable
  5. 5.`
  6. 6.**Recreate the table definition and import tablespace":
  7. 7.```sql
  8. 8.-- Recreate the table with the exact same structure
  9. 9.CREATE TABLE mydb.mytable (
  10. 10.id BIGINT NOT NULL AUTO_INCREMENT,
  11. 11.data JSON,
  12. 12.created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  13. 13.PRIMARY KEY (id)
  14. 14.) ENGINE=InnoDB;

-- Discard the new tablespace ALTER TABLE mydb.mytable DISCARD TABLESPACE;

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

-- Import the original tablespace ALTER TABLE mydb.mytable IMPORT TABLESPACE; ```

  1. 1.**If the data dictionary is severely corrupted, rebuild it":
  2. 2.```bash
  3. 3.# Dump all data from a running instance
  4. 4.mysqldump --all-databases --single-transaction > full_backup.sql

# Stop MySQL sudo systemctl stop mysql

# Remove data directory (BACKUP FIRST) sudo mv /var/lib/mysql /var/lib/mysql.corrupted

# Reinitialize sudo mysqld --initialize-insecure --user=mysql

# Start MySQL sudo systemctl start mysql

# Restore data mysql < full_backup.sql ```

Prevention - Use reliable storage with battery-backed write cache for InnoDB data - Set `innodb_flush_log_at_trx_commit = 1` for crash-safe writes - Test MySQL 8.0 upgrade procedures thoroughly before production - Regular full backups including the data dictionary - Monitor MySQL error log for data dictionary warnings - Use `mysqlcheck --all-databases` periodically to check table integrity - Maintain separate schema dumps for disaster recovery