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