Home / server / Questions / 1002704
Accepted
Zenexer
Asked: 2020-02-12 14:54:51 +0800 CST

Can't upgrade RDS instance from MySQL 5.7.x to 8.0.x

  • 772

I need to recover data from an old 5.6.x snapshot. With a little bit of tinkering, I managed to get the 5.6.x snapshot restored on an m3.xlarge instance (any instance type other than m3.* won't work). I then successfully upgraded from 5.6.x to the latest 5.7.x.

The next step was to go from 5.7.x to 8.0.x. Each time I tried to perform the upgrade, RDS would claim it was "Upgrading", but it would return the "Available" state quickly, and it would be back to 5.7.x, with no visible errors in the console.

How do I upgrade it to 8.0.x?

mysql amazon-web-services amazon-rds

1 Answers

  1. Best Answer
    1. Under Logs & events, open PrePatchCompatibility.log.
    2. Scroll to the very bottom where it lists the error count. You'll likely see that there were errors.
    3. Scroll back up until you find the error(s). In my case, I saw:

    16) Schema inconsistencies resulting from file removal or corruption
    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 some_db - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table

    1. This isn't a particularly helpful error. This error is supposed to show something like some_db.some_table, but there is only a database listed.
    2. Run: select * from information_schema.innodb_sys_tables where name like '%#%'; In my case, there was one table listed: some_db/#sql-ib1408.
    3. Apparently, this can happen if an alter table statement is interrupted. Unfortunately, running drop table `#sql-ib1408`; doesn't work. While most of the responses at that link restate the problem rather than providing a solution, one hints at a possible fix.
    4. Run: use some_db; drop table `#mysql50##sql-ib1408`;, substituting the database and table names for your own. The #mysql50# prefix tells MySQL not to attempt to encode the table name; normally, the # character would be encoded.
    5. Reattempt the upgrade.

    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.

    • 6