Previously, for a user to access definitions of routines the user did not define, the user was required to have the global
SELECTprivilege, which is very broad. The new
SHOW_ROUTINEprivilege may be granted instead as a privilege with a more restricted scope that permits access to routine definitions. (That is, an administrator can rescind global
SELECTfrom users that do not otherwise require it and grant
SHOW_ROUTINEinstead.) This enables an account to back up stored routines without requiring a broad privilege.
SHOW_ROUTINEprovides access to:
Solaris: Clang and GCC now can be used for compiling MySQL on Solaris, although both are experimental and cannot currently be used for production code. (Bug #30562248)
On EL7 and EL8, CMake configuration was adjusted to look for GCC 9 before GCC 8. Because
libmysqlclientships with MySQL distributions, client applications built against
libmysqlclienton those platforms are affected and may need to be recompiled. (Bug #30722756)
On Windows, the CMake compiler-version check for Visual Studio was updated to indicate that Visual Studio 2019 is the currently supported version. (The version check can be bypassed by running CMake with
-DFORCE_UNSUPPORTED_COMPILER=1.) (Bug #30688403)
JSON: Previously, it was possible to specify
ON ERRORclauses in either order when invoking the
JSON_TABLE()function. This runs counter to the SQL standard, which stipulates that when
ON EMPTYis specified, it must always come before any
ON ERRORclause. For this reason, specifying
ON EMPTYis now deprecated, and trying to do so causes the server to issue a warning. Support for the nonstandard syntax will be removed in a future version of MySQL.
max_length_for_sort_datasystem variable is now deprecated due to optimizer changes that make it obsolete and of no effect.
References: See also: Bug #30473261.
The use of
VALUES()to access new row values in
INSERT ... ON DUPLICATE KEY UPDATEstatements is now deprecated, and is subject to removal in a future MySQL release. Instead, you should use aliases for the new row and its columns as implemented in MySQL 8.0.19 and later.
For example, the statement shown here uses
VALUES()to access new row values:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Henceforth, you should instead use a statement similar to the following, which uses an alias for the new row:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new ON DUPLICATE KEY UPDATE c = new.a+new.b;
Alternatively, you can employ aliases for both the new row and each of its columns, as shown here:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p) ON DUPLICATE KEY UPDATE c = m+n;
For more information and examples, see INSERT ... ON DUPLICATE KEY UPDATE Statement.
rapidjsonlibrary included with MySQL has been upgraded to the GitHub snapshot of 16 January 2020. A fix for a compiler error encountered when building from the snapshot on Mac OS X has been added. (Bug #30898701)
SIGHUPsignal to the server no longer causes it to write a status report to the error log. Other actions performed by the server in response to
SIGHUPcontinue to be done. See Unix Signal Handling in MySQL. (Bug #30578923)
The JSON-format error log writer now includes a
ts(timestamp) in log messages. The value is an integer indicating milliseconds since the epoch (
'1970-01-01 00:00:00'UTC). See Error Log Message Format.
Hash joins are now used any time a nested block loop would be employed. This means that hash joins can be used for the following types of queries:
Left outer joins
Right outer joins
This builds on work done for MySQL 8.0.18, and removes a limitation in the implementation such that a hash join could be used only with a query having at least one equi-join condition. In addition, both inner and outer joins (including semijoins and antijoins) can now employ batched key access (BKA), which allocates join buffer memory incrementally so that individual queries need not use up large amounts of resources that they do not actually require for resolution. For more information, see Batched Key Access Joins.
This fix completes the task of replacing the executor used in previous versions of MySQL with the iterator executor, including replacement of the old index subquery engines that governed queries of the form
INqueries which have not been converted into semijoins, as well as queries materialized into the same form, which depended on internals from the old executor.
For more information and examples, see Hash Join Optimization. (Bug #30528604, Bug #30473261, Bug #30912972)
This release implements several new index-level optimizer hints, which function much like existing index hints that employ SQL keywords such as
IGNORE INDEX. These are intended to replace the equivalent index hints, which will be deprecated in a future MySQL release (and eventually removed). The new hints are listed here, along with a brief description of each:
JOIN_INDEX: Forces MySQL to use the specified index or indexes for any available access method, such as
index_merge, and so on. This is equivalent to the
FORCE INDEX FOR JOINindex hint.
NO_JOIN_INDEX: Causes the server to ignore the specified index or indexes for any access method. The equivalent index hint is
IGNORE INDEX FOR JOIN.
GROUP_INDEX: Makes the server use the specified index or indexes for index scans for
GROUP BYoperations. Equivalent to
FORCE INDEX FOR GROUP BY.
NO_GROUP_INDEX: Forces MySQL to ignore the specified index or indexes for index scans for
GROUP BYoperations. The equivalent index hint is
IGNORE INDEX FOR GROUP BY.
ORDER_INDEX: Causes MySQL to use the specified index or indexes for sorting rows. It is equivalent to
FORCE INDEX FOR ORDER BY.
NO_ORDER_INDEX: Keeps the server from using the specified index or indexes for performing row sorts. Equivalent to
IGNORE INDEX FOR ORDER BY.
INDEX: Acts as the combination of
ORDER_INDEX, forcing the server to use the specified index or indexes for any and all scopes. Equivalent to
NO_INDEX: Acts as the combination of
NO_ORDER_INDEX; that is, it forces MySQL to ignore the specified index or indexes for any and all scopes. It is equivalent to the index hint
Consider the following query using index hints on a table having the indicated columns and index:
SELECT a,b FROM t1 USE INDEX FOR ORDER BY (i_ab) ORDER BY a;
Using the index-level optimizer hints introduced in this release, this query can be rewritten as shown here:
SELECT /*+ ORDER_INDEX(t1 i_ab) */ a,b FROM t1 ORDER BY a;
The new index-level optimizer hints can be used with
DELETEstatements. (This is unlike index hints using
IGNORE INDEX, which can be used only with
UPDATE.) Thus, statements like the following are possible:
UPDATE /*+ INDEX(t1 i_ab) */ t1 SET d = 1 WHERE a = 1 AND b = 2 AND c = 3; DELETE /*+ INDEX(t1 i_a,i_c) */ FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
Multiple hints can be specified within the same comment, like this:
DELETE /*+ INDEX(t1 i_a) JOIN_INDEX(t1 i_c) */ FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
Index-level optimizer hints can be used concurrently with other optimizer hints. When you do so, the index-level hints apply first; the effects of any other optimizer hints are limited to the set of indexes permitted by the index-level hints.
Index-level hints can also be used when creating views, as shown here:
CREATE VIEW v1 AS SELECT /*+ NO_INDEX(t1 i_a,i_b) */ a FROM t1 WHERE b IN (SELECT /*+ NO_INDEX(t1 i_ab,i_b) */ a FROM t1 WHERE a > 3) ORDER BY a;
If these index-level optimizer hints are used in the same statement as index hints, the index hints are ignored.
The new index-level optimizer hints are equivalent to
FORCE INDEXrather than
USE INDEX; in other words, using one or more of the index-level optimizer hints means that a table scan is used only if there is no way to use one of the named indexes to find rows in the table. To cause MySQL to use the same index or set of indexes as with a given instance of
USE INDEX, you can use
NO_ORDER_INDEX, or some combination of these.
For more information and examples, see Index-Level Optimizer Hints.
Binary packages that include
curlrather than linking to the system
curllibrary have been upgraded to use
curl7.69.0. (Bug #30866333)
For RPM packages, the comp_err utility has been moved to the
-testsubpackage and marked as a test component. (Bug #30716034)
The bundled LZ4 library was upgraded to version 1.9.2. This fixes certain issues such as Bug #30369643 producing a mysqlpump runtime error.
References: See also: Bug #30369643.
libeditlibrary was upgraded to version 3.1.
The Performance Schema collected session-related statistics for errors that can occur only globally and not per session. This is no longer done, reducing memory overhead for error instrumentation. Additionally, rows for global errors are no longer included in error summaries reported per thread, account, user, or host. (Bug #30311574)
An LDAP server can be configured to delegate LDAP searches to another LDAP server, a functionality known as LDAP referral. However, enabling LDAP referral can cause searches to fail with LDAP operation errors under certain conditions. To enable the MySQL Enterprise Edition LDAP authentication plugins to avoid referral errors, the new
authentication_ldap_sasl_referralsystem variables are available. These variables enable each plugin to control whether the LDAP server should use referral during MySQL authentication. See LDAP Search Referral.
The MySQL Enterprise Edition SASL LDAP authentication plugin now supports GSSAPI/Kerberos as an authentication method for MySQL clients and servers on Linux. This is useful in Linux environments where applications access LDAP using Microsoft Active Directory, which has Kerberos enabled by default. See LDAP Authentication Methods.
This feature is available for all RPM and DEB packages for Linux, but not for the TAR archive packages.
SELECTstatements could appear at either of two positions:
SELECT * INTO OUTFILE 'file_name' FROM table_name;
Before a trailing locking clause:
SELECT * FROM table_name INTO OUTFILE 'file_name' FOR UPDATE;
INTOnow can appear in a third position, at the end of
SELECT * FROM table_name FOR UPDATE INTO OUTFILE 'file_name';
INTOat the end is the preferred position. The position before a locking clause is now deprecated and support for it will be removed in a future MySQL version. In other words,
FROMbut not at the end of the
SELECTproduces a warning.
... UNION SELECT * FROM table_name INTO OUTFILE 'file_name'; ... UNION (SELECT * FROM table_name) INTO OUTFILE 'file_name'; ... UNION SELECT * INTO OUTFILE 'file_name' FROM table_name; ... UNION (SELECT * INTO OUTFILE 'file_name' FROM table_name);
However, the last two variants are confusing, as if they collect information from the named table rather than the entire query expression (the
UNION). Those two
INTOnow are deprecated and support for them will be removed in a future MySQL version. Thus:
In the trailing query block of a query expression, use of
FROMproduces a warning.
In a parenthesized trailing block of a query expression, use of
INTO(regardless of its position relative to
FROM) produces a warning.
The deprecations apply to all
INTO DUMPFILE, and
perfschema.idx_compare_replication_applier_statustest case was updated to store the old value of number of transaction retries and compare it with the new value of number of transaction retries. Thanks to Facebook for the contribution. (Bug #30810627, Bug #98389)
If the MySQL Server instance's client connections limit, as specified by the
max_connectionsserver system variable, was reached while X Plugin was starting up, X Plugin was unable to create a session to get the server configuration, so failed to start. X Plugin now creates an administrative session (using the
mysql_admin_sessionservice) during startup, which is not subject to the client connections limit. (Bug #30894981)
When an X Protocol session could not be initialized because there were too many X Protocol connections already, the error code 5011 Could not open session was returned. The more relevant error code 1040 Too many connections is now returned in this situation. (Bug #30753637)
An issue with validating JSON references caused an error when creating a collection with a validation schema. (Bug #30733330)
During shutdown of a MySQL Server instance with X Protocol connections to clients, a race condition in X Plugin could cause invalid client connections to be accepted for processing. Because invalid clients were ignored for client timeout verification during shutdown, these clients blocked shutdown until the timeout set by the
mysqlx_wait_timeoutsystem variable was reached, which defaults to 8 hours. To prevent this issue, client timeout verification now includes clients that are in an invalid state. (Bug #30702685)
When connecting to a MySQL 8.0 server, X Plugin set a different collation for the session to that used by the mysql client, which could cause issues with queries that depended on the collation. X Plugin now uses the
utf8mb4_0900_ai_cicollation, which is the default for the
utf8mb4characterset. (Bug #30516849)
The worker threads for X Protocol connections were identified as system threads on creation, and assigned to the
SYS_defaultresource group. This identification meant they could not be assigned to user resource groups for resource management purposes. They are now identified as user threads and assigned to the
USR_defaultresource group. Note that X Protocol does not currently support
SET RESOURCE GROUPstatements, but these statements can operate on X Protocol connection threads using classic MySQL protocol connections. (Bug #30059288)
X Plugin can now access the MySQL system variables as soon as initialization starts, so the plugin install thread can set up the required connectivity itself rather than starting a separate thread. (Bug #29127302)
Important Change: Previously, including any column of a blob type larger than
BLOBas the payload in an ordering operation caused the server to revert to sorting row IDs only, rather than complete rows; this resulted in a second pass to fetch the rows themselves from disk after the sort was completed. Since
GEOMETRYcolumns are implemented internally as
LONGBLOB, this caused the same behavior with these types of columns even though they are almost always much shorter than the 4GB maximum for
LONGBLOB(or even the 16 MB maximum for
MEDIUMBLOB). The server now converts columns of these types into packed addons in such cases, just as it does
BLOBcolumns, which in testing showed a significant performance increase. The handling of
LONGBLOBcolumns in this regard remains unchanged.
One effect of this enhancement is that it is now possible for Out of memory errors to occur when trying to sort rows containing very large (multi-megabtye)
GEOMETRYcolumn values if the sort buffers are of insufficient size; this can be compensated for in the usual fashion by increasing the value of the
sort_buffer_sizesystem variable. (Bug #30400985, Bug #30804356)
InnoDB: The Contention-Aware Transaction Scheduling (CATS) algorithm, which prioritizes transactions that are waiting for locks, was improved. Transaction scheduling weight computation is now performed a separate thread entirely, which improves computation performance and accuracy.
The First In First Out (FIFO) algorithm, which had also been used for transaction scheduling, was removed. The FIFO algorithm was rendered redundant by CATS algorithm enhancements. Transaction scheduling previously performed by the FIFO algorithm is now performed by the CATS algorithm.
TRX_SCHEDULE_WEIGHTcolumn was added to the
INFORMATION_SCHEMA.INNODB_TRXtable, which permits querying transaction scheduling weights assigned by the CATS algorithm.
INNODB_METRICScounters were added for monitoring code-level transaction scheduling events:
The number of attempts to release record locks.
The number of attempts to grant record locks.
The number of times the wait-for graph was analyzed to update transaction schedule weights.
InnoDB: The storage area for the doublewrite buffer was moved from the system tablespace to doublewrite files. Moving the doublewrite buffer storage area out of the system tablespace reduces write latency, increases throughput, and provides flexibility with respect to placement of doublewrite buffer pages. The following system variables were introduced for advanced doublewrite buffer configuration:
Defines the doublewrite buffer file directory.
Defines the number of doublewrite files.
Defines the maximum number of doublewrite pages per thread for a batch write.
Defines the number of doublewrite pages to write in a batch.
For more information, see Doublewrite Buffer.
EXPLAIN FORMAT=TREEnow displays
inversioninformation for windowing functions. (Bug #30770631)
EXPLAIN FORMAT=TREEoutput has been improved to provide more information about evaluated window functions, and to match that supplied for regular aggregates. (Bug #30573446, Bug #30582782)
Configuring with the
-DWITH_LTO=1CMake option now works on macOS. (Bug #30125902)
From MySQL 8.0.20, you can enable binary log transaction compression on a MySQL server instance. When binary log transaction compression is enabled, transaction payloads are compressed using the zstd algorithm, and then written to the server's binary log file as a single event (a
Transaction_payload_event). Compressed transaction payloads remain in a compressed state while they are sent in the replication stream to replicas, other Group Replication group members, or clients such as mysqlbinlog. They are not decompressed by receiver threads, and are written to the relay log still in their compressed state. Binary log transaction compression therefore saves storage space both on the originator of the transaction and on the recipient (and for their backups), and saves network bandwidth when the transactions are sent between server instances.
You can enable binary log transaction compression on a MySQL server instance using the
binlog_transaction_compressionsystem variable, which defaults to
OFF. You can also use the
binlog_transaction_compression_level_zstdsystem variable to set the level for the zstd algorithm that is used for compression. This value determines the compression effort, from 1 (the lowest effort) to 22 (the highest effort).
A new option for the
CHANGE MASTER TOstatement,
REQUIRE_TABLE_PRIMARY_KEY_CHECK, enables a replication slave to select its own policy for primary key checks. When the option is set to
ONfor a replication channel, the slave always uses the value
sql_require_primary_keysystem variable in replication operations, requiring a primary key. When the option is set to
OFF, the slave always uses the value
sql_require_primary_keysystem variable in replication operations, so that a primary key is never required, even if the master required one. When the
REQUIRE_TABLE_PRIMARY_KEY_CHECKoption is set to
STREAM, which is the default, the slave uses whatever value is replicated from the master for each transaction.
For multisource replication, setting
OFFenables a slave to normalize behavior across the replication channels for different masters, and keep a consistent setting for the
sql_require_primary_keysystem variable. Using
ONsafeguards against the accidental loss of primary keys when multiple masters update the same set of tables. Using
OFFallows masters that can manipulate primary keys to work alongside masters that cannot.
PRIVILEGE_CHECKS_USERis set to apply replication privilege checks to the channel, setting
OFFmeans that the user account does not need session administration level privileges to set restricted session variables, which are required to change the value of
sql_require_primary_keyto match the master's setting for each transaction.
Since MySQL 8.0.19, compression has been supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a compression algorithm to use. By default, the server permits the Deflate, LZ4, and zstd compression algorithms, or you can set the
mysqlx_compression_algorithmssystem variable to include only the ones you permit. In MySQL 8.0.19, X Protocol uses the library default compression level for each algorithm, and the client cannot negotiate this.
From MySQL 8.0.20, the client can request a specific compression level during capability negotiations for an X Protocol connection. X Protocol sets a maximum compression level for each algorithm, which prevents the server from agreeing to high compression levels that are requested by clients if that would consume too much resource on the server. The maximum compression levels are initially set to 5 for Deflate, 8 for LZ4, and 11 for zstd. You can adjust these settings using the new
New default compression levels for X Protocol have also been selected through performance testing as being a good trade-off between compression time and network transit time. These defaults are not necessarily the same as the library default for each algorithm. They are applied if the client does not request a compression level for the algorithm. The default compression levels are initially set to 3 for Deflate, 2 for LZ4, and 3 for zstd. You can adjust these settings using the new
Incompatible Change: Some queries that used
ST_Contains()did not return any results unless
> 0was added.Note
For upgrades from earlier versions of MySQL, you should recreate spatial indexes in tables that have them.
(Bug #30461595, Bug #97347)
Performance: Certain queries against tables with spatial indexes were not performed as efficiently following an upgrade from MySQL 5.7 to MySQL 8.0. (Bug #94655, Bug #29488350)
References: See also: Bug #89551, Bug #27499984.
SPJworker per node owning a primary partition of the root table. If this table used read from any replica,
SPJworkers in the same
DBSPJinstance, which effectively removed the use of some
SPJworkers. (Bug #30639165)
NDB Cluster: Executing the
SHOWcommand using an ndb_mgm client binary from NDB 8.0.16 or earlier to access a management node running NDB 8.0.17 or later produced the error message Unknown field: is_single_user. (Bug #30599413)
References: See also: Bug #16275500.
CREATE UNDO TABLESPACEoperation that specified an undo data file name without specifying a path removed an existing undo data file of the same name from the directory specified by
innodb_undo_directoryvariable. The file name conflict check was performed on the data directory instead of the directory specified by the
innodb_undo_directoryvariable. (Bug #30908328, Bug #98628)
InnoDB: In debug builds, a regression introduced in MySQL 8.0.19 slowed down mutex and rw-lock deadlock debug checks. (Bug #30886393)
References: This issue is a regression of: Bug #30628872.
InnoDB: Valgrind testing raised an error indicating that a conditional jump or move depends on an uninitialized value. The error was a false-positive due to invalid validation logic. (Bug #30837136)
InnoDB: Missing barriers in
rw_lock_debug_mutex_enter()(in source file
sync0debug.cc) could cause a thread to wait without ever being woken up. (Bug #30819167)
InnoDB: To improve server initialization speed,
fallocate()is now used to allocate space for redo log files. (Bug #30804431)
InnoDB: A data dictionary table open function was implemented with incorrect lock ordering. (Bug #30782103, Bug #97825)
InnoDB: Changes to parallel read threads functionality introduced in MySQL 8.0.17 caused a degradation in
SELECT COUNT(*)performance. Pages were read from disk unnecessarily. (Bug #30766089)
InnoDB: DDL logging was not performed for SQL operations executed by the bootstrap thread using the
init_filestartup variable, causing files to be left behind that should have been removed during a post-DDL stage. (Bug #30721214)
InnoDB: Adding an index on a column cast as a JSON array on a table with a specific number of records failed with an “Incorrect key file for table” error. (Bug #30709525, Bug #98098)
InnoDB: A Valgrind error reported that an uninitialized
lock->writer_threadvalue was used in a conditional jump. (Bug #30694177)
InnoDB: An internal buffer pool statistics counter (
n_page_gets) was partitioned by page number to avoid contention when accessed by multiple threads. (Bug #30604841, Bug #97822)
InnoDB: A tablespace import operation failed with a schema mismatch error due to the
.cfgfile and the data dictionary both containing default values for a column that was added using
ALGORITHM=INSTANT. An error should only occur if default values differ. (Bug #30561144)
InnoDB: A slow shutdown failed to flush some GTIDs, requiring recovery of unflushed GTIDs from the undo log. (Bug #30548229)
InnoDB: A broken alignment requirement in the code that allocates a prefix in memory for Performance Schema memory allocations caused a failure on MySQL builds optimized for macOS and FreeBSD. (Bug #30530857)
InnoDB: Adding a virtual column raised an assertion failure due to data that was missing from the new data dictionary object created for the table. (Bug #30524263)
InnoDB: A required latch was not taken when checking the mode of an undo tablespace. A required latch was also not taken when checking whether an undo tablespace is empty. (Bug #30509134)
InnoDB: Allocating an update undo log segment to an XA transaction for persisting a GTID value before the transaction performed any data modifications caused a failure. (Bug #30456328)
InnoDB: A query executed on a partitioned table with a discarded tablespace raised an assertion failure. (Bug #30437407, Bug #97271)
row_upd_clust_rec_by_insertfunction, which marks a clustered index record as deleted and inserts an updated version of the record into the clustered index, passed an incorrect
n_extvalue (the total number of external fields) to lower level functions, causing an assertion failure. (Bug #30437378)
InnoDB: During a cloning operation, writes to the data dictionary buffer table at shutdown were too late, causing a failure. Newly generated dirty pages were not being flushed. (Bug #30427369, Bug #30405535, Bug #30405535)
InnoDB: An operation performed with the
innodb_buffer_pool_evictdebug variable set to
uncompressedcaused an assertion failure. (Bug #30405531)
InnoDB: Read-write lock code (
rw_lock_t) that controls ordering of access to the boolean
recursiveflag and the writer thread ID using GCC builtins or
os_mutexwhen the builtins are not available, was revised to use C++
std::atomicin some instances.
Thanks to Yibo Cai from ARM for the contribution. (Bug #30401416, Bug #97150)
InnoDB: A failure occurred while upgrading from MySQL 5.7 to MySQL 8.0. A server data dictionary object was missing information about the
FTS_DOC_ID_INDEXthat remain after dropping a
FULLTEXTindex. (Bug #30357954)
InnoDB: Unnecessary messages about parallel scans were printed to the error log. (Bug #30330448)
InnoDB: During upgrade from MySQL 5.7 to MySQL 8.0, clustered indexes named
GEN_CLUST_INDEXare renamed to
PRIMARY, which resulted in duplicate entries for the clustered indexes being added to the
mysql.innodb_index_statstable. (Bug #30330448)
InnoDB: Various internal functions computed write event slots in an inconsistent manner. (Bug #30228108, Bug #96519)
InnoDB: Under specific circumstances, it was possible that tablespace encryption key information would not be applied during the redo log apply phase of crash recovery. (Bug #30209760)
InnoDB: A file operation failure caused the page tracking archiver to fail, which in turn caused the main thread to hang, resulting in an assertion failure. Also, incorrectly, the page tracking archiver remained enabled in
innodb_read_onlymode. (Bug #30202643)
InnoDB: An index corruption error was reported when attempting to import a tablespace containing a table column that was added using
ALGORITHM=INSTANT. The error was due to missing metadata associated with the instantly added column. (Bug #30191523, Bug #96477)
InnoDB: A transaction attempting to fetch an LOB record encountered a null LOB reference, causing an assertion failure. However, the null LOB reference was valid in this particular scenario because the LOB value was not yet fully written. (Bug #30144303)
InnoDB: During a parallel read operation, the rollback of a table load operation while
autocommitwas disabled resulted in a server to exit due to assertion code that did not account for the possibility of tree structure changes during a parallel read. (Bug #30060690)
InnoDB: The current size value maintained in a rollback segment memory object was found to be invalid, causing an assertion failure in function
trx_purge_free_segment(). A validation routine (
trx_rseg_t::validateCurrSize()) was added to verify the current size value. (Bug #29947027)
InnoDB: A prepared statement executed with invalid parameter values raised an assertion failure. (Bug #29880907)
InnoDB: An add column operation caused an assertion failure. The failure was due to a dangling pointer. (Bug #29866408)
References: This issue is a regression of: Bug #28491099.
InnoDB: Updating certain
InnoDBsystem variables that take string values raised invalid read errors during Valgrind testing. (Bug #29717909, Bug #95215)
InnoDB: Redo log records for modifications to undo tablespaces increased in size in MySQL 8.0 due to a change in undo tablespace ID values, which required additional bytes. The change in redo log record size caused a performance regression in workloads with heavy write I/O. To address this issue, the redo log format was modified to reduce redo log record size for modifications to undo tablespaces. (Bug #29536710)
InnoDB: Additional information about
InnoDBfile writes, including progress data, is now printed to the error log. (Bug #29472295, Bug #94634)
InnoDB: An insert statement on a table with a spatial index raised a record type mismatch assertion due to a tuple corruption. (Bug #29465567)
InnoDB: A function that calculates undo log record size could calculate an incorrect length value in the case of a corrupted undo log record, resulting in a malloc failure. Assertion code was added to detect incorrect calculations. (Bug #29448406, Bug #82734)
Replication: The thread used by Group Replication's message service was not correctly registered by the Performance Schema instrumentation, so the thread actions were not visible in Performance Schema tables. (Bug #30824676)
Replication: Group Replication initiates and manages cloning operations for distributed recovery, but group members that have been set up to support cloning may also participate in cloning operations that a user initiates manually. In releases before MySQL 8.0.20, you could not initiate a cloning operation manually if the operation involved a group member on which Group Replication was running. From MySQL 8.0.20, you can do this, provided that the cloning operation does not remove and replace the data on the recipient. The statement to initiate the cloning operation must therefore include the
DATA DIRECTORYclause if Group Replication is running. (Bug #30798640)
Replication: For Group Replication channels, issuing the
CHANGE MASTER TOstatement with the
PRIVILEGE_CHECKS_USERoption while Group Replication was running caused the channel's relay log files to be deleted. Transactions that had been received and queued in the relay log, but not yet applied, could be lost in this situation. The
CHANGE MASTER TOstatement can now only be issued when Group Replication is not running. (Bug #30655369)
Replication: Group Replication's failure detection mechanism raises a suspicion if a server stops sending messages, and the member is eventually expelled provided that a majority of the group members are still communicating. However, the failure detection mechanism did not take into account the situation where one or more of the group members in the majority had actually already been marked for expulsion, but had not yet been removed from the group. Where the network was unstable and members frequently lost and regained connection to each other in different combinations, it was possible for a group to end up marking all its members for expulsion, after which the group would cease to exist and have to be set up again.
Group Replication's Group Communication System (GCS) now tracks the group members that have been marked for expulsion, and treats them as if they were in the group of suspect members when deciding if there is a majority. This ensures at least one member remains in the group and the group can continue to exist. When an expelled member has actually been removed from the group, GCS removes its record of having marked the member for expulsion, so that the member can rejoin the group if it is able to. (Bug #30640544)
Replication: While an SQL statement was in the process of being rewritten for the binary log so that sensitive information did not appear in plain text, if a
SHOW PROCESSLISTstatement was used to inspect the query, the query could become corrupted when it was written to the binary log, causing replication to stop. The process of rewriting the query is now kept private, and the query thread is updated only when rewriting is complete. (Bug #30569003, Bug #97531, Bug #30654405)
Replication: When a
REVOKEstatement is only partially executed, an incident event is logged in the binary log, which makes the replication slave's applier thread stop so that the slave can be reconciled manually with the master. Previously, if a failed
REVOKEstatement was the first statement executed in the session, no GTID was applied to the incident event (because the cache manager did not yet exist for the session), causing an error on the replication slave. Also, no incident event was logged in the situation where a
GRANTstatement created a user but then failed because the privileges had been specified incorrectly, again causing an error on the replication slave. Both these issues have now been fixed. (Bug #30566518, Bug #30324661)
Replication: Compression is now triggered for the
mysql.gtid_executedtable when the
thread/sql/compress_gtid_tablethread is launched after the server start, and the effects are visible when the compression process is complete. (Bug #30541799)
Replication: Performance Schema tables could not be accessed on a MySQL server with Group Replication that was running under high load conditions. (Bug #30112711, Bug #30675790)
Replication: Internal queries from Group Replication to the Performance Schema for statistics on local group members failed if they occurred simultaneously with changes to the group's membership. Locking for the internal queries has been improved to fix the issue. (Bug #30049349, Bug #30791583, Bug #30963553)
Replication: In the event of an unplanned disconnection of a replication slave from the master, the reference to the master's dump thread might not be removed from the list of registered slaves, in which case statements that accessed the list of slaves would fail. The issue has now been fixed. (Bug #29915479)
Replication: When a partitioned table was involved, the server did not correctly handle the situation where a row event could not be written to the binary log due to a lack of cache space. An appropriate error is now returned in this situation. (Bug #29848931)
Replication: During Group Replication's distributed recovery process, if a joining member is unable to complete a remote cloning operation with any donor from the group, it uses state transfer from a donor's binary log to retrieve all of the required data. However, if the last attempted remote cloning operation was interrupted and left the joining member with incomplete or no data, an attempt at state transfer immediately afterwards could also fail. Before attempting state transfer following a failed remote cloning operation, Group Replication now checks that the remote cloning operation did not reach the stage of removing local data from the joining member. If data was removed, the joining member leaves the group and takes the action specified by the
group_replication_exit_state_actionsystem variable. (Bug #29669099, Bug #29944828)
Replication: With the settings
INSERT ... SELECTquery involving a transactional storage engine omitted any columns with a null value from the row image written to the binary log. This happened because when processing
INSERT ... SELECTstatements, the columns were marked for inserts before the binary logging format was selected. The issue has now been fixed. (Bug #29110804, Bug #93423)
Replication: Before taking certain actions, Group Replication checks what transactions are running on the server. Previously, the service used for this check did not count transactions that were in the commit phase, which could result in the action timing out. Now, transactions that are in the commit phase are included in the set of currently ongoing transactions. (Bug #28327838)
JSON_TABLE()was used as part of an
INSERTstatement in strict mode, conversion errors handled by any
ON ERRORclause could cause the
INSERTto be rejected. Since errors are handled by an
ON ERRORclause, the statement should not be rejected unless
ERROR ON ERRORis actually specified.
This issue is fixed by ignoring warnings when converting values to the target type if
NULL ON ERRORor
DEFAULT ... ON ERRORhas been specified or is implied. (Bug #30628330)
JSON: The output from
JSON_TABLE()was not always correct when used in views. This fix corrects the following issues:
Column names were not quoted, causing syntax errors when quoting was needed for these.
Some column types were misreported.
Some column type attributes such as UNSIGNED were lost.
Column character set and collation were lost.
JSON: The functions
JSON_SCHEMA_VALIDATION_REPORT()formerly checked to ensure that their arguments were convertible to
JSONeach time a prepared statement including these was executed, which was neither efficient nor necessary. Now in such cases, the check is performed only once, when the statement is prepared. (Bug #97878, Bug #30622327)
Privilege requirements were checked incorrectly for stored objects with a
DEFINERthat has the
SYSTEM_USERprivilege. (Bug #31077699)
A number of errors reported by Clang in the documentation generated from the MySQL sources have been corrected. (Bug #30956093)
On FreeBSD, the krb5 package is a now a dependency. (Bug #30887620)
If a query contained multiple references to the same common table expression (CTE) and a pseudo-comment crossed borders of the CTE definition, the parser failed with confusing syntax error messages. (Bug #30871301)
For installation using Debian packages, the
/var/run/mysqlddirectory was not created. (Bug #30855015, Bug #98484)
mysqlslap did not shut down its threads properly when SQL statements returned an error. This could result in attempts to free already freed memory. (Bug #30850310)
When X Plugin was attempting to add a document to a collection as either an insertion or an update in the case of a duplicate key, in the case where the document failed a unique key constraint in a field other than the primary key, the error returned by X Plugin did not state that this was the cause of the issue. The appropriate error is now returned. (Bug #30843865)
An integer value generated by transformations in the resolver was supplied to a test which expected a boolean. (Bug #30837240)
A query using an
INexpression that accessed one or more columns holding large string values could lead to a memory leak. (Bug #30814171)
Statements did not work properly when the target of a
DELETEwas a common table expression. (Bug #30796015, Bug #98330)
When a table had both a primary key and a secondary key on the same column, but for different lengths, the range optimizer chose the wrong key part in the secondary index for comparing range values. (Bug #30783011)
In some cases, errors caused when
DISTINCTwas used with an aggregate function whose argument was of an incorrect type were not propagated correctly. (Bug #30782687)
For replication using compression, the slave could raise an assertion if the master was restarted. (Bug #30774692)
For debug builds, the server could exit trying to print an optimizer trace. (Bug #30773218, Bug #98258)
mysql_real_connect_nonblocking()C API function exhibited blocking behavior. (Bug #30771233)
LOCK TABLESactive, while processing
INFORMATION_SCHEMAqueries, the server could attempt to lock internal temporary tables (which need no locks), causing an assertion to be raised. (Bug #30764651, Bug #98221)
The mysqldump internal network timeout was increased from 700 to 86400 seconds to accommodate connecting to busy or unresponsive servers. (Bug #30755992, Bug #98203)
libsaslto be linked into the
daemon_memcachedplugin. (Bug #30755301)
After deleting the temporary table associated with a window function's frame buffer, the temporary table parameter for the frame buffer was not cleaned up, causing string buffers associated with copy fields not to be freed properly. (Bug #30752366)
-libs-compatRPM package is now built with system
zlibto avoid problems with unrestricted export of symbols in
libmysqlclient.so.18. (Bug #30722389, Bug #98130)
The server exited histogram sampling prematurely, causing an assertion failure. An unnecessary boolean variable that marked the completion of a sampling operation was removed. (Bug #30717778)
When removing a
WHEREcondition because one of the participating conditions was always false, a materialized derived table was not cleaned up properly, resulting in a memory leak. (Bug #30712243)
Multiple comparisons with the same
GEOMETRYvalue were not always handled correctly. (Bug #30697042)
References: See also: Bug #30306306.
Server startup failed if MySQL Enterprise Firewall was enabled at startup but the whitelist and user tables were missing. (Bug #30690181)
For prepared statements, re-execution could cause a server exit if a cleaned-up materialized temporary table was still being referred to. (Bug #30674598)
ER_WARN_DEPRECATED_FOUND_ROWSerror messages were incorrectly categorized in the range of messages meant to be written to the error log. They are now correctly categorized as messages meant to be sent to clients. The old errors are now designated as
OBSOLETE_ER_WARN_DEPRECATED_FOUND_ROWSin the range of error-log messages. (Bug #30673043)
Some joins within subqueries where an outer query used
NOT EXISTSwere not always handled correctly. (Bug #30671329)
ORDER BYare permitted but an
ORDER BYclause of this sort should not have any effect on the result; such queries were not always handled correctly. (Bug #30669493)
A missing out-of-bounds check in
wild_case_match()caused a pointer to read out of bounds. (Bug #30668886)
strconvert()function was not safe for conversions between
utf8_general_cistrings. (Bug #30668847)
Some filesorts using keys of fixed length were not always handled correctly. (Bug #30665034)
When performing a hash join on two string columns that were potentially very large (in particular,
PAD SPACEcollations), MySQL stored the entire sort key in the row, which impacted performance by requiring large amounts of memory. Now only a collation-aware hash is stored, with an added equality comparison prevent a wrong answer, even in the event of a 64-bit hash collision. (Bug #30664831)
When at least two tables were joined to at least two other tables using a semijoin, and the join optimizer chose to use a loose scan, it was possible to place both of the left tables below the deduplicating nested loop iterator, leading to excessive deduplication. We fix this by treating a loose scan across multiple tables as a separate internal structure. (Bug #30659810)
In unions of a
consttable and zero or more known-zero expressions, derived tables of exactly one row could be read incorrectly as having zero rows. (Bug #30655712, Bug #97967)
A MySQL 8.0.19 patch set an invalid
INFORMATION_SCHEMAand data dictionary version number. Assertion code was added to prevent future version information errors. (Bug #30645158, Bug #97948)
References: This issue is a regression of: Bug #29871530.
When setting up the iterator tree, the optimizer now filters away and subsequently ignores conditions which are known to be trivially true. (Bug #30644591)
References: This issue is a regression of: Bug #28811287, Bug #92834.
The Event Scheduler had a memory leak. (Bug #30628268)
Using the asynchronous C API functions could result in freeing already freed memory. (Bug #30596999, Bug #97805)
On tables containing a
CHECKconstraint, certain simple queries were inefficient due to excessive memory allocation and Performance Schema calls. (Bug #30594613)
Under certain circumstances, a memcached command could result in reading an uninitialized memory buffer, causing a failure. (Bug #30592346)
A race condition could occur between
InnoDBissuing requests for schema and table metadata while filling
INFORMATION_SCHEMA.INNODB_TABLES, and the schema being dropped, leading to user queries on
INNODB_TABLESreporting an error. (Bug #30591967)
ALTER USERto reset an account
MAX_USER_CONNECTIONSvalue did not take effect until all current account connections terminated, if there were any. (Bug #30578217, Bug #97735)
When the optimizer sets up a weedout, it notifies all tables that are part of the weedout that they should provide row IDs. For confluent weedouts (weedouts returning at most one row), the optimizer expects that the executor handles the weedout without row IDs. In the iterator executor, confluent weedouts are implemented using
LIMIT 1; the normal weedout iterator does not handle confluent weedouts, and thus always expects row IDs. In the case of a confluent weedout on the right side of an outer join, the confluent weedout was processed as a normal weedout, causing the iterator executor to ask for row IDs where the tables did not supply them. Now in such cases, the
LIMIT 1optimization is also applied. (Bug #30566549, Bug #30282693)
SET PERSISTcould fail due to attempting to persist variables to the wrong directory. (Bug #30561982)
Within a stored program with an error handler defined for the error condition of accessing a nonexistent table, the handler was not invoked if the table was nonexistent because it was named in a nonexistent database. (Bug #30561920, Bug #97682)
The duplicate weedout optimization strategy employed by MySQL (see Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations) uses an internal table of row IDs which it has already seen, with a unique index on the column containing these IDs. When the key for the unique index became too large, which could happen with very large row IDs, the server reverted to deduplication by hash key instead, with a separate index (not unique) over the hash field only, as with other temporary tables. Because the latter index was not properly initialized, affected queries were not executed properly and could lead to a premature exit. (Bug #30556257)
For debug builds, under
LOCK TABLES, the server could mishandle materialized temporary tables and raise an assertion. (Bug #30476213, Bug #97404)
The internal array of materialized query blocks
SELECT_LEX_UNIT::m_query_blocks_to_materializewas not reset between executions, which meant that it pointed to objects which were no longer valid when a prepared statement was executed a second time, causing the second execution to fail. (Bug #30438038)
Altering column collations did not affect unique indexes until a server restart. (Bug #30386119, Bug #97103)
When using roles, the
EXECUTEprivilege for stored functions was treated as a privilege for stored procedures. As a result, it was not possible to use
EXECUTEas a role privilege for functions. (Bug #30376231)
A materialized subquery including a condition in which a column value was used as input to a nondeterministic function produced incorrect results. (Bug #30368937)
Several fixes were applied to the
InnoDBmemcached plugin. The fixes addressed potential deadlock issues, issues related to connection list latches, and removal of an obsolete flush mutex. (Bug #30354225)
Strings that used the
utf8mb4_0900_bincollation could not be compared with
utf8mb4strings that used a different collation. Now the comparison is done by using
utf8mb4_0900_binfor both strings. (Bug #30350111)
During optimization, MySQL removes conditions in which all arguments are considered equal; for example,
1 <> 1is removed and replaced with
false. In doing so, conditions containing non-deterministic arguments were also removed, which caused a condition such as
RAND() < RAND()to be considered an impossible condition. Now, the optimizer no longer removes conditions containing nondeterministic arguments. (Bug #30311271)
Scheduling of events could be disturbed by removing events. (Bug #30301356, Bug #96849)
The Event Scheduler reported warnings for Valgrind builds. (Bug #30301340)
Shutting down the server while using the clone plugin raised a Valgrind error. (Bug #30248419)
mysqld-auto.cnffile was malformed, the server did not start (expected), but did not report any error (unexpected). (Bug #30169731, Bug #96501)
UPDATEstatements could give an inconsistent number of rows matched (found rows) in cases where not all matched rows were updated, depending on the reason for rows not being updated. For example, rows not updated due to being updated through a view with a
WITH CHECK OPTIONclause were not counted as matching rows, whereas rows not updated due to a failing
CHECK CONSTRAINTwere counted. For consistency, rows that fail a
WITH CHECK OPTIONclause now are counted as matching rows. (Bug #30158954)
When restarting the MySQL server on a cloned directory,
InnoDBreported an error indicating that it could not find a tablespace file for a statistics table that was dropped by the server previously. (Bug #30093799)
The server did not handle correctly a
UNIONin which one of the queries contained a subquery that used
ORDER BY. (Bug #29952565)
INFORMATION_SCHEMAqueries, a race condition could result in multiple attempts to insert a key when updating the dynamic statistics tables, producing a duplicate-key error. (Bug #29948755, Bug #95929)
SHOW CREATE VIEWcould fail with an illegal mix of collations for views defined on a function that returns a string. (Bug #29904087)
A query with a
WHEREclause whose predicate contained a numeric value in scientific notation was not handled correctly.
In addition, attempting to insert a particular integer specified as a string caused a server exit when the string-to-integer conversion was not successful. (Bug #29723340, Bug #30441969)
An internal interface was added for retrieving and parsing errors that occur on the donor MySQL server instance (
ER_CLONE_DONORerrors) and for checking if data on the recipient has been dropped. (Bug #29682642)
It was not possible to drop any columns from a table when the
DEFAULTvalue. (Bug #29661106)
CONNECTION_CONTROLplugin, the Performance Schema instrumentation used keys that were not discoverable to the Performance Schema unless the associated code actually executed. (Bug #29539976)
For a nullable column
c, the optimizer now recognizes when the conditions
c < c,
c > c, and
c <> care always false and need not be evaluated for every row. Thanks to Daniel Black for the contribution. (For nonnullable columns, the optimizer already recognized always-false conditions.) (Bug #29115386, Bug #93642)
Reinitialization of character sets from
Index.xmlcould cause a use-after-free error. (Bug #28956360, Bug #93276)
An earlier change to reduce Performance Schema memory instrumentation overhead had the unintended effect of causing Group Replication performance degradation. (Bug #28719976)
References: This issue is a regression of: Bug #27500610.
ps_setup_reset_to_default()procedure used MySQL 5.7 defaults, not MySQL 8.0 defaults. (Bug #27636611)
Some connection encryption ciphers did not work. (Bug #27045306)
Previously, mysqlpump read the
[client]groups from option files. mysqlpump now additionally reads the
[mysql_dump]group is still accepted but is deprecated. (Bug #24733245, Bug #83144)
For a query of the form
SELECT DISTINCT ... ORDER BY ..., when the
ORDER BYwas pushed down onto the first table in the join, the result was not always sorted in the correct order. (Bug #98217, Bug #30760534)
NULLindicator was not properly written for items used as variable-length keys, such that all such items were assumed to be not
NULL, which was considered equal to the empty string when using certain collations. One visible effect of this issue was that ordering by an expression using a nullable string was sometimes not performed correctly. An example of such a query, where column
NULLand empty string values, is shown here:
SELECT c1, SUBSTR(c1, 1) AS c2 FROM t ORDER BY c2;
(Bug #98035, Bug #30687020)
A query returned inaccurate results when an expression in a
GROUP BYclause used a column name differing in case from that used for the name of the column when the table containing this column was created. An example of this would be when the query used
GROUP BY idalthough the column name as shown in the original
CREATE TABLEstatement was
This occurred because, the server performed case-sensitive comparisons of column names in expressions with names of columns in tables. This issue is fixed by ensuring that such comparisons are performed in a case-insensitive fashion as expected. (Bug #97628, Bug #98222, Bug #30541701, Bug #30761372)
UPDATEstatement which updated a table joined to a derived table that joined two other tables was not optimized properly as it had been in MySQL 5.6, instead being treated as if
STRAIGHT_JOINhad been used with the subquery creating the derived table. (Bug #97418, Bug #30488700)
hash joininstead of
block nested loop, since the latter no longer exists and is replaced by a hash join in nearly all cases. (Bug #97299, Bug #30444550)
The execution plan for a query that filtered on the first column of a composite hash index wrongly used this index, producing erroneous results. (Bug #94737, Bug #29527115)
References to columns from tables of outer query blocks in an
ONcondition of a
JOINdid not work, and could be used only in a
WHERE. The fix for this problem means that a query such as this one now works correctly:
SELECT o.order_date FROM orders o WHERE o.order_date IN ( SELECT c.contact_name FROM customers c INNER JOIN order_details od ON o.order_id = od.discount );
Previously this had to be rewritten as as shown here:
SELECT o.order_date FROM orders o WHERE o.order_date IN ( SELECT c.contact_name FROM customers c INNER JOIN order_details od ON 1 WHERE o.order_id = od.discount );
References to other tables of the same
FROMclause as the
JOIN, as in the query
SELECT * FROM t1 CROSS JOIN (t2 LEFT JOIN t3 ON t1.c=3), are not outer references and remain forbidden. In this case, a lateral join is required, like this:
SELECT * FROM t1 JOIN LATERAL (SELECT * FROM t2 LEFT JOIN t3 ON t1.c=3). (Bug #35242, Bug #96946, Bug #11748138, Bug #30350696)
There could be a mismatch between the version of OpenSSL used to build the server and the version used for other parts of MySQL such as libraries or plugins. This could cause certain features not to work, such as the LDAP authentication plugins. Now the same version of OpenSSL is used for building everything.
Previous work in MySQL 8.0 to optimize impossible expressions such as
a=b AND FALSEas
FALSEcould make for less efficient execution when such expressions appeared as outer join conditions, due to the fact that the join was interpreted as a Cartesian product followed by a filter. (Bug #8202, Bug #89739, Bug #97552, Bug #11745046, Bug #27581277, Bug #30520749)
References: See also: Bug #98206, Bug #30756135.