Aurora MySQL migration from 5.7 to 8 failed due to INNODB orphaned tables

When upgrading Aurora MySQL from 5.7 to 8.0, schema inconsistencies like orphaned tables in the InnoDB data dictionary can block the upgrade process. This is a common issue where tables appear in the InnoDB internal tables but are missing from the MySQL data dictionary.

To identify these orphaned tables, you can run:

select * from information_schema.innodb_sys_tables where name like '%#%';

In your case, the tables "tracker_clicknew13" and "tracker_clicknew15" are showing this inconsistency. They can't be dropped normally because they don't exist in the MySQL data dictionary, but they can't be created either because InnoDB still has references to them.

To resolve this issue, you could try:

1. Use the MySQL 8.0 Shell upgrade checker utility to get a comprehensive view of all upgrade issues.

2. For orphaned tables specifically, these might be temporary tables from interrupted ALTER TABLE operations. The table names with patterns like "#sql-ib*" indicate potential corruption.

3. Match the numeric ID in the table name to the TABLE_ID column in INNODB_SYS_TABLES to determine which table was affected.

4. Before proceeding with any cleanup, ensure you have a complete backup of your database.

5. Create a parameter group compatible with Aurora MySQL 3 (aurora-mysql8.0 family) before attempting the upgrade again.

If you're unable to resolve these inconsistencies through standard methods, you may need to consider more advanced approaches like using a new Aurora MySQL 8.0 cluster and migrating your data using tools like mydumper and myloader.
Sources
Unable to upgrade MySQL from 5.7.44 to 8.x | AWS re:Post
Upgrade MySql 5.7 to 8.0 : schemaInconsistencyCheck | AWS re:Post
Unable to upgrade AuroraDB v2 for MySQL 5.7 to AuroraDB v3 using default cluster and instance parameter group | AWS re:Post

You're definitely not alone — this happens during Aurora MySQL upgrades when orphaned tables are left in InnoDB’s internal data dictionary, even though the MySQL metadata says they’re gone.

At this point, manual DROP or CREATE won’t work — that’s expected, since InnoDB still holds a reference. This usually stems from a failed ALTER TABLE or crash during write.

A few steps that may help:

Use the MySQL Shell Upgrade Checker to confirm metadata issues.

Review any #sql-ib* remnants in InnoDB’s internal tables.

Back up everything first, and then either:

Rebuild a fresh Aurora MySQL 8 cluster and migrate data (e.g., mydumper/myloader)

Or contact AWS Support to clean up internal InnoDB metadata (since direct access isn’t allowed)

You're doing the right thing digging into this before retrying the upgrade.

Hi and thanks for your support here. We'd like to create a blue/green deployment in order to not go down during the upgrade procedure since this is our production db. But we need to fix the orphaned tables first so i think the best way is to contact the AWS support.

Is it possible to have this kind of support from AWS here in the forum? because we do not have any support plan at the moment.

Thanks again