By Evan Elias | Updated July 29, 2024
MySQL’s new long-term support (LTS) version series, MySQL 8.4, had its first release at the end of April. In this post, we’ll review a few unexpected developments in this latest server version.
Overall, 8.4 is an important release with some nice improvements, especially regarding InnoDB default values, as well as the enhanced flexibility of the CLONE plugin. However, this post isn’t intended to be a general overview of MySQL 8.4 changes. For a broader summary, see excellent posts from Percona or ReadySet, as well as the official What Is New in MySQL 8.4 since MySQL 8.0 section of the MySQL manual.
Faster removal of deprecated features
Prior to MySQL 8.0, the server typically followed a deprecation lifecycle of waiting until the next release series to remove a deprecated feature – a process that often took a couple years. For example, if a feature was deprecated in MySQL 5.6 (GA in Feb 2013), it wouldn’t be removed until MySQL 5.7 (GA in Oct 2015).
When the new post-8.0 release and versioning scheme was announced, the deprecation-to-removal period was described as being shortened to as little as one year. However, in 8.4 we’re even seeing a few cases of syntax removals occurring only six months after deprecation.
For one example, consider the SET_USER_ID privilege, which was added in MySQL 8.0. This privilege was deprecated in MySQL 8.2 (Oct 2023), which split its responsibilities into the separate new privileges SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER. MySQL 8.4 removes SET_USER_ID entirely. Happily, the MySQL upgrade process helps with this particular case by automatically converting privileges for existing users, which prevents disruption at upgrade time. However, the change could still be problematic to many companies' home-grown user/grant automation or CI pipeline setup scripts, as any direct reference to SET_USER_ID now causes a syntax error in MySQL 8.4+.
Similarly, some replication syntax using problematic terminology was removed entirely in 8.4, six months after being deprecated in 8.2. We fully support the use of inclusive language, but complete removal of the old syntax may break a lot of replication automation, metrics collectors, and monitoring tools.
That said, overall it isn’t necessarily a bad thing to move faster with deprecations and removals. Historically, MySQL has erred on the side of extreme backwards compatibility and slow removals, which has caused landmines like “utf8” still referring to utf8mb3. It will understandably take some time to find the right lifecycle balance with the new LTS vs Innovation release schedule, based on the experiences of the community.
Foreign key restrictions
When creating a foreign key constraint, most relational database systems require a uniqueness constraint (unique index) on the relevant columns of the parent table. InnoDB has always been more lenient than the SQL standard in this area though, simply requiring the parent table to have an index (not necessarily unique) that begins with the necessary columns. This is no longer the case in MySQL 8.4, at least with default settings.
By default, creating a new InnoDB foreign key now requires a unique key on the parent table, consisting of the exact referenced columns in the foreign key constraint. Otherwise, the DDL will fail with ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint '...' in the referenced table '...'. This error code 6125 (ER_FK_NO_UNIQUE_INDEX_PARENT) was newly introduced in 8.4.0.
For now, you can disable this restriction and restore the pre-8.4.0 behavior by setting the new restrict_fk_on_non_standard_key server variable to OFF. However, this variable is deprecated, and a future MySQL release series will remove the ability to circumvent the uniqueness restriction entirely.
Initially, the most surprising aspect of this change was its accidental omission from the 8.4 documentation. The information above was originally learned by community experience and discussions on bug #114838. A few weeks after that bug report, Oracle updated the 8.4.0 release notes and manual to cover this topic. The documentation now also references a group replication issue which provides further context for the motivation behind this change: without a unique key on the parent table, replication could randomly break when using the multi-threaded applier.
July 2024 update: Skeema v1.12 contains a new linter check lint-fk-parent for detecting problems on the parent side of a foreign key constraint, including lack of a MySQL 8.4-compliant unique index.
Authentication changes
Last summer, MySQL 8.0.34 finally deprecated the mysql_native_password authentication scheme, which was first introduced way back in MySQL 4.1 (Oct 2004). The newer caching_sha2_password authentication plugin is generally preferred moving forwards. Continuing along this modernization effort in MySQL 8.4, mysql_native_password is still available, but is now disabled by default. If you still need mysql_native_password, you must start the server with mysql_native_password=ON configured in an option file or on the command-line.
If you leave mysql_native_password disabled, note that caching_sha2_password has some behavior differences. For example, if you intentionally use --ssl-mode=disabled with the mysql command-line client, i.e. to avoid TLS overhead on an already-secure network link, you must now also either enable --get-server-public-key or set --server-public-key-path. Otherwise, the caching_sha2_password scheme refuses to attempt authentication when it cannot guarantee it is doing so securely. This is an excellent security precaution, but the errors can be confusing when first encountered.
Separately, we have also experienced some inconsistent authentication error code behavior when leaving mysql_native_password disabled. In 8.4.0, authentication failures will occasionally return ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded at random, instead of the usual ERROR 1045 (28000): Access denied for user ... (using password: ...). This will hopefully be fixed in an upcoming point release.
InnoDB change buffer and AHI defaults
MySQL 8.4 improves the default values of quite a few InnoDB server variables. Among these many changes are the default disabling of the InnoDB adaptive hash index (AHI), as well as the InnoDB change buffer.
The AHI is intended to speed up access patterns for hot B-tree keys. However, on modern hardware, the AHI’s overhead tends to exceed its benefits for a majority of workloads. So, disabling the AHI by default isn’t a huge shock. MariaDB made a similar default change four years ago, disabling the AHI by default since MariaDB 10.5.
Meanwhile, the InnoDB change buffer is a feature that caches secondary index modifications when the corresponding page isn’t already in memory. The default value change here is a bit more surprising, as the cost/benefit calculus isn’t quite as negative for the change buffer as it is for the AHI.
That said, MariaDB took an even more aggressive stance on the change buffer: they disabled it by default in all Feb 2022 point releases, deprecated it a month later in MariaDB 10.9 GA, and then removed the feature entirely beginning in MariaDB 11.0 (June 2023). MariaDB’s removal of the change buffer was criticized by a top database performance expert.
So far MySQL has only changed the default here, without any deprecation or removal. In any case, we look forward to seeing more benchmarks from the community analyzing these changes on modern hardware.
Version numbering, Docker, and CI config
MySQL 8.x is a special-case, in that 8.0 and 8.4 are both LTS release series. This is due to the change in versioning scheme announced a year ago.
MySQL doesn’t follow Semantic Versioning (SemVer), and users should keep this in mind whenever specifying a database server version number in CI pipelines or Docker configurations. For example, let’s say you use MySQL 8.0 in production and intend to match that in CI. If your CI or Docker config simply specifies “mysql:8”, you’re now getting MySQL 8.4. This can cause subtle behavior changes or breakages.
To fix this, be sure to specify at least the minor version as well, i.e. “mysql:8.0”. To more closely mirror production in CI, it can be best to move the server version to an environment variable, and specify the full major.minor.patch version, such as “mysql:8.0.37”.
This situation will improve with time, as we expect the next MySQL release series (in 3 months) to be versioned as 9.x. Based on Oracle’s blog posts, we anticipate MySQL 9.0 through roughly 9.6 to be short-term-support Innovation releases, followed by an LTS of MySQL 9.7. That new numbering scheme, which has the LTS being the “last” major.minor version of a given major release series, will solve this configuration confusion moving forwards.
Schema design implications
Of the five surprises discussed above, only the foreign key change has a potential impact on schema design. To help detect that particular problem, in the next release of Skeema we plan to introduce a new linter check for FK parent-side unique constraints.
Otherwise, only a few minor MySQL 8.4 changes affect table design or schema management. Compared to MySQL 8.0, in 8.4 there are four new reserved words (manual, parallel, qualify, tablesample) and two no-longer-reserved words (master_bind, master_ssl_verify_server_cert). The next release of Skeema will include updates to our reserved word linter check to account for these changes. And finally, MySQL 8.4 has removed the ability to use AUTO_INCREMENT on FLOAT or DOUBLE type columns, which was deprecated since 8.0.17. If needed, you can flag those in Skeema already by configuring allow-auto-inc.
We test Skeema for compatibility with every new release of MySQL and MariaDB, using our comprehensive test suite. For the latest Skeema updates, be sure to follow us on GitHub, Twitter, or RSS.