The Problem

Connect a classic client (for example Sequel Pro, older Sequel Ace, older JDBC tools) to a Local site running MySQL 8 and you’re greeted with authentication errors about caching_sha2_password.

(I talked about this already, here)

The classic clients speak mysql_native_password; MySQL 8 defaults to the new plugin, and on 8.4+ the old plugin isn’t just non-default, but (and in my opinion, this is a bug of omission) it ships disabled.

Meanwhile Local’s own connections work fine, so from Local’s point of view there is no bug. From MY point of view, sitting in front of a client that can’t connect to my own database; there very much is.

So what exactly is the problem here?

This one turned out to be TWO bugs wearing a trenchcoat;

1. The directive. MySQL 8.4 removed default_authentication_plugin entirely. Local dutifully strips the removed directive from the config… and never adds the replacement (mysql_native_password=ON). So the plugin that classic clients need is disabled, permanently. Bonus points; the OLD directive on a NEW MySQL isn’t ignored; it prevents mysqld from starting at all. Which matters, because Local’s config templates travel with exports, imports and clones, so a template from one MySQL version WILL meet the mysqld of another. What could possibly go wrong.
2. The account. Even with the plugin enabled, nothing migrates existing users. root was created as caching_sha2_password, and that choice lives per-account in mysql.user. Flipping the server default does exactly nothing for accounts that already exist.

Fix only the first half and root still can’t connect. Fix only the second half and the ALTER can’t even run because the plugin is disabled. Lovely dependency loop you’ve got there, MySQL.

The Solution

MySQL Fixes for Local fixes both halves, automatically, per site;

• It ensures the version-appropriate directive — mysql_native_password=ON on 8.4+, the legacy default_authentication_plugin=mysql_native_password below that; in both the site’s my.cnf.hbs template (persistent, travels with exports) and the compiled my.cnf after every config compile. The latter is the version-correcting safety net; the compile runs on every site start, so it always matches the MySQL that’s actually running, even for a site you just imported from a machine with a different MySQL.
• On every site start, once the database is up, it runs the idempotent
  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'.

No UI. No settings. Nothing to configure. Install it, restart your sites once, and connect with your favorite ancient client like it’s 2015 again :) Local’s own connections (socket, root/root) are completely unaffected.

To be clear; this is a workaround for an upstream Local bug, but unfortunately, Local is closed source, so a pull-request to fix it is sadly not on the menu.

Get it

Source on GitHub: rmpel/Local-MySQL-Fixes. Be sure to read the notes.

Install from source (MacOS and Linux);
git clone git@github.com:rmpel/Local-MySQL-Fixes.git
cd Local-MySQL-Fixes
./scripts/install.sh # symlinks into Local's addons dir + npm install

Restart Local to make it visible in the Installed addons list, toggle on as usual.

Install from a dist file (All OS); grab the latest .tgz from the dist folder, or build it with ./scripts/build.sh, and use the “Install from disk” feature in LocalWP.