Upgrading RDS MySQL 8.0 to 8.4 broke our auth and the “easy fix” is gone

AWS sent the end-of-support email for MySQL 8.0. Upgrade to 8.4 or pay for Extended Support. I assumed this was a snapshot-and-click job. It took most of a week, almost all of it fighting authentication and an old client library, not the database.

Notes, in case you’re about to do the same thing.

The upgrade is really an auth migration

8.4 disables mysql_native_password by default. Our app authenticates with mysql_native_password, like basically everything written before caching_sha2_password existed. So the second the engine flips to 8.4, the app can’t log in. The account is still there, it just can’t authenticate.

That’s the actual problem. The version number is a footnote.

The parameter everyone tells you to flip is read-only now

Obvious first move: turn mysql_native_password back on. It’s still a loadable plugin in 8.4, and on RDS that’s a parameter group setting. No app changes. There are blog posts and re:Post threads from earlier this year doing exactly this.

It doesn’t work anymore:

aws rds modify-db-parameter-group --db-parameter-group-name my-84-group \
  --parameters "ParameterName=mysql_native_password,ParameterValue=ON,ApplyMethod=pending-reboot"

An error occurred (InvalidParameterValue): The parameter mysql_native_password cannot be modified.

Not a value problem — the parameter is read-only. Check it directly:

aws rds describe-db-parameters --db-parameter-group-name my-84-group \
  --query "Parameters[?ParameterName=='mysql_native_password'].[ParameterName,IsModifiable]"

mysql_native_password   False

IsModifiable: False. On current RDS 8.4 engines this is locked. authentication_policy doesn’t save you either — it sets the default plugin for new users, it can’t load a disabled one.

If you’re planning a zero-app-change upgrade around that parameter: check describe-db-parameters on your own engine before you build anything on it. It probably isn’t modifiable. Which means caching_sha2_password is the only way onto 8.4.

Also worth knowing: Extended Support is not a cliff. You get up to three years on 8.0 and it’s cheap for a small instance. There’s no reason to rush this.

Do the auth change on 8.0, not during the upgrade

caching_sha2_password works on 8.0. So don’t couple the auth change to the engine change. Migrate auth while you’re still on 8.0, where it’s reversible, then upgrade.

1. Turn on SSL in the app (caching_sha2 needs an encrypted connection). Native password works over SSL, so nothing breaks yet.
2. ALTER USER ... IDENTIFIED WITH caching_sha2_password. On 8.0. If it goes wrong, ALTER it back to native in one line. No downtime, no clock.
3. Upgrade the engine. Auth is already correct, so this step carries no auth risk.

Everything dangerous happens in steps 1–2, on the old engine, with an undo. Step 3 ends up boring, which is the goal.

caching_sha2 wouldn’t load, and the driver version was a red herring

Before changing anything real I made a throwaway caching_sha2 user and connected from the app box over SSL:

Authentication plugin 'caching_sha2_password' cannot be loaded:
/usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

with this one line above it, which is the real cause:

Your mysql client library version 5.6.51 does not support ssl_mode as expected

5.6.51. From 2013. The driver gem was current, but the C client library it was compiled against was ancient, and caching_sha2_password didn’t exist back then, so the plugin file isn’t on disk. The driver version is irrelevant. What matters is the linked client.

Fixing that on an old box was its own detour: the OS was past EOL so its package mirrors were dead and had to be repointed to the archive first; the installed MySQL repo package only knew about 5.6 and conflicted with the current one; and there was a local MySQL 5.6 server sitting on the app box, unused, running for three years, that nobody remembered installing — its client was the one getting linked. Removed the server package, installed the 8.0 client, rebuilt the driver against it. Then the linked version finally came back 8.0.x and the proof connected.

Check the linked client, not the driver version. They’re not the same thing.

Rebuild the driver in every environment, not the one you tested in

I rebuilt the driver, tested it by hand, saw 8.0, migrated the user, deployed. The deploy died on migrate:

Your mysql client library version 5.6.51 does not support ssl_mode
Mysql2::Error: caching_sha2_password cannot be loaded

5.6 again. The native extension is compiled per runtime environment, and this box had several. My manual test ran in one. The deploy’s migration ran in another. The web server booted from a third. Background workers from a fourth. I’d rebuilt one and hit the other three as separate production errors, in sequence, because that’s apparently how I learn.

Enumerate every environment that opens a DB connection first — web, workers, migrate, your shell — and rebuild the driver in all of them before you touch anything. Then verify each one prints the 8.0 client, individually.

Blue/Green was the easy part

The engine upgrade itself, the thing I’d been dreading, was fine. Because auth was already caching_sha2 + SSL on 8.0, Blue/Green was just mechanics: parallel 8.4 instance, keeps replicating from the live one, test it on its own endpoint, switch over reassigns the same endpoint in a few seconds. App reconnected with no config change. Downtime was seconds.

The risk was all in the reversible 8.0 prep. The irreversible step was the boring one.

Short version

• You can’t keep mysql_native_password on RDS 8.4 — the parameter is IsModifiable: False. Verify on your engine before planning around it.
• Extended Support means no deadline. Don’t rush.
• Migrate auth (SSL + caching_sha2) on 8.0 first, reversibly, then upgrade.
• Check the linked C client version, not the driver version.
• Rebuild the driver in every environment that talks to the DB.
• Connect to RDS explicitly; local DB servers will lie about which server you’re on.
• Blue/Green for the cutover; keep the irreversible step boring.

If you’ve gotten mysql_native_password modifiable on a current RDS 8.4 engine, tell me how, because I couldn’t.