MySQL 8.4 LTS: What DBAs Should Check Before Upgrade

MySQL 8.4, released April 30, 2024, is the first long-term support release in the 8.x series and will receive extended security and bug-fix support — but the upgrade path has real breaking changes that will silently break application authentication, pagination queries, and GROUP BY logic if you do not check them first. The most dangerous change is the authentication plugin enforcement. Old client libraries that do not support caching_sha2_password will fail to connect after the upgrade, and the failure mode is a hard connection error, not a graceful fallback.

Situation

Oracle shipped MySQL 8.4 as the first LTS release in April 2024, consolidating changes introduced throughout the 8.x Innovation releases. MySQL 8.0 introduced caching_sha2_password as the new default authentication plugin in 2018, but left mysql_native_password available as a fallback. Many applications stayed on the native password plugin because connector support for caching_sha2_password was uneven in the early years. In MySQL 8.4, that path is now narrower: caching_sha2_password is fully enforced as the default, and mysql_native_password is deprecated and disabled by default.

The LTS designation matters operationally: 8.4 will receive bug fixes and security patches through a longer window than standard Innovation releases, making it the natural target for organizations that want a stable upgrade from 8.0. But “long-term support” does not mean “backward compatible with everything in 8.0.” Five specific changes require explicit verification before any production upgrade.

The Problem

The authentication change is the most disruptive because it fails at connection time, before the application executes any SQL. A Django app using mysqlclient 1.x, a PHP application using an outdated mysqlnd, or any service using the legacy mysql-connector-python without SHA-2 support will fail to connect to a MySQL 8.4 server where user accounts are configured with the new default plugin.

Beyond authentication, MySQL 8.4 removes two features that appear in more production codebases than most DBAs realize: SQL_CALC_FOUND_ROWS and the associated FOUND_ROWS() function, which are commonly used for pagination. Applications that use SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE ... LIMIT 20 to get both the page results and the total row count in one query will encounter a syntax error after the upgrade. How can engineering teams ensure their applications survive the transition to MySQL 8.4 LTS?

Core Concept

The core concept for a safe MySQL 8.4 upgrade is a pre-flight verification checklist that audits client connector capabilities, application query patterns, and server configuration prior to the cutover.

flowchart TD
    A[Pre-flight Check] --> B[Audit Authentication]
    A --> C[Audit Query Patterns]
    A --> D[Audit Server Config]
    B --> E[Identify Legacy Accounts]
    B --> F[Verify SHA-2 Support]
    C --> G[Remove SQL_CALC_FOUND_ROWS]
    C --> H[Add Explicit ORDER BY]
    D --> I[Enforce GTID Consistency]
    D --> J[Audit utf8mb3 Usage]

1. Authentication plugin: caching_sha2_password enforcement

Check which accounts still use mysql_native_password:

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

For each account returned, verify the connecting client library version supports caching_sha2_password. Upgrade connectors before migrating accounts. To migrate an account:

ALTER USER 'appuser'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

2. SQL_CALC_FOUND_ROWS removal

Search application code for SQL_CALC_FOUND_ROWS and FOUND_ROWS(). The replacement is a separate COUNT() subquery:

-- Old pattern (breaks in 8.4)
SELECT SQL_CALC_FOUND_ROWS * FROM orders WHERE status = 'active' LIMIT 20;
SELECT FOUND_ROWS();

-- Replacement pattern
SELECT COUNT(*) FROM orders WHERE status = 'active';
SELECT * FROM orders WHERE status = 'active' LIMIT 20;

The MySQL 8.4 release notes document this removal explicitly.

3. GROUP BY implicit sort behavior

MySQL historically returned GROUP BY results in the grouped column order as a side effect of implementation. This was not documented behavior, but applications developed against it. MySQL 8.0 already weakened this guarantee; 8.4 continues that path. Any query relying on implicit GROUP BY ordering needs an explicit ORDER BY clause added before the upgrade.

4. GTID enforcement

MySQL 8.4 more strongly encourages gtid_mode=ON and treats GTID-related settings as preferred defaults. Verify your replication setup:

SELECT @@gtid_mode, @@enforce_gtid_consistency;

If you are on OFF or OFF_PERMISSIVE, test the upgrade path in staging with GTID implications in scope.

5. utf8mb3 deprecation acceleration

MySQL 8.4 accelerates warnings around utf8mb3 (the 3-byte UTF-8 variant that MySQL labeled as utf8). Any schema still using the utf8 alias that intends 3-byte encoding should be explicitly audited. The MySQL documentation notes that utf8mb3 remains functional but its deprecation path is active.

In Practice

The documented pattern from Oracle’s MySQL engineering team confirms that mysql_native_password is officially deprecated in MySQL 8.4 and disabled by default. Based on how MySQL’s authentication handshake behaves, the server will reject connections from clients lacking SHA-2 capabilities with a fatal error, rather than falling back to older mechanisms.

Oracle’s public release notes for MySQL 8.4 explicitly document the removal of SQL_CALC_FOUND_ROWS and FOUND_ROWS(), noting that the features were deprecated in MySQL 8.0.20 and are now entirely removed from the parser. Any application submitting these tokens will receive a syntax error.

Furthermore, the behavior of MySQL’s optimizer regarding GROUP BY sorting has been formally documented as non-deterministic unless an ORDER BY clause is provided. Systems relying on legacy implicit sorting will observe unpredictable result sets when upgrading to the 8.4 execution engine.

Where It Breaks

Scenario	What breaks	Why
Old client library without SHA-2 support	Hard connection failure at connect time	Client cannot negotiate caching_sha2_password handshake
SQL_CALC_FOUND_ROWS in pagination layer	Syntax error on execution	Function removed from MySQL 8.4 parser
Implicit GROUP BY ordering in report queries	Result order changes silently	Undocumented sort behavior not guaranteed in 8.4

What to Do Next

• Problem: The upcoming MySQL 8.4 LTS has breaking changes that fail silently or hard depending on the client library, query patterns, and schema encoding in use.
• Solution: Run the authentication query to find mysql_native_password accounts, search application code for SQL_CALC_FOUND_ROWS, and verify connector versions before any upgrade.
• Proof: Deploy to a staging environment running 8.4 with production schema and a representative set of application queries; connection failures and syntax errors surface immediately.
• Action: This week, run SELECT User, Host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password' on any server targeted for 8.4 upgrade and cross-reference each account against the connecting application’s connector version.

The LTS designation makes 8.4 worth upgrading to — but LTS means the maintenance window is longer, not that the upgrade is risk-free. The five checks above are the difference between a smooth cutover and an unplanned rollback at 2 AM.