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

gefragt vor 2 Jahren1732 Aufrufe
1 Antwort
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
beantwortet vor 2 Jahren

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen