As mentioned in the AWS discussion forum back in October, Amazon has started the end of life (EOL) process for RDS MySQL version 5.5. What this means is:

  • AWS will upgrade RDS instances to MySQL 5.7 starting February 9 2021 00:00 UTC during your next defined maintenance window, provided you have one.
  • If you don’t have a maintenance window defined, RDS will automatically upgrade you on March 9 00:00 UTC and there is no opt-out.

As any seasoned administrator knows, upgrades can be painful and things might go wrong.

Risks

I think we can safely assume that the upgrade will be performed in-place, as it would be too complex and time-consuming otherwise. Since a direct upgrade from 5.5 to 5.7 is not supported, we need to go through 5.6 first. This means instances need to be restarted twice; first to go from 5.5 to 5.6, and then from 5.6 to 5.7.

As per the RDS upgrade guide, any read replicas are upgraded first, while the primary instance is upgraded last.

If there are issues with any of the restarts, you will need to depend on AWS support to help you. Remember you don’t have access to the underlying instance.

Here is a list of other important issues you need to be aware of:

Query Regressions

By far the most common issue, some queries will perform differently in 5.7 due to changes in the optimizer code. Some will be faster, some will be slower. There is even the possibility of some queries starting to do a full table scan, in spite of them using an index before.

It is of utmost importance to do regression testing in advance of the upgrade to avoid any unpleasant surprises.

Tables Using the Old Datetime format

Starting with MySQL 5.6, the datetime columns have microsecond precision. This means old 5.5 tables using such columns have to be rebuilt, which can be a lengthy process. This happens at upgrade time by default – more precisely while running mysql_upgrade script.

We can avoid this by converting tables on a read replica, and then promoting it to primary. For more information check out the article about Upgrading to MySQL 5.7 focusing on temporal types.

Reserved Words

It is a good practice to check for new reserved words usage prior to doing a database upgrade. Any reserved keywords will cause syntax errors on the new version, potentially breaking your application.

Luckily, the MySQL manual provides a list of reserved words for each version.

Backups

It is not clear what will happen with snapshots from the old version. To be on the safe side, a new full backup should be taken right after the upgrade.

Rollback Options

Since there is no mention of any rollback mechanism in the communication, you need to think about the strategy if things go wrong past the point of no return.

One possibility is to create an external replica. Even though it is not supported officially, replication from a higher to lower MySQL version is possible most of the time.

The replica could be useful as a temporary data source, while any unexpected issues with the new version are addressed.

Final Words

MySQL 5.5 is ancient by now, and it definitely makes sense to upgrade. Even MySQL 5.6 is going out of support soon