MySQL 8.4, and the defaults that changed underneath us

MySQL 8.4 is the first long-term-support release under the new versioning model, and the version number suggests a patch. It removes variables, disables an authentication plugin by default and changes replication behaviour, which is a major release wearing a minor number.

The symptom

$ docker compose up -d db
$ docker compose logs app | tail -3
  SQLSTATE[HY000] [1524] Plugin 'mysql_native_password'
  is not loaded

$ docker compose exec db mysql -e 
    "SELECT user, host, plugin FROM mysql.user WHERE user='app'"
  app  10.0.%  mysql_native_password

# the plugin is not removed. it is not loaded by default.

A clear error naming exactly what is wrong, which is the good case. The user had been created in 2019 with the then-default plugin and had been carried forward through every upgrade since, because a plugin that still works gives nobody a reason to change it.

Why it happens

A long-term-support release is where deprecations that have been accumulating across the innovation track land at once. The version number does not signal that, and the release notes are the only place the accumulation is visible.

The fix

The three defaults that changed

  mysql_native_password
    deprecated since 8.0.34, disabled by default in
    8.4, and removable with --mysql-native-password=ON
    for one more release cycle.
    → the escape hatch exists and is a way of hitting
      this again in two years.

  binlog_transaction_dependency_tracking
    the variable is REMOVED. the WRITESET behaviour it
    selected is now the default and unconditional.
    → a configuration file naming it refuses to start.

  innodb_buffer_pool_in_core_file
    default changed. affects core dump size on a
    crash, which matters on a host with a small disk.

The removed variable is the one that produces the most alarming failure — the server refuses to start rather than warning — and it is the correct severity. A configuration file naming a variable that no longer exists is a file describing a system that no longer exists.

Reading the release notes properly

two hours, and what it found beyond the three above:

  6  variables deprecated and still present
  4  default value changes with no deprecation
     period, all of them in the innovation releases
     we skipped
  2  reserved words added — one of which, `PARALLEL`,
     was a column name in a reporting table
  1  a change to how utf8mb3 is reported, which broke
     a schema comparison script

none of these were in the upgrade guide's summary.

The reserved word is the finding that would have been a production failure — a column named parallel in a table nobody queries often, which would have failed on the first report after the upgrade. Reading the full change list rather than the summary is two hours and it is the difference between an upgrade and an incident.

The authentication change, done properly

-- not this, which needs the client to support it and
-- fails differently if it does not
ALTER USER 'app'@'10.0.%' IDENTIFIED WITH caching_sha2_password BY '...';

-- first: confirm the client can
SELECT VERSION();
-- and on the application side
php -r 'echo (new ReflectionExtension("mysqlnd"))->getVersion();'

-- caching_sha2_password over a non-TLS connection
-- requires the server's public key, which the client
-- fetches unless server_public_key_path is set. on a
-- private network that is a round trip on first connect
-- and is worth knowing about before it appears as
-- latency.

The public key exchange on the first connection of each process is the detail that surprises people — under a process model that creates connections frequently it is a measurable cost, and it is invisible in a test that reuses one connection. Enabling TLS removes it, which is the answer we took.

The upgrade path: a replica first

  1  a new replica on 8.4, replicating from the 8.0
     primary. supported: replication from an older
     source to a newer replica is the documented
     direction.

  2  a week of it running, with the query plans for
     the top twenty compared daily.
     → two plans changed, both improved.

  3  reads moved to it, gradually, by connection
     weight. a week.

  4  promoted. the old primary kept, stopped, for a
     fortnight.

  5  a second replica rebuilt on 8.4.

what this does not allow: a rollback after step 4. the
data directory is upgraded in place and 8.0 will not
read it.

The one-way nature of step four is the thing to be explicit about. Keeping the old primary stopped is a rollback for the first fortnight only if no writes have happened since — which they have — so the real rollback is a restore from backup, and that is the plan that had to be timed before starting.

The LTS model, and what it changes about planning

  LTS         8.4, then roughly 9.7. supported ~8
              years, bug fixes only after year one.
  Innovation  one every quarter, supported until the
              next one ships.

which means an application on an innovation release
must upgrade quarterly or run something unsupported —
and the highest version number is always an innovation
release, which is how people end up there by accident.

ours: LTS. the next decision is around 2027, and it
will be larger than this one.

Verifying it worked

$ mysql -e 'SELECT VERSION()'
8.4.0

$ mysql -e "SELECT user, plugin FROM mysql.user WHERE user='app'"
  app  caching_sha2_password

$ ./bin/explain-top-20 --compare=plans-8.0.json
  identical: 18
  changed:    2   (both faster — a NOT EXISTS now
                   executes as an antijoin)

$ ./bin/replication-lag --p99 --since=7d
  p99 180ms        # was 210ms

$ ./bin/reserved-words-check
  0 conflicts      # after renaming one column

$ mysqlcheck --all-databases --check-upgrade
  OK

Comparing the top twenty query plans against a captured baseline is the check that catches a cost model change, and it is the one nobody runs because the upgrade appears to have worked. Two plans improving is a good outcome and the value of the check is the case where one gets worse.

What this costs

An LTS cadence makes upgrades rarer and larger, which is the trade every long-term-support model makes. The next one is three years away and will contain three years of accumulated deprecations, and the institutional memory of how this one was done will be a blog post.

The escape hatch for the authentication plugin is also available and is the thing that will cause this again. Enabling it would have made the upgrade a one-line change, and the flag disappears in the next major — at which point somebody will hit exactly this error with less time and a larger version jump.