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?

  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

  4. 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.
  5. Run: select * from information_schema.innodb_sys_tables where name like '%#%'; In my case, there was one table listed: some_db/#sql-ib1408.
  6. 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.
  7. Run: drop table `#mysql50##sql-ib1408`;, substituting the table name for your own. The #mysql50# prefix tells MySQL not to attempt to encode the table name; normally, the # character would be encoded.
  8. 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.

  • 1
    LGTM. Execute use xxxxx before drop table statement to tell the database to the database engine. – Yuki Matsukura Aug 12 '20 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.