RDS MySQL: "table does not exist"

Question

Hello,  
  
Our database hasn't been properly working for a few hours. Users notified us that they couldn't access a page, that's how we noticed.  
  
For some reason, a single table from our database logs "table xxx.xxx does not exist". We haven't changed anything that could cause this. It's pretty weird since we can fetch said database name with "SHOW TABLES". However, the schema doesn't seem to load correctly.  
After trying to restore the database with a couple of backups made by AWS, the error didn't disappear, although the affected table changed?  
  
I don't know how to approach this error and the table that it is affecting is critical... please help!  
  
Raúl

misu · Answer

After messing around and researching all possible options, I managed to solve the error by disabling and enabling foreign key checks.  
  
For those that may encounter this error in the future, I noticed that it was due to foreign keys after opening the database error logs and finding the following lines:   
InnoDB: Load table `xxxx`.`xxxx` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.  
InnoDB: Cannot open table xxxx/xxxx from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.  
  
Raúl  
  
Edited by: misu on Jun 18, 2021 4:24 AM  
  
Edited by: misu on Jun 18, 2021 4:25 AM

RDS MySQL: "table does not exist"

Add your answer

Relevant content

RDS MySQL: &quot;table does not exist&quot;