InnoDB: Failed to find tablespace for table `X` in the cache

MySQL service fails to start with the error “InnoDB: Failed to find tablespace for table `X` in the cache” –

2021-02-15 22:00:01 8 [ERROR] InnoDB: Failed to find tablespace for table `X`.`Y` in the cache. Attempting to load the tablespace with
 space id 100483

This most likely happens due to some kind of corruption in the Database that will prevent the entire MySQL service from starting. Our initial resolution will be to try and use the “innodb_force_recovery“, however in some edge cases this won’t work and we need to physically remove the affected DB from the system. We need to understand that most likely the affected database is lost, but we still need access to the MySQL service for other databases.

Assuming the MySQL service is down Let’s remove the affected DB from the MySQL data folder

mv /var/lib/mysql/DB_NAME /root

At this point, if we will try to start MySQL service we will most likely see –

2021-02-15 22:06:22 0 [ERROR] InnoDB: Tablespace 112219 was not found at ./DB_NAME/TABLE_NAME.ibd.
2021-02-15 22:06:22 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.

To clear all affected tablespace, add the following to “/etc/my.cnf” –

innodb_force_recovery=1
skip-grant-tables

You can try and start the MySQL service now. If this was successful, we can’t really work with MySQL while “innodb_force_recovery” is on, so you should stop the service, remove the added lines above and start it again.

Now that the service is started again, it is recommended to run “auto-repair” –

mysqlcheck -u root --auto-repair --all-databases

As we did a “hard cut” for the affected database, there are most likely some old references for it, so it is advised to create a database in the same exact name and drop it right after.

Now it’s time to restore your lost DB from backup!