[GCP Cloud SQL MySQL Upgrade Error] InnoDB Metadata Inconsistency - Unable to Drop Temporary Table #sql-xxx

[M Rudi Awalludin]

Hi everyone,

We're facing a critical issue during a MySQL upgrade (from version 5.7 to 8.0) on a Google Cloud SQL instance. The upgrade process fails due to metadata inconsistencies in InnoDB, and possibly corrupted or missing .frm files or table data directories.

Here’s a snippet of the error log:

INFO 2025-04-22T14:11:17.034592Z Error: Following tables show signs that either table datadir directory or frm
INFO 2025-04-22T14:11:17.034744Z file was removed/corrupted. Please check server logs, examine datadir to
INFO 2025-04-22T14:11:17.034915Z detect the issue and fix it before upgrade
INFO 2025-04-22T14:11:17.041222Z database.#sql-7_2b1a9e4 - present in INFORMATION_SCHEMA's
INFO 2025-04-22T14:11:17.041413Z INNODB_SYS_TABLES table but missing from TABLES table
INFO 2025-04-22T14:11:17.045358Z 19) Tables recognized by InnoDB that belong to a different engine (engineMixup)

Problem Summary:

Table #sql-7_2b1a9e4 is listed in INFORMATION_SCHEMA.INNODB_SYS_TABLES but not in INFORMATION_SCHEMA.TABLES.

It appears to be a leftover temporary table from a failed/crashed operation.

The logs indicate engineMixup, where InnoDB tables are being detected as belonging to a different storage engine.

This prevents the MySQL upgrade from proceeding.

What We've Tried:

Running:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

Attempting to drop the table with:

DROP TABLE `#mysql50##sql-7_2b1a9e4`;

But this fails, stating the table does not exist.

Additional Constraints:

Dump and restore is not feasible, as the database size exceeds 500GB. This would result in unacceptable downtime for a production environment.

Questions:

Is there a safe and clean way to remove orphan InnoDB table metadata (like #sql-7_2b1a9e4) without requiring a full export/import?

Are there any tools (e.g., from Percona, Oracle, or Google Cloud internal tools) that can repair or clean up InnoDB metadata inconsistencies?

If file-level repairs are an option, how can this be done securely within GCP Cloud SQL without risking data integrity?

Any insights or similar experiences would be greatly appreciated, especially from those who have dealt with this type of upgrade issue on managed MySQL services. Thanks in advance!