Aurora upgrade 2 to 3 / MySql 5.7 to 8.0: schemaInconsistencyCheck

1

In our attempts to upgrade our Aurora 2 MySql 5.7 DB to Aurora 3 MySql 8.0, we are running into a pre-check that we are unable to resolve. The error is:

{
      "id": "schemaInconsistencyCheck",
      "title": "Schema inconsistencies resulting from file removal or corruption",
      "status": "OK",
      "description": "Error: Following tables show signs that either table datadir directory or frm file was removed/corrupted. Please check server logs, examine datadir to detect the issue and fix it before upgrade",
      "detectedProblems": [
        {
          "level": "Error",
          "dbObject": "trax",
          "description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
        }
      ]
    }

Simple searches for this error yield this promising serverFault post. I followed the steps there:

Running (Query1)

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

yeilds two tables: trax/#sql-ib2245-1278264407-0 & trax/#sql-ib2248-918043578-0

Working with the latter and following the answer in the post yields some queries I attempted to no avail:

drop table trax.`#sql-ib2248-918043578-0`;
drop table trax.`#mysql50##sql-ib2248-918043578-0`;
use trax;
drop table `#sql-ib2248-918043578-0`;
drop table `#mysql50##sql-ib2248-918043578-0`;

All of which fail with a variation of the same error:

Error Code: 1051. Unknown table 'trax.#mysql50##sql-ib2248-918043578-0'

or

Error Code: 1051. Unknown table 'trax.#sql-ib2248-918043578-0'

There's one comment towards the end that seems like it could be helpful but doesn't seem to lead anywhere:

Note that the existence of an #sql-ib* table indicates that some of your data may be corrupt. Match the numeric ID in the #sql-ib table name to the TABLE_ID column of INNODB_SYS_TABLES to determine which table was affected. Be sure to inspect your data for integrity before proceeding.

For the two tables returned by Query1 above, both the TABLE_ID and the "numeric ID in the #sql-ib table name" match already, and I'm not sure where else that TABLE_ID might point:

TABLE_ID, NAME
2245, trax/#sql-ib2245-1278264407-0
2248, trax/#sql-ib2248-918043578-0

for more context:

select * from INFORMATION_SCHEMA.TABLES where table_schema = 'trax' and table_type = 'BASE TABLE'; -- returns 309 entries
select * from INFORMATION_SCHEMA.innodb_sys_tables where name like 'trax/%'; -- returns 311 entries

The difference is just those two tables, trax/#sql-ib2245-1278264407-0 & trax/#sql-ib2248-918043578-0, so I think that implies they don't correspond to real tables (unlike the resolution found in this MySql bug) and thus can just be removed; but I'm not sure how to do that since DROP TABLE is failing.

This is the last error holding up our upgrade process, so any help/suggestions would be appreciated.

  • Was there a solution for this or did you just have to create a support ticket? I'm running into this exact issue while upgrading to Aurora 3

asked 2 years ago1696 views
1 Answer
0

I suggest creating a support ticket for this issue, they will be able to take a look at what was happening and help with the remediation.

AWS
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions