Fun times with MySQL upgrade
Tags: sql, tips, tipsandtricks, mysql
Published: 2021-12-17 09:26:07

---
title: Fun times with MySQL upgrade
published: true
date: 2021-12-17 09:25:00 UTC
tags: sql,tips,tipsandtricks,mysql
canonical_url: 
---

![](https://cdn-images-1.medium.com/max/1024/1*hjC30NH5V2DHVq-POKlMpg.png)

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](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_error_for_division_by_zero)
- _NO\_ZERO\_IN\_DATE_ → [in later versions, no longer an option but part of strict mode](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_in_date)
- _NO\_AUTO\_CREATE\_USER_ → [in version 8.0, no longer an option but part of strict mode](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_create_user)
- _NO\_ZERO\_DATE_ → [in later versions, no longer an option but part of strict mode](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_in_date)
- _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:

```
bash-4.4$ mysql -hsphinx -usphinx 
ERROR 2003 (HY000): Can't connect to MySQL server on 'sphinx' (111)
```

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

```
bash-4.4$ mysql -hsphinx -P9306 
ERROR 2000 (HY000): Unknown MySQL error
```

[Issues with connecting to MySQL client 8.0+ were fixed in Sphinx v3.1.1](http://sphinxsearch.com/docs/sphinx3.html#version-3.1.1-17-oct-2018).

This left us with two possible ways to go:

1. Upgrade legacy parts of the application, that we all want removed, to v3.1.1 — not too much effort
2. 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](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html).

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._](https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html)

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:

```
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes
```

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](https://bugs.mysql.com/bug.php?id=99791) 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](https://www.percona.com/blog/2018/02/07/proxysql-query-cache/).

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. :)

* * *