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

Question

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?

Answer

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

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

相關內容