MySQL 8.4 Upgrade - A Cautionary Tale

May 5, 2024 · 5 min read ·

This is a cautionary tale about ignoring deprecation errors in infrastructure.

On April 30th MySQL 8.4.0 came out and I managed to get caught with my digital pants down on the 2nd of May when I upgraded a group of servers running MySQL under docker that were the back end for a number of websites.

To add to the pain I didn't notice as the caching setup in front of the servers made things appear to be fine after the breaking update on the two sites I spot checked.

Once the cache expired and someone else noticed their Wordpress sites had blown up I found myself contemplating the error of my ways and docker logs on one of the affected systems.

I was happy to see the deprecation messages I'd been ignoring for, ummmm, years, were gone but I had quite a few things that were not so good.

A seemingly random selection of applications were still up but many applications could not connect to their respective databases and logging in as root to one instance using the mysql client highlighted that I'd be a bad, bad sysadmin and I was being punished.

1ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

A quick check of the docker images on one of the boxes told me that I'd gone from MySQL 8.3.0 to 8.4.0 as I'm rolling with the cool kids and don't have a version tag on the docker image name.

I switched back to 8.3 by using mysql:8.3 in the docker-compose.yml and held my breath while the gods of OCI containers did their thing.

My hopes were dashed as the lack of connectivity was replaced with something slightly more sinister:

1[System] [MY-013576] [InnoDB] InnoDB initialization has started.
2[ERROR] [MY-014061] [InnoDB] Invalid MySQL server downgrade: Cannot downgrade from 80400 to 80300. Downgrade is only permitted between patch releases.
3mysqld: Can't open file: 'mysql.ibd' (errno: 0 - )
4[ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
5[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
6[ERROR] [MY-010119] [Server] Aborting

Now I had twice as many sites down and it was only a matter of time before my cellphone started ringing.

This of course was an excellent opportunity to test the database backups taken before the upgrade. At least I didn't fail that part of sys admin 101. I did however clearly fail to test the upgrade offline first. Moving on...

I suspect that the individual databases may have been OK and just the 'mysql' database that holds the users, ACLs and config was busted but with a number of sites in lala-land and the clock running a restore was the quickest option as I have that scripted and well tested.

With everything back online on MySQL 8.3 I retreated from the ssh prompt.

I sat down a couple of days later with a cup of hot cocoa and ran through why this had gone wrong.

The original deprecation notice in 8.0 - 8.3 is 99% of the story:

1[Warning] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated
2and will be removed in a future release. Please use caching_sha2_password instead'

When upgrading a pre-existing MySQL 8.3 instance to 8.4 this message tells the final 1% of the story.

1[System] [MY-011090] [Server] Data dictionary upgrading from version '80200' to '80300'.
2[System] [MY-013413] [Server] Data dictionary upgrade from version '80200' to '80300' completed.
3[System] [MY-013381] [Server] Server upgrade from '80300' to '80400' started.
4[System] [MY-013381] [Server] Server upgrade from '80300' to '80400' completed.

Upgrading MySQL broke the users created with the old auth plugin because it was removed in 8.4 and rolling back didn't bring it back online because the format of the database files had been updated.

This wont happen if you're working with a database created after 8.0 came out unless you are forcing MySQL to use the old mysql_native_password plugin to support an old application stack.

The offending command line option is: --default-authentication-plugin=mysql_native_password and you'll find plenty of sites recommending using that to get various things to work.

MySQL 8.0 came out back in April 2018 and the earliest post I found recommending the default-authentication-plugin option as a quick-fix was the same month.

That makes it six years of accumulated internet how-to history so don't feel bad if you got caught.

Two of the servers I broke were using that option and the others were just old databases which had been upgraded repeatedly since before 2018.

The solution I used to resolve this comes in parts.

Verify that the applications will connect to MySQL 8.4 using the new cached_sha2_password plugin.
Upgrade old application platforms before updating the MySQL instance they use!
Update the user passwords to use the new plugin.
Upgrade MySQL.

The first one is easy, I loaded up a copy of the applications locally in docker on my laptop and tested them connecting to a shiny new MySQL 8.4 container. The only problem child I found was an older install of MediaWiki so I left that server on 8.3 and will tackle that upgrade in a few days.

Updating the users is pretty straight forward. Log into the MySQL console of the running pre-8.4 server and run the following query:

 1select User,Host,plugin from mysql.user where plugin='mysql_native_password';
 2+--------------+-----------+-----------------------+
 3| User         | Host      | plugin                |
 4+--------------+-----------+-----------------------+
 5| wp-test      | %         | mysql_native_password |
 6| ad-scheduler | %         | mysql_native_password |
 7| nzart        | %         | mysql_native_password |
 8| root         | localhost | mysql_native_password |
 9+--------------+-----------+-----------------------+
104 rows in set (0.00 sec)
11mysql>

Then for each user using the old plugin update the password using:

1mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'xxxxxx';
2Query OK, 0 rows affected (0.03 sec)
3mysql> ALTER USER 'wp-test'@'%' IDENTIFIED WITH caching_sha2_password BY 'xxxxxx';
4Query OK, 0 rows affected (0.03 sec)

From that point I removed the :8.3 tag and got back on the rolling update wave.

Next time I see a deprecation notice in a log file I might do something in under 5 years. Maybe.

Note that I could have also done the upgrade and started mysql with --skip-grant-tables the updated the accounts on the upgraded database. That is the normal way to recover a lost password but that would have meant a much longer outage that doing it pre-upgrade and re-running docker compose up -d.