Fun times with MySQL upgrade

Like all service upgrades, MySQL is no different. Bump the service version, build the docker image, try to “make up“ the project and hope for the best. I’ve decided to dedicate this blog to four things that turned this “it is going to be an easy project” to “8 months in hell while upgrading MySQL from v5.6 to v8.0”.

1. Fantastic SQL modes and where to find them

While looking at all changes made to MySQL between v5.6 and v8.0, I came across something rather interesting. They announced they had enabled a bunch of other previously optional SQL modes as a part of strict mode.

To my surprise, only NO_ENGINE_SUBSTITUTION mode was enabled in our database. 😕?!?! What could possibly go wrong after years of using the database with strict mode off?

A lot of things apparently, so I made a list of modes I need to enable/check out before finally enabling STRICT_TRANS_TABLES.

ERROR_FOR_DIVISION_BY_ZERO → in later versions, no longer an option but part of strict mode

NO_ZERO_IN_DATE → in later versions, no longer an option but part of strict mode

NO_AUTO_CREATE_USER → in version 8.0, no longer an option but part of strict mode

NO_ZERO_DATE → in later versions, no longer an option but part of strict mode

ONLY_FULL_GROUP_BY → decided to leave it disabled

NO_ENGINE_SUBSTITUTION → was already enabled

STRICT_TRANS_TABLES → needs to be enabled

This little side quest of cleaning up migrations, Doctrine entities which were not in sync with corresponding database tables, fixtures, using null for everything — does not matter is the field nullable or not, missing primary keys on tables etc. cost us 193,4 hours (around 33 days) just to make the application run with some additional SQL modes enabled.

2. Sphinx client issues

Some legacy parts of our application still used Sphinx instead of ElasticSearch. We faced a tough choice then and there because MySQL v8.0 had a new default authentication plugin caching_sha2_password, and Sphinx v2.2.4, that we were still using, uses the old authentication plugin mysql_native_password.

Connecting to Sphinx with a user that was using caching_sha2_password authentication plugin resulted in:

So, we tried to create a user that used the old authentication plugin. That still resulted with an error:

This left us with two possible ways to go:

Upgrade legacy parts of the application, that we all want removed, to v3.1.1 — not too much effort

Remove Sphinx from the project — little sub-project

We went with a 751 hour endeavor for 5 people and removed Sphinx from the project.

3. The magnificent world of charsets, collates and row formats

About charsets and collates

MySQL uses UTF8 as an alias for the now deprecated UTF8MB3. It is expected, at some point in the future, that UTF8 will become an alias for the UTF8MB4 charset. In a future MySQL release, UTF8MB3 should be removed. You can read more about it here.

To follow the recommendation, we decided to change our charset to UTF8MB4. To match our brand new charset, we had to change the collate to any one compatible with UTF8MB4 charset.

About row formats

There are four row formats: REDUNDANT, COMPACT, DYNAMIC and COMPRESSED. MySQL v5.6 uses COMPACT by default, and v5.7 and later use DYNAMIC.

The DYNAMIC row format offers the same storage characteristics as the COMPACT row format but adds enhanced storage capabilities for long variable-length columns and supports large index key prefixes.

In our code, we like to use these “cutting edge” things from the “era gone by”, meaning, we used row format FIXED. It is so deprecated, that if innodb_strict_mode is disabled, InnoDB issues a warning and assumes row format DYNAMIC, and if innodb_strict_mode is enabled, InnoDB returns an error. We replaced FIXED and COMPACT row formats with DYNAMIC.

There was a bug. 🐛

If you try to create an index on a field that exceeds 767 bytes you will get an error that looks like this:

But it was still possible to create an index like that if you were using row format COMPRESSED/REDUNDANT, or you didn’t explicitly define row format DYNAMIC. The result, after a server reboot, the table was inaccessible and could not be recovered. But luckily, this issue was fixed in MySQL v8.0.22.

If you still want to create an index on VARCHAR field, make sure the length is less or equal to 190. This is because UTF8 takes up to (3*255) 765 bytes, and UTF8MB4 takes up to (4*255) 1020 bytes.

The real issue

Ok, not to hard. So we change the charset, collate and row format. Big woop, right?

The real hard part was altering every single table in the production database. This will not be a problem if you do not have any huge tables, but if you do, these alters can and will take hours.

The trickiest thing of all is altering all these tables with some reasonable downtime. Because, if you try to join two tables with a different collate and charset — it will fail. If you try to alter everything on one slave, and then replicate it — it will fail.

Your safest bet is to backup or delete some data you can spare to reduce the table size. Or create new empty tables that will be used while the real ones are being altered and sync the deltas once it is over.

4. MySQL query cache is no more

If you rely on MySQL query cache, you will have to replace it with something else. It was deprecated in MySQL v5.7 and completely removed in MySQL v8.0.

There are some alternatives, like ProxySQL query cache. But there are definitely some cutbacks.

It is the simplest alternative, really easy to setup, benchmarks show better throughput — meaning performance boost. But…

Unlike MySQL query cache that would invalidate the cache every time there was a write, in ProxySQL there is no way to define a way to invalidate the cache other then cache_ttl. This can definitely be a limitation because there is a chance you will serve some stale data.

Other then that, it does not support caching prepared statements and there is no way to manually purge the query cache. There is a parameter mysql-query_cache_size_MB that defines how big your cache can get. But this is not strict, it is only used to automatically trigger the query cache purge.

In any case, it just depends on whether or not this is acceptable to you. You can find more about it here.

If you are planning on upgrading MySQL, I hope you will find this helpful. The biggest problem for me was underestimating the time needed for delivering the project. I wrote this post, if for nothing else, to help you know what to look out for. :)