The maximum size of FIDO authenticator data was increased. (Bug #33655192)

Important Note: The server now uses utf8mb3 rather than utf8 in the following cases:

(Bug #33385252, Bug #33395007)

The server now uses utf8mb3 in place of the alias utf8 for character set names when populating data dictionary tables from built-in character sets. This affects the display of character set and related information in the MySQL Information Schema tables listed here:

This change also affects the output of the SQL SHOW CHARACTER SET, SHOW COLLATION, SHOW CREATE DATABASE, and SHOW CREATE TABLE statements. (Bug #30624990)

InnoDB: After addressing the associated issues, the C4100, C4127, C4245 and C4389 MSVC++ level 4 compiler warnings were re-enabled. (Bug #33437498, Bug #33571677)

GCC 11 is now a supported compiler for building MySQL on EL7 or EL8. This compiler is available in the devtoolset-11 (EL7) or gcc-toolset-11 (EL8) package. It is also recommended to use GCC 11 when building third-party applications that are based on the libmysqlclient C API library. (Bug #33730302)

The server could not be compiled with Bison 3.8.1 or newer. (Bug #33488047)

Important Change: Previously, MySQL allowed arbitrary delimiters and an arbitrary number of them in TIME, DATE, DATETIME, and TIMESTAMP literals, as well as an arbitrary number of whitespaces before, after, and between the date and time values in DATETIME and TIMESTAMP literals. This behavior is now deprecated, and you should expect it to be removed in a future version of MySQL. With this release, the use of any nonstandard or excess delimiter or whitespace characters now triggers a warning of the form Delimiter 'char' in position pos in datetime value 'value' at row rownum is superfluous and is deprecated, followed by Please remove, or in cases in which a suitable replacement can be suggested, Prefer the standard 'replacementchar'.

A deprecation warning is returned only for the first nonstandard delimiter or whitespace character encountered in the literal value. An example is shown here:

mysql> SELECT DATE"2020/02/20";
+------------------+
| DATE"2020/02/20" |
+------------------+
| 2020-02-20       |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4095
Message: Delimiter '/' in position 4 in datetime value '2020/02/20' at row 1 is
deprecated. Prefer the standard '-'. 
1 row in set (0.00 sec)

Such warnings are not elevated to errors in strict mode.

For more information and examples, see String and Numeric Literals in Date and Time Context. (WL #13601)

Replication: The replica_parallel_type system variable is now deprecated; whenever this variable is read or set, or the equivalent option is set in my.cnf, the server now issues a deprecation warning, as shown here:

mysql> SELECT @@replica_parallel_type; SHOW WARNINGS\G
+-------------------------+
| @@replica_parallel_type |
+-------------------------+
| LOGICAL_CLOCK           |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: '@@replica_parallel_type' is deprecated and will be removed in a future
release. 
1 row in set (0.00 sec)

You should expect this variable and the equivalent server option --replica-parallel-type to be removed in a future MySQL release.

The myisam_repair_threads system variable and myisamchk --parallel-recover option are deprecated; expect support for both to be removed in a future release of MySQL.

Values other than 1 (the default) for myisam_repair_threads produce a warning. (WL #14937)

The server system variables query_prealloc_size and transaction_prealloc_size are now deprecated, and setting either or both of these no longer has any effect in the MySQL server. Expect them to be removed in a future MySQL release.

For more information, see the descriptions of these variables in the documentation (Server System Variables). (WL #13720)

References: See also: Bug #26940369.

The --abort-slave-event-count and --disconnect-slave-event-count options for mysqld, previously used in testing, and not normally required in a production setting, have been deprecated. Expect them to be removed in a future version of MySQL. (WL #14526)

Aggregate functions based on expressions comparing values with a NULL were not ignoring the NULL correctly. (Bug #33624777, Bug #105762)

When an aggregation function is evaluated during optimization, it is presumed that the WHERE condition has already been evaluated; thus, it can be removed. Before the removal, the optimizer verifies whether the WHERE condition has any table-independent conditions by making a call to make_cond_for_table(). When a condition was considered expensive (for example, it used a stored procedure), it was incorrectly assumed that there was a table-independent condition. We fix this by excluding expensive conditions in the call to make_cond_for_table().

In addition, a constant condition which was expensive to evaluate, such as f() = 1 where function f() did not use any tables, often led to incorrect results. In most cases, if a condition is constant for execution, it is evaluated when optimizing the WHERE condition, skipping it if it is considered too expensive. The present issue arose due to skipping the condition while optimizing an implicitly grouped query. To avoid this, we now evaluate such conditions prior to aggregation. (Bug #33305617)

Columns used by the DEFAULT() function were not marked internally as READ when fixing its arguments. (Bug #33142135)

If the column of a table being loaded using LOAD DATA was used in the WHERE clause of a subquery used inside a SET statement, the column was reported as unknown. (Bug #33714885)

Aggregated queries that performed an index lookup (eq_ref) could in some cases return wrong results. This was seen when the execution plan used streaming aggregation instead of aggregation in temporary tables.

We fix this by disabling eq_ref caching below aggregate nodes. (Bug #33491183)

References: This issue is a regression of: Bug #100614, Bug #31790217.

The derived materialized table condition pushdown optimization can now be used with most unions. This means that an outer WHERE condition can now be pushed down to every query block of the query expression of the materialized derived table or view.

Suppose we create tables t1 and t2, and then the view v based on these two tables, using the SQL statements shown here:

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  c1 INT, 
  KEY i1 (c1)
);

CREATE TABLE t2 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  c1 INT, 
  KEY i1 (c1)
);

CREATE OR REPLACE VIEW v AS
     SELECT id, c1 FROM t1
     UNION ALL
     SELECT id, c1 FROM t2;

Now, when the query SELECT * FROM v WHERE c1 = 12 is executed, the condition c1 = 12 is pushed down to both query blocks of view v, as shown here in the output of EXPLAIN:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM v WHERE c1 = 12\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on v  (cost=1.26..2.52 rows=2)
    -> Union materialize  (cost=2.16..3.42 rows=2)
        -> Covering index lookup on t1 using i1 (c1=12)  (cost=0.35 rows=1)
        -> Covering index lookup on t2 using i1 (c1=12)  (cost=0.35 rows=1)

1 row in set (0.00 sec)

This can now be done for most UNION queries. For exceptions, and additional information, see Derived Condition Pushdown Optimization. (Bug #24012, Bug #36802, Bug #106006, Bug #11746156, Bug #11748590, Bug #13650627, Bug #30587347, Bug #33318096, Bug #33738597, WL #13730)

New Performance Schema instrumentation collects information about whether a query was processed on the PRIMARY or SECONDARY engine, where the PRIMARY engine is InnoDB and the SECONDARY engine is HeatWave. This instrumentation is intended for use with MySQL Database Service with HeatWave.

Two new Performance Schema table columns were added:

An execution_engine column was also added to the sys.processlist and sys.x$processlist views in the MySQL sys Schema. (WL #14346)

For platforms on which OpenSSL libraries are bundled, the linked OpenSSL library for MySQL Server has been updated to version 1.1.1n from 1.1.1l. Issues fixed in OpenSSL are described at https://www.openssl.org/news/cl111.txt and at http://www.openssl.org/news/vulnerabilities.html. (Bug #33840722, Bug #33970835)

An IF NOT EXISTS option is now supported for the statements CREATE FUNCTION, CREATE PROCEDURE, and CREATE TRIGGER.

For CREATE FUNCTION, when used to create a stored function, and for CREATE PROCEDURE, this option prevents an error from occurring if there is already a routine having the same name. For CREATE FUNCTION, when used to create a loadable function, the option prevents an error in the event there already exists a loadable function having that name. For CREATE TRIGGER, the option prevents an error from occurring if there is already a trigger with the same name, on the same table, and in the same schema. This is intended to improve ease of use for these statements in scripting, rapid (re)deployment, replication, and other instances of potential repeated use.

This enhancement makes the syntax of these statements more consistent with that of CREATE DATABASE, CREATE TABLE, CREATE USER, and CREATE EVENT, all of which already support IF NOT EXISTS. This also provides a complement to the IF EXISTS option already supported by DROP PROCEDURE, DROP FUNCTION, and DROP TRIGGER.

For more information, see Stored Objects, as well as Function Name Resolution. See also Replication of CREATE TABLE ... SELECT Statements. (Bug #15287, Bug #11745440, WL #14722)

References: See also: Bug #33301931.

Null pointers now are checked to reduce heap-use-after-free errors under Valgrind with the --async-client option. (Bug #33702755)

Group Replication: Group replication in some scenarios faced problems because it was not possible to commit an XA transaction prepared on another connection. To address such issues, MySQL now supports detached XA transactions; once prepared, an XA transaction is no longer connected to the current session. This happens as part of executing XA PREPARE. The prepared XA transaction can be committed or rolled back by another connection, and the current session can then initiate another XA or local transaction without waiting for the prepared XA transaction to complete.

Support for this feature can be disabled, and the classic behavior reinstated, by setting the xa_detach_on_prepare system variable introduced in this release to OFF. The default is ON, which is recommended, especially in a replication setting.

You should be aware that, when detached XA transaction support is enabled (xa_detach_on_prepare = ON), it is not possible to use temporary tables within XA transactions.

For more information, see XA Transaction States, as well as Server Instance Configuration. (Bug #100163, Bug #31599926, WL #14700)

A difference in format between Unix socket lock files for classic MySQL protocol and X Protocol meant that the server could not start if the file for the other protocol was present. For the benefit of instances where it is not possible to remove the file manually, such as MySQL Database Service instances, the protocols now use the same format for the file. (Bug #31468581)

Incompatible Change: MySQL Server now has the capability to store securely persisted system variable values containing sensitive data such as private keys or passwords, and to restrict viewing of the values. No MySQL Server system variables are currently marked as sensitive, but the new capability allows system variables containing sensitive data to be persisted securely in the future. A keyring component must be enabled on the MySQL Server instance to support secure storage for persisted system variable values, rather than a keyring plugin, which do not support the function.

In the operating system file where persisted system variables are stored, the names and values of sensitive system variables are stored in an encrypted format, along with a generated file key to decrypt them. The generated file key is in turn encrypted using a master key that is stored in a keyring. After upgrading to MySQL 8.0.29, the format of the mysqld-auto.cnf option file remains the same until the first time a SET PERSIST or SET PERSIST ONLY statement is issued, and at that point it is changed to a new format, even if the system variable involved is not sensitive. An option file using the new format cannot be read by older releases of MySQL Server.

The new system variable persist_sensitive_variables_in_plaintext controls whether the server is permitted to store the values of sensitive system variables in an unencrypted format, if keyring component support is not available at the time when SET PERSIST is used to set the value. The default setting, ON, encrypts the values if keyring component support is available, and persists them unencrypted (with a warning) if it is not. This setting also allows the server to start if encrypted system variable values cannot be decrypted, in which case their defaults are used. When persist_sensitive_variables_in_plaintext is set to OFF, which is the most secure setting, sensitive system variable values cannot be persisted if keyring component support is unavailable, and the server does not start if encrypted system variable values cannot be decrypted.

The values of sensitive system variables are also protected when they are handled. If a SET statement is issued for a sensitive system variable, the query is rewritten to replace its value with redacted before it is logged to the general log and audit log. The new privilege SENSITIVE_VARIABLES_OBSERVER allows a holder to view the values of sensitive system variables in the Performance Schema tables global_variables, session_variables, variables_by_thread, and persisted_variables, to issue SELECT statements to return their values, and to track changes to them in session trackers for connections. Users without this privilege cannot view those system variable values. (WL #13469)

InnoDB: To improve code quality and facilitate debugging, instances of #define in the InnoDB sources were replaced by constexpr specifiers or inline functions. (WL #14680)

InnoDB: InnoDB now supports ALTER TABLE ... DROP COLUMN operations using ALGORITHM=INSTANT.

Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. Table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by DDL operations that support it.

Prior to MySQL 8.0.29, an instantly added column could only be added as the last column of the table. From MySQL 8.0.29, an instantly added column can be added to any position in the table.

Instantly added or dropped columns create a new row version. Up to 64 row versions are permitted. A new TOTAL_ROW_VERSIONS column was added to the INFORMATION_SCHEMA.INNODB_TABLES table to track the number of row versions.

For more information about DDL operations that support ALGORITHM=INSTANT, see Online DDL Operations. (WL #13899)

Replication: Automatic purging of binary log files by the server is now controlled using the binlog_expire_logs_auto_purge system variable introduced in this release. By default, automatic purging is enabled (binlog_expire_logs_auto_purge set to ON); to disable it, set the value of this variable to OFF.

The interval to wait before purging is controlled by binlog_expire_logs_seconds and expire_logs_days. Setting both of these system variables to 0 stops automatic purging from taking place, even when binlog_expire_logs_auto_purge is set to ON. (WL #14930)

Microsoft Windows: Added jemalloc support on Windows, and enabled it for official MySQL binaries. The new WITH_WIN_JEMALLOC CMake option accepts a directory containing jemalloc.dll that's copied to the same directory as mysqld.exe and/or mysqld-debug.exe and utilizes it for memory management operations. Standard memory functions are used if jemalloc.dll is not found or does not export the required functions. An INFORMATION level log message records whether or not jemalloc is found and used. (WL #14633)

The new clone_delay_after_data_drop variable permits specifying a delay period immediately after removing existing data on the recipient MySQL Server instance at the start of a remote cloning operation. The delay is intended to provide enough time for the file system on the recipient host to free space before data is cloned from the donor MySQL Server instance. Certain file systems free space asynchronously in a background process. On these file systems, cloning data too soon after dropping existing data can result in clone operation failures due to insufficient space. The maximum delay period is 3600 seconds (1 hour). The default setting is 0 (no delay). (Bug #32826134, WL #14857)

The group_replication_set_as_primary function appoints a specified group member as the new primary and overrides the election process. Previously, the function waited for all active transactions on the existing primary to end, including incoming transactions after the function was used, before making the current primary read only and changing to the new primary. There was no upper limit to the wait time.

An optional timeout parameter is now available to let you set a timeout for running transactions. You can set a timeout from 0 seconds (immediately) up to 3600 seconds (60 minutes) for transactions that are running when you use the function. There is no default setting for the timeout, so if you do not set it the former behavior of the function still applies, with no upper limit to the wait time. When the timeout expires, for any transactions that did not yet reach their commit phase, the client session is disconnected so that the transaction does not proceed. Transactions that reached their commit phase are allowed to complete. When you set a timeout, it also prevents new transactions starting on the primary from that point on. Explicitly defined transactions (with a START TRANSACTION or BEGIN statement) are subject to the timeout, disconnection, and incoming transaction blocking even if they do not modify any data. To allow inspection of the primary while the function is operating, single statements that do not modify data, as listed in Permitted Queries Under Consistency Rules, are permitted to proceed. (WL #14585)

The keyring_okv keyring plugin, which uses the Key Management Interoperability Protocol (KMIP) to communicate securely, is used with MySQL’s keyring service to store keyring data in a KMIP-compatible back end keyring storage product such as Oracle Key Vault, Gemalto SafeNet KeySecure Appliance, Townsend Alliance Key Manager, and Entrust KeyControl. The plugin now allows you to specify more than one standby server to provide backup connections to the back end keyring storage product if the primary server is unavailable. You can add up to 64 standby servers by editing the okvclient.ora file to specify the IP addresses and port numbers of the servers as a comma-separated list in the value of the STANDBY_SERVER variable. The plugin iterates over the standby servers until it can establish a connection, with a 20-second wait for each connection attempt. You should therefore ensure that the list of standby servers is kept short, accurate, and up to date, and servers that are no longer valid are removed, as they can significantly affect the keyring_okv plugin’s connection time and therefore the server startup time. (WL #14363)

Isolation of access to system variables is now better enforced in the server code that parses, resolves, and executes SQL statements accessing or updating such variables. (WL #14529)

MySQL Server now supports SSL session reuse by default with a timeout setting to control how long the server maintains a session cache that establishes the period during which a client is permitted to request session reuse for new connections. All MySQL client programs support session reuse. For server-side and client-side configuration information, see Reusing SSL Sessions.

In addition, C applications now can use the C API capabilities to enable session reuse for encrypted connections (see SSL Session Reuse). (WL #13075)

InnoDB: A failure occurred when attempting to purge undo records for a table with an instantly added column. (Bug #33924532)

InnoDB: High-priority transactions were not permitted to stop waiting when interrupted or to timeout while waiting for a lock, preventing deadlocks from being resolved. In cases where the blocking transaction is also high-priority, high-priority transactions are now permitted to stop waiting when interrupted or timeout when exceeding the lock wait timeout period. If a blocking transaction is not high-priority, high-priority transactions wait for the blocking transaction to release its locks. (Bug #33856332)

InnoDB: The AIO synchronization queue used on Windows was removed. The synchronous file I/O read-write function (SyncFileIO::execute) was revised to handle files opened for both normal and overlapped I/O, as it does on Linux. (Bug #33840645)

InnoDB: Table version metadata was not reset after truncating all partitions of a table with an instantly added column. (Bug #33822729)

InnoDB: The srv_error_monitor_thread() function, which prints warnings about semaphore waits, failed to handle a long semaphore wait as expected. To address this issue, a blocking call was moved to a more appropriate location. Related monitor thread code was simplified and improved, and missing shutdown signals were added for several server threads.

Enabling and disabling of the standard monitor by InnoDB is now performed independently of the user-settable innodb_status_output variable. This change addresses an issue in which the monitor was enabled by InnoDB in a particular scenario but not set back to its previous value. Thanks to Yuhui Wang for the contribution. (Bug #33789526, Bug #93878)

InnoDB: Valgrind testing identified an off-by-one error in rec_convert_dtuple_to_rec_old() in the InnoDB sources. (Bug #33784672)

InnoDB: The UNIV_DEBUG variant of the mem_heap_alloc() function in the InnoDB sources was modified to improve Valgrind error detection. (Bug #33783709)

InnoDB: A fast shutdown did not wait for all active I/O operations to finish before closing all files. (Bug #33768584)

InnoDB: A Clang warning reported an incorrectly placed @return command. (Bug #33734011)

InnoDB: Values of the new record locks array (m_prebuilt->new_rec_locks[]) were not properly synchronized when switching between partitions, causing an assertion failure due to locks being freed or not freed as expected. (Bug #33724166)

InnoDB: A race condition in the function that updates the double write buffer when a write request is completed caused a long semaphore wait error. (Bug #33712370)

InnoDB: A function wrongly assumed that changing a record in an indexed column always requires creating a new record in the secondary index, resulting in an lock-related assertion failure. To address this and other similar cases, the lock_rec_convert_impl_to_expl() function that converts an implicit record lock to an explicit record lock is now used only when an implicit record lock is actually held. (Bug #33657235)

InnoDB: A number of Doxygen issues in the InnoDB sources were addressed. (Bug #33603036)

InnoDB: A missing null pointer check for an index instance caused a failure. (Bug #33600109)

InnoDB: Starting the server after an incremental backup failed with an error indicating that page tracking was unusable. (Bug #33521528)

InnoDB: An assertion failure was raised during InnoDB recovery. The failure was due to a server exit that occurred after pages were freed in a local mini-transaction before the free list and index list could be reset. The free list and index list, which spanned the freed pages, were traversed during recovery. (Bug #33454557)

InnoDB: The btr_insert_into_right_sibling() B-tree function, which is inserts a tuple into the right sibling page when the cursor is at the end of a page, caused an ACID violation due to an incorrect lock function call. (Bug #33405696)

InnoDB: When using COMPACT or REDUNDANT row format, it was possible to create a table that exceeded the maximum row size, which could eventually result in 'Row size too large' errors when inserting data. BLOB prefixes were not included in the record size check performed during the CREATE TABLE operation. (Bug #33399379)

InnoDB: A function that retrieves the master encryption key (get_master_key()) tried to acquire the master key ID mutex (master_key_id_mutex), which was not yet initialized. (Bug #33067891)

InnoDB: A redundant and costly check was removed from the lock_sec_rec_read_check_and_lock lock system function, which is used to place a lock on a secondary index record. (Bug #33059387)

InnoDB: The TempTable storage engine did not properly handle a file-full error. (Bug #32929392)

InnoDB: A DDL log error encountered during an ALTER TABLE ... ALGORITHM=COPY operation was not handled, causing a failure during a subsequent ALTER TABLE ... ALGORITHM=INPLACE operation. (Bug #32716838)

InnoDB: Purge threads processed undo records of an encrypted table for which the tablespace was not loaded, causing a failure. (Bug #32586721)

InnoDB: A thread posted an asynchronous I/O operation and closed itself, resulting in an operating system file operation error. (Bug #30567295)

InnoDB: Incorrect AUTO_INCREMENT values were generated when the maximum integer column value was exceeded. The error was due to the maximum column value not being considered. The previous valid AUTO_INCREMENT value should have been returned in this case, causing a duplicate key error. (Bug #87926, Bug #26906787)

InnoDB: Transaction lock priority was modified so that a transaction holding a shared lock and waiting to upgrade to an exclusive lock is prioritized ahead of a another transaction waiting for an exclusive lock on the same row. In other words, the transaction that already holds a shared lock is granted an exclusive lock first. Some other transaction holding a shared lock on the same row can still prevent an exclusive lock from being a granted in this scenario.

The lock_rec_find_set_bit and lock_rec_has_expl functions in the transaction lock system sources were optimized. (Bug #21356, Bug #11745929)

Partitioning: In some cases, establishing a connection to MySQL server could fail if the .ibd file for a partition was missing. (Bug #33459653)

Partitioning: Following work done in MySQL 8.0.17 to cause all NOT IN and NOT EXISTS subqueries to be transformed into antijoins, the inner part of a nested outer join produced by this transformation was not considered while pruning table partitions. (Bug #33060953)

Replication: In a replication group with group_replication_consistency=AFTER set, if a new primary was elected and then the previous primary was restarted, the new primary left the group with an error message. The handling of delayed view changes and transaction prepares has been adjusted to prevent this situation. (Bug #33755920)

Replication: A deadlock caused when the network provider was being stopped could cause STOP GROUP_REPLICATION to wait indefinitely on some group members. (Bug #33044886)

Group Replication: Group Replication checked too late in the auto-rejoin process that a rejoining member was the only type of instance allowed to enter a group while in an error state. The issue has now been fixed. (Bug #33615493)

Group Replication: Group Replication could log a warning message about a member joining while a group configuration operation was running, in situations where there was no joining member, just a view change. The message is now only logged when appropriate. (Bug #33378364)

Group Replication: Group Replication now logs operating system errors returned when there is a problem connecting to the local XCom instance, so it is easier to resolve issues such as a firewall blocking the connection. (Bug #33189767, Bug #104523)

Group Replication: To help prevent a primary election from hanging, Group Replication stops waiting for a confirmation from any members that leave the group during the election. If all the members for which confirmation was pending leave, Group Replication declares that the primary election completed successfully. Previously, if the primary member was one of the members that left, a primary change was incorrectly stated as having happened when that had not been able to take place. Now, if the primary member leaves during an election, Group Replication declares the primary election as an unsuccessful election with no primary change. (Bug #33059773)

Microsoft Windows: On Windows, added WinFlexBison support. (Bug #33788290)

On EL6/EL7, a mysql-community-server-debug installation was not obsoleted by installing commercial packages. (Bug #33956760)

MySQL Server would not build EL7 on ARM when building against shared libraries that are copied by the cmake build system, such as a non-system OpenSSL. Now --page-size is set as reported by getconf. (Bug #33904267)

An anonymous user with the PROCESS privilege could not select rows from the processlist table. (Bug #33869388)

The range optimizer did not take into account the session memory limit. (Bug #33869004)

When sorting subqueries, the same filesort operation may be executed many times, with different input, and so the longest add-on column may change between executions, but the buffer for this column was not reallocated when necessary. Now, when the previously allocated buffer is sufficiently large, we reuse it, otherwise we allocate a new one. (Bug #33865094)

An invalid result was obtained from a common table expression that was materialized and used in multiple query blocks, where, in the first query block that used the CTE, there were multiple possible key definitions, which caused the internal function JOIN::finalize_derived_keys() to move the definition of the key used into position zero, and the second query block using the CTE manipulated an index that occupied the same position as the original position for the key chosen for the first query block. This happened as an unintended side effect of work done in MySQL 8.0.22 to prepare DML statements once only.

For more information, see WITH (Common Table Expressions). (Bug #33856374)

Improved error handling when executing stored procedures and stored functions. (Bug #33851256)

A trigger that selected from a view did not always yield the expected result. (Bug #33847722)

Computations performed while loading time zone information require negative values of my_time_t. Normally, that type's minimum value is zero, corresponding to the start of the UNIX Epoch (1970-01-01 00:00:00, with no time zone offset), which is reflected in the constant MYTIME_MIN_VALUE. This minimum value did not work correctly for setting up the TIME_ZONE_INFO data structures in all cases, since some time zone offsets are negative. This fix permits negative values for my_time_t while populating the time zone information database cache. (Bug #33837691)

If a MySQL instance stopped unexpectedly or was restarted shortly after a SET PERSIST statement was used to record system variable settings, the configuration file mysqld-auto.cnf could be left empty, in which case the server restart could not proceed. The persisted system variables are now written to a backup file, which is only renamed to mysqld-auto.cnf after the success of the write has been verified, leaving the original mysqld-auto.cnf file still available. On a restart, if a backup file with valid contents is found, the server reads from that file. Otherwise the mysqld-auto.cnf file is used and the backup file is deleted. (Bug #33830493)

filesort did not always check the lengths of string values properly when calculating sort keys. (Bug #33830073)

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

A primary key comprising the GROUP_ID and MEMBER_ID columns was added to the mysql.firewall_membership table. (Bug #33824544)

A fix made in MySQL 8.0.20 for an inconsistency in the rows matched value when updating an updatable view having WITH CHECK OPTION handled this issue for an update of a single table only. In this release, we fix the issue for multi-table UPDATE statements as well. (Bug #33815426)

References: See also: Bug #30158954.

The linked ProtoBuf library for MySQL Server has been updated to version 3.19.4. Issues fixed in the new Protobuf version are described at https://github.com/protocolbuffers/protobuf/releases. (Bug #33813846)

It was possible to insert invalid characters into a utf32 column. (Bug #33810558)

Reinstalling the MySQL Enterprise Firewall plugin caused a failure. The failure was due to a call to update a previously persisted firewall system variable before the plugin was fully initialized. (Bug #33809478)

Hash joins using VARCHAR or DECIMAL join columns returned an excessive number of rows. An example using VARCHAR is shown here:

# Create and populate table
CREATE TABLE t(a VARCHAR(10), b VARCHAR(10));
INSERT INTO t VALUES ('x', 'xx'), ('xx', 'x');

# No indexes, so uses a hash join
SELECT * FROM t AS t1, t AS t2 WHERE t1.a = t2.a AND t1.b = t2.b;

Prior to the fix, the query just shown returned four rows; now it returns two rows as expected. (Bug #33794977)

The INFORMATION_SCHEMA.KEY_COLUMN_USAGE table did not list invisible key columns. (Bug #33781534)

The server did not perform proper cleanup after accessing a view created from a view. (Bug #33777821)

In a grouped query using WITH ROLLUP and a window function which ordered on an aggregate already present in the SELECT list, an extra (and unnecessary) aggregate was added as a hidden column when resolving the window's ordering clause. (Bug #33769911)

The data masking mask_ssn() function returned a 'String argument width too large' error for a valid non-constant value (data read from a database row as opposed to a hard-coded value). Additionally, validation was not performed for zero-length constant and non-constant values (empty strings) or for constant values that are too small. These issues were addressed for all data masking functions that string inputs with length boundaries. (Bug #33759276)

ROLLUP was not handled correctly in some cases with ORDER BY and a window function. (Bug #33753245)

Corrected a number of issues in Doxygen comments. (Bug #33734001, Bug #33734035, Bug #33734062, Bug #33734075, Bug #33734104, Bug #33734117, Bug #33734129, Bug #33734143, Bug #33734155, Bug #33734181, Bug #33734188, Bug #33734206)

Statements that cannot be parsed (due, for example, to syntax errors) are no longer written to the slow query log. (Bug #33732907)

The message describing MySQL Enterprise Thread Pool’s initial configuration is now issued as a SYSTEM message rather than informational, so that it is visible at the default logging level. (Bug #33729821)

A pre-locking optimization related to session tracking caused recursive locking in some scenarios. (Bug #33728209)

A union involving an IN subquery was not always handled properly when it also used an ORDER BY clause. (Bug #33725507)

The mysqlpump client utility could stop unexpectedly when a password prompt was requested using command line options. If an error is encountered during this process an appropriate error message is now returned before the client exits. (Bug #33688141)

Implemented standard package policy to ship separate debuginfo RPMs for the following platforms: SLES/openSUSE, and community EL6 and EL7. Commercial versions of EL6 and EL7 are now built with the RelWithDebInfo build type instead of Debug, which greatly decreases their size. (Bug #33663811, Bug #33684418, Bug #33664929)

It was not possible to run mysqld with the trace log enabled. (Bug #33653824)

When the --fido-register-factor option was used at startup for FIDO device registration, the mysql client could use incorrect syntax in the ALTER USER statement, resulting in a failure. The issue has now been fixed. (Bug #33650498)

When searching a SET column value, a user variable passed as the first argument to FIND_IN_SET() did not produce the same result as a constant value used in the same manner.

Example: Consider the table t1 created and populated as shown here:

CREATE TABLE t1 (c1 SET('a', 'b', 'c', 'd'));

INSERT INTO t1 (c1) VALUES ('a, c, d'), ('c');

The values shown for var and str should be the same in both rows of the output from the following query, but were not:

SET @a = 'c';

SELECT FIND_IN_SET(@a, c1) AS var, FIND_IN_SET('c', c1) AS str FROM t1;

Now we make sure in such cases to return the position of the match within the set used for the column definition whether the value sought is a constant or a column value. (Bug #33635637)

A SET PASSWORD operation using an authentication method that does support it produced a warning instead of an expected error. (Bug #33635445)

If the plugin directory was not specified at MySQL Server startup, and a user attempted FIDO device registration, the mysql client stopped unexpectedly. The situation is now handled and an appropriate error is reported. (Bug #33631144)

A change made in MySQL 8.0.27 moved the EXPLAIN output for composite access paths including filter and sort from inside the table path to the MATERIALIZE access path, but did not include INDEX_RANGE_SCAN among the composite access paths. This could in some cases result in undefined behavior. (Bug #33611545)

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

It was not possible to revoke the DROP privilege on the Performance Schema. (Bug #33578113)

The internal CreateIteratorFromAccessPath() function has been rewritten so that it is no longer employs recursion; this is expected to reduce stack usage significantly when executing queries. (Bug #33569770)

The mysql client could stop unexpectedly when attempting FIDO device registration with a challenge-response error. The issue has now been fixed. (Bug #33568944)

An INFORMATION_SCHEMA.FILES or INFORMATION_SCHEMA.TABLES query executed by prepared statement or stored function returned stale data. Cached data was reported after the data had changed. (Bug #33538106, Bug #105450)

A query returned an incorrect result when it met all of the following conditions:

Given an empty table t with column c1, the statement INSERT INTO t SELECT MAX(t.c1), RAND(0) AS x FROM t provides an example of such a query; by inserting into the same table, we ensure that the query plan aggregates from a temporary table. The temporary table has a column for c1, and the (single) result row picks c1 from the first row of the temporary table. If the temporary table was empty, there was no first row, and c1 became 0; this led to the insertion of (NULL, 0) rather than, for example, (NULL, 0.155) into t.

We fix this by not storing such columns in the temporary table. (Bug #33535379)

References: See also: Bug #32384355.

Following changes in password handling in MySQL 8.0.27, if the mysql client was interrupted with Ctrl + C, a password prompt could be issued requiring the user’s password in order to action the interrupt. The password is now recorded when it is provided in response to a prompt (as well as when it is provided on the command line at startup), so it can be used automatically by the new connection that is established for the interrupt. (Bug #33514253)

The internal keyread flag indicates reading only index entries and not full rows. When this flag was set for an index merge scan during initialization, it was subsequently reset when initializing the individual range scans picked as part of the index merge scan, which indicated reading rows instead of index entries. We fix this by setting the indexes chosen as covering indexes while reading the index entries. (Bug #33499071)

Data copying stalled when cloning page-compressed tables where the compressed data size on the donor host was equivalent to the available disk space on the recipient host. The holes punched on the recipient host were smaller than the page size, resulting in disk fragmentation that limited the amount of space available for cloned data. The process for handling page-compressed tables on the recipient host was modified to reduce fragmentation and improve data copying speed.

To improve the rate of flushing, the clone plugin now uses the fsync flush method when flushing page-compressed tables on file systems that support hole punching. (Bug #33482747)

It was possible, when creating a view, for a subquery to be evaluated during resolution when it was deemed constant. (Bug #33438883)

Nominally, in EXPLAIN FORMAT=TREE output, we write a number of rows only as an integer. For a result of zero, it was difficult to determine whether this was 0.49 or 0.00001, so we add enough precision to get one leading digit in such cases. (Bug #33426283)

VALUES in some instances did not function as specified. (Bug #33414289)

In debug builds, certain combinations of user variables of type MYSQL_TYPE_DOUBLE hit an assert when used in the context of CAST() or CONVERT(). (Bug #33406728)

When setting up multiple equalities, the optimizer added an outer reference when it was not required. (Bug #33394701)

The minimum supported version of the Boost library for MySQL builds has now been raised to 1.77.0. (Bug #33353637)

References: See also: Bug #33052171.

Redundant init and checkout_access_maps function code was removed from the Security_context class. (Bug #33339129, Bug #104915)

The sys schema view schema_unused_indexes did not update correctly.

schema_unused_indexes retrieves unused index information from table_io_waits_summary_by_index_usage, returning no data if the index was used to query a row which is not present in the table.

As of this release, schema_unused_indexes is updated even if the row is not found. (Bug #33302400)

Some queries using common table expressions were not handled correctly by EXPLAIN. (Bug #33300271)

The server did not obtain index statistics correctly for certain complex queries. (Bug #33270516)

Under certain circumstances, when executing TRUNCATE TABLE performance_schema.status_by_thread; a race condition could occur.

Our thanks to Facebook for their contribution to this fix. (Bug #33165726)

A page cleaner thread timed out as it waited for an exclusive lock on an index page held by a full-text index creation operation on a large table. (Bug #33101844)

For an InnoDB table, a secondary index is extended to include the table's primary key; that is, all columns that are part of the primary key are marked as being part of all secondary indexes. An exception to this is when a secondary index is defined as unique, server does not use the primary key extension. This could lead to problems while setting up the read sets for dynamic range scan. We fix this issue by making sure in the case of a unique seconary index to include all primary key parts in the read set. (Bug #33101025)

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

An existing foreign key constraint that referenced a non-existing table could result in a connection error after the table was created or renamed. (Bug #33054071)

While creating a value list for an empty VALUES during execution of an INSERT statement, a hidden element was added, which subsequently led to an assertion in debug builds. To fix the problem, we now skip any hidden elements when the VALUES list is created.

See INSERT ... ON DUPLICATE KEY UPDATE Statement, for more information. (Bug #32774799)

Aliases to SELECT list items in ORDER BY which were part of expressions were not resolved in the same way as standalone aliases, so that ORDER BY alias was resolved correctly, but ORDER BY function(alias) was not. (Bug #32695670)

An offline keyring migration from an empty or non-existing keyring source (specified by keyring_file_data) appeared to complete successfully, without warning. The keyring migration process now checks for errors when fetching keys. If no keys are found at the specified keyring source, a warning message is written to the error log indicating that the keyring has no keys. (Bug #32327411)

When removing a condition which was always true, a subquery referring to a view was removed even though the subquery was itself referred to elsewhere in the query. (Bug #31946448)

References: See also: Bug #31216115.

An EXPLAIN statement, if used with multiple-table UPDATE statements that contained derived tables, could cause the server to exit. (Bug #31884434)

A memory leak occurred if mysqldump was used on more than one table with the --order-by-primary option. The memory allocated for sorting each table’s rows is now freed after every table, rather than only once. (Bug #30042589, Bug #96178)

mysqld_safe log message textual errors were corrected. Thanks to Bin Wang at China Mobile for the contribution. (Bug #106590, Bug #33903639)

Equivalent queries could return different results because the execution paths differed slightly. For example, Item::val_int_from_string() and Item_string::val_int() should use the same algorithm for string to integer conversion. (Bug #106517, Bug #33881339)

Updated CMake rules to handle the deprecated PermissionsStartOnly systemd option. The alternative executable prefix was added in systemd 231 (July 2016) while PermissionsStartOnly was deprecated in systemd 240 (Dec 2018). The preferred executable prefix is now used where available. (Bug #106468, Bug #33862323)

Following work done in MySQL 8.0.22 to implement condition pushdown for materialized derived tables, an implicitly grouped query with an aggregation function in some cases returned an empty set instead of NULL. (Bug #106414, Bug #33838439)

A prepared and executed query like 'SELECT ?' with a DATE, TIME, DATETIME, or TIMESTAMP as the bound argument could return the wrong column type. The issue is fixed by restoring the previous logic that reprepared such queries to give each dynamic parameter its literal value. (Bug #106352, Bug #33813951)

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

A null-safe comparison (≪=>) did not evaluate correctly for a TIMESTAMP column in a trigger. (Bug #106286, Bug #33790919)

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

When writing DECIMAL values to the hash join buffer, the values were first normalized. This normalization could in some cases unintentionally alter the values, which made the values fall into the wrong hash bucket and caused incorrect results to be returned from the join.

The problem was that the normalization removed leading zeros in a manner which caused some values to lose some of their least significant digits. We fix this by calculating the desired precision and passing the calculated value instead of changing the original value. (Bug #106272, Bug #33787914)

A query having a WHERE condition of the form c NOT BETWEEN NULL AND COALESCE(a, b), having DATETIME columns a, b, and c, with columns a and c created as NOT NULL, did not return correct results. Further investigation showed that, in such cases, when the second or third argument to BETWEEN was NULL, the comparison results were inverted, causing it to evaluate as NULL for FALSE, and FALSE for NULL. (Bug #106267, Bug #33788832)

A NULL-safe comparison between 0 (FALSE) and NULL could return TRUE, even though the implementation responsible for evaluating an object of this type can never result in NULL and should always return FALSE. (Bug #105773, Bug #33630225)

The QUICK_RANGE constructor used the main thread memory root for allocating keys, rather than the same memory root as used for allocating the QUICK_RANGE object itself. This resulted in unexpectedly high memory usage when executing multiple queries using a range check for the query plan. (Bug #105331, Bug #33516707)

References: See also: Bug #28805105, Bug #28857990.

SELECT DISTINCT on a VARCHAR column returned 2 distinct values when the column contained an empty string and a string consisting of a single whitespace character when the TempTable storage engine was used (internal_tmp_mem_storage_engine = TempTable). This behavior was incorrect when using a utf8_bin collation with PAD SPACE; trailing spaces should have been ignored when selecting distinct values.

For temporary tables created using the TempTable storage engine, the two strings were hashed to different values, since the TempTable hashing function always hashed zero-length data to 0. (In the case just described, the empty string was hashed to 0 while the space character from the other string was used to generate a nonzero hash value.)

This is now fixed by removing any special handling for zero-length data in the TempTable storage engine's hashing function.

Our thanks to Brian Yue for the contribution. (Bug #105316, Bug #33506241)

SELECT COUNT(*) using a multi-valued index reported the wrong number of rows. (Bug #104898, Bug #33334928)

The test program main.ssl-big failed with Too many connections.

Our thanks to Facebook for the contribution. (Bug #104377, Bug #33139204, Bug #33165693)

The two types of histograms supported by MySQL, singleton and equi-height, previously stored their collections of buckets in a map and a set, respectively. This release changes both histogram types such that they now store buckets in dynamic arrays instead, which reduces space overhead and speeds up selectivity estimation due to reduced indirection when performing binary search on the buckets.

Because both types of histograms are constructed by inserting buckets in sorted order, and buckets are serialized to JSON in sorted order, there is no additional work involved in switching to a dynamic array. (Bug #104109, Bug #33045204)

An invalid conversion between signed and unsigned variables led to incorrect result from a query of the form SELECT ALL t1.c1,t2.c1 FROM t1,t2 WHERE t1.c1 > t2.c1. (Bug #102025, Bug #32302724)

As of this release, the Performance Schema is collation-aware (Bug #98545, Bug #30881109)

An assert error could occur when inserting data larger than 32 bytes in the ROLE column of setup_actors. As of this release, the ROLE column is increased from CHAR(32) to CHAR(96). (Bug #74678, Bug #19947842)