How to fix missing tables from information_schema

Question

Hi there,

We are attempting to migrate one of our databases from **MySQL 5.7** to **MySQL 8**. Upgrading the snapshot for this database failed so I've attempted to restore it in a micro instance and perform an upgrade directly using the upgrade instance action.

It came back with errors hours later. The problem is that we have a couple of tables that were dropped years ago that are still referenced in the `information_schema.innodb_sys_tables`. Attempting to drop these tables again have no effects. Attempting to create these tables (using their old schema) are failing mentioning they already exists.

```shell
mysql> CREATE TABLE `_SomeVeryOldTable` ( ... );
ERROR 1813 (HY000): Tablespace '`MY_DATABASE`.`_SomeVeryOldTable`' exists.

mysql> drop table if exists `_SomeVeryOldTable`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

As mentioned, these tables were dropped years ago and I don't believe we have a backup that has these. I'm not sure where to go from here. I'm seeing solution online but it assume we have access to the physical files on the instance which is not the case for RDS?

Thanks for your help, 
Maxime

re:Post Agent · Answer

The issue you're experiencing with missing tables in the information_schema is a known problem that can occur during database migrations or upgrades, especially when moving from MySQL 5.7 to MySQL 8. This situation where tables appear to exist in the InnoDB data dictionary but not in the actual database can be challenging to resolve in an Amazon RDS environment due to limited access to the underlying file system.

Here are some steps you can try to address this issue:

1. First, attempt to drop the table using the IF EXISTS clause, which you've already tried. While this didn't resolve the issue, it's always a good first step.

2. Try rebuilding the table using the ALTER TABLE command:

ALTER TABLE `MY_DATABASE`.`_SomeVeryOldTable` ENGINE=INNODB;

This command might force InnoDB to rebuild the table metadata and potentially resolve the discrepancy.

3. If the above doesn't work, you can try to create a new table with a different name but the same structure as the problematic table, then attempt to rename it to the original name:

CREATE TABLE `MY_DATABASE`.`_SomeVeryOldTable_new` (...);
RENAME TABLE `MY_DATABASE`.`_SomeVeryOldTable_new` TO `MY_DATABASE`.`_SomeVeryOldTable`;

4. If these methods don't work, you may need to involve AWS Support. They have access to lower-level tools and can potentially help remove the orphaned table entries from the InnoDB data dictionary.

5. As a last resort, you might consider dumping the data from your current database (excluding the problematic tables), creating a new RDS instance with MySQL 8, and then importing the data. This approach bypasses the upgrade process entirely and creates a clean MySQL 8 environment.

It's important to note that these issues can be complex, especially in a managed service like Amazon RDS where you don't have direct access to the file system. If you continue to face difficulties, don't hesitate to reach out to AWS Support for assistance. They can provide more targeted solutions based on your specific RDS setup and the exact nature of the problem.

Remember to always take backups before attempting any of these operations, and if possible, test these solutions in a non-production environment first.  
**Sources**  
[Resolve storage full issues on DB instances | AWS re:Post](https://repost.aws/knowledge-center/rds-mysql-storage-full-issues)  
[Table not found when using Athena with Amazon QuickSight - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/troubleshoot-athena-table-not-found.html)

How to fix missing tables from information_schema

Relevant content