Changes in MySQL 8.0.22 (2020-10-19, General Availability)

For general information about upgrades, downgrades, platform support, etc., please visit https://dev.mysql.com/doc/relnotes/mysql/8.0/en/.

• Account Management Notes

• Audit Log Notes

• C API Notes

• Compilation Notes

• Configuration Notes

• Deprecation and Removal Notes

• Keyring Notes

• Optimizer Notes

• Packaging Notes

• Performance Schema Notes

• Pluggable Authentication

• SQL Syntax Notes

• X Plugin Notes

• Functionality Added or Changed

• Bugs Fixed

Account Management Notes

• Lock handling for statements involving the grant tables was improved. (Bug #31291237, Bug #31576185, WL #14084)

• Modifying the mysql.infoschema and mysql.sys reserved accounts now requires the SYSTEM_USER privilege. (Bug #31255458)

• For the CREATE USER, DROP USER, and RENAME USER account-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_ID privilege, 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. (WL #14073)

Audit Log Notes

• 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. (WL #13936)

C API Notes

• 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-name option that takes precedence over --host and 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 FEDERATED storage engine, and client programs other than mysql. (WL #13905)

Compilation Notes

• On Windows, Visual Studio 2019 Update 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)

Configuration Notes

• The new WITH_TCMALLOC CMake option indicates whether to link with -ltcmalloc. If enabled, built-in malloc(), calloc(), realloc(), and free() routines are disabled. The default is OFF. WITH_TCMALLOC and WITH_JEMALLOC are mutually exclusive. (Bug #31785166)

• The new COMPRESS_DEBUG_SECTIONS CMake 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_SECTIONS environment variable is set, the option takes its value from that variable. (Bug #31498296)

• The WITH_DEFAULT_FEATURE_SET CMake 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 bind_address, admin_address, and mysqlx_bind_address system 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-namespace option for specifying the network namespace.

  • For replication connections from replica servers to source servers, the CHANGE MASTER TO statement supports a NETWORK_NAMESPACE option for specifying the network namespace.

  For replication monitoring purposes, the Performance Schema replication_connection_configuration table, the replica server connection metadata repository (see Replication Metadata Repositories), and the SHOW REPLICA | SLAVE STATUS statement 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. (WL #12720)

Deprecation and Removal Notes

• From MySQL 8.0.22, the group_replication_ip_whitelist system variable is deprecated, and the system variable group_replication_ip_allowlist has 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 group_replication_ip_allowlist is used. (WL #14175)

• From MySQL 8.0.22, the statements START SLAVE, STOP SLAVE, SHOW SLAVE STATUS, SHOW SLAVE HOSTS and RESET SLAVE are deprecated. The following aliases should be used instead:

  • Instead of START SLAVE use START REPLICA

  • Instead of STOP SLAVE use STOP REPLICA

  • Instead of SHOW SLAVE STATUS use SHOW REPLICA STATUS

  • Instead of SHOW SLAVE HOSTS use SHOW REPLICAS

  • Instead of RESET SLAVE use RESET REPLICA

  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_start is equivalent to Com_replica_start

  • Com_slave_stop is equivalent to Com_replica_stop

  • Com_show_slave_status is equivalent to Com_show_replica_status

  • Com_show_slave_hosts is equivalent to Com_show_replicas

  (WL #14171)

• The InnoDB memcached plugin is deprecated and support for it will be removed in a future MySQL version. (WL #14131)

• The INFORMATION_SCHEMA.TABLESPACES table is unused. It is now deprecated and will be removed in a future MySQL version. Other INFORMATION_SCHEMA tables may provide related information, as described in The INFORMATION_SCHEMA TABLESPACES Table. (WL #14064)

Keyring Notes

• MySQL Enterprise Edition now includes a keyring_oci plugin 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. (WL #9770)

Optimizer Notes

• 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 expr1, expr2, ... FROM table ORDER BY ?, passing an integer value N for the parameter no longer causes ordering of the results by the N^th expression in the select list; the results are no longer ordered, as is expected with ORDER BY constant.

  • It was possible (although unsupported) to insert a floating point value into a LIMIT or OFFSET clause by means of a parameter (and possibly a user variable). This is now explicitly disallowed; such values must be integers.

  • The window functions NTILE(NULL), NTH_VALUE(expr, NULL), LEAD(expr, nn), and LAG(expr, nn), where nn is a negative number, are now disallowed, to comply with the SQL standard.

  • 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 CAST() expression.

  See PREPARE Statement, for the rules governing how the effectiue data types of parameters and user variables used within prepared statements are determined.

  In addition, the rows (N) argument to the window functions LAG(), LEAD(), and NTILE() must now be an integer in the range 0 to 263, inclusive, in any of the following forms:

  • an unsigned integer constant literal

  • a positional parameter marker (?)

  • a user-defined variable

  • a local variable in a stored routine

  In addition, this argument is no longer permitted to be NULL. See the descriptions of the functions just referenced for more information. (Bug #48612, Bug #99601, Bug #100150, Bug #105166, Bug #11756670, Bug #23599127, Bug #31119132, Bug #31365678, Bug #31393719, Bug #31592822, Bug #31810577, Bug #33448735, WL #9384)

• The filesort algorithm 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 JOIN as 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 JOIN was originally a RIGHT JOIN as 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 JOIN is in fact, in all respects, a LEFT JOIN. (Bug #30887665, Bug #30964002, WL #6509)

  References: See also: Bug #12567331, Bug #21350125.

• Added support for periodic synchronization when writing to files with SELECT INTO DUMPFILE and SELECT INTO OUTFILE statements. This feature can be enabled by setting the select_into_disk_sync system 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 (2¹⁷) bytes. An optional delay following synchronization to disk can also be set using the select_into_disk_sync_delay system 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, WL #13926)

• 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 > constant, it is now possible in many cases to push the outer WHERE condition down to the derived table, in this case resulting in SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt. Previously, if the derived table was materialized and not merged, MySQL materialized the entire table—in this case t1—then qualified the rows with the WHERE condition.

  When the derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer WHERE condition down to the derived table can reduce the number of rows that need to be processed, which should improve the query's performance.

  An outer WHERE condition 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 BY and uses no window functions, the outer WHERE condition can be pushed down to the derived table as a HAVING condition. If the derived table uses a window function and the outer WHERE references columns used in the window function's PARTITION clause, the WHERE condition can also be pushed down.

  This optimization cannot be employed for a derived table that contains a UNION or LIMIT clause.

  To enable derived condition pushdown, the optimizer_switch system variable's derived_condition_pushdown flag (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_PUSHDOWN optimizer hint (also added in this release). Use the NO_DERIVED_CONDITION_PUSHDOWN optimizer 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, WL #8084)

Packaging Notes

• For RPM and Debian packages, client-side plugins were moved to their own client-plugins package. (Bug #31584093)

• The VERSION file in MySQL source distributions is now named MYSQL_VERSION due to a naming conflict with Boost. (Bug #31466846)

• For platforms on which MySQL is built with WITH_SYSTEMD=1, packages no longer include legacy System V files: the mysqld_multi.server and mysql.server scripts, and the mysql.server.1, mysqld_multi.1, and mysqld_safe.1 man pages. (Bug #31450888)

Performance Schema Notes

• The SHOW PROCESSLIST statement 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.

  An alternative SHOW PROCESSLIST implementation is now available based on the new Performance Schema processlist table. 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 performance_schema_show_processlist system variable.

  • The alternative implementation of SHOW PROCESSLIST also applies to the mysqladmin processlist command.

  • The alternative implementation does not apply to the INFORMATION_SCHEMA PROCESSLIST table or the COM_PROCESS_INFO command 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.

  (WL #9090)

• 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_log table. 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_internal and JSON-format log_sink_json sinks). Several new status variables provide information about error_log table operation. See The error_log Table. (WL #13681)

Pluggable Authentication

• These changes were made for the LDAP authentication plugins:

  • For the SASL LDAP authentication plugin, the SCRAM-SHA-1 authentication method is not supported on On SLES 12 and 15 and EL6 systems. The default method on those systems is now GSSAPI.

  • 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)

SQL Syntax Notes

• 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 YEAR values 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.

  DATE, DATETIME, and TIMESTAMP are cast to the YEAR portion of the value. A TIME value is cast to the current year. Not specifying the value to be cast as a TIME value 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.

  Casting of GEOMETRY values to YEAR is not supported. A cast of an incompatible type or an out-of-range or illegal value returns NULL.

  YEAR can 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 CAST() and CONVERT() when performing casts to YEAR.

  For more information, see the description of the CONVERT() function. (WL #14015)

• When selecting a TIMESTAMP column value, it is now possible to convert it from the system time zone to a UTC DATETIME when retrieving it, using the AT TIME ZONE operator which is implemented for the CAST() function in this release.

  The syntax is CAST(value AT TIME ZONE specifier AS DATETIME[(precision)]), where the value is a TIMESTAMP, and the specifier is one of [INTERVAL] '+00:00' or 'UTC'. (INTERVAL is optional with the first form of the specifier, and cannot be used with 'UTC'.) The precision of the DATETIME value 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 ZONE cannot be used with CONVERT(), or in any other context other than as part of a CAST() function call. The ARRAY keyword 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. (WL #12535)

X Plugin Notes

• In specific conditions, terminating an X Protocol connection could cause MySQL Server to stop unexpectedly. (Bug #31671503)

Functionality Added or Changed

• LOCK TABLES privilege checking for views was improved. (Bug #31304432, WL #14092)

• 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. To activate asynchronous connection failover for a replication channel set SOURCE_CONNECTION_AUTO_FAILOVER=1 on the CHANGE MASTER TO statement for the channel, and set up a source list for the channel using the asynchronous_connection_failover_add_source and asynchronous_connection_failover_delete_source functions. (WL #12649)

• The new innodb_extend_and_initialize variable permits configuring how InnoDB allocates space to file-per-table and general tablespaces on Linux. By default, when an operation requires additional space in a tablespace, InnoDB allocates 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_initialize on Linux systems to avoid physically writing NULLs to newly allocated tablespace pages. When innodb_extend_and_initialize is disabled, space is allocated using posix_fallocate() calls, which reserve space without physically writing NULLs.

  A 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 InnoDB attempts to access those pages. To prevent this scenario, InnoDB writes 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. (WL #13782)

• 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:

  • SELECT statements and other read-only statements that read data from grant tables through join lists and subqueries, including SELECT ... FOR SHARE statements, using any transaction isolation level.

  • 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 SHARE statements that read data from grant tables now report a warning. With the FOR SHARE clause, read locks are not supported on grant tables.

  DML operations that read data from grant tables and are executed using the SERIALIZABLE isolation level now report a warning. Read locks that would normally be acquired when using the SERIALIZABLE isolation level are not supported on grant tables. (WL #14087)

• The ALTER DATABASE statement now supports a READ ONLY option 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 ONLY is enabled can be migrated to another MySQL instance without concern that the database might be changed during the operation. See ALTER DATABASE Statement.

  A new INFORMATION_SCHEMA table named SCHEMATA_EXTENSIONS displays database options. Currently, it displays READ ONLY=1 for read-only databases. See The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table. (WL #13369)

Bugs Fixed

• 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)

• InnoDB: The TTASEventMutex::exit function was optimized for ARM64. Thanks to Krunal Bauskar for the contribution. (Bug #31589019, Bug #100132)

• InnoDB: InnoDB failed to compile with the DISABLE_PSI_RWLOCK CMake option enabled. (Bug #31578289)

• InnoDB: The transaction isolation level, which is set to READ UNCOMMITTED for 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_FREE value to be defined as “sometimes lock-free” while __atomic_always_lock_free returns true for the same type on the same platform. (Bug #31504609)

• InnoDB: A REDUNDANT row 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 REDUNDANT row 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_TABLESPACES metadata 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_DEBUG option enabled caused compilation errors. (Bug #31369540)

• InnoDB: DDL operations on a partitioned table could cause a failure. TABLE_SHARE and table instance objects were opened for all partitions unnecessarily. (Bug #31365127)

• InnoDB: After changing a VARCHAR column collation from utf8mb4 to utf8mb4_bin in an in-place ALTER TABLE operation and adding an index on the same column, a case-sensitive query on the VARCHAR column returned an incorrect result. The VARCHAR column collation was changed in the data dictionary but not in the in-memory table object. Consequently, the index created on the VARCHAR column used stale column information causing comparisons to use the previously defined collation. (Bug #31361838)

• InnoDB: An ALTER TABLE ... IMPORT TABLESPACE operation 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 FULLTEXT index. 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 MUTEX statement 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_mutex was 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_pointer_is_block_field_instance() function. 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 INPLACE DDL operation on the raw-disk partition tablespace failed with an error instead of switching to the COPY algorithm. (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_shutdown setting 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_threads variable 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 DATA performance regression. (Bug #30088404, Bug #30003849)

• InnoDB: Restarting the server with an incorrect lower_case_table_names setting after a failure caused a hang condition. At startup, InnoDB waited 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_names setting. (Bug #29833945)

• Packaging; Group Replication: MySQL Server Docker images did not expose the Group Replication recommended port (33061). (Bug #31627536)

• Packaging: Added a client-plugins RPM that was separated from the client package. It contains shared plugins for MySQL client applications. (Bug #35162346)

• Replication: You can now set the value of the gtid_purged system variable in a stored procedure, which was not previously permitted. You cannot set gtid_purged in a stored function. (Bug #31571427)

• Replication: When a replication source server shuts down and restarts, its MEMORY tables become empty. To replicate this effect to replicas, the first time that the source uses a given MEMORY table 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 DELETE statement, but it is now a TRUNCATE TABLE statement. 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 read_only or super_read_only mode is set on the server. (Bug #29848785, Bug #95496)

• Replication: When the system variable session_track_gtids was set to OWN_GTID on 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)

• Group 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)

• Group Replication: Group Replication handling of memory allocation issues when adding transaction write sets has been improved. (Bug #31586243)

• Group Replication: While a remote cloning procedure was taking place on a joining member during distributed recovery, Group Replication considered the pre-cloning gtid_executed value on 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_validating field 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 ONLINE status when identifying the common set of transactions that have been applied on all members. When a joining member enters ONLINE state 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)

• Group 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 error management and member status handling has now been corrected to prevent this situation. (Bug #31401797)

• Microsoft Windows: On Windows, build targets could fail if the build was on a file system root, such as R:/. (Bug #31315467)

• JSON: JSON_OBJECT() did not always perform proper checking for NULL values. (Bug #31393934)

• The new WITH_SYSTEMD_DEBUG CMake 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 ts key by the log_sink_json JSON-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 WHERE clause and the HAVING clause 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 ref access. (Bug #31661309)

• When a value was compared using LIKE with a table column not defined as one of the MySQL string types, the server sometimes did not raise the expected error. (Bug #31659015)

• The 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)

• 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 BY is in use. (Bug #31587575)

• While pushing conditions down to a derived table, a constant condition such as WHERE FALSE or WHERE TRUE was 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 WHERE column > (... IN (SELECT ...)) could sometimes trigger an assertion in the range optimizer. (Bug #31586906)

  References: This issue is a regression of: Bug #30473261.

• It was possible for ANALYZE TABLE to 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 TABLE now 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 SELECT with FOR UPDATE requests 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_NONE due 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 ANY subquery gave an incorrect result when the subquery_to_derived optimizer switch was enabled. (Bug #31566339)

• When FALSE AND condition was simplified as FALSE, temporary table resources allocated for the condition were not always released afterwards. (Bug #31565009)

• A value equal to ULLONG_MAX could 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 BY was 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)

• Merging during filesort operations could fail to remove duplicates for queries that used DISTINCT. (Bug #31498664, Bug #99900)

• MySQL's internal DYNAMIC_STRING class 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_mutex mishandling 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)

• A LATERAL subquery was incorrectly converted into an antijoin. (Bug #31465717)

• NATURAL JOIN evaluation 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 HAVING when the HAVING clause was constant and evaluated as true even though a view with HAVING as part of its definition should not be updatable. (Bug #31429865)

• Privilege requirements were checked incorrectly for the INFORMATION_SCHEMA.USER_ATTRIBUTES table. (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 (SELECT const FROM table WHERE column=FROM_UNIXTIME(value) was not handled correctly. (Bug #31425664)

• In some cases, key_hint handling was improperly applied to derived and internal temporary tables. (Bug #31424455)

• Re-execution of prepared INSERT statements could fail for inserts through a view. (Bug #31417951)

• JSON scalar evaluation could enter an infinite loop. (Bug #31406724)

• The user_attributes column in mysql.user table 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)

• Using the authentication_ldap_simple authentication 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 LATERAL was 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)

• Enabling the create_admin_listener_thread system variable could cause a server exit during startup. (Bug #31335279)

• After ALTER TABLE to 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 USE statement to result in an unexpected server exit. (Bug #31311312)

• When a filesort sorted a buffer and LIMIT was 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 TIMESTAMP if it included a timezone offset. (Bug #31239157)

• The keyring_hashicorp keyring plugin did not limit the size of keys for key operations. (Bug #31205715)

• Configuring with -DWITH_ZSTD=system failed for older versions of the zstd library. CMake now checks the zstd version and requires at least 1.0.0 for compilation, 1.2.0 to run compression checks. (Bug #31174920, Bug #99241)

• In some cases, a SELECT that 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, ROUND() and 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 BIGINT.

  • For any floating-point type or any non-numeric type, the return type is DOUBLE.

  • For DECIMAL, the return type is also DECIMAL.

  • 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)

• A SELECT ... FOR SHARE statement now only requires the SELECT privilege. Previously, the SELECT privilege was required with at least one of the DELETE, LOCK TABLES, or UPDATE privileges. (Bug #31096384, Bug #99101)

• A semijoin strategy was chosen for the join of a correlated subquery having a LIMIT clause and requiring a row other than the first, which caused the LIMIT clause to be ignored and invalid rows to be returned. Now, when LIMIT used 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 read_only or super_read_only were 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 BY did not work as expected for queries with ROLLUP in which window functions were also used. (Bug #31073133)

• Some INSERT statements were not handled correctly. (Bug #31072198)

• Date interval calculations checked for overflow but not underflow. Now they check for both. (Bug #31054071)

• If an XA prepared transaction rollback XID was incorrectly formatted, the transaction remained in recovered state for XA COMMIT and XA ROLLBACK statements (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)

• When the --local option was given, mysqlimport mishandled the MYSQL_OPT_LOAD_DATA_LOCAL_DIR option for mysql_options() so that it had no effect. (Bug #31001550)

• Certain prepared statements could cause an unexpected server exit. (Bug #30943963)

• OPTIMIZE TABLE for MyISAM tables could cause table size to increase and query performance to decrease. REPAIR TABLE for MyISAM tables could cause the Table is already up to date status produced by a previous OPTIMIZE TABLE to be lost. (Bug #30869674, Bug #98511, Bug #29755517)

• mysqlpump object validation included objects in excluded databases. (Bug #30819012)

• Inserting a TIMESTAMP value 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_mode setting. (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 TIME value with NULL in 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 BY queries were not executed correctly when sort_buffer_size and max_sort_length were 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)

• When explicit_defaults_for_timestamp was disabled and a NULL was inserted into a generated column declared as TIMESTAMP 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)

• The SET_VAR hint did not accept a floating point value specified as a system variable setting. (Bug #29349748)

• Previously, when NULL was used as the format argument to STR_TO_DATE(), irrelevant warnings were printed. Now, when NULL is passed to it, the function returns NULL. (Bug #27265863)

• In some cases, incorrect use of IS NULL generated multiple warnings about invalid arguments. (Bug #27264652)

• Resolving an ORDER BY column that referred to a SELECT list column from a derived table was not performed correctly when executing certain prepared statements. (Bug #26808862)

• When using EXPLAIN on a multi-table UPDATE statement 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 InnoDB about 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_indexes optimizer switch was not set to OFF. (Bug #100024, Bug #31550839)

• Regular expression functions such as REGEXP_LIKE() yielded inconsistent results with binary string arguments. These functions now reject binary strings with an error. (Bug #98950, Bug #98951, Bug #31031886, Bug #31031888)

• 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 id of type INT was 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 < 0 and 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 UPDATE statement. (Bug #80655, Bug #22891840)