1.4 What Is New in MySQL 8.4 since MySQL 8.0

Features Added or Changed in MySQL 8.4

MySQL native password authentication changes. Beginning with MySQL 8.4.0, the deprecated mysql_native_password authentication plugin is no longer enabled by default. To enable it, start the server with --mysql-native-password=ON (added in MySQL 8.4.0), or by including mysql_native_password=ON in the [mysqld] section of your MySQL configuration file (added in MySQL 8.4.0).
For more information about enabling, using, and disabling mysql_native_password, see Section 8.4.1.1, “Native Pluggable Authentication”.

InnoDB system variable default value changes. The default values for a number of server system variables relating to the InnoDB storage engine were changed in MySQL 8.4.0, as shown in the following table:

Table 1.1 InnoDB system variable default values in MySQL 8.4 differing from MySQL 8.0

InnoDB System Variable Name	New Default Value (MySQL 8.4)	Previous Default Value (MySQL 8.0)
`innodb_buffer_pool_in_core_file`	`OFF` if `MADV_DONTDUMP` is supported, otherwise `ON`	ON
`innodb_buffer_pool_instances`	If `innodb_buffer_pool_size` <= 1 GiB, then `innodb_buffer_pool_instances=1` If `innodb_buffer_pool_size` > 1 GiB, then this is the minimum value from the following two calculated hints in the range of 1-64: Buffer pool hint: Calculated as 1/2 of (`innodb_buffer_pool_size` / `innodb_buffer_pool_chunk_size`) CPU hint: Calculated as 1/4 of the number of available logical processors	8 (or 1 if `innodb_buffer_pool_size` < 1 GiB)
`innodb_change_buffering`	`none`	`all`
`--innodb-dedicated-server`	If `ON`, the value of `innodb_flush_method` is no longer changed as in MySQL 8.0, but the calculation of `innodb_redo_log_capacity` is changed from memory-based to CPU-based. For more information, see Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”. (The actual default value of this variable is `OFF`; this is unchanged from MySQL 8.0.)	`OFF`
`innodb_adaptive_hash_index`	`OFF`	`ON`
`innodb_doublewrite_files`	2	`innodb_buffer_pool_instances` * 2
`innodb_doublewrite_pages`	128	`innodb_write_io_threads`, which meant a default of 4
`innodb_flush_method` on Linux	`O_DIRECT` if supported, otherwise `fsync`	fsync
`innodb_io_capacity`	10000	200
`innodb_io_capacity_max`	2 * `innodb_io_capacity`	2 * `innodb_io_capacity`, with a minimum default value of 2000
`innodb_log_buffer_size`	67108864 (64 MiB)	16777216 (16 MiB)
`innodb_numa_interleave`	`ON`	`OFF`
`innodb_page_cleaners`	`innodb_buffer_pool_instances`	4
`innodb_parallel_read_threads`	available logical processors / 8, with a minimum default value of 4	4
`innodb_purge_threads`	1 if available logical processors is <= 16, otherwise 4	4
`innodb_read_io_threads`	available logical processors / 2, with a minimum default value of 4	4
`innodb_use_fdatasync`	`ON`	`OFF`
`temptable_max_ram`	3% of total memory, with a default value within a range of 1-4 GiB	1073741824 (1 GiB)
`temptable_max_mmap`	0, which means `OFF`	1073741824 (1 GiB)
`temptable_use_mmap` (Deprecated in MySQL 8.0.26)	`OFF`	`ON`

