1.4 What Is New in MySQL 8.4 since MySQL 8.0 (original) (raw)

Features Added or Changed in MySQL 8.4

• MySQL native password authentication changes. Beginning with MySQL 8.4.0, the deprecatedmysql_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 includingmysql_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 disablingmysql_native_password, seeSection 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	Ifinnodb_buffer_pool_size <= 1 GiB, theninnodb_buffer_pool_instances=1 Ifinnodb_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 ofinnodb_flush_method is no longer changed as in MySQL 8.0, but the calculation ofinnodb_redo_log_capacity is changed from memory-based to CPU-based. For more information, seeSection 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”. (The actual default value of this variable isOFF; 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, otherwisefsync	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 theauthentication_ldap_sasl_client plugin.
  For more information, seeSASL-Based LDAP Authentication (Without Proxying).
• MySQL Replication: SOURCE_RETRY_COUNT change. The default value for theSOURCE_RETRY_COUNT option of theCHANGE REPLICATION SOURCE TO statement was changed to 10. This means that, using the default values for this option and forSOURCE_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 useSOURCE_RETRY_COUNT, instead.)
  For more information, seeSection 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 isUUID:_`TAG`_:NUMBER, where TAG is a string of up to 8 characters, which is enabled by setting the value of thegtid_next system variable toAUTOMATIC:_`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 toAUTOMATIC:_`TAG`_ or_`UUID`_:_`TAG`_:_`NUMBER`_ requires a newTRANSACTION_GTID_TAG privilege which is added in this release; this is true both on the originating server as well as for thePRIVILEGE_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 theTRANSACTION_GTID_TAG privilege.
  The built-in functionsGTID_SUBSET(),GTID_SUBTRACT(), andWAIT_FOR_EXECUTED_GTID_SET() are compatible with tagged GTIDs.
  For more information, see the descriptions of thegtid_next system variable and the TRANSACTION_GTID_TAG privilege, as well asSection 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 warningER_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 thereplica_parallel_workers system variable. See alsoSection 19.2.3.2, “Monitoring Replication Applier Worker Threads”, andSection 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 theterminology_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 valueBEFORE_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, seeSection 11.7, “Comments”.
• group_replication_set_as_primary() and DDL statements. Thegroup_replication_set_as_primary() function waits for ongoing DDL statements such asALTER 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:
  • ALTER DATABASE
  • ALTER FUNCTION
  • ALTER INSTANCE
  • ALTER PROCEDURE
  • ALTER SERVER
  • ALTER TABLESPACE
  • ALTER USER
  • ALTER VIEW
  • CREATE DATABASE
  • CREATE FUNCTION
  • CREATE PROCEDURE
  • CREATE ROLE
  • CREATE SERVER
  • CREATE SPATIAL REFERENCE SYSTEM
  • CREATE TABLESPACE
  • CREATE TRIGGER
  • CREATE USER
  • CREATE VIEW
  • DROP DATABASE
  • DROP FUNCTION
  • DROP PROCEDURE
  • DROP ROLE
  • DROP SERVER
  • DROP SPATIAL REFERENCE SYSTEM
  • DROP TABLESPACE
  • DROP TRIGGER
  • DROP USER
  • DROP VIEW
  • GRANT
  • RENAME TABLE
  • REVOKE
    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 thegroup_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 thegroup_replication_consistency system variable was changed toBEFORE_ON_PRIMARY_FAILOVER in MySQL 8.4.0. (Previously, it was EVENTUAL.)
  • The default value of thegroup_replication_exit_state_action system variable was changed toOFFLINE_MODE in MySQL 8.4.0. (Previously, it was READ_ONLY.)
    For more information, seeSection 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 newMEMBER_FAILURE_SUSPICIONS_COUNT column was added to the Performance Schemareplication_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 theRELOAD privilege, theFLUSH_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 theFLUSH_PRIVILEGES privilege; if there are none, any users having theRELOAD 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 theFLUSH_PRIVILEGES privilege, a user previously granted this privilege is unable to executeFLUSH PRIVILEGES statements unless the user has theRELOAD privilege.
• OPTIMIZE_LOCAL_TABLE privilege. MySQL 8.4.0 adds a newOPTIMIZE_LOCAL_TABLE privilege. Users must have this privilege to executeOPTIMIZE LOCAL TABLE andOPTIMIZE NO_WRITE_TO_BINLOG TABLE statements.
  When upgrading from a previous release series, users having the SYSTEM_USER privilege are automatically granted theOPTIMIZE_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, themysql system schema provided the only storage option. The newcomponent_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 themasking_dictionaries_flush() function added in this release.
  • The component can be configured to flush the memory periodically, leveraging the Scheduler component, by setting the newcomponent_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 wheneverANALYZE TABLE is run on the table to which it belongs. In addition, automatic recalculation of persistent statistics byInnoDB (seeSection 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 theANALYZE 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, seeHistogram Statistics Analysis.
• Added thetls-certificates-enforced-validation system variable, which permits a DBA to enforce certificate validation at server startup or when using theALTER 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, seeConfiguring 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:
  • authentication_ldap_simple_connect_timeout
  • authentication_ldap_simple_response_timeout
  • authentication_ldap_sasl_connect_timeout
  • authentication_ldap_sasl_response_timeout
    Connection and response timeouts are configurable through the system variables on Linux platforms only. For more information, seeSetting 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 usingWITH_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 aSELECT 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 thetp_thread_state table:TIME_OF_ATTACH,MARKED_STALLED,STATE,EVENT_COUNT,ACCUMULATED_EVENT_TIME,EXEC_COUNT, andACCUMULATED_EXEC_TIME
  • Added the following columns to thetp_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, andTIME_OF_EARLIEST_CON_EXPIRE.
    For more information, see Section 7.6.3, “MySQL Enterprise Thread Pool”, andSection 29.12.16, “Performance Schema Thread Pool Tables”.
• Information Schema PROCESSLIST table usage. Although theINFORMATION_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 thePROCESSLIST 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 andINTERSECT by means of a new hash table optimization which is enabled automatically for such statements, and controlled by setting thehash_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 tooff.
  The amount of memory allocated for this optimization can be controlled by setting the value of theset_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 theUSE_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 ofDELETE plusINSERT statements, as well as those ofINSERT IGNORE plusUPDATE 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 ofsp_set_firewall_mode() orsp_reload_firewall_rules() generates such a warning. SeeFirewall Account Profile Stored Procedures, as well asMigrating 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. Themysql_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 themysql_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, seeMySQL 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.
  SeeKey Migration Using a Migration Server, for more information.
• MySQL Enterprise Audit. Added theaudit_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, andTABLESAMPLE (R).
• Preemptive group replication certification garbage collection. A system variable added in MySQL 8.4.0group_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 variablegroup_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 whengroup_replication_preemptive_garbage_collection is enabled.
  SeeSection 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 therelay_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 namedmysql_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 thesystem 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.