MySQL 8.4 LTS and 9.x: What WordPress Developers Need to Know

MySQL 8.4 LTS and 9.x: what WordPress developers need to know

MySQL changed its release model: since 2023 there are Long-Term Support releases (8.4, released in April 2024) for production, and Innovation releases (9.0, 9.1 and so on, roughly every quarter) with new features, each supported only until the next one. At the same time MySQL 8.0, the version most WordPress servers ran for years, reached its end of support in April 2026. So many sites move to 8.4 now, and some hosts offer 9.x. Most of it is transparent for WordPress, but a few changes matter.

Problem: The hosting announces an upgrade from MySQL 8.0 to 8.4 or to a 9.x release. What can break for a WordPress site, and what is new that is worth knowing?

Solution: Check the authentication plugin of the database user, choose LTS for production, and know which new features are actually available in the Community edition.

Authentication: mysql_native_password

The most common upgrade problem is the login, not SQL. MySQL 8.4 disables the old mysql_native_password authentication plugin by default, and MySQL 9.0 removed it. Users created long ago with it cannot connect anymore, and WordPress shows “Error establishing a database connection”.

Check the users before the upgrade:

SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password';

and switch them to the default caching_sha2_password:

ALTER USER 'wp_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'the-same-or-a-new-password';

PHP’s mysqli and PDO (with mysqlnd, the default) support caching_sha2_password since PHP 7.4, so a WordPress site on a supported PHP version connects without changes. Over a TCP connection without TLS the first login needs the server’s RSA key exchange; with a local socket (DB_HOST = localhost) it just works.

LTS or Innovation for WordPress

For a production site, choose 8.4 LTS: it gets bug and security fixes for years without new features changing behavior. An Innovation release is supported only until the next one, so a server on 9.x has to upgrade every few months to stay supported. Use 9.x on development machines to try new features, not under a shop.

New features worth knowing

-- EXPLAIN ANALYZE (since 8.0.18): runs the query and shows real times and row counts
EXPLAIN ANALYZE
SELECT p.ID, p.post_title
FROM wp_posts p
JOIN wp_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_featured' AND pm.meta_value = '1'
WHERE p.post_status = 'publish';

-- TABLE (since 8.0.19): a short form of SELECT * for quick looks
TABLE wp_terms ORDER BY term_id LIMIT 20;

The VECTOR data type arrived in MySQL 9.0: a column for embeddings (arrays of floating-point numbers), with conversion functions like STRING_TO_VECTOR(), VECTOR_TO_STRING() and VECTOR_DIM(). Be careful with articles that show vector similarity search in plain MySQL: the distance function for it is part of Oracle’s HeatWave service, not of the Community edition. On a normal server MySQL can store the vectors, but the similarity search has to happen elsewhere (in PHP, or in a database with vector search).

Before the upgrade

1. Make a full dump with mysqldump --single-transaction and test the restore.
2. Check the authentication plugins as above.
3. Run the site on a copy with the new version and look at the PHP error log: removed SQL modes or deprecated syntax in old plugins show up there.
4. After the upgrade, ANALYZE TABLE the large tables to refresh the statistics, see optimizing the database tables.

NOTE: MariaDB, which many hosts use instead of MySQL, has its own versions and features (including its own vector search), and the notes above about authentication plugins do not apply to it in the same way. Check SELECT VERSION(); to know which one your site really runs. Reading query plans is described in EXPLAIN and indexes.