BY 'clause was written to the audit log and general query log as an
AS 'clause. (Bug #33184550)
Previously, MySQL user accounts authenticated to the server using a single authentication method. MySQL now supports multifactor authentication (MFA), which makes it possible to create accounts that have up to three authentication methods. MFA support entails these changes:
authentication_policysystem variable enables MFA policy to be established by controlling how many factors can be used and the types of authentication permitted for each factor. This places constraints on how the authentication-related clauses of
ALTER USERstatements may be used.
Client programs have new
--password3command-line options for specifying multiple passwords. For applications that use the C API, the new
MYSQL_OPT_USER_PASSWORDoption for the
mysql_options4()C API function enables the same capability.
In addition, MySQL Enterprise Edition now supports authentication to MySQL Server using devices such as smart cards, security keys, and biometric readers. This authentication method is based on the Fast Identity Online (FIDO) standard, and uses a pair of plugins,
authentication_fidoon the server side and
authentication_fido_clienton the client side. The server-side FIDO authentication plugin is included only in MySQL Enterprise Edition distributions.
Multifactor authentication can use existing MySQL authentication methods, the new FIDO authentication method, or a combination of both. For more information, see Multifactor Authentication, and FIDO Pluggable Authentication. (Bug #33159968)
In cases where an authentication plugin performed no hashing of the authentication string,
CREATE USERstatements with a
BY 'clause failed with an error. (Bug #33125289)
MySQL now can be compiled using C++17. The following minimum version requirements apply for compiler support:
GCC 7.1 or Clang 5 (Linux)
XCode 10 (macOS)
GCC 10 (Solaris)
Visual Studio 2019 Update 4 (Windows)
In particular, on Solaris, GCC is now the only supported compiler. The code has been cleaned up to remove adaptations and workarounds for Sun Studio, Oracle Studio, and SunPro. (Bug #32907274, Bug #103757, Bug #32907475, Bug #32992125, Bug #32992242, Bug #33004840, Bug #33086882)
Previously, if the server restricted a client to the sandbox mode used to handle client connections for accounts with expired passwords, the client could use the
SETstatement. This is no longer permitted. For more information about sandbox mode, see Server Handling of Expired Passwords. (Bug #16369085)
BLACKHOLEstorage engine maximum key length has been increased from 1000 to 3072 bytes (the same as
InnoDB). Thanks to Adam Cable for the contribution. (Bug #32788749, Bug #103371)
FIREWALL_EXEMPTprivilege exempts a user from firewall restrictions. This is useful, for example, for any database administrator who configures the firewall, to avoid the possibility of a misconfiguration causing even the administrator to be locked out and unable to execute statements. See MySQL Enterprise Firewall.
keyring_hashicorpplugin configuration issues have been improved. (Bug #32075854)
To assist monitoring and troubleshooting, the Performance Schema instrumentation is now used to export names of instrumented threads to the operating system. This enables utilities that display thread names, such as debuggers and the Unix ps command, to display distinct mysqld thread names rather than “mysqld”. This feature is supported only on Linux, macOS, and Windows. For more information, see The setup_instruments Table.
Microsoft Windows: The Kerberos authentication method added in MySQL 8.0.26 for MySQL server and client hosts running Linux is now supported on the client side for Windows. This enables MySQL client applications running on Windows to connect to MySQL accounts on Linux server hosts that authenticate using Kerberos. For details, see Kerberos Pluggable Authentication.
For platforms on which OpenSSL libraries are bundled, the linked OpenSSL library for MySQL Server has been updated to version 1.1.1l. Issues fixed in the new OpenSSL version are described at https://www.openssl.org/news/cl111.txt and and http://www.openssl.org/news/vulnerabilities.html. (Bug #33273138, Bug #33309871)
Setting the session value of the following system variables is now a restricted operation and the session user must have privileges sufficient to set restricted session variables:
For information about the privileges required to set restricted session variables, see System Variable Privileges.
Important Change: The
default_authentication_pluginvariable is deprecated as of MySQL 8.0.27; expect support for it to be removed in a future version of MySQL.
default_authentication_pluginvariable is still used in MySQL 8.0.27, but in conjunction with and at a lower precedence than the new
authentication_policysystem variable, which is introduced in MySQL 8.0.27 with the multifactor authentication feature. For details, see The Default Authentication Plugin. (Bug #27515356)
Important Change: The system variable
group_replication_components_stop_timeoutspecifies the time that Group Replication waits for each of its modules to complete ongoing processes while shutting down. The component timeout applies after a
STOP GROUP_REPLICATIONstatement is issued, which happens automatically during server restart or auto-rejoin. The timeout is used to resolve situations in which Group Replication components cannot be stopped normally, which might happen if the member is expelled from the group while it is in an error state, or while a process such as MySQL Enterprise Backup is holding a global lock on tables on the member. In such situations, the member cannot stop the applier thread or complete the distributed recovery process to rejoin. The
STOP GROUP_REPLICATIONstatement does not complete until either the situation is resolved (for example, by the lock being released), or the component timeout expires and the modules are shut down regardless of their status.
Previously, the timeout value defaulted to 31536000 seconds (365 days), which did not help in situations such as those just described. The new default value is 300 seconds, so that Group Replication components are stopped after 5 minutes if the situation is not resolved before that time, allowing the member to be restarted and to rejoin.
References: See also: Bug #31460690, Bug #31648211, Bug #32309647.
Replication: Multithreading is now enabled by default for replica servers. A multithreaded applier has a number of applier threads that execute transactions in parallel. This behavior can avoid many cases of unwanted replication lag that can cause temporary divergence between the source and replicas.
The following default server settings are used to produce the multithreading behavior:
replica_parallel_workers=4. This setting enables multithreading and creates four applier threads on the replica, plus a coordinator thread to manage them. If you are using multiple replication channels, each channel has this number of threads. Four applier threads provide a base level of parallelism, and you can change the setting to specify up to 1024 applier threads.
replica_preserve_commit_order=1. This setting ensures that transactions are externalized on the replica in the same order as they appear in the replica's relay log, so the replica never enters a state that the master was not in, and there are no gaps in the sequence of transactions that have been executed from the relay log.
replica_parallel_type=LOGICAL_CLOCK. This setting specifies that transactions that are part of the same binary log group commit on a replication source server are applied in parallel on a replica. It is required when
To override the new defaults and disable multithreading for a replica server, specify
replica_parallel_workers=0. This setting disables parallel execution and gives the replica a single applier thread and no coordinator thread. When you apply this setting, the
replica_preserve_commit_orderoptions have no effect and are ignored.
Replication: In previous releases, Group Replication secured group communication connections and distributed recovery connections between members using its own implementation of the security protocols, including TLS/SSL and the use of an allowlist for incoming Group Communication System (GCS) connections. Replication groups can now use the MySQL Server's own connection security in place of the Group Replication implementation. Using the MySQL protocol means that standard methods of user authentication can be used for granting (or revoking) access to the group in place of the allowlist, and that the latest functionality of the server's protocol is always available on release. Network namespaces are supported for Group Replication when the MySQL communication stack is used.
To use the MySQL Server's implementation of the connection security management in place of the Group Replication implementation, set the new system variable
MYSQL. In addition, the network address set by
group_replication_local_addressfor each group member must be changed to one of the IP addresses and ports which MySQL Server is listening on, as specified by
bind_address. If a network namespace is used, this must be configured using the
CHANGE REPLICATION SOURCE TOstatement in the
Authentication is carried out using the existing replication user account that Group Replication uses for distributed recovery, as set using
CHANGE REPLICATION SOURCE TO, and this user must be given the new
GROUP_REPLICATION_STREAMprivilege. The TLS/SSL configuration for the connection is taken from Group Replication's existing settings for securing distributed recovery, plus the
group_replication_ssl_modesystem variable that specifies whether TLS/ SSL is enabled or disabled for group communications. These settings must be configured if they are not already in place. All these settings must be the same on all group members to avoid communication issues.
As part of this work, the default value for the
performance_schema_max_cond_classessystem variable is increased from 100 to 150.
See Configuring a group to use the MySQL Communication Stack for more details.
Programs that encounter issues while processing
includedirdirectives in option files now produce error messages that are more informative about the cause of the errors. (Bug #32798288, Bug #103397)
The default value for the
thread_stacksystem variable has been increased to 1048576 on all supported platforms. (Bug #103912, Bug #32965326)
References: See also: Bug #32934187.
A default time zone can now be set for a server by using the server option
--default-time-zonewhile starting a MySQL Server Docker container. Before, the container failed to start if the option was used.
When GTID-based replication is in use on a replica server, the replication applier and receiver threads still track and have some dependencies on binary log file names and file positions, as used for the alternative binary log file position based replication. A new option for the
CHANGE REPLICATION SOURCE TOstatement,
GTID_ONLY, removes the persistence of file names and file positions from the replication metadata repositories. For replication channels with this setting, in-memory file positions are still tracked, and file positions can still be observed for debugging purposes in error messages and through interfaces such as
SHOW REPLICA STATUSstatements (where they are shown as being invalid if they are out of date). However, the writes and reads required to persist and check the file positions are avoided in situations where GTID-based replication does not actually require them, including the transaction queuing and application process.
GTID_ONLYoption is disabled by default for asynchronous replication channels, but it is enabled by default for group replication channels, and it cannot be disabled for them. To set
GTID_ONLY = 1for a replication channel, GTIDs must be in use on the server (
gtid_mode = ON), and row-based binary logging must be in use on the source (statement-based replication is not supported). The
CHANGE REPLICATION SOURCE TOoptions
SOURCE_AUTO_POSITIONmust each be set to 1 for the replication channel. When
GTID_ONLYis set to 1, the replica uses
replica_parallel_workers=1if that system variable is set to zero for the server, so it is always technically a multi-threaded applier. This is because a multi-threaded applier uses saved positions rather than the replication metadata repositories to locate the start of a transaction that it needs to reapply.
The asynchronous connection failover mechanism for MySQL replication now enables a replica that is part of a managed replication group to automatically reconnect to the sender if the current receiver (the primary of the group) fails. The new feature works with Group Replication, on a group configured in single-primary mode, where the group’s primary is a replica that has a replication channel with
ON. The feature operates by default on a group in this situation, although you can disable it for the group by disabling the new member action
mysql_start_failover_channels_if_primary, using the
group_replication_disable_member_action()function. The feature is designed for a group of senders and a group of receivers to keep synchronized with each other even when some members are temporarily unavailable. It also synchronizes a group of receivers with one or more senders that are not part of a managed group. A replica that is not part of a replication group cannot use this feature.
To configure this feature, the replication channel and the replication user account and password for the channel must be set up on all the member servers in the replication group, and on any new joining members. You can do this using the
CHANGE REPLICATION SOURCE TOstatement, or if the new servers are provisioned using MySQL’s clone functionality, this all happens automatically. The
SOURCE_CONNECTION_AUTO_FAILOVERsetting for the channel is broadcast to group members from the primary when they join, and also if it is changed. The source list is broadcast to all members when they join or when it is updated. If the primary goes offline or into an error state, the new primary that is selected for the group has the source list and the channel configuration already in place, and establishes a replacement asynchronous replication connection with the source.
A new function
asynchronous_connection_failover_reset()is also provided for administrators to remove all settings relating to the asynchronous connection failover mechanism. Use this function to clean up a server that is no longer being used in a managed group.
The group communication engine for Group Replication (XCom, a Paxos variant) defaults to using every member of the group as a leader. When the Group Replication communication protocol version is set to 8.0.27 or later, the group communication engine can now use a single leader to drive consensus when the group is in single-primary mode. Operating with a single consensus leader improves performance and resilience in single-primary mode, particularly when some of the group’s secondary members are currently unreachable.
The single consensus leader is colocated with the group’s primary, and changes when a new primary is elected. The Performance Schema table
replication_group_communication_informationshows the preferred and actual consensus leader, or leaders if all members are used as a leader, the communication protocol version, and the write concurrency.
To enable the new behavior, set the system variable
ON(the default is
OFF). When Group Replication is running in multi-primary mode, or with earlier communication protocol versions, or when
group_replication_paxos_single_leaderis set to
OFF, the group communication engine operates using every member of the group as a leader.
Note that when you manually upgrade the members of a replication group to a new MySQL Server release, the group's communication protocol version is not automatically upgraded to match. If you no longer need to support members at earlier releases, you can use the
group_replication_set_communication_protocol()function to set the communication protocol version to the new MySQL Server version to which you have upgraded the members. MySQL InnoDB Cluster manages the communication protocol version automatically for replication groups created using that function.
For online DDL operations, storage is usually the bottleneck. To address this issue, CPU utilization and index building has been improved. Indexes can now be built simultaneously instead of serially. Memory management has also been tightened to respect memory configuration limits set by the user. See Configuring Parallel Threads for Online DDL Operations.
innodb_ddl_threadsvariable defines the maximum number of parallel threads for the sort and build phases of index creation.
innodb_ddl_buffer_sizevariable defines the maximum buffer size for DDL operations. The default setting is 1048576 bytes (approximately 1 MB). Defining a buffer size limit avoids potential out of memory errors for online DDL operations that create or rebuild secondary indexes. See Online DDL Memory Management.
The clone plugin now permits concurrent DDL operations on the donor MySQL Server instance while a cloning operation is in progress. Previously, a backup lock was held during the cloning operation, preventing concurrent DDL on the donor. To revert to the previous behavior of blocking concurrent DDL on the donor during a clone operation, enable the
clone_block_ddlvariable. See Cloning and Concurrent DDL.
Incompatible Change: For all
SELECTstatements on a view, the query digest was based on the view definition. As a result, different queries had the same digest and aggregated together in the Performance Schema table
events_statements_summary_by_digest, so statistics in that table were not usable for distinguishing distinct
The query digest for each
SELECTstatement on a view now is based on the
SELECT, not the view definition. This enables distinguishing distinct
SELECTstatements in the
events_statements_summary_by_digesttable. However, tools that use query digests may need some adjustment to account for this change. For example, MySQL Enterprise Firewall and query rewrite plugins rely on query digests and existing rules for them that are associated with views may need to be updated. (Bug #27540213, Bug #89559, Bug #31761802)
EXPLAIN FORMAT=TREEnow shows whether an index scan uses a covering index, and thus does not need to look up other columns from the table/clustered index. For example, if
idx1is a covering index, the old output
Index scan on t1 using idx1is now shown as
Covering index scan on t1 using idx1. Previously, this information was shown only for
This fix also improves the wording used for full-text search to align with this change. For example, the old output
Indexed full text search on t1(which was the same in both the covering and non-covering cases) is now
Full-text index search on t1when there is no covering index, and
Full-text covering index search on t1when a covering index is used. (Bug #32825235)
InnoDB: An excessive number of notes were written to the error log when the
innodb_open_fileslimit was temporarily exceeded. (Bug #33343690)
InnoDB: An in-place DDL operation failed to flush all modified pages. (Bug #33290335, Bug #33238133)
InnoDB: A parallel scan returned an incorrect partition ID when loading data into HeatWave from a subpartitioned
InnoDBtable. (Bug #33276021)
InnoDB: The unused
os_event::event_iterfield in the
InnoDBsources was removed to reduce memory use in the
Our thanks to Facebook for the contribution. (Bug #33252468)
InnoDB: A workaround was implemented for a Clang issue that causes a build failure on Windows (Bugzilla – Bug 51538). (Bug #33217633)
srv_worker_threadthreads were duplicated in the
Thanks to Kaige Ye for the contribution. (Bug #33209066, Bug #104575)
InnoDB: Truncation of an undo tablespace during use by an active transaction raised an assertion failure. The transaction was prematurely marked as complete, permitting the truncation operation. (Bug #33162828)
InnoDB: When loading data into HeatWave from a partitioned table with concurrent DML modifying the primary key, the partition ID reported in the load callback was found to be incorrect for some records. (Bug #33139692)
InnoDB: Instances of
InnoDBsources were replaced by C++17
[[maybe_unused]]attributes. (Bug #33112971)
InnoDB: Each buffer pool block includes a
block->lock_hash_valfield. Caching of this value was determined to be unnecessary, as it introduced unnecessary coupling of the buffer and lock system and unnecessary memory usage. (Bug #33072415)
InnoDB: A query that performed an index merge with retrieval ordered by row ID raised an assertion failure. The record buffer set up for the index merge could not be used due to the scanned table containing a primary key with a BLOB component. A record buffer cannot be used for reading BLOBs, which are stored outside of the record. The BLOB primary key was not detected when the record buffer was set up, as the primary key column was not yet in the read set. Retrievals ordered by row ID temporarily add the primary key at a later stage when needed. To address this issue, a record buffer is no longer requested for row-ordered retrievals if the primary key has a BLOB component. (Bug #33067554)
InnoDB: Deleting or updating a row from a parent table initiated a cascading
SET NULLoperation on the child table that set a virtual column value to NULL. The virtual column value should have been derived from the base column value.
Thanks to Yin Peng at Tencent for the contribution. (Bug #33053297)
InnoDB: On a system that was nearing disk capacity, an
InnoDBrecovery operation involving application of file extension redo log records (
MLOG_FILE_EXTEND) could cause a failure. (Bug #33002492)
InnoDB: Conflicting explicit locks granted on the same record raised an assertion failure. (Bug #33000142)
InnoDB: Freeing the first page of LOB at the end of purge batch raised an assertion failure. The failure was due an invalid root page number. (Bug #32958624)
InnoDB: To facilitate failure reporting and resolution, the
ib::fatal()function in the
InnoDBsources was revised to include the caller's location. (Bug #32957311)
InnoDB: Recovery on the clone recipient server failed with the following error: Error reading encryption for innodb_undo_007. The encryption key was not written to encrypted spaces created during the page copy phase of the clone operation. (Bug #32950216)
InnoDB: To avoid generating unwanted warning messages, the
fil_space_acquire()function in the
InnoDBsources was replaced by the
fil_space_acquire_silent()function where possible. Both functions are used by background threads to acquire a tablespace. (Bug #32944543)
InnoDBCRC32 checksum algorithm implementations have now been optimized for use with ARM and x86/x64 architectures. (Bug #32887066)
InnoDB: Startup on an instance with thousands of tables took an excessive amount of time due a large amount of traffic on the error logging subsystem. (Bug #32846656)
INFORMATION_SCHEMA.FILESview did not show the current path of the temporary tablespace file, and the file name shown was different from the one defined by the
innodb_temp_data_file_pathvariable. (Bug #32840635, Bug #103553)
InnoDB: On Windows, keeping a file open without a shared write lock while attempting to acquire the
fil_shardmutex caused a deadlock with another thread that had acquired the
fil_shardmutex and was attempting to access the same file. (Bug #32808809)
InnoDB: Starting a MySQL Server instance using the same
InnoDBdata files as an another running MySQL Server instance resulted in an initialization failure. (Bug #32777654, Bug #103338)
InnoDBrecovery process did not recognize that page compression had been applied to data that was being recovered, causing the tablespace data file to increase in size during the redo log apply phase, which could lead to a recovery failure for systems approaching a disk-full state. (Bug #32771259)
InnoDB: An assert that traversed a list of file segments which were not full to calculate the number of used pages for comparison with the number of used pages tracked by a field in the file segment inode failed sporadically. (Bug #31685095)
InnoDB: A transaction failed to roll back when the server was restarted after failure occurred during an online DDL operation. Table locks could not be resurrected for the uncommitted transaction and the data dictionary table object could not be loaded for the affected table.
Thanks to Shaohua Wang for the contribution. (Bug #31131530, Bug #99174)
InnoDB: A query that used a temporary table for aggregation exhausted the memory available to the
TempTablestorage engine, causing an update operation to fail with a table is full error. (Bug #31117893, Bug #99100)
Replication: During the Group Replication auto-rejoin procedure, a group member sets its status to
RECOVERING. If the group member does not manage to rejoin, it should change the status to
ERROR, but if a view change occurred in the meantime, it was possible for the status to remain in
RECOVERING. The member status is now set to
ERRORafter an unsuccessful auto-rejoin procedure, regardless of any ongoing or stuck view changes. (Bug #33276418)
Replication: Garbage collection for certification information has been moved from the Group Replication Group Communication System (GCS) thread to a background thread, so that sending and receiving of messages are not blocked while garbage collection is in progress. (Bug #33190276)
Replication: When Group Replication is configured with
group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER, in the event of a primary failover, client connections are held until the new primary has the same state as the previous primary. Some monitoring and administration statements are exempt from this hold, so that the new primary can be inspected during the failover process.
DOstatements had a blanket exemption from the hold, but now only
DOstatements that do not use tables or loadable functions are exempt, as for
SELECTstatements. (Bug #33130768)
Replication: The replication applier (SQL) thread overrode retryable errors (such as deadlocks and wait timeouts) from storage engines with a key not found error, causing replication to stop without retrying the transaction. These errors are no longer overridden. (Bug #33107663)
Replication: MySQL Server incorrectly permitted reads from Performance Schema tables relating to Group Replication while Group Replication was stopping or restarting, and the data concerned should not have been used. The server now checks whether Group Replication is in
OFFLINEstatus or uninitialized before executing the query. (Bug #33085494)
Replication: When Group Replication is configured with
group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER, in the event of a primary failover, client connections are held until the new primary has the same state as the previous primary. Some monitoring and administration statements are exempt from this hold, so that the new primary can be inspected during the failover process. Previously,
SHOWstatements had a blanket exemption from the hold (with the exception of
SHOW CREATE USER), but now only
SHOWstatements that do not depend on data (only on status or configuration) are exempt. The exempt
SHOWstatements are listed in the documentation for the feature. (Bug #33082509)
Replication: While Group Replication's distributed recovery process was ongoing to synchronize a joining member with the donor, the Performance Schema table
replication_group_member_statstable was not updated with the current number of transactions queued on the
COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUEfield.) The count is now tracked while new transactions are arriving during distributed recovery, although it remains zero until the joiner has certified the transactions that were received in the first phase of distributed recovery. (Bug #33067441)
Replication: For multithreaded replicas (replicas on which
replica_parallel_workersis set to a value greater than 0), if
replica_parallel_workersis set to 1, the setting for
replica_preserve_commit_orderis now ignored. When there is a single applier, transactions are always executed and committed in the same order as in the replica's relay log; ignoring the process to preserve the commit order avoids potential performance degradation. (Bug #33048169)
Replication: A deadlock could occur when a statement referencing Access Control Lists (ACLs), such as
CREATE USER, was executed on the primary of a Group Replication group, and a member joined the group immediately afterwards before the transaction commit was confirmed by the other group members. The distributed recovery process needs a read lock on the ACL cache which is locked by the ACL statement. This situation blocked Group Replication's Group Communication System (GCS) thread until the ACL statement timed out, making the primary unreachable and possibly preventing the new member from joining. The ACL cache lock is now no longer required for the distributed recovery process, although the lock in the situation described is only released after the view change is complete and the ACL statement is committed. Any new connections or statements that require the ACL cache lock, including a member join when Group Replication uses the MySQL communication stack, must therefore wait on this or fail and retry. (Bug #33025231)
Replication: An assertion was raised if a replica MySQL Server instance with unpopulated time zones attempted to replicate a statement that set a time zone value that was unknown to the replica. Replicas now handle this situation correctly. (Bug #32986721)
Replication: The error messages issued by MySQL Replication when GTIDs required for auto-positioning have been purged could be incorrectly assigned or scrambled in some situations. (Bug #32965864)
Replication: If the thread that runs Group Replication’s applier module is stopped, the group cannot function properly because it cannot exchange group transactions and messages. Previously, a member in this situation remained in
ONLINEstatus and ignored the internal errors. The member now changes to
ERRORstatus if the thread is stopped, and takes the action specified by the
group_replication_exit_state_actionsystem variable. (Bug #32934479)
Replication: If a group member was elected as the primary right before or while it was shutting down, the shutdown process hung while waiting on the primary election process, which was attempting to make the server leave the group since the election had failed due to the shutdown. The error handling process for primary elections now takes this into account, and does not take any further actions if the member is already leaving the group. (Bug #32884709)
Replication: The contents of the
gtid_purgedGTID sets were not persisted after restoring a dump taken using mysqldump. The dump file sequence has now been changed so that the
mysqlschema (which contains the
mysql.gtid_executedtable) is not dropped after the
gtid_purgedGTID set is written. A new option
--skip-mysql-schemais added for mysqldump which lets you choose not to drop the
mysqlschema at all. (Bug #32843447)
Replication: Querying the Performance Schema table
replication_asynchronous_connection_failovercould return an error if a row was deleted during the query process. In this situation, the row count is now returned as zero, and the query can be retried. (Bug #32701593)
Replication: In some situations, a replica that used connection compression was not able to re-establish a lost connection to the source server. The issue has now been fixed. (Bug #32494609)
Replication: From MySQL 8.0.22, a replication source server writes a
TRUNCATE TABLEstatement to the binary log to notify replicas to empty a
MEMORYtable the first time it is used after a server restart. Previously, the thread where the statement was logged was not registered with the global thread manager, so Group Replication was not able to acknowledge it. The issue has now been corrected. (Bug #32355801)
JSON: Made additional improvements in JSON function error handling to those made in MySQL 8.0.23. (Bug #32864910)
References: See also: Bug #31856260.
JSON_TABLE()allowed duplicate column names when the names differed in case only, although column names are case-insensitive in MySQL.
Now this function compares column names in case-insensitive fashion. (Bug #102824, Bug #32591074)
Added Ubuntu 21.10 packages. (Bug #33501583, Bug #105274)
The MySQL client library could contribute to a memory leak if MySQL was linked against OpenSSL 1.0.1, as is the case for builds on EL6. (Bug #33335046)
Regular expression functions now report an error when an expression or pattern cannot be converted to a character set which is suitable for the ICU regular expression engine.
In addition, error checking in several geometry functions has been improved. (Bug #33290245)
Implicitly grouped queries sometimes calculate aggregates during optimization when their values can be easily retrieved from indexes. When a predicate referenced a column that was declared with a
NO PADcollation, that predicate might be evaluated using
PAD SPACEsemantics, and so return wrong results. This was because an internal function that checked for insignificant trailing spaces made the assumption that all nonbinary collations had
PAD SPACEsemantics, which was true of MySQL 5.7, but is not the case for MySQL 8.0, which has added many collations having
NO PADsemantics, including the default collation (
We fix this by explicitly checking the padding attribute of the collation in such cases. (Bug #33282123)
A query containing a common table expression with a
MATCH() AGAINST()clause executed on a table defined without a full-text index raised an assertion failure. (Bug #33264864)
Several Performance Schema tables contained default timestamp values of 0 (zero) which conflicted with the default
For example, attempting to create a new table based on such a Performance Schema table resulted in an error similar to the following:
ERROR 1067 (42000): Invalid default value for 'FIRST_SEEN'
Default timestamp values have been removed from the following tables:
(Bug #33240123, Bug #104643)
A failed write to the
NOTIFY_SOCKETenvironment variable caused a failure. The
ER_SYSTEMD_NOTIFY_WRITE_FAILEDerror associated with the failed write has two parameters, but only one parameter was passed to the error logging routine. (Bug #33239183)
An incorrectly type-casted variable was used when setting the
--ssl-fips-modeoption. (Bug #33223230)
The following threads were not present in the
Thanks to Kaige Ye for the contribution.
Thanks to Kaige Ye for the contribution. (Bug #33214130, Bug #104582, Bug #33214136, Bug #104583)
A recursive call to an internal save function led to an unexpected error. (Bug #33198164)
SPACE()function did not handle certain large or unsigned values correctly. (Bug #33180446)
mysqld_list_fields()function failed to remove temporary tables created to evaluate
JSONtable functions. (Bug #33177686)
The code to produce minimal TAR packages added debug symbols to the packages, which caused larger (roughly by 10x) builds. Now DEB/RPM compiler flags are on by default for debug symbol builds, and off by default for minimal sized release builds. (Bug #33151629, Bug #104402)
DELETEstatements were found to leak memory. (Bug #33151275)
References: See also: Bug #18684036.
The return value for a copy function internal to the server was not handled as expected. (Bug #33142669)
References: This issue is a regression of: Bug #31982292.
YEARvalues were not always interpreted correctly. (Bug #33142669)
References: This issue is a regression of: Bug #31994744.
Empty range frames were not always handled correctly. (Bug #33142418)
References: This issue is a regression of: Bug #90300, Bug #27808099.
Function arguments were not always evaluated correctly during resolution of functions defined within views. (Bug #33142010)
References: This issue is a regression of: Bug #29904087.
In debug builds, the
ALTER TABLEstatement could produce an error if it added a new virtual column with the same name as one of the columns later referred to by a foreign key. This fix now ignores a virtual column if the name is duplicated and instead uses existing, non-virtual column names to check conditions. (Bug #33114045)
An assert condition to ensure that execution of a stored program instruction is started when there are no errors did not work properly for a
CASEstatement in a loop. (Bug #33079184)
In debug builds,
ANALYZE TABLEwith the
UPDATE HISTOGRAMclause could return a non-success value to the caller, instead of a success value, after successfully clearing the diagnostics area. (Bug #33079073)
mecab_charsetsystem status variable now reports its value as
utf8, which is deprecated. (Bug #33078623)
In debug builds, MySQL Enterprise Encryption UDFs did not set the nullable flag when returning NULL. (Bug #33077931)
The range optimizer was sometimes called when a plan lock was in force. This caused issues since the range optimizer can call itself, but a plan lock does not allow for recursion. (Bug #33076462)
References: This issue is a regression of: Bug #18684036.
String functions that use temporary string buffers during evaluation could lead to unexpected shutdowns. (Bug #33073951)
The error message emitted after a host name failed to resolve to an IP address did not include a meaningful
EAI_NONAMEis returned in the message instead of
(0). (Bug #33064143)
A statement such as
CREATE TABLE t SELECT 1created an
InnoDBtable that was written incorrectly to the binary log if the value of
binlog_formatwas set to
sql_modewas in ANSI mode. As a result, replication of the statement failed with an error on the replica. Applying the mysqlbinlog utility to such a binary log could also fail.
CREATE...SELECTwas implemented by adding a new clause to
START TRANSACTION. However, this clause was not added when ANSI mode was enabled. This in turn caused the execution of an ordinary implicitly committed
CREATE TABLEin the middle of the transaction and produced an error in GTID mode if the transaction had an assigned GTID. The issue is fixed by removing the SQL mode dependency from the new clause. (Bug #33064062, Bug #104153)
A log file containing a malformed ISO8601 timestamp was processed incorrectly. (Bug #33060440)
String conversion warnings that previously referred to
utf8mb3instead. (Bug #33059330)
For Enterprise Linux 8 (and Fedora), fixed the debuginfo RPMS packages by disabling REPRODUCIBLE_BUILD in fprofile.cmake. (Bug #33037380)
EXPLAIN FORMAT=TREEnow shows more precise information than displayed previously about scans generated by the range optimizer. In particular, sub-iterators are now displayed explicitly, and are properly timed with
EXPLAIN ANALYZE; index range scans now show the actual ranges being scanned. Descriptions in the output are also more user-friendly than before; for example,
index_for_group_byshown for a query using
DISTINCTis replaced by
index skip scan for deduplication.
In addition, a roundoff error causing inaccuracies in row count estimation for read over range intersection scans has been corrected, and optimizer traces for index range scans now correctly displays implicit key parts from
InnoDBprimary keys when they are used. (Bug #33037007, Bug #33062448)
For a query with rollup, when setting an expression as nullable because it had a grouping column, we missed setting all expressions within that expression as nullable, doing so only for the topmost expression. This meant that, during evaluation, a
NULLgenerated by rollup was not always propagated correctly. To fix this, we now set all the expressions having a grouping column as nullable when the query uses rollup. (Bug #33036184)
During execution of
EXPLAIN, when crossing into a different query block through a streaming or materialization node, this node was counted as the root, rather than the actual root node. (Bug #33030136)
Fixed an undefined conversion from double to
sql/join_optimizer/cost_model.cc. (Bug #33024410)
The internal function
find_in_group_list()did not match up match up all items correctly during
ROLLUPprocessing. We fix this by adding casts to
GROUP BYexpressions. (Bug #33022742, Bug #33123934)
References: This issue is a regression of: Bug #30969045.
A missing test for success of a memory allocation in the MySQL client library could lead to a client exit. (Bug #33019026)
An audit log function call from a prepared statement caused an error. (Bug #33016004)
Avoid adding column names prefixed with
!hidden!to ensure that new names do not collide with names used by existing hidden columns for functional indexes. Generated hidden column names now have the following new form that extends the use of functional indexes into environments that do not support names generated by
countervalue of a generated name is zero unless a column with that name already exists in the table. In this case, the value is incremented until the name becomes unique. (Bug #32983024)
Removed an unnecessary hard-coded dependency on the range optimizer from
sql_help.cc. (Bug #32976042)
Insufficient buffer space allocation during window function execution could cause an assertion to be raised. (Bug #32975889)
When finding the list of tables under a hash join, we did not take into account those that were also hidden under ZERO_ROWS iterators. This could lead to NULL row flags not being set correctly, which also caused problems when weedout wanted to save row IDs for them. (Bug #32975168)
Creation and deletion of temporary tables used in resolution of common table expressions and having table references created within subqueries were not always managed correctly. (Bug #32962511)
-–binary-as-hexoption is enabled for the mysql client, empty strings are now printed as
NULL. (Bug #32961656, Bug #103906)
The resolver usually terminates the analysis and exits after encountering an error in a statement. In the case of duplicate column analysis, the resolver continued to the end of the column list, possibly adding multiple error messages to the diagnostics object. (Bug #32960158)
When a scalar subquery returned multiple rows, the resulting error was not always handled correctly. (Bug #32956779)
Changing the server SQL mode after creating a table containing generated columns could cause spurious messages to be written to the error log. (Bug #32954466)
Manifest file reading could fail on Windows. (Bug #32950322)
Evaluation of the values in an
IN()list did not stop immediately on error, which led to assert failures. We fix this by stopping evaluation in such cases as soon as an error has been raised. (Bug #32942328)
If an error was raised while evaluating a comparison of two non-nullable values as strings, the result of the comparison was set to
NULL, even though the result was non-nullable according to the comparison operator metadata. The error was correctly returned to the user, but an assertion was raised by this inconsistency when running in debug mode.
This is fixed by causing
Arg_comparatornot to set its owner to
NULLwhen the owner is not nullable. (Bug #32942327)
An unset variable referenced in an SQL script executed during an upgrade operation caused a failure. (Bug #32939819)
Improper error propagation in
filesortoperations could raise an assertion. (Bug #32932969)
Bit functions in window expressions assert that the runtime size of a bit mask is not bigger than its resolve time size. We found several violations of this rule, listed here:
ENCRYPT()sometimes computed the maximum size of the result incorrectly.
During resolution of
REPLACE(we assumed that the entire length of
from_strwould be replaced for each match in
str, but since
from_strmay be only 1 character long, it is possible for
strto be replaced with multiple copies of
COMPRESS()computed the maximum result length in an arbitrary fashion. Now we use
(Bug #32922688, Bug #33117410, Bug #33275424)
References: See also: Bug #33516898.
In the internal
WalkAndReplace()function, errors from
set_cmp_func()were not correctly propagated. (Bug #32918927, Bug #33007298)
References: This issue is a regression of: Bug #32548377.
A deadlock could occur if a
RESET REPLICA ALLstatement was used while the channel configuration was being read. (Bug #32906709)
A potential race condition in accessing the persisted variables cache has been eliminated. (Bug #32901419)
The constant propagation performed by the MySQL optimizer could in some cases replace references to a column that was not nullable with a nullable expression. When this occurred, the parent item of the replaced column reference could sometimes have the wrong nullability, leading assert failures later, during execution, when a non-nullable item unexpectedly returned
We fix this by skipping constant propagation in cases where a non-nullable column reference is replaced by a nullable expression. (Bug #32895824)
References: This issue is a regression of: Bug #32371039.
A column name provided in a query could differ in collation details, or because the name was provided as an expression alias in the query, and still match a column name in the dictionary. The query output contained the column name specified in the query (for example,
aaa) rather than the column name from the dictionary (for example,
AAA). (Bug #32892045)
When the server SQL mode is other than strict mode, certain string functions return
NULLto indicate that the result is too large for the result buffer, which could lead to in inconsistent behaviour such as incorrectly sorted output. In addition, the functions
CAST(... AS CHAR)did not maintain nullability properly for all cases. (Bug #32864958)
Hidden items added as part of an
ORDER BY, windowing function, or a reference to a view were not always handled correctly in implicitly grouped queries. (Bug #32863279, Bug #33079592)
Type resolution for negation did not set the proper precision when converting the type from integer to decimal. This is fixed by assigning the same precision as the argument. (Bug #32863037)
References: This issue is a regression of: Bug #31348202.
Improper error propagation for failed
CREATE TABLE ... SELECTstatements caused rollback not to occur. (Bug #32855882)
When used in a subquery, a
VALUEShaving more than one
ROW()was not always handled correctly. (Bug #32851684)
The error packet that MySQL Server sends to a client program when the wait timeout expires (
ER_CLIENT_INTERACTION_TIMEOUT) used an incorrect sequence number of 2 instead of 0 in the packet header when protocol compression was used. (Bug #32835205, Bug #103412)
Concurrent insert operations on multiple tables with full-text indexes caused a large number of full-text index synchronization requests, resulting in an out of memory condition. (Bug #32831765, Bug #103523)
The fix for a previous issue, following subsequent work which made it redundant and which led to invalid results from expressions used in window functions, has been reverted. (Bug #32820802)
References: Reverted patches: Bug #26389508.
In prepared statements,
NULLIF()result type determination could be incorrect. (Bug #32816305, Bug #103458)
EXISTSto a semijoin, and when the query contained a view reference, the query was not processed correctly. (Bug #32813550)
References: This issue is a regression of: Bug #30671329.
Creating and dropping of views within stored routines were not always handled correctly. (Bug #32807430)
The fix for a previous issue included a minor refactoring of how the precision and scale of a decimal expression were determined. It later emerged that, for the
TRUNCATE()function, we might end up with a precision of zero, which is invalid.
We fix this problem by treating a precision of zero as one. (Bug #32802251)
References: See also: Bug #31348202.
For legacy reasons, we can have composite access paths including
table_path. For ease of analysis and better formatting, we move the
EXPLAINoutput for these previous to the
We show here examples of an
EXPLAINstatement run both prior to and following this change:
# Table created as follows: mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t1 ( f1 INTEGER ); Query OK, 0 rows affected (0.03 sec) # Previous to change: mysql> EXPLAIN FORMAT=TREE -> SELECT * FROM ( SELECT * FROM t1 LIMIT 2 OFFSET 1 ) AS alias1 -> WHERE f1 <= ANY ( SELECT f1 FROM t1 ) ORDER BY f1\G *************************** 1. row *************************** EXPLAIN: -> Sort: alias1.f1 -> Filter: <nop>((alias1.f1 <= (select #3))) (cost=2.62 rows=2) [other sub-iterators not shown] -> Table scan on alias1 (cost=2.62 rows=2) -> Materialize (cost=0.35..0.35 rows=0) -> Limit/Offset: 2/1 row(s) (cost=0.35 rows=0) -> Table scan on t1 (cost=0.35 rows=1) # Following change: mysql> EXPLAIN FORMAT=TREE -> SELECT * FROM ( SELECT * FROM t1 LIMIT 2 OFFSET 1 ) AS alias1 -> WHERE f1 <= ANY ( SELECT f1 FROM t1 ) ORDER BY f1\G *************************** 1. row *************************** EXPLAIN: -> Sort: alias1.f1 (cost=0.35..0.35 rows=0) -> Filter: <nop>((alias1.f1 <= (select #3))) (cost=2.62 rows=2) -> Table scan on alias1 (cost=2.62 rows=2) -> Materialize (cost=0.35..0.35 rows=0) -> Limit/Offset: 2/1 row(s) (cost=0.35 rows=0) -> Table scan on t1 (cost=0.35 rows=1) -> Select #3 (subquery in condition; run only once) -> Aggregate: max(t1.f1) (cost=0.45 rows=1) -> Table scan on t1 (cost=0.35 rows=1)
After this change, the only legal access paths within
ALTERNATIVE. (Bug #32788576, Bug #32915233)
Constant folding did not always handle errors correctly when evaluating decimal expressions. (Bug #32785804)
A call order mismatch in
setup_order()to be called after
resolve_subquery(), which meant that, for a
VALUESclause that was a subquery, the subquery could be merged into the outer query block before calling
setup_order(), leading to inconsistent data structures and an error.
We fix this issue by performing
setup_order()earlier, and, if the column is not found, resolution is aborted. (Bug #32783943)
References: This issue is a regression of: Bug #31387510.
In the Performance Schema table
variables.info, the system variable
skip_slave_startwas incorrectly listed as
COMPILEDwhen the global value was actually loaded from the persisted variables file, so
PERSISTEDshould have been used. (Bug #32640588)
When a query uses a temporary table for aggregation, the group by item is used as a unique constraint on the temporary table: If the item value is already present, the row is updated; otherwise, a new row is inserted into the temporary table. If the item has a result field or reference item, it it evaluated twice, once to check whether the result exists in the temporary table and, if not, again while constructing the row to be inserted. When the group by item was nondeterministic, the result value used to check for existence differed from that with which an insert was attempted, causing the insert to be rejected if the value already existed in the table.
We fix this by using the hash of any nondeterministic items as the unique constraint, so that the hash is evaluated once only. (Bug #32552332)
In the case of a lateral derived table, if the creation of the cache invalidator was delayed, the table materialization was emitted without the invalidator, which kept rematerialization from occurring during execution and led to wrong results.
The pending cache invalidator was emitted only when the index of the lateral table was less than that of the last table in the table list being considered. When the table index of the pending invalidator was equal to the last table of the join slice, the cache invalidator was skipped and the materialization was emitted without the invalidator.
We fix this by creating the pending cache invalidator if the table index of the pending invalidator is less than or equal to that of the last table in the table list of the current join slice. (Bug #32407774)
Privilege-checking for table-specific roles was in some contexts not restrictive enough. (Bug #32400788)
Inconsistencies in how certain comparison predicates were evaluated (for example, when part of a
WHEREclause) could return different results if a function was used instead of a string literal. (Bug #32345941, Bug #102151)
Columns of type
SETare ordered based on numeric comparison, but the comparison function for range expressions (that is, expressions used for ordering in case of a range frame specification) of a window function is set based on the result type of the column, which for
String. As a result, processing of rows for a window frame (to see whether a row is before or after the frame) did not work correctly; for example, a string comparison might determine that a row occurs before a frame, while a numeric comparison would have placed the row after.
To fix this problem, we implement integer cache items for
SET, as well as integer comparison functions for use when
SETtypes are involved in range expressions. (Bug #32328576)
A DML statement, when accessing a subquery which had been optimized away and cleaned up, led to an unplanned shutdown of the server. (Bug #32244822)
When resolving columns, their names are compared in case-insensitive fashion using
utf8_general_ci, which does not always follow the same comparison rules as those for the collation actually used for the table. Previously, when a table had in excess of 32 columns, name lookup was performed using a hash table. Hashing is collation-aware, and so follows the collation's comparison rules; this caused name lookup and duplication detection to be done in an inconsistent fashion. We solve this problem by removing the hash, and performing column name resolution in the same way in all cases regardless of the number of columns. (Bug #32169656)
For a nullable column, when adjacent ranges were rounded off to the same value by range optimizer, wrong results were returned. (Bug #31870920)
References: See also: Bug #98826, Bug #30988735.
Quote handling was improved for the
SHOW GRANTSstatement. (Bug #31716706)
An attempt could be made to write a
JSON_TABLE()expression to the optimizer trace before the temporary table backing the table function had been created, causing an assertion to be raised. Now when the column type is not yet available,
<column type not resolved yet>is written. (Bug #31578783)
keyring_hashicorp_update_config()function was not safe for concurrent execution. (Bug #31205028)
gen_dictionary()function now takes
latin1as the character set of its argument, and returns the same character set. (Bug #30389649)
The query rewrite plugin failed when refreshing the rewrite rules and the table holding the rewrite rules contained rows that had been marked as deleted, but not physically removed.
We fix this by causing the query rewrite plugin to skip the deleted rows instead of failing when it sees them. (Bug #22654105)
Refactoring done as part of implementing window functions in MySQL made it possible to refer to aliases of aggregates in
ORDER BYclauses but also allowed direct references to such aggregates, even though this should not be allowed. Now the server checks explicitly for such illegal references. (Bug #13633829, Bug #30106081)
In certain cases, the view reference cloned when pushing a condition down to a derived table was not always resolved in the desired context. In addition, a check for a null condition was not performed correctly. (Bug #104574, Bug #33209907, Bug #33197276)
Otherwise identical strings, using, respectively, the ASCII (collation
ascii_general_ci) and UCS2 (collation
ucs2_general_ci) character sets did not match as expected in join conditions. (Bug #104571, Bug #33204161)
References: See also: Bug #24847620, Bug #30746908, Bug #32244631, Bug #32501472.
Given the default collation
c1of a character set
cs, and a different collation
c2(that is, not equal to
c1), then the statement
CREATE DATABASE d COLLATE c2 CHARACTER SET cscreated a new database with the default collation set to
c2. (Bug #104504, Bug #33183590)
Some queries using
HAVING COUNT(DISTINCT ...)did not return any rows when one was expected. (Bug #104411, Bug #33152269)
References: This issue is a regression of: Bug #31790217.
Multi-valued indexes were not used in the following cases:
In prepared statements
MEMBER OF()combined using
ORwith another predicate
Our thanks to Yubao Liu for the contribution. (Bug #104325, Bug #104700, Bug #104721, Bug #33123079, Bug #33268466, Bug #33275457)
References: See also: Bug #102359, Bug #32427727. This issue is a regression of: Bug #30838807.
NULLwas passed to a user-created function that called
REGEXP_INSTR(), the first invocation of the function returned
NULLas expected, but each subsequent invocation of the function also returned
NULLwithout regard to the value passed to it. (Bug #104239, Bug #33089668)
Some of the functions defined in
mbr_utils.ccthrew heap-allocated exceptions in some situations. Memory allocated for the exception object in these cases was never freed, which meant that a small amount of memory leaked each time an exception was thrown.
This is fixed by allocating the exception on the stack in such cases, instead. (Bug #104214, Bug #33086286)
Column names were not displayed correctly in the results of
ROLLUPqueries when the
subquery_to_derivedoptimization was enabled. (Bug #104139, Bug #33057397, Bug #33104036)
A stored procedure containing an
EXISTS, which acted on one or more tables that were deleted and recreated between executions, did not execute correctly for subsequent invocations following the first one. (Bug #103607, Bug #32855634)
When executing a range query with multiple identical ranges joined by
OR(for example, a query with
WHERE (a=1 AND b=2 AND c=3) OR (a=1 AND b=2 AND c=3)), the optimizer lost part of the range, and so chose a query plan that was not optimal.
Our thanks to Facebook for the contribution. (Bug #102634, Bug #32523520)
While evaluating a loose index scan as a possible option for performing grouping and finding the minimum value, the cost calculation did not reflect the fact that the query looked at one group only, due to the equality predicates on the grouping attributes. This resulted in examination of additional rows since grouping is performed after reading the rows from the index.
We fix this by determining whether a query produces only one group by checking for the presence of equality predicates on grouping attributes and using these for calculating the cost. This causes the optimizer to pick loose index scan for such cases when doing so is found to be beneficial. (Bug #101838, Bug #32266286)
References: See also: Bug #18109609.
When resolving integer division, the precision of the result is taken from the dividend. When the divisor is a decimal number, it may be less than 1, which may cause the result to use more digits than the dividend. This yielded incorrect values in some cases in which the result of integer division was a decimal or float. (Bug #100259, Bug #31641064)
Added an in-memory estimate to the optimizer trace to indicate how much of a given table is buffered in the buffer pool.
Our thanks to Øystein Grøvlen for the contribution. (Bug #99993, Bug #31544522)
EXPLAINoutput for a DML statement contains the table identifier, which normally includes the database name, in the output of
SHOW WARNINGS. For some statements such as
CREATE VIEW, the database name should be omitted, which is enforced by setting the
alias_name_usedflag to true in the cached table object, but when the cached table was reused following
CREATE VIEW, the flag was not reset, which caused the database name to be omitted from the warnings following
EXPLAINfor statements run after a
CREATE VIEWwhich access the same cached table as the view.
We fix this by ensuring that the
alias_name_usedflag is always set to an appropriate value during table initialization.
Our thanks to Kaiwang Chen for the contribution. (Bug #98635, Bug #30909064)