Unable to restore Aurora MySQL 5.7 snapshot into a Aurora MySQL 8 instance due to schema inconsistencies

0

I have an Aurora MySQL 5.7 instance which I have snapshot. Then using that snapshot, I have tried to restore into an Aurora MySQL 8 instance. I encountered the below error.

{
            "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": "mywebsite",
                    "description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
                }
            ]
},

This is due to an orphaned table called mywebsite/#sql-ib402-103581324 inside the INNODB_SYS_TABLES table which I have tried to drop like below:

use mywebsite;
drop table `#sql-ib402-103581324`;

AND also tried

use mywebsite;
drop table `#mysql50##sql-ib402-103581324`;

Both times, I've gotten Unknown table error message.

Can anyone help me out here?

asked 3 years ago842 views
1 Answer
0

Hi

I would suggest you to restore the snapshot in the same version and try upgrading the DB to the latest version in your case 8, Did you tried this ?

Information from AWS docs: "During the upgrade process, all snapshot actions are disabled for this DB snapshot. Also, the DB snapshot status changes from available to upgrading, and then changes to active upon completion. If the DB snapshot can't be upgraded because of snapshot corruption issues, the status changes to unavailable. You can't recover the snapshot from this state.""

Note If the DB snapshot upgrade fails, the snapshot is rolled back to the original state with the original version. Thank You GK

profile picture
EXPERT
answered 3 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