Stumbling Blocks

Benjamin Franklin famously said, "… In this world nothing can be said to be certain, except death and taxes." From this quotation, we can determine that he was not, in fact, a system administrator; if he were, he'd have added "software upgrades" to that list.

MySQL is no exception to Benjamin Franklin's famous quote. After all, on October 21, 2023, MySQL 5.7 entered end-of-life (EOL) status. Consequently, no patches or updates will be available from official sources, and although MySQL 8.0 has been in General Availability (GA) status since 2018, many users have not yet upgraded. Since you're reading this article, you may very well be among them.

Fortunately, with some planning, you can take the sting out of this particular mandatory upgrade. I won't be able to discuss every breaking change or possible issue you might encounter, so be sure to follow the official MySQL instructions.

To begin, I'll discuss which systems can upgrade to MySQL 8.0 and what you can do if you aren't eligible.

Who Can Upgrade?

The upgrade to MySQL 8.0 or later is only possible from the 5.7 General Availability (GA) releases, not release candidates or other development releases. The earliest GA release is 5.7.9; versions of 5.7 earlier than that will have a suffix (e.g., "5.7.8 rc" for the release candidate or "5.7.1 m11" for milestone 11).

Likewise, upgrades from versions before the 5.7 major release are not supported. So, for example, if you have a MySQL 5.6 installation still running, you will first need to upgrade to MySQL 5.7. After that, you can upgrade an installation to 8.0.

Generally speaking, MySQL only officially supports upgrades across one major release, so if you want to upgrade from, say, 5.7 to 8.2, you'll first have to upgrade your MySQL 5.7 installation to MySQL 8.0, then 8.1, and finally 8.2. It's worth noting that MySQL 8.0 goes EOL in April 2026, which is not terribly far in the future. MySQL 8.0 is a long-term support (LTS) release, however, which means you won't get much additional time by upgrading past that – at least until 8.4 is released, which is scheduled to be the next LTS release.

In any event, minor upgrades such as from 8.2.0 to 8.2.1, can be done several at a time. If, for example, you're on an older version of MySQL 5.7, there's no need to upgrade to each minor version in between individually; you can skip minor versions as needed. Officially, you can upgrade from any GA release of MySQL 5.7 to 8.0, although some have preferred to upgrade to the latest MySQL 5.7 release before attempting an upgrade to 8.0.

It's also important to note that a major breaking upgrade, such as that from MySQL 5.7 to 8.0, is a good time to evaluate your choices in technology and vendors carefully. For example, if you're considering changing operating systems, architectural changes such as moving from on-premises to cloud or vice versa, and so on, now might be a good time to schedule that move. You're likely going to have to schedule significant testing and downtime, so there may be an efficiency benefit to doing both at once.

Likewise, if you're considering changing database technologies, either to an entirely different family such as PostgreSQL or to something closely related like MariaDB, the upgrade from 5.7 is a good time to do that.

Potential Upgrade Issues

One significant change involves correlations and encoding. The default collation and encoding for MySQL 5.7 was latin1, meaning that when a CREATE TABLE statement is run, it creates the new table with the latin1 character set and the latin1_swedish_ci collation. Many installations, however, are on UTF8 encoding, which is the new default on MySQL 8.0.

The default meaning of UTF8 is different under MySQL 5.7 and MySQL 8.0. On MySQL 5.7, utf8 is interpreted as utf8mb3 , whereas under MySQL 8.0 it is utf8mb4 . Although utf8 and utf8mb3 are largely compatible – with utf8mb4 supporting more characters – it is worth checking to see whether your applications use utf8mb3, utf8mb4, or latin1, and it might be a good time to standardize all of your collation and encoding settings. The utf8mb4 choice would be excellent for most situations. An unexpected shift from latin1 to utf8mb4 might cause data imports or other processes to fail, so setting the encoding specifically to utf8mb4 wherever possible may be a good move. Both MySQL 5.7 and 8.0 support all three encodings, so you can change your databases and tables to utf8mb4 ahead of time to avoid a surprise caused by a change in defaults.

If you want to keep the old defaults when you upgrade to MySQL 8.0, you can add the lines

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

to the my.cnf file before you upgrade, because those settings are supported by MySQL 5.7, as well. Conversely, you can add the lines

[mysqld]
character_set_server=utf8
collation_server=utf8mb4_0900_ai_ci

to your MySQL 5.7 configuration file to make its behavior match that of 8.0.

Removed Temporal Data Types

Some data types, or at least some variants of data types, have been removed from the new version of MySQL. So-called old temporal data types are no longer supported. These data types only have second precision. Now, newly created tables (i.e., any tables created after MySQL 5.5) will automatically have fractional second precision; under normal circumstances, the 5.6 upgrade will automatically upgrade some types, but under some circumstances they can still exist.

The MySQL documentation helpfully provides these two queries to detect temporal columns of the old type:

SET show_old_temporals = ON;
SELECT table_schema, table_name,column_name,column_type FROM information_schema.columns WHERE column_type LIKE 'timestamp /* 5.5 binary format */'\G

If you find any, you can fix it with the command

ALTER TABLE <some_table> FORCE;

which rebuilds the table, possibly taking a good deal of time. You can also dump the table as an SQL file, recreate it, and then reload from a backup, which should recreate the columns as new, more precise columns, as well.