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



  • 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 https://serverfault.com/questions/1002704/cant-upgrade-rds-instance-from-mysql-5-7-x-to-8-0-x/1002705#1002705?newreg=28b5caf6754045f2911c24fd7ff317be . 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 https://bugs.mysql.com/bug.php?id=94303 ) and thus can just be removed; but I'm not sure how to do that since DROP TABLE is failing and I don't believe we have access to frm files through AWS.

    I've already asked https://repost.aws/questions/QUNKcZd0MBSPmyPcCJanyKSQ/aurora-upgrade-2-to-3-my-sql-5-7-to-8-0-schema-inconsistency-check

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



  • (Partial answer.)

    Tables with names like that are probably temp tables created during ALTER (or any of several other DDL statements). Normally they are removed after the DDL is successfully finished. Sometimes the machine crashes leaving such files behind. Meanwhile, the original table is still intact, as if the DML had not been performed.

    I don't know the specifics for that version, but in the past, it was "safe" to either ignore the #sql file or go into the filesystem and delete it. Since you are on a hosted service, you would need to get them to delete the file. (Or ignore it.)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2