Clone plugin. The clone plugin versioning requirement was relaxed to allow cloning between different point releases in the same series. In other words, only the major and minor version numbers must match when previously the point release number also had to match.
For example, clone functionality now permits cloning 8.4.0 to 8.4.14 and vice-versa.
SASL-based LDAP authentication on Windows. On Microsoft Windows, the server plugin for SASL-based LDAP authentication is now supported. This means that Windows clients can now use GSSAPI/Kerberos for authenticating with the authentication_ldap_sasl_client plugin.
For more information, see SASL-Based LDAP Authentication (Without Proxying).
MySQL Replication: SOURCE_RETRY_COUNT change. The default value for the SOURCE_RETRY_COUNT option of the CHANGE REPLICATION SOURCE TO statement was changed to 10. This means that, using the default values for this option and for SOURCE_CONNECT_RETRY (60), the replica waits 60 seconds between reconnection attempts, and keeps attempting to reconnect at this rate for 10 minutes before timing out and failing over.
This change also applies to the default value of the deprecated --master-retry-count server option. (You should use SOURCE_RETRY_COUNT, instead.)
For more information, see Section 19.4.9.1, “Asynchronous Connection Failover for Sources”.
MySQL Replication: tagged GTIDs. The format of global transaction identifiers (GIDs) used in MySQL Replication and Group Replication has been extended to enable identification of groups of transactions, making it possible to assign a unique name to the GTIDs which belong to a specific group of transactions. For example, transactions containing data operations can easily be distinguished from those arising from administrative operations simply by comparing their GTIDs.
The new GTID format is UUID:TAG:NUMBER, where TAG is a string of up to 8 characters, which is enabled by setting the value of the gtid_next system variable to AUTOMATIC:TAG, added in this release (see the description of the variable for tag format and other information). This tag persists for all transactions originating in the current session (unless changed using SET gtid_next), and is applied at commit time for such transactions, or, when using Group Replication, at certification time. It is also possible to set gtid_next to UUID:TAG:NUMBER to set the UUID of a single transaction to an arbitrary value, along with assigning it a custom tag. The assignments of UUID and NUMBER are otherwise unchanged from previous MySQL releases. In either case, the user is responsible for making sure that the tag is unique to a given replication topology.
The original UUID:NUMBER format for GTIDs continues to be supported unchanged, as implemented in previous versions of MySQL; changes to existing replication setups using GTIDs are not required.
Setting gtid_next to AUTOMATIC:TAG or UUID:TAG:NUMBER requires a new TRANSACTION_GTID_TAG privilege which is added in this release; this is true both on the originating server as well as for the PRIVILEGE_CHECKS_APPLIER for the replica applier thread. This also means that an administrator can now restrict the use of SET @gtid_next=AUTOMATIC:TAG or UUID:TAG:NUMBER to a desired set of MySQL users or roles so that that only those users related to a given data or operational domain can commit new transactions with assigned tags.

Note

When upgrading from a previous version of MySQL to MySQL 8.4, any user accounts or roles which already have the BINLOG_ADMIN privilege are automatically granted the TRANSACTION_GTID_TAG privilege.

The built-in functions GTID_SUBSET(), GTID_SUBTRACT(), and WAIT_FOR_EXECUTED_GTID_SET() are compatible with tagged GTIDs.
For more information, see the descriptions of the gtid_next system variable and the TRANSACTION_GTID_TAG privilege, as well as Section 19.1.4, “Changing GTID Mode on Online Servers”.
Replication: SQL_AFTER_GTIDS and MTA. The START REPLICA statement option SQL_AFTER_GTIDS is now compatible with the multi-threaded applier. (Previously, when MTA was enabled and the user attempted to use this option, the statement raised the warning ER_MTA_FEATURE_IS_NOT_SUPPORTED, and the replica was switched to single-threaded mode.) This means that a replica which needs to catch up with missing transactions can now do so without losing the performance advantage from multithreading.
For more information, see Section 15.4.2.4, “START REPLICA Statement”, as well as the documentation for the replica_parallel_workers system variable. See also Section 19.2.3.2, “Monitoring Replication Applier Worker Threads”, and Section 25.7.11, “NDB Cluster Replication Using the Multithreaded Applier”.
Replication terminology backwards compatibility. This release adds the --output-as-version option for mysqldump. This option allows you to create a dump from a MySQL 8.2 or newer server that is compatible with older versions of MySQL; its value, one of those listed here, determines the compatibility of replication terminology used in the dump:
- SERVER: Gets the version of the server and uses the latest versions of replication statements and variable names compatible with that MySQL version.
- BEFORE_8_2_0: Output is compatible with MySQL servers running versions 8.0.23 through 8.1.0, inclusive.
- BEFORE_8_0_23: Output is compatible with MySQL servers running versions prior to 8.0.23.
See the description of this option for more information.
In addition a new value is added to those already allowed for the terminology_use_previous system variable. BEFORE_8_2_0 causes the server to print DISABLE ON SLAVE (now deprecated) instead of DISABLE ON REPLICA in the output of SHOW CREATE EVENT. The existing value BEFORE_8_0_26 now also has this effect in addition to those it already had previously.
The MySQL version number used in version-specific comments supports a major version consisting of one or two digits; this means that the entire version can be either five or six digits long. For more information about how this change affects handling of versioned comments in MySQL, see Section 11.7, “Comments”.
group_replication_set_as_primary() and DDL statements. The group_replication_set_as_primary() function waits for ongoing DDL statements such as ALTER TABLE when waiting for all transactions to complete, prior to electing a new primary.
For more information, see the description of this function.
DDL and DCL statement tracking for group_replication_set_as_primary(). group_replication_set_as_primary() now waits for the following statements to complete before a new primary is elected:
These are in addition to those statements added in MySQL 8.1 or otherwise already supported in this regard. For more information, including a listing of all such statements supported in MySQL 8.3, see the description of the group_replication_set_as_primary() function.
Group Replication version compatibility. Version compatibility for servers within groups has been extended as follows:
In-place downgrades of servers within groups are supported within the MySQL 8.4 LTS series. For example, a member of a group running MySQL 8.4.2 can be downgraded to MySQL 8.4.0.
Similarly, cross-version group membership is also supported within the 8.4 release series. For example, a server running MySQL 8.4.0 can join a group all of whose members currently run MySQL 8.4.2, as can a server running MySQL 8.4.3.
Group Replication variable defaults. The default values of two server system variables relating to Group Replication have been changed in MySQL 8.4:
- The default value of the group_replication_consistency system variable was changed to BEFORE_ON_PRIMARY_FAILOVER in MySQL 8.4.0. (Previously, it was EVENTUAL.)
- The default value of the group_replication_exit_state_action system variable was changed to OFFLINE_MODE in MySQL 8.4.0. (Previously, it was READ_ONLY.)
For more information, see Section 20.5.3.2, “Configuring Transaction Consistency Guarantees”, and Section 20.7.7, “Responses to Failure Detection and Network Partitioning”, as well as the descriptions of the variables listed.
Added a number of status variables specific to the Group Replication plugin that improve diagnosis and troubleshooting of network instabilities, providing statistics about network usage, control messages, and data messages for each group member.
See Section 20.9.2, “Group Replication Status Variables”, for more information.
As part of this work, a new MEMBER_FAILURE_SUSPICIONS_COUNT column was added to the Performance Schema replication_group_communication_information table. The contents of this column are formatted as a JSON array whose keys are group members ID and whose values are the number of times the group member has been considered suspect. See the description of this table for more information.
FLUSH_PRIVILEGES privilege. A new privilege is added in MySQL 8.4.0 specifically to allow use of FLUSH PRIVILEGES statements. Unlike the RELOAD privilege, the FLUSH_PRIVILEGES privilege applies only to FLUSH PRIVILEGES statements.
In MySQL 8.4, the RELOAD privilege continues to be supported in this capacity to provide backwards compatibility.
When upgrading, a check is performed to see whether there are any users having the FLUSH_PRIVILEGES privilege; if there are none, any users having the RELOAD privilege are automatically assigned the new privilege as well.
If you downgrade from MySQL 8.4 (or later) to a version of MySQL which does not support the FLUSH_PRIVILEGES privilege, a user previously granted this privilege is unable to execute FLUSH PRIVILEGES statements unless the user has the RELOAD privilege.
OPTIMIZE_LOCAL_TABLE privilege. MySQL 8.4.0 adds a new OPTIMIZE_LOCAL_TABLE privilege. Users must have this privilege to execute OPTIMIZE LOCAL TABLE and OPTIMIZE NO_WRITE_TO_BINLOG TABLE statements.
When upgrading from a previous release series, users having the SYSTEM_USER privilege are automatically granted the OPTIMIZE_LOCAL_TABLE privilege.
MySQL Enterprise Data Masking and De-Identification. Data-masking components added support for specifying a dedicated schema to store the related internal table and masking functions. Previously, the mysql system schema provided the only storage option. The new component_masking.masking_database read-only variable enables setting and persisting an alternative schema name at server startup.
Flushing of data masking dictionaries. The MySQL Enterprise Data Masking and De-Identification component now includes the ability to flush the data on the secondary or replica into memory. This can be done in either of the ways described here:
- A flush can be performed by the user at any time using the masking_dictionaries_flush() function added in this release.
- The component can be configured to flush the memory periodically, leveraging the Scheduler component, by setting the new component_masking.dictionaries_flush_interval_seconds system variable to an appropriate value.
For more information, see Section 8.5, “MySQL Enterprise Data Masking and De-Identification”, and the descriptions of these items.
Automatic histogram updates. MySQL 8.4.0 adds support for automatic updates of histograms. When this feature is enabled for a given histogram, it is updated whenever ANALYZE TABLE is run on the table to which it belongs. In addition, automatic recalculation of persistent statistics by InnoDB (see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”) also updates the histogram. Histogram updates continue to use the same number of buckets as they were originally specified with, if any.
You can enable this feature when specifying the histogram by including the AUTO UPDATE option for the ANALYZE TABLE statement. To disable it, include MANUAL UPDATE instead. MANUAL UPDATE (no automatic updates) is the default if neither option is specified.
For more information, see Histogram Statistics Analysis.
Added the tls-certificates-enforced-validation system variable, which permits a DBA to enforce certificate validation at server startup or when using the ALTER INSTANCE RELOAD TLS statement to reload certificates at runtime. With enforcement enabled, discovering an invalid certificate halts server invocation at startup, prevents loading invalid certificates at runtime, and emits warnings. For more information, see Configuring Certificate Validation Enforcement.
Added server system variables to control the amount of time MySQL accounts that connect to a MySQL server using LDAP pluggable authentication must wait when the LDAP server is down or unresponsive. The default timeout became 30 seconds for the following simple and SASL-based LDAP authentication variables:
Connection and response timeouts are configurable through the system variables on Linux platforms only. For more information, see Setting Timeouts for LDAP Pluggable Authentication.
Logging of the shutdown process was enhanced, with the addition of startup and shutdown messages for the MySQL server, plugins, and components. Such messages are now also logged for closing connections. These additions should facilitate troubleshooting and debugging problems, particularly in the event that the server takes an excessively long time to shut down.
For more information, see Section 7.4.2, “The Error Log”.
Additions to server startup and shutdown messages. Added the following types of messages to the server startup and shutdown processes as noted in this list:
- Start and end messages for server initialization when the server is started with --initialize or --initialize-insecure; these are in addition to and distinct from those shown during normal server startup and shutdown.
- Start and end messages for InnoDB initialization.
- Start and end messages for init file execution during server initialization.
- Start and end messages for for execution of compiled-in statements during server initialization.
- Start and end mesages for crash recovery during server startup (if crash recovery occurs).
- Start and end messages for initialization of dynamic plugins during server startup.
- Start and end messages for compoenents initialization step (apparent during server startup).
- Messages for shutdown of replica threads, as well as graceful and forceful shutdown of connection threads, during server shutdown.
- Start and end messages for shutdown of plugins and components during server shutdown.
- Exit code (return value) information with shutdown messages during initialization or server shutdown and end)
In addition, if the server was built using WITH_SYSTEMD, the server now includes every systemd message in the error log.
Added the SHOW PARSE_TREE statement, which shows the JSON-formatted parse tree for a SELECT statement. This statement is intended for testing and development use only, and not in production. It is available only in debug builds, or if MySQL was built from source using the CMake -DWITH_SHOW_PARSE_TREE option, and is not included or supported in release builds.
Thread pool plugin connection information. Added thread pool connection information to the MySQL Performance Schema, as follows:
- Added a tp_connections table, with information about each thread pool connection.
- Added the following columns to the tp_thread_state table: TIME_OF_ATTACH, MARKED_STALLED, STATE, EVENT_COUNT, ACCUMULATED_EVENT_TIME, EXEC_COUNT, and ACCUMULATED_EXEC_TIME
- Added the following columns to the tp_thread_group_state table: EFFECTIVE_MAX_TRANSACTIONS_LIMIT, NUM_QUERY_THREADS, TIME_OF_LAST_THREAD_CREATION, NUM_CONNECT_HANDLER_THREAD_IN_SLEEP, THREADS_BOUND_TO_TRANSACTION, QUERY_THREADS_COUNT, and TIME_OF_EARLIEST_CON_EXPIRE.
For more information, see Section 7.6.3, “MySQL Enterprise Thread Pool”, and Section 29.12.16, “Performance Schema Thread Pool Tables”.
Information Schema PROCESSLIST table usage. Although the INFORMATION_SCHEMA.PROCESSLIST table was deprecated in MySQL 8.0.35 and 8.2.0, interest remains in tracking its usage. This release adds two system status variables providing information about accesses to the PROCESSLIST table, listed here:
- Deprecated_use_i_s_processlist_count provides a count of the number of references to the PROCESSLIST table in queries since the server was last started.
- Deprecated_use_i_s_processlist_last_timestamp stores the time the PROCESSLIST table was last accessed. This is a timestamp value (number of microseconds since the Unix Epoch).
Hash table optimization for set operations. MySQL 8.2 improves performance of statements using the set operations EXCEPT and INTERSECT by means of a new hash table optimization which is enabled automatically for such statements, and controlled by setting the hash_set_operations optimizer switch; to disable this optimization and cause the optimizer to used the old temporary table optimization from previous versions of MySQL, set this flag to off.
The amount of memory allocated for this optimization can be controlled by setting the value of the set_operations_buffer_size server system variable; increasing the buffer size can further improve execution times of some statements using these operations.
See Section 10.9.2, “Switchable Optimizations”, for more information.
WITH_LD CMake option. WITH_LD: Define whether to use the llvm lld or mold linker, otherwise use the standard linker. WITH_LD also replaces the USE_LD_LLD CMake option that was removed in MySQL 8.3.0.
MySQL Enterprise Firewall enhancements. A number of enhancements were made since MySQL 8.0 to MySQL Enterprise Firewall. These are listed here:
- Stored procedures provided by MySQL Enterprise Firewall now behave in transactional fashion. When an error occurs during execution of a firewall stored procedure, an error is reported, and all changes made by the stored procedure up to that point in time are rolled back.
- Firewall stored procedures now avoid performing unnecessary combinations of DELETE plus INSERT statements, as well as those of INSERT IGNORE plus UPDATE operations, thus consuming less time and fewer resources, making them faster and more efficient.
- User-based stored procedures and UDFs, deprecated in MySQL 8.0.26, now raise a deprecation warning. Specifically calling either of sp_set_firewall_mode() or sp_reload_firewall_rules() generates such a warning. See Firewall Account Profile Stored Procedures, as well as Migrating Account Profiles to Group Profiles, for more information.
- MySQL Enterprise Firewall now permits its memory cache to be reloaded periodically with data stored in the firewall tables. The mysql_firewall_reload_interval_seconds system variable sets the periodic-reload schedule to use at runtime or it disables reloads by default. Previous implementations reloaded the cache only at server startup or when the server-side plugin was reinstalled.
- Added the mysql_firewall_database server system variable to enable storing internal tables, functions, and stored procedures in a custom schema.
- Added the uninstall_firewall.sql script to simplify removing an installed firewall.
For more information about firewall stored procedures, see MySQL Enterprise Firewall Stored Procedures.
Pluggable authentication. Added support for authentication to MySQL Server using devices such as smart cards, security keys, and biometric readers in a WebAuthn context. The new WebAuthn authentication method is based on the FIDO and FIDO2 standards. It uses a pair of plugins, authentication_webauthn on the server side and authentication_webauthn_client on the client side. The server-side WebAuthn authentication plugin is included only in MySQL Enterprise Edition distributions.
Keyring migration. Migration from a keyring component to a keyring plugin is supported. To perform such a migration, use the --keyring-migration-from-component server option introduced in MySQL 8.4.0, setting --keyring-migration-source to the name of the source component, and --keyring-migration-destination the name of the target plugin.
See Key Migration Using a Migration Server, for more information.
MySQL Enterprise Audit. Added the audit_log_filter_uninstall.sql script to simplify removing MySQL Enterprise Audit.
New Keywords. Keywords added in MySQL 8.4 since MySQL 8.0. Reserved keywords are marked with (R).
AUTO, BERNOULLI, GTIDS, LOG, MANUAL (R), PARALLEL (R), PARSE_TREE, QUALIFY (R), S3, and TABLESAMPLE (R).
Preemptive group replication certification garbage collection. A system variable added in MySQL 8.4.0 group_replication_preemptive_garbage_collection enables preemptive garbage collection for group replication running in single-primary mode, keeping only the write sets for those transactions that have not yet been committed. This can save time and memory consumption. An additional system variable group_replication_preemptive_garbage_collection_rows_threshold (also introduced in MySQL 8.4.0) sets a lower limit on the number of certification rows needed to trigger preemptive garbage collection if it is enabled; the default is 100000.
In multi-primary mode, each write set in the certification information is required from the moment a transaction is certified until it is committed on all members, which makes it necessary to detect conflicts between transactions. In single-primary mode, where we need be concerned only about transaction dependencies, this is not an issue; this means write sets need be kept only until certification is complete.
It is not possible to change the group replication mode between single-primary and multi-primary when group_replication_preemptive_garbage_collection is enabled.
See Section 20.7.9, “Monitoring Group Replication Memory Usage with Performance Schema Memory Instrumentation”, for help with obtaining information about memory consumed by this process.
Sanitized relay log recovery. In MySQL 8.4.0 and later, it is possible to recover the relay log with any incomplete transactions removed. The relay log is now sanitized when the server is started with --relay-log-recovery=OFF (the default), meaning that all of the following items are removed:
- Transactions which remain uncompleted at the end of the relay log
- Relay log files containing incomplete transactions or parts thereof only
- References in the relay log index file to relay log files which have thus been removed
For more information, see the description of the relay_log_recovery server system variable.
MySQL upgrade history file. As part of the installation process in MySQL 8.4.0 and later, a file in JSON format named mysql_upgrade_history is created in the server's data directory, or updated if it already exists. This file includes information about the MySQL server version installed, when it was installed, and whether the release was part of an LTS series or an Innovation series.
A typical mysql_upgrade_history file might look something like this (formatting adjusted for readability):
```
{
  "file_format":"1",

  "upgrade_history":
  [
    {
      "date":"2024-03-15 22:02:35",
      "version":"8.4.0",
      "maturity":"LTS",
      "initialize":true
    },

    {
      "date":"2024-05-17 17:46:12",
      "version":"8.4.1",
      "maturity":"LTS",
      "initialize":false
    }
  ]

}
```
In addition, the installation process now checks for the presence of a mysql_upgrade_info file (deprecated in MySQL 8.0, and is no longer used). If found, the file is removed.
mysql client --system-command option. The --system-command option for the mysql client, available in MySQL 8.4.3 and later, enables or disables the system command.
This option is enabled by default. To disable it, use --system-command=OFF or --skip-system-command, which causes the system command to be rejected with an error.
mysql client --commands option. The mysql client --commands option, introduced in MySQL 8.4.6, enables or disables most mysql client commands.
This option is enabled by default. To disable it, start the mysql client with --commands=OFF or --skip-commands.
For more information, see Section 6.5.1.1, “mysql Client Options”.
Scalar correlated subqueries to derived tables. MySQL 8.4.0 lifts a previous restriction on transforming a correlated scalar subquery to a derived table such that an operand of the equality expression which did not contain an outer reference could be a simple column reference only.
This means that inner columns can be contained in deterministic expressions, as shown here:
```
func1(.., funcN(.., inner-column-a, ..), inner-column-b) = outside-expression

inner-column-a + inner-column-b = outside-expression
```
For example, the following query is now supported for optimization:
```
SELECT * FROM t1 
  WHERE ( SELECT func(t2.a) FROM t2 
            WHERE func(t2.a) = t1.a ) > 0;
```
The inner operand cannot contain outer column references; likewise, the outer operand cannot contain inner column references. In addition, the inner operand cannot contain a subquery.
If the transformed subquery has explicit grouping, functional dependency analysis may be excessively pessimistic, resulting in an error such as ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column .... For the InnoDB storage engine, the transform is disabled by default (that is, the subquery_to_derived flag of the optimizer_switch variable is not enabled); in this case, such queries pass without raising any errors, but are also not transformed.
See Section 15.2.15.7, “Correlated Subqueries”, for more information.