For general information about upgrades, downgrades, platform support, etc., please visit https://dev.mysql.com/doc/relnotes/mysql/8.0/en/.
Lock handling for statements involving the grant tables was improved. (Bug #31291237, Bug #31576185)
mysql.sysreserved accounts now requires the
SYSTEM_USERprivilege. (Bug #31255458)
DROP USER, and
RENAME USERaccount-management statements, the server now performs additional security checks designed to prevent operations that (perhaps inadvertently) cause stored objects to become orphaned or that cause adoption of stored objects that are currently orphaned. Such operations now fail with an error. If you have the
SET_USER_IDprivilege, it overrides the checks and those operations produce a warning rather than an error; this enables administrators to perform the operations when they are deliberately intended. See Orphan Stored Objects.
For JSON-format log files, MySQL Enterprise Audit supports log-reading operations using the
audit_log_read()user-defined function. Previously, specifying the position at which to begin reading was possible only by passing to
audit_log_read()an argument containing a bookmark indicating the exact timestamp and event ID of a particular event. For greater flexibility, the argument now can be a start specifier that names any timestamp, to read starting from the first event that occurs on or after that timestamp. See Reading Audit Log Files.
The MySQL client library now includes a
mysql_real_connect_dns_srv()C API function that is similar to
mysql_real_connect()but uses a DNS SRV record to determine the candidate hosts for establishing a connection to a MySQL server, rather than explicit host, port, and socket arguments.
Applications that use the C API can call the new function directly. In addition, the mysql client program is modified to use DNS SRV capability; it now supports a
--dns-srv-nameoption that takes precedence over
--hostand causes the connection to be based on a DNS SRV record. See mysql_real_connect_dns_srv().
Connection establishment in other contexts is unaffected, including connections made by replicas, the
FEDERATEDstorage engine, and client programs other than mysql.
Visual Studio 16.4 is now the minimum version for MySQL compilation. (Bug #31655401)
The minimum version of the Boost library for server builds is now 1.73.0. (Bug #31309800)
WITH_TCMALLOCCMake option indicates whether to link with
-ltcmalloc. If enabled, built-in
free()routines are disabled. The default is
WITH_JEMALLOCare mutually exclusive. (Bug #31785166)
COMPRESS_DEBUG_SECTIONSCMake option indicates whether to compress the debug sections of binary executables (Linux only). Compressing executable debug sections saves space at the cost of extra CPU time during the build process. The default is
OFF. If this option is not set explicitly but the
COMPRESS_DEBUG_SECTIONSenvironment variable is set, the option takes its value from that variable. (Bug #31498296)
WITH_DEFAULT_FEATURE_SETCMake option was removed. (Bug #31122507)
On platforms that implement network namespace support (such as Linux), MySQL now enables configuring the network namespace for TCP/IP connections from client programs to the MySQL server or X Plugin:
On the server side, the
mysqlx_bind_addresssystem variables have extended syntax for specifying the network namespace to use for a given IP address or host name on which to listen for incoming connections.
For client connections, the mysql client and the mysqlxtest test suite client support a
--network-namespaceoption for specifying the network namespace.
For replication connections from replica servers to source servers, the
CHANGE MASTER TOstatement supports a
NETWORK_NAMESPACEoption for specifying the network namespace.
For replication monitoring purposes, the Performance Schema
replication_connection_configurationtable, the replica server connection metadata repository (see Replication Metadata Repositories), and the
SHOW REPLICA | SLAVE STATUSstatement have a new column that displays the applicable network namespace for connections.
For more information, including the host system prerequisites that must be satisfied to use this feature, see Network Namespace Support.
The InnoDB memcached plugin is deprecated and support for it will be removed in a future MySQL version.
INFORMATION_SCHEMA.TABLESPACEStable is unused. It is now deprecated and will be removed in a future MySQL version. Other
INFORMATION_SCHEMAtables may provide related information, as described in The INFORMATION_SCHEMA TABLESPACES Table.
MySQL Enterprise Edition now includes a
keyring_ociplugin that uses Oracle Cloud Infrastructure Vault as a back end for keyring storage. No key information is permanently stored in MySQL server local storage. All keys are stored in Oracle Cloud Infrastructure Vault, making this plugin well suited for Oracle Cloud Infrastructure MySQL customers for management of their MySQL Enterprise Edition keys. For more information, see The MySQL Keyring.
Important Change: A prepared statement is now prepared only once, when executing
PREPARE, rather than once each time it is executed. In addition, a statement inside a stored procedure is also now prepared only once, when the stored procedure is first executed. This change enhances performance of such statements, since it avoids the added cost of repeated preparation and rollback of preparation structures, the latter being the source of several bugs.
As part of this work, the manner in which dynamic parameters used in prepared statements are resolved is changed, with the resulting changes in prepared statement use cases listed here:
A parameter used in a prepared statement has its data type determined when the statement is prepared, and the type persists for each subsequent execution of the statement, unless the statement is reprepared (see PREPARE Statement, for information about when this may occur).
For a prepared statement of the form
SELECT, passing an integer value
expr2, ... FROM
tableORDER BY ?
Nfor the parameter no longer causes ordering of the results by the
Nth expression in the select list; the results are no longer ordered, as is expected with
A user variable that is read by a prepared statement now has its type determined when the statement is prepared; the type persists for each subsequent execution of the statement.
A user variable that is read by a statement within a stored procedure now has its type determined the first time the statement is executed; the type persists for all subsequent invocations of the containing stored procedure.
For parameters for which no contextual information is available to determine the parameter type, the server assumes the parameter is a character string with the default character set, not a binary string. Parameters for which this is incorrect may be placed within a
See PREPARE Statement, for the rules governing how the effectiue data types of parameters and user variables used within prepared statements are determined.
an unsigned integer constant literal
a positional parameter marker (
a user-defined variable
a local variable in a stored routine
In addition, this argument no longer accepts
NULLas a value. See the descriptions of the functions just referenced for more information. (Bug #48612, Bug #99601, Bug #100150, Bug #11756670, Bug #23599127, Bug #31119132, Bug #31365678, Bug #31393719, Bug #31592822, Bug #31810577)
filesortalgorithm now supports sorting a join on multiple tables, and not just a single table. (Bug #31310238, Bug #31559978, Bug #31563876)
When using a
RIGHT JOIN, some internal objects, were not converted to those suitable for use with a
LEFT JOINas intended. These included some lists of tables built at parse time, but which did not have their order reversed. This required maintaining code to handle instances in which a
LEFT JOINwas originally a
RIGHT JOINas special cases, and was the source of several bugs. Now the server performs any necessary reversals at parse time, so that after parsing, a
RIGHT JOINis in fact, in all respects, a
LEFT JOIN. (Bug #30887665, Bug #30964002)
References: See also: Bug #12567331, Bug #21350125.
Added support for periodic synchronization when writing to files with
SELECT INTO DUMPFILEand
SELECT INTO OUTFILEstatements. This feature can be enabled by setting the
select_into_disk_syncsystem variable to
ON; the size of the write buffer cn be set using the server system variable
select_into_buffer_size; the default buffer size is 131072 (217) bytes. An optional delay following synchronization to disk can also be set using the
select_into_disk_sync_delaysystem variable; the default behaviour is not to allow any delay (that is, a delay time of 0 milliseconds).
For more information, see the descriptions of the system variables referenced previously.
Our thanks to Facebook for this contribution to MySQL 8.0. (Bug #30284861)
MySQL now implements derived condition pushdown for eligible queries. What this means is that, for a query such as
SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i >, it is now possible in many cases to push the outer
WHEREcondition down to the derived table, in this case resulting in
SELECT * FROM (SELECT i, j FROM t1 WHERE i >. Previously, if the derived table was materialized and not merged, MySQL materialized the entire table—in this case
constant) AS dt
t1—then qualified the rows with the
When the derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer
WHEREcondition down to the derived table can reduce the number of rows that need to be processed, which should improve the query's performance.
WHEREcondition can be pushed down directly to a materialized derived table when the derived table uses no aggregate or window functions. In addition, when the derived table has a
GROUP BYand uses no window functions, the outer
WHEREcondition can be pushed down to the derived table as a
HAVINGcondition. If the derived table uses a window function and the outer
WHEREreferences columns used in the window function's
WHEREcondition can also be pushed down.
This optimization cannot be employed for a derived table that contains a
To enable derived condition pushdown, the
derived_condition_pushdownflag (added in this release) must be set to
on. This is the default setting. If this optimization is disabled by the optimizer switch setting, you can enable it for a specific query using the
DERIVED_CONDITION_PUSHDOWNoptimizer hint (also added in this release). Use the
NO_DERIVED_CONDITION_PUSHDOWNoptimizer hint to disable the optimization for a given query.
For further information and examples, see Derived Condition Pushdown Optimization. (Bug #59870, Bug #88381, Bug #11766303, Bug #27590273)
For RPM and Debian packages, client-side plugins were moved to their own client-plugins package. (Bug #31584093)
VERSIONfile in MySQL source distributions is now named
MYSQL_VERSIONdue to a naming conflict with Boost. (Bug #31466846)
For platforms on which systemd is used to run MySQL, packages no longer include legacy System V files: the mysqld_multi.server and mysql.server scripts, and the
mysqld_safe.1man pages. (Bug #31450888)
SHOW PROCESSLISTstatement provides process information by collecting thread data from all active threads. However, because the implementation iterates across active threads from within the thread manager while holding a global mutex, it has negative performance consequences, particularly on busy systems.
SHOW PROCESSLISTimplementation is now available based on the new Performance Schema
processlisttable. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex:
To enable the alternative implementation, enable the
The alternative implementation of
SHOW PROCESSLISTalso applies to the mysqladmin processlist command.
The alternative implementation does not apply to the
PROCESSLISTtable or the
COM_PROCESS_INFOcommand of the MySQL client/server protocol.
To ensure that the default and alternative implementations yield the same information, certain configuration requirements must be met; see The processlist Table.
An SQL interface to the most recent events written to the MySQL server error log is now available by means of queries on the new Performance Schema
error_logtable. This table has a fixed size, with old events automatically discarded as necessary to make room for new ones. The table is populated if error log configuration includes a log sink component that supports this capability (currently the traditional-format
log_sink_jsonsinks). Several new status variables provide information about
error_logtable operation. See The error_log Table.
These changes were made for the LDAP authentication plugins:
For the SASL LDAP authentication plugin, the
SCRAM-SHA-1authentication method is not supported on On SLES 12 and 15 and EL6 systems. The default method on those systems is now
If the LDAP host is not set, the LDAP connection pool will not be initialized, which enables successful authentication plugin installation in cases when previously it would fail. (This might be the case when a site installs a plugin first, then configures it later.)
If an LDAP connection parameter is changed at runtime, the LDAP connection pool is reinitialized for the first subsequent authentication attempt.
If the LDAP server is restarted, existing connections in the connection pool become invalid. The LDAP authentication plugin detects this case and reinitializes the connection pool and (for the SASL LDAP plugin) the SASL challenge is resent.
(Bug #31664270, Bug #31219323)
The parser now supports parenthesized query expressions using this syntax:
( query_expression ) [order_by_clause] [limit_clause] [into_clause]
Other variations are possible; see Parenthesized Query Expressions (Bug #30592703)
It is now possible to cast values of other types to
YEAR, using either the
CAST()function or the
CONVERT()function. These functions now support
YEARvalues of one or two digits in the range 0-99, and four-digit values in the range 1901-2155. Integer 0 is converted to Year 0; a string consisting of one or more zeroes (following possible truncation) is converted to the year 2000. Casting adds 2000 to values in the range 1-69 inclusive, and 1900 to values in the range 70-99 inclusive.
Strings beginning with one, two, or four digits followed by at least one non-digit character (and possibly other digit or non-digit characters) are truncated prior to conversion to
YEAR; in such cases, the server emits a truncation warning. Floating-point values are rounded prior to conversion;
CAST(1944.5 AS YEAR)returns 1945 due to rounding, and
CAST("1944.5" AS YEAR)returns 1944 (with a warning) due to truncation.
TIMESTAMPare cast to the
YEARportion of the value. A
TIMEvalue is cast to the current year. Not specifying the value to be cast as a
TIMEvalue may yield a different result from what is expected;
CAST("13:47" AS YEAR)returns 2013 due to truncation of the string value, and
CAST(TIME "13:47" AS YEAR)returns 2020 as of the year of this release.
YEARis not supported. A cast of an incompatible type or an out-of-range or illegal value returns
YEARcan also be used as the return type for the
JSON_VALUE()function. This function supports four-digit years only, and otherwise follows the same rules as apply to
CONVERT()when performing casts to
For more information, see the description of the
When selecting a
TIMESTAMPcolumn value, it is now possible to convert it from the system time zone to a UTC
DATETIMEwhen retrieving it, using the
AT TIME ZONEoperator which is implemented for the
CAST()function in this release.
The syntax is
CAST(, where the
valueAT TIME ZONE
TIMESTAMP, and the
specifieris one of
INTERVALis optional with the first form of the specifier, and cannot be used with
DATETIMEvalue returned by the cast can optionally be specified up to 6 decimal places.
Values that were inserted into the table using a timezone offset are also supported.
AT TIME ZONEcannot be used with
CONVERT(), or in any other context other than as part of a
CAST()function call. The
ARRAYkeyword and creation of multi-valued indexes are also not supported when using
AT TIME ZONE.
A brief example is shown here:
mysql> SELECT @@system_time_zone; +--------------------+ | @@system_time_zone | +--------------------+ | EDT | +--------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE ex (ts TIMESTAMP); Query OK, 0 rows affected (0.81 sec) mysql> INSERT INTO ex VALUES > ROW(CURRENT_TIMESTAMP), > ROW('2020-07-31 21:44:30-08:00'); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> TABLE ex; +---------------------+ | ts | +---------------------+ | 2020-07-28 21:39:31 | | 2020-08-01 01:44:30 | +---------------------+ 2 rows in set (0.00 sec) mysql> SELECT ts, CAST(ts AT TIME ZONE 'UTC' AS DATETIME) AS ut FROM ex; +---------------------+---------------------+ | ts | ut | +---------------------+---------------------+ | 2020-07-28 21:39:31 | 2020-07-29 01:39:31 | | 2020-08-01 01:44:30 | 2020-08-01 05:44:30 | +---------------------+---------------------+ 2 rows in set (0.00 sec)
For more information and examples, see the description of the
CAST()function in the MySQL Manual.
In specific conditions, terminating an X Protocol connection could cause MySQL Server to stop unexpectedly. (Bug #31671503)
LOCK TABLESprivilege checking for views was improved. (Bug #31304432)
You can use MySQL Server's new asynchronous connection failover mechanism to automatically establish an asynchronous (source to replica) replication connection to a new source after the existing connection from a replica to its source fails. The connection fails over if the replication I/O thread stops due to the source stopping or due to a network failure. The asynchronous connection failover mechanism can be used to keep a replica synchronized with multiple MySQL servers or groups of servers that share data, including asynchronous replication from servers where Group Replication is in use. To activate asynchronous connection failover for a replication channel set
CHANGE MASTER TOstatement for the channel, and set up a source list for the channel using the
innodb_extend_and_initializevariable permits configuring how
InnoDBallocates space to file-per-table and general tablespaces on Linux. By default, when an operation requires additional space in a tablespace,
InnoDBallocates pages to the tablespace and physically writes NULLs to those pages. This behavior affects performance if new pages are allocated frequently. As of MySQL 8.0.22, you can disable
innodb_extend_and_initializeon Linux systems to avoid physically writing NULLs to newly allocated tablespace pages. When
innodb_extend_and_initializeis disabled, space is allocated using
posix_fallocate()calls, which reserve space without physically writing NULLs.
posix_fallocate()operation is not atomic, which makes it possible for a failure to occur between allocating space to a tablespace file and updating the file metadata. Such a failure can leave newly allocated pages in an uninitialized state, resulting in a failure when
InnoDBattempts to access those pages. To prevent this scenario,
InnoDBwrites a redo log record before allocating a new tablespace page. If a page allocation operation is interrupted, the operation is replayed from the redo log record during recovery.
To permit concurrent DML and DDL operations on MySQL grant tables, read operations that previously acquired row locks on MySQL grant tables are now executed as non-locking reads. The operations that are now performed as non-locking reads on MySQL grant tables include:
DML operations that read data from grant tables (through join lists or subqueries) but do not modify them, using any transaction isolation level.
Statements that no longer acquire row locks when reading data from grant tables report a warning if executed while using statement-based replication.
When using -
binlog_format=mixed, DML operations that read data from grant tables are now written to the binary log as row events to make the operations safe for mixed-mode replication.
SELECT ... FOR SHAREstatements that read data from grant tables now report a warning. With the
FOR SHAREclause, read locks are not supported on grant tables.
DML operations that read data from grant tables and are executed using the
SERIALIZABLEisolation level now report a warning. Read locks that would normally be acquired when using the
SERIALIZABLEisolation level are not supported on grant tables.
From MySQL 8.0.22, the
group_replication_ip_whitelistsystem variable is deprecated, and the system variable
group_replication_ip_allowlisthas been added to replace it. The system variable works in the same way as before, only the terminology has changed.
For both system variables, the default value is
AUTOMATIC. If either one of the system variables has been set to a user-defined value and the other has not, the changed value is used. If both of the system variables have been set to a user-defined value, the value of
From MySQL 8.0.22, the statements
SHOW SLAVE STATUS,
SHOW SLAVE HOSTSand
RESET SLAVEare deprecated. The following aliases should be used instead:
SHOW SLAVE STATUSuse
SHOW REPLICA STATUS
SHOW SLAVE HOSTSuse
The statements work in the same way as before, only the terminology used for each statement and its output has changed.
New status variables have been added as aliases for the related status variables. Both the old and new versions of the statements update both the old and new versions of these status variables:
Com_slave_startis equivalent to
Com_slave_stopis equivalent to
Com_show_slave_statusis equivalent to
Com_show_slave_hostsis equivalent to
ALTER DATABASEstatement now supports a
READ ONLYoption that controls whether to permit modification of a database and objects within it. This option is useful for database migration because a database for which
READ ONLYis enabled can be migrated to another MySQL instance without concern that the database might be changed during the operation. See ALTER DATABASE Statement.
InnoDB: Code related to transaction support for histogram sampling was removed, including related assertion code that caused test failures. Transaction support is not required for histogram sampling. (Bug #31787736)
InnoDB: Encryption information was not set for redo log archive log writer thread write operations. (Bug #31690196)
TTASEventMutex::exitfunction was optimized for ARM64. Thanks to Krunal Bauskar for the contribution. (Bug #31589019, Bug #100132)
InnoDBfailed to compile with the
DISABLE_PSI_RWLOCKCMake option enabled. (Bug #31578289)
InnoDB: The transaction isolation level, which is set to
READ UNCOMMITTEDfor histogram sampling to avoid unnecessary lookups of old record versions, was not reset after the sampling operation completed. (Bug #31564407)
InnoDB: A query that updated the clustered index of an internal temporary table returned an incorrect result. The modified pages of the clustered index were not added to the flush list resulting in lost changes when the modified pages were evicted from the buffer pool. (Bug #31560679)
References: This issue is a regression of: Bug #29207450.
InnoDB: A build dependency on the Boost library defined for the TempTable storage engine was removed. (Bug #31505048)
InnoDB: A workaround was implemented to handle a Clang compiler issue in 32-bit builds that causes the
ATOMIC_LLONG_LOCK_FREEvalue to be defined as “sometimes lock-free” while
__atomic_always_lock_freereturns true for the same type on the same platform. (Bug #31504609)
REDUNDANTrow format table created in an earlier version of MySQL, where the row format was not defined explicitly, permitted the addition of an index that exceeded the
REDUNDANTrow format index column size limit. (Bug #31479542, Bug #99791)
InnoDB: A DML operation on a column defined with a multi-valued index caused a failure. (Bug #31479282)
InnoDB: A failure occurred during master key rotation. An undo tablespace in-memory object was freed prematurely. (Bug #31467626)
InnoDB: Unused physical read ahead code was removed from the parallel read interface. (Bug #31429385)
InnoDB: A master key rotation operation failed to skip an undo tablespace that was already truncated, which lead to an assertion failure when shutting down the server. (Bug #31400195)
InnoDB: After importing a tablespace for a page-compressed table, pages were no longer compressed, and
INFORMATION_SCHEMA.INNODB_TABLESPACESmetadata incorrectly indicated that pages were compressed. The table's compression information was unavailable during the import operation. (Bug #31396947)
InnoDB: A rollback and update operation after performing an instant DDL operation raised an assertion. (Bug #31391126)
InnoDB: The log system (
log_sys) sharded read-write lock caused a performance regression in CPU-bound workloads. (Bug #31389135)
InnoDB: Compiling with the
UNIV_ENCRYPT_DEBUGoption enabled caused compilation errors. (Bug #31369540)
InnoDB: DDL operations on a partitioned table could cause a failure.
TABLE_SHAREand table instance objects were opened for all partitions unnecessarily. (Bug #31365127)
InnoDB: After changing a
VARCHARcolumn collation from
utf8mb4_binin an in-place
ALTER TABLEoperation and adding an index on the same column, a case-sensitive query on the
VARCHARcolumn returned an incorrect result. The
VARCHARcolumn collation was changed in the data dictionary but not in the in-memory table object. Consequently, the index created on the
VARCHARcolumn used stale column information causing comparisons to use the previously defined collation. (Bug #31361838)
ALTER TABLE ... IMPORT TABLESPACEoperation on a large encrypted and compressed table failed with a Page decompress failed after reading from disk error. The decryption operation did not use the encryption block size used during encryption. Also, the encryption process did not consider compressed length, while the decryption process decrypts data by compressed length only. (Bug #31313533)
InnoDB: A failure occurred during a concurrent update operation. The failure was due to an invalid previous record value. (Bug #31205266, Bug #99286)
InnoDB: Upgrade from MySQL 5.7 to MySQL 8.0 failed on an instance with a table created in a general tablespace and defined with a
FULLTEXTindex. The correct data dictionary space ID for table could not determined. (Bug #31154128, Bug #99211)
InnoDB: The function used to process the
SHOW ENGINE INNODB MUTEXstatement was insufficiently isolated from other threads adding new mutexes concurrently. (Bug #31105262)
InnoDB: Failure to call a buffer pool page I/O completion routine resulted in orphan buffer pool I/O write pages. (Bug #31073853)
InnoDB: Numerous system temporary table pages at the tail of the buffer pool flush list caused a performance degradation. The
flush_list_mutexwas held while the flush list scan traversed over system temporary table pages. The flush list scan now excludes system temporary table pages. (Bug #31060470, Bug #98974)
InnoDB: The buffer control block structure (
buf_block_t) was freed while reducing the size of the buffer pool, causing an assertion failure. The fix for this bug also backports important aspects of the fix for Bug #20735882 / Bug #76343, and replaces the internal
buf_block_is_uncompressed()function with the
buf_block_is_uncompressed()function returned false in too many cases, affecting OLTP query throughput. (Bug #31036301, Bug #31389823)
InnoDB: Parallel read threads failed to respond to an explicit transaction interruption. (Bug #31016076)
InnoDB: In session started with
START TRANSACTION WITH CONSISTENT SNAPSHOT, a range query returned a truncated result. The end range flag was not reset at the beginning of the index read resulting in an aborted read and missing rows. (Bug #30950714, Bug #98642)
References: This issue is a regression of: Bug #23481444.
InnoDB: A full-text phrase search raised an assertion failure. Thanks to TXSQL (Tencent MySQL) for the contribution. (Bug #30933728, Bug #31228694)
References: This issue is a regression of: Bug #22709692.
InnoDB: A failure occurred while attempting to initialize the system tablespace on a raw disk partition. Additionally, a
INPLACEDDL operation on the raw-disk partition tablespace failed with an error instead of switching to the
COPYalgorithm. (Bug #30867065, Bug #98091)
InnoDB: LOB purge code (
lob::purge()) did not properly handle latches taken during B-tree mini-transaction (
btr_mtr) commit and restore operations, which could lead to conflicts between B-tree and LOB mini-transactions. (Bug #30620011)
InnoDB: A long running statistics calculation operation on a large table blocked other operations requiring access to the table's statistics, causing those operations to fail. A new statistics calculation mutex was introduced, which permits concurrent access table statistics. Thanks to Kamil Holubicki for the contribution. (Bug #30607708)
InnoDB: Two connections attempted to use the same transaction handler object resulting in a stalled query. (Bug #30594501)
InnoDB: Shutting down the server with
innodb_fast_shutdownsetting greater than 0 raised an assertion failure. The assertion was caused by the presence of recovered transactions that were not yet rolled back. Assertion code was revised to ignore recovered transactions during a fast shutdown. Messages are now written to the error log when recovered transactions that are not rolled back are left behind by a fast shutdown. Slow shutdown now waits for recovered transactions to be rolled back. Various other shutdown logic improvements were implemented. (Bug #30226841)
InnoDB: Dedicated log writer threads, introduced in MySQL 8.0.11, caused a CPU-bound performance regression on low-concurrency systems. To address this issue, the new
innodb_log_writer_threadsvariable permits disabling dedicated log writer threads so that redo log records are written from the log buffer to the system buffers and flushed from the system buffers to the redo log files by each user thread, which is the behavior prior to the introduction of dedicated log writer threads. Other redo logging optimizations were implemented, including the removal of an unnecessary log closer thread that wasted CPU time, and optimizations to remedy too-aggressive checkpoint activity and excessive flush calls. The issues addressed by this fix also manifested in a
LOAD DATAperformance regression. (Bug #30088404, Bug #30003849)
InnoDB: Restarting the server with an incorrect
lower_case_table_namessetting after a failure caused a hang condition. At startup,
InnoDBwaited for a transaction to roll back, but the rollback thread was not initiated due to a startup validation failure caused by the incorrect
lower_case_table_namessetting. (Bug #29833945)
Replication: X Plugin could stop unexpectedly if a Group Replication notification was issued after a new X Protocol connection was made but before the session was created. The dispatcher thread that handles Group Replication notifications now checks that the session pointer is valid. (Bug #31742798)
Replication: Group Replication's handling of memory allocation issues when adding transaction write sets has been improved. (Bug #31586243)
Replication: While a remote cloning procedure was taking place on a joining member during distributed recovery, Group Replication considered the pre-cloning
gtid_executedvalue of the joining member when identifying the common set of transactions that had been applied on all members. This meant that garbage collection for applied transactions from the group's set of certification information (shown as the
count_transactions_rows_validatingfield in the Performance Schema table
replication_group_member_stats) did not take place during the remote cloning procedure. If the remote cloning procedure took a long time, the certification information could therefore get too large to transmit to the joining member when it restarted after the remote cloning procedure, in which case an error was raised and the member was not able to join the group.
To avoid this issue, Group Replication now considers only group members with
ONLINEstatus when identifying the common set of transactions that have been applied on all members. When a joining member enters
ONLINEstate after distributed recovery, its certification information is updated with the certification information from the donor at the time when the member joined, and garbage collection takes place for this on future rounds.
As a workaround for this issue in earlier releases, after the remote cloning operation completes, wait two minutes to allow a round of garbage collection to take place to reduce the size of the group's certification information. Then issue the following statement on the joining member, so that it stops trying to apply the previous set of certification information:
RESET SLAVE FOR CHANNEL group_replication_recovery;
(Bug #31446381, Bug #99778)
Replication: It was possible for a group member that left the group due to a communication error to reconnect between auto-rejoin attempts while the auto-rejoin procedure was still ongoing, which left Group Replication unable to function on the member. Group Replication's error management and member status handling has now been corrected to prevent this situation. (Bug #31401797)
Replication: When a replication source server shuts down and restarts, its
MEMORYtables become empty. To replicate this effect to replicas, the first time that the source uses a given
MEMORYtable after startup, it logs an event that notifies replicas that the table must be emptied by writing a statement to the binary log to that effect. Previously, this was a
DELETEstatement, but it is now a
TRUNCATE TABLEstatement. A replica server also writes this statement to its own binary log when it shuts down and restarts. The statement is always logged in statement format, even if the binary logging format is set to
ROW, and it is written even if
super_read_onlymode is set on the server. (Bug #29848785, Bug #95496)
Replication: When the system variable
session_track_gtidswas set to
OWN_GTIDon a multithreaded replica, the replica’s performance would degrade over time and begin to lag behind the master. The cause was the buildup of the GTIDs recorded by the replica’s worker threads at each transaction commit, which increased the time taken by the worker threads to insert new ones. Session state tracking is now disabled for worker threads on a multithreaded replica. Thanks to Facebook for the contribution. (Bug #29049207, Bug #92964)
Replication: When using row-based replication, the replica was allowed to use an invisible index when searching for rows to synchronize. (Bug #96148, Bug #30072179)
Microsoft Windows: On Windows, build targets could fail if the build was on a file system root, such as
R:/. (Bug #31315467)
JSON_OBJECT()did not always perform proper checking for
NULLvalues. (Bug #31393934)
WITH_SYSTEMD_DEBUGCMake option, if enabled, produces additional systemd debugging information, for platforms on which systemd is used to run MySQL. The default is
OFF. (Bug #31788834)
For RPM and Debian packages, client-side plugins were moved from the server package to the client package in MySQL 8.0.21. This could cause failures relating to LDAP authentication plugins when upgrading from 5.7 packages to 8.0 packages. Packaging adjustments were made to avoid this problem. (Bug #31782612)
References: This issue is a regression of: Bug #31123564, Bug #31336340.
The timestamp written for the
tskey by the
log_sink_jsonJSON-format error log sink did not have the same value as other timestamps in the same log message. (Bug #31749103)
Kerberos authentication for the SASL LDAP authentication plugin incorrectly handled failure to acquire a ticket-granting ticket. (Bug #31727195)
For some third-party libraries, enabling link-time optimization caused build failures. (Bug #31701553, Bug #100410)
Printing an excessively long diagnostic message could cause the server to exit unexpectedly. (Bug #31686926)
A page-compressed table was cloned as an uncompressed table. The associated tablespace object, which includes a compression flag, was not initialized prior to the cloning operation. (Bug #31677990, Bug #100243)
Certain cases of successful LDAP authentication could cause the server to hang. (Bug #31661437)
During transformation of a grouped query into a derived table, when the
WHEREclause and the
HAVINGclause became part of the derived table, the condition count was not updated for the derived table. This resulted in reduced memory allocation while creating keys for
refaccess. (Bug #31661309)
When a value was compared using
LIKEwith a table column not defined as one of the MySQL string types, the server sometimes did not raise the expected error. (Bug #31659015)
acquire_related()service function returned the default service in some cases when it should have returned an error. (Bug #31655906)
In bootstrapping mode, certain multiple-statement transactions could cause unexpected server behavior. (Bug #31650096)
A remote cloning operation checked for the availability of a plugin on the recipient that was removed from the donor instance previously. References to the uninstalled plugin had not been released. Error reporting issues related to plugin mismatches and availability were also addressed. (Bug #31639732, Bug #100244)
MySQL Server Docker images did not expose the Group Replication recommended port (33061). (Bug #31627536)
In debug builds, the server attempted to evaluate subqueries while creating a view. (Bug #31590301)
References: This issue is a regression of: Bug #25466100.
A condition using
RAND()was not pushed down even in cases where it was safe to do so, that is when no windowing function or
GROUP BYis in use. (Bug #31587575)
While pushing conditions down to a derived table, a constant condition such as
WHERE TRUEwas pushed down to the first table in the derived table, which is not necessary as the condition has nothing to do with the derived table. MySQL now avoids pushing constant conditions down to derived tables in such cases.
In addition used tables are now updated for the condition that needs to be pushed down to the derived table, following code inspection revealing that this was not done after replacing the columns in the condition with the derived table expressions. (Bug #31587493)
A query using
WHEREcould sometimes trigger an assertion in the range optimizer. (Bug #31586906)
column> (... IN (SELECT ...))
References: This issue is a regression of: Bug #30473261.
It was possible for
ANALYZE TABLEto fail with Duplicate key error if a row was inserted in the interval between the check for the existence of the row and the execution of the insert, and the statistics table was updated concurrently.
ANALYZE TABLEnow ignores the error in this situation. (Bug #31582758)
The range optimizer does not use the correct lock type after cloning the handler needed to perform merged scans, and instead used a read lock unconditionally. This resulted in various different side effects for different scenarios.
For example, a
FOR UPDATErequests a write lock, but after cloning the handler for an index merge scan, the range optimizer requested a read lock which resulted in a mismatch. Similarly, for data dictionary tables, the lock type was set to
LOCK_NONEdue to the special handling required for such tables.
To prevent this problem from occurring, we now ensure that the original lock type of the handler is always used in the cloned handler as well. (Bug #31582383)
In some cases, a query using an
ANYsubquery gave an incorrect result when the
subquery_to_derived optimizerswitch was enabled. (Bug #31566339)
FALSE ANDwas simplified as
FALSE, temporary table resources allocated for the
conditionwere not always released afterwards. (Bug #31565009)
A value equal to
ULLONG_MAXcould be inserted into a
BIT(64)column, but not retrieved. (Bug #31564742, Bug #100053)
While removing an unused window definition, a subquery that was part of an
ORDER BYwas not removed. The optimizer then tried to optimize the subquery without locking the tables. Now, when removing an unused window definition, the server cleans up any subqueries present as part of the definition. (Bug #31518806)
References: This issue is a regression of: Bug #27062031.
A coding problem introduced in MySQL 8.0.20 could cause client applications to exit unexpectedly during shutdown. (Bug #31515752)
References: This issue is a regression of: Bug #27045306.
Added a missing error code translation from ICU U_REGEX_NUMBER_TOO_BIG to MySQL
ER_REGEX_NUMBER_TOO_BIG. (Bug #31514995)
filesortoperations could fail to remove duplicates for queries that used
DISTINCT. (Bug #31498664, Bug #99900)
DYNAMIC_STRINGclass formerly allocated memory in a linear fashion, that is, by a predetermined number of bytes. The class has been revised such that it now allocates memory exponentially, which should make operations such as repeated string appends more efficient. (Bug #31491799)
LOCK_mutexmishandling could result in a memory leak. (Bug #31491146)
A newly added collation was not added and could cause an unexpected exit on shutdown. (Bug #31470422)
On Windows, file name reuse by the
GetTempFileName()function could cause an assertion to be raised. (Bug #31468590)
LATERALsubquery was incorrectly converted into an antijoin. (Bug #31465717)
NATURAL JOINevaluation could inadvertently match hidden virtual columns created by functional indexes. (Bug #31463511, Bug #99807)
Sort keys for string hash join keys using more than 1024 bytes were not handled correctly by the server. (Bug #31437753)
The server attempted to delete from a view whose definition included
HAVINGclause was constant and evaluated as true even though a view with
HAVINGas part of its definition should not be updatable. (Bug #31429865)
Privilege requirements were checked incorrectly for the
INFORMATION_SCHEMA.USER_ATTRIBUTEStable. (Bug #31427410)
When the internal function
replace_index_subquery()failed, the server still attempted to create iterators for the affected subquery. Now the function raises a clear error instead. (Bug #31427072)
A query using
WHERE NOT EXISTS (SELECTwas not handled correctly. (Bug #31425664)
In some cases,
key_hinthandling was improperly applied to derived and internal temporary tables. (Bug #31424455)
Re-execution of prepared
INSERTstatements could fail for inserts through a view. (Bug #31417951)
JSONscalar evaluation could enter an infinite loop. (Bug #31406724)
mysql.usertable rows could be affected incorrectly by partial revokes. (Bug #31405985)
Improper window function initialization could cause a server exit. (Bug #31389573, Bug #31437834)
Sensitive LDAP authentication plugin system variables now display as asterisks when retrieved in SQL statements. (Bug #31388444, Bug #31391864)
mysql-test-run.pl tests under no-threads connection handling failed with ASAN builds due to improper resource group initialization. This has been fixed. Thanks to Xiaoyu Wang, Tencent Technology for the contribution. (Bug #31378900, Bug #99609)
authentication_ldap_simpleauthentication plugin with SSL could cause a segmentation fault during shutdown. (Bug #31364927)
Killing a query could raise spurious assertions in the hash join iterator. (Bug #31361354)
In some cases, an outer reference that was not
LATERALwas not marked as read-only as expected. (Bug #31359965)
A failure occurred when upgrading from MySQL 5.7 to MySQL 8.0 due to invalid references to orphaned events (events for which a database no longer exists). The server now fails with an appropriate error messages when orphaned events are encountered during upgrade. Error messages for orphaned stored routines were also revised. (Bug #31335554)
create_admin_listener_threadsystem variable could cause a server exit during startup. (Bug #31335279)
ALTER TABLEto add an expression default to a column, the first insert inserted a value as if the expression had been evaluated at alter time and not insert time. (Bug #31330789, Bug #99513)
The LDAP authentication plugins did not properly compare the user-supplied authentication method against the permitted methods. (Bug #31320532)
Certain views could cause a following
USEstatement to result in an unexpected server exit. (Bug #31311312)
When a filesort sorted a buffer and
LIMITwas active, it first sorted all rows and then discarded those that did not fit within the limit, which required sorting many rows that were certain to be discarded later. Now the optimizer sorts only the rows actually needed. Internal testing shows that this change can speed up the sort phase for a simple string sorting benchmark (as measured by
EXPLAIN ANALYZE) by up to 15%. (Bug #31303537)
A dynamic range scan runs the range optimizer for each row fetched from the first table in a join to determine whether a range scan can be picked for the second table using the value available from that row. If the row contains no usable indexes, a table scan may be chosen instead. For the query giving rise to this issue, a table scan is chosen once, followed by a range scan on a non-covering index, and the dynamic range iterator has two read sets which are used for both these cases. One of these, used for the table scan, includes the base columns of generated columns required for processing the query; the other read set does not include the base columns in the read set used for range scans. This is because, for covering indexes, the read set should not include base columns to avoid adding unneeded columns by hash join or batched key access. The issue arose because the second read set was also used for a non-covering index, which resulted in an assert.
To prevent this from happening, when initializing a table read set in the dynamic range iterator, we now make sure that it includes the base columns when the range optimizer picks a non-covering index. (Bug #31280526)
References: This issue is a regression of: Bug #30417361.
It was possible to insert an out-of-range value for a
TIMESTAMPif it included a timezone offset. (Bug #31239157)
keyring_hashicorpkeyring plugin did not limit the size of keys for key operations. (Bug #31205715)
-DWITH_ZSTD=systemfailed for older versions of the
zstdlibrary. CMake now checks the
zstdversion and requires at least 1.0.0 for compilation, 1.2.0 to run compression checks. (Bug #31174920, Bug #99241)
In some cases, a
SELECTthat obtained status variable information from Performance Schema tables and that included a sort by a column containing temporal values was not handled correctly. (Bug #31168097)
In some cases,
TRUNCATE()did not return the data type of their first arguments as expected. This fix insures that return types from these functions follow these rules, where the first argument is of the type shown:
For any integer type, the return type is
For any floating-point type or any non-numeric type, the return type is
DECIMAL, the return type is also
The type attributes for the return value are also copied from the first argument, except in the case of
DECIMAL, when the second argument is a constant value.
When the desired number of decimal places is less than the scale of the argument, the scale and the precision of the result are adjusted accordingly. In addition, for the
ROUND()function, the precision is extended by one place to accomodate rounding that increases the number of significant digits. If the second argument is negative, the return type is adjusted such that its scale is 0, with a corresponding precision.
For more information, see the description of the
ROUND()function. (Bug #31128028)
SELECT ... FOR SHAREstatement now only requires the
SELECTprivilege. Previously, the
SELECTprivilege was required with at least one of the
LOCK TABLES, or
UPDATEprivileges. (Bug #31096384, Bug #99101)
A semijoin strategy was chosen for the join of a correlated subquery having a
LIMITclause and requiring a row other than the first, which caused the
LIMITclause to be ignored and invalid rows to be returned. Now, when
LIMITused with this type of join specifies a row other than the first row, or more than one row, the semijoin strategy is no longer employed. (Bug #31096309)
After the fix for Bug #81009, privilege checks for truncating Performance Schema tables were too restrictive when
super_read_onlywere enabled, causing truncation to fail even for users with appropriate table privileges. (Bug #31080309, Bug #99072)
References: This issue is a regression of: Bug #81009.
ORDER BYdid not work as expected for queries with
ROLLUPin which window functions were also used. (Bug #31073133)
INSERTstatements were not handled correctly. (Bug #31072198)
Date interval calculations checked for overflow but not underflow. Now they check for both. (Bug #31054071)
Regular expression functions such as
REGEXP_LIKE()yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #31031886, Bug #98951, Bug #31031888, Bug #98950)
If an XA prepared transaction rollback XID was incorrectly formatted, the transaction remained in recovered state for
XA ROLLBACKstatements (or raised an assertion for debug builds) rather that reporting an error. (Bug #31030205)
Database-level privileges inherited through a role were not handled properly for database names that contained wildcard characters. (Bug #31013538, Bug #98876)
--localoption was given, mysqlimport mishandled the
mysql_options()so that it had no effect. (Bug #31001550)
Certain prepared statements could cause an unexpected server exit. (Bug #30943963)
MyISAMtables could cause table size to increase and query performance to decrease.
MyISAMtables could cause the
Table is already up to datestatus produced by a previous
OPTIMIZE TABLEto be lost. (Bug #30869674, Bug #98511, Bug #29755517)
mysqlpump object validation included objects in excluded databases. (Bug #30819012)
TIMESTAMPvalue having a timezone offset which also had a zero for the month, day, or both, led to an assert. Such a value should be and is now rejected, regardless of the
sql_modesetting. (Bug #30786762)
References: See also: Bug #31239157.
Privileges granted using roles could be mishandled at the column-privilege level. (Bug #30660403, Bug #97971)
Comparison of a
NULLin some cases raised an assertion. (Bug #30324587)
References: This issue is a regression of: Bug #25949639.
LDAP authentication plugins enforced CA verification incorrectly, which could result in use of an incorrect CA. (Bug #30220357)
ORDER BYqueries were not executed correctly when
max_sort_lengthwere set to values which caused the internal limit on the maximum number of keys allowed per sort buffer to be set to 0. (Bug #30175483)
A large number of nested arguments in full-text search query caused an error. (Bug #29929684)
A potential misreporting of memory use by the Performance Schema has been corrected. (Bug #29912403)
explicit_defaults_for_timestampwas disabled and a
NULLwas inserted into a generated column declared as
NOT NULL, the server would attempt to convert the inserted value to
CURRENT_TIMESTAMP. Such an insertion is now rejected with ER_BAD_NULL_ERROR. (Bug #29449518)
SET_VARhint did not accept a floating point value specified as a system variable setting. (Bug #29349748)
NULLwas used as the format argument to
STR_TO_DATE(), irrelevant warnings were printed. Now, when
NULLis passed to it, the function returns
NULL. (Bug #27265863)
In some cases, incorrect use of
IS NULLgenerated multiple warnings about invalid arguments. (Bug #27264652)
ORDER BYcolumn that referred to a
SELECTlist column from a derived table was not performed correctly when executing certain prepared statements. (Bug #26808862)
EXPLAINon a multi-table
UPDATEstatement in which a generated column was referenced in a condition, the output always showed the table containing this column as being updated, whether the table was actually updated or not. (Bug #22671310)
An assertion could be raised when the SQL layer passed incorrect information to
InnoDBabout the type of operation to be performed on a temporary table. (Bug #22503696)
This construct works for base tables to insert a row using all default values but failed for views:
INSERT INTO name () VALUES ();
(Bug #15988466, Bug #67863)
In some cases, the server issued an error when an invisible index was used in an index hint even when the
use_invisible_indexesoptimizer switch was not set to
OFF. (Bug #100024, Bug #31550839)
When range values specified in a predicate are not compatible with the data type of the column with which the values are compared, the range optimizer rounds off the range values and assigns certain flags so that it does not exclude rows that qualify for the range because of rounding. In the specific query that triggered the reported issue, a column named
INTwas tested using
id NOT IN (-0.1, 0.1), and the values being tested are rounded to integers, with the predicate thus being treated as
NOT IN (0,0). The optimizer then treats this as the intervals
id < 0and
0 < id < 0, but in this case it also set a flag to a value that indicated that reads should begin following rows containing 0 for the value to be compared. Now in such cases, the flag is set in such a way that the values which have been rounded are treated correctly. (Bug #98826, Bug #30988735)
References: This issue is a regression of: Bug #80244, Bug #22661012.
For a view based on a join having an updatable part and one that was not, the error message generated when attempting to update a column of this view that was not updatable referenced the source table or view instead of the view actually named in the offending
UPDATEstatement. (Bug #80655, Bug #22891840)