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” –
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!Tags: InnoDB, MySQL
This one is a lifesaver!
I did tried the innodb_force_recovery first, but the idea of moving out the affected DB is great. I did lost that DB, but at least I could save the entire MySQL with the rest of the databases