MySQL now enables administrators to configure user accounts such that too many consecutive login failures due to incorrect passwords cause temporary account locking. The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements. See Password Management. (Bug #27733694, Bug #90169, WL #13515)

ANALYZE TABLE statements now produce read audit events. (Bug #29625461)

Audit log connect events now include any connection attributes passed by the client. Connection attribute logging is supported for new-style XML log file format and JSON format, but not old-style XML format. See Audit Log File Formats. (WL #11378)

Microsoft Windows: On Windows, the minimum version of CMake for builds from the command line is now 3.15. (Bug #30332632, Bug #96954)

New FPROFILE_GENERATE and FPROFILE_USE CMake options are available for experimenting with profile guided optimization (PGO) with GCC. See the cmake/fprofile.cmake in a MySQL source distribution for information about using them. These options have been tested with GCC 8 and 9, and with Clang.

Enabling FPROFILE_USE also enables WITH_LTO (link time optimization). (Bug #30089834, Bug #96314, Bug #30133324, Bug #96410, Bug #30164113, Bug #96486)

Innodb_system_rows_read, Innodb_system_rows_inserted, Innodb_system_rows_deleted status variables were added for counting row operations on InnoDB tables that belong to system-created schemas. The new status variables are similar to the existing Innodb_rows_read, Innodb_rows_inserted, Innodb_rows_deleted status variables, which count operations on InnoDB tables that belong to both user-created and system-created schemas.

The new status variables are useful in replication environments where relay_log_info_repository and master_info_repository variables are set to TABLE, resulting in higher row operation counts on slaves due to operations performed on the slave_master_info, slave_replay_log_info, and slave_worker_info tables, which belong to the system-created mysql schema. For a valid comparison of master and slave row operation counts, operations on tables in system-created schemas can now be excluded using the count data provided by the new status variables.

Thanks to Facebook for the contribution. (Bug #27724674, Bug #90148)

The thread_pool plugin used display widths in definitions for integer columns of Performance Schema tables. This resulted in warnings written to the error log because integer column display widths are now deprecated. (Bug #30597673)

Display width specification for integer data types was deprecated in MySQL 8.0.17, and now statements that include data type definitions in their output no longer show the display width for integer types, with these exceptions:

This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

For DESCRIBE statements and INFORMATION_SCHEMA queries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width. (Bug #30556657, Bug #97680, WL #13528)

Setting the hash_join optimizer switch (see optimizer_switch system variable) no longer has any effect. The same applies with respect to the HASH_JOIN and NO_HASH_JOIN optimizer hints. Both the optimizer switch and the optimizer hint are now deprecated, and subject to removal in a future release of MySQL.

This also fixes an issue whereby SELECT DISTINCT ... WITH ROLLUP did not always return all distinct rows. (Bug #27549694, Bug #30471809)

In MySQL 5.7.14, the mysqlx namespace parameter was introduced for X Protocol's StmtExecute request, replacing the xplugin parameter, which was therefore deprecated. X Plugin continued to support the deprecated xplugin namespace for backward compatibility. In MySQL 8.0.19, the xplugin namespace has now been removed. If the xplugin namespace is used from this release on, an error message is returned as for an unknown namespace. X Plugin's Mysqlx_stmt_execute_xplugin status variable, which counted the number of StmtExecute requests received for the xplugin namespace, is no longer used from MySQL 8.0.19. (WL #13057)

Support for the YEAR(2) data type was removed in MySQL 5.7.5, leaving only YEAR and YEAR(4) as valid specifications for year-valued data. Because YEAR and YEAR(4) are semantically identical, specifying a display width is unnecessary, so YEAR(4) is now deprecated and support for it will be removed in a future MySQL version. Statements that include data type definitions in their output no longer show the display width for YEAR. This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

For DESCRIBE statements and INFORMATION_SCHEMA queries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width.

The (undocumented) UNSIGNED attribute for YEAR is also now deprecated and support for it will be removed in a future MySQL version. (WL #13537)

Error messages regarding crash recovery for XA were revised to indicate XA context to distinguish them from non-XA crash recovery messages. (Bug #30578290, Bug #97743)

Previously, the server returned this error message for attempts to use LOAD DATA LOCAL with LOCAL capability disabled: The used command is not allowed with this MySQL version. This was misleading because the error condition is not related to the MySQL version. The server now returns an error code of ER_CLIENT_LOCAL_FILES_DISABLED and this message: Loading local data is disabled; this must be enabled on both the client and server side. (Bug #30375698, Bug #29377985, Bug #94396)

Previously, loadable functions took no account of the character set or collation of string arguments or return values. In effect, string arguments and return values were treated as binary strings, with the implication that only string arguments containing single-byte characters could be handled reliably.

Loadable function behavior is still the same by default, but the interface for writing loadable functions has been extended to enable them to determine the character set and collation of string arguments, and to return strings that have a particular character set and collation. These capabilities are optional for loadable function writers, who may take advantage of them as desired. See Loadable Function Character Set Handling

Of the loadable functions distributed with MySQL, those associated with the following features and extensions have been modified to take advantage of the new capabilities: MySQL Enterprise Audit, MySQL Enterprise Firewall, MySQL Enterprise Data Masking and De-Identification, MySQL Keyring (the general-purpose keyring functions only), and Group Replication. The modification applies only where it make sense. For example, a function that returns encrypted data is intended to return a binary string, not a character string.

Character-set capabilities for loadable functions are implemented using the mysql_udf_metadata component service. For information about this service, see the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html (search for s_mysql_mysql_udf_metadata and udf_metadata_imp). Source code for the MySQL Keyring functions is available in Community source distributions and may be examined as examples for third-party loadable function writers who wish to modify their own functions to be character set-aware. (WL #12370)

The INFORMATION_SCHEMA contains several new tables that expose role information:

(WL #10895)

A new SECRET key type is available that is intended for general-purpose storage of sensitive data using the MySQL keyring. The keyring encrypts and decrypts SECRET data as a byte stream upon storage and retrieval. The SECRET key type is supported by all keyring plugins. See Supported Keyring Key Types and Lengths. (WL #12859)

The SIGUSR1 signal now causes the server to flush the error log, general query log, and slow query log. One use for SIGUSR1 is to implement log rotation without having to connect to the server (which to flush logs requires an account that has the RELOAD privilege). The server response to SIGUSR1 is a subset of the response to SIGHUP, enabling SIGUSR1 to be used as a more “lightweight” signal that flushes certain logs without the other SIGHUP effects such as flushing the thread and host caches and writing a status report to the error log. See Unix Signal Handling in MySQL. (WL #13689)

Binary packages that include curl rather than linking to the system curl library have been upgraded to use curl 7.66.0. (Bug #30356844)

The zstd library bundled with MySQL has been upgraded from version 1.3.3 to 1.4.3. MySQL uses the zstd library to support connection compression. (Bug #30236685)

For package types for which OpenSSL shared libraries are included, they are now also included under lib/private if the package has private-to-MySQL libraries located there that need OpenSSL. (Bug #29966296)

Important Change: MySQL now supports explicit table clauses and table value constructors according to the SQL standard. These have now been implemented, respectively, as the TABLE statement and the VALUES statement, each described in brief here:

For more information and examples, see TABLE Statement, and VALUES Statement, as well as INSERT ... SELECT Statement, CREATE TABLE ... SELECT Statement, JOIN Clause, UNION Clause, and Subqueries. (Bug #77639, WL #10358)

Previously, it was not possible to use LIMIT in the recursive SELECT part of a recursive common table expression (CTE). LIMIT is now supported in such cases, along with an optional OFFSET clause. An example of such a recursive CTE is shown here:

WITH RECURSIVE cte AS  (
  SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL
  UNION ALL
  SELECT CONCAT("x",cte.a) FROM cte
    WHERE LENGTH(cte.a) < 10
    LIMIT 3 OFFSET 2
)
SELECT * FROM cte;

This statement produces the following output in the mysql client:

+-------+
| a     |
+-------+
| xxx   |
| xxxx  |
| xxxxx |
+-------+

Specifying LIMIT in this fashion can make execution of the CTE more efficient than doing so in the outermost SELECT, since only the requested number of rows is generated.

For more information, see Recursive Common Table Expressions. (Bug #92857, Bug #28816906, WL #12534)

When CHECK constraints were implemented in MySQL 8.0.16, ALTER TABLE supported DROP CHECK and ALTER CHECK syntax as MySQL extensions to standard SQL for modifying check constraints, but did not support the more general (and SQL standard) DROP CONSTRAINT and ALTER CONSTRAINT syntax for modifying existing constraints of any type. That syntax is now supported; the constraint type is determined from the constraint name. (WL #12787)

MySQL now supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement for the row to be inserted and its columns. Consider a statement such as this one:

INSERT INTO t
    VALUES (9,5), (7,7), (11,-1)
    ON DUPLICATE KEY UPDATE a = a + VALUES(a) - VALUES(b);

Using the alias new for the inserted row, you can now rewrite the statement, referring back to the row alias in the ON DUPLICATE KEY UPDATE clause, like this:

INSERT INTO t
    VALUES (9,5), (7,7), (11,-1) AS new
    ON DUPLICATE KEY UPDATE a = a + new.a - new.b;

Using the same row alias, and, additionally, the column aliases m and n for the columns of the inserted row, you can omit the row alias and use only the column aliases, as shown here:

INSERT INTO t
    VALUES (9,5), (7,7), (11,-1) AS new(m,n)
    ON DUPLICATE KEY UPDATE a = a + m - n;

The row alias must be distinct from the table name; column aliases must be distinct from one another.

See INSERT ... ON DUPLICATE KEY UPDATE Statement, for more information and examples. (WL #6312)

sys schema objects have been reimplemented not to invoke the deprecated sys.format_bytes(), sys.format_time(), and sys.ps_thread_id() stored functions. Instead, they invoke the equivalent built-in SQL functions implemented in MySQL 8.0.16 that format or retrieve Performance Schema data (see Section 28, “Changes in MySQL 8.0.16 (2019-04-25, General Availability)”).

sys.format_bytes(), sys.format_time(), and sys.ps_thread_id() will be removed in a future MySQL version, so applications that use them should be adjusted to use the built-in functions instead, keeping in mind some minor differences between the sys functions and the built-in functions. See Performance Schema Functions. (WL #13439)

By default, the thread pool plugin tries to ensure a maximum of one thread executing in each group at any time. The default algorithm takes stalled threads into account and may temporarily permit more active threads. The plugin now implements a new thread_pool_max_active_query_threads system variable for controlling number of active threads per group. If thread_pool_max_active_query_threads is 0, the default algorithm applies. If thread_pool_max_active_query_threads is greater than 0, it places a limit on the number of active threads per group. See Thread Pool Operation. (WL #12915)

X Plugin could not be compiled on Debian with GCC 9. The --no-as-needed linker option was added to provide a workaround for the issue. (Bug #30445201)

Using X Protocol to query the Information Schema table TRIGGERS could result in errors being returned or some rows not being returned. (Bug #30318917)

Group Replication: Replication connections to a replication slave, and Group Replication connections for distributed recovery, now have full client side configuration options for the TLSv1.3 protocol. In MySQL releases where TLSv1.3 support was available but these configuration options were not available, if TLSv1.3 was used for these connection types, the client in the connection (the replication slave or the joining Group Replication member that initiated distributed recovery) could not be configured. This meant that the server in the connection (the replication master or the Group Replication existing member that was the donor for distributed recovery) had to permit the use of at least one TLSv1.3 ciphersuite that is enabled by default. From MySQL 8.0.19, you can use the configuration options to specify any selection of ciphersuites for these connections, including only non-default ciphersuites if you want.

The new configuration options are as follows:

(Bug #29960735, WL #13392)

Group Replication: The Group Replication plugin interacts with MySQL Server using internal sessions to perform SQL API operations. Previously, these sessions counted towards the client connections limit specified by the max_connections server system variable. If the server had reached this limit when Group Replication was started or attempted to perform an operation, the operation was unsuccessful and Group Replication or the server itself might stop. From MySQL 8.0.19, Group Replication interactions with MySQL Server use a new component service that handles the internal sessions separately, which means that they do not count towards the max_connections limit and are not refused if the server has reached this limit. (Bug #29635001, WL #13378)

Microsoft Windows: Previously, the system (\!) command for the mysql command-line client worked only for Unix systems. It now works on Windows as well. For example, system cls or \! cls may be used to clear the screen. (Bug #11765690, Bug #58680, WL #13391)

JSON: When using JSON_SCHEMA_VALID() to specify a CHECK constraint on a table containing one or more JSON columns and experiencing a validation failure, MySQL now provides detailed information about the reasons for such failures. A new error ER_JSON_SCHEMA_VALIDATION_ERROR_WITH_DETAILED_REPORT is implemented containing this information, which can be viewed in the mysql client by issuing SHOW WARNINGS when an INSERT statement is rejected by the server.

For more information and examples, see JSON_SCHEMA_VALID() and CHECK constraints. For more general information, see also CHECK Constraints. (WL #13195)

Debian packages now contain more general systemd support that better supports manual mysqld execution. (Bug #29702050, Bug #95163)

Duplicate key error information was extended to include the table name of the key. Previously, duplicate key error information included only the key value and key name. Thanks to Facebook for the contribution. (Bug #28686224, Bug #925308, WL #12589)

When the mysql client operates in interactive mode, the --binary-as-hex option now is enabled by default. In addition, output from the status (or \s) command includes this line when the option is enabled implicitly or explicitly:

Binary data as: Hexadecimal

To disable hexadecimal notation, use --skip-binary-as-hex (Bug #24432545, WL #13038)

MySQL now supports datetime literals with time zone offsets, such as '2019-12-11 10:40:30-05:00', '2003-04-14 03:30:00+10:00', and '2020-01-01 15:35:45+05:30'; these offsets are respected but not stored when inserting such values into TIMESTAMP and DATETIME columns; that is, offsets are not displayed when retrieving the values.

The supported range for a timezone offset is -13:59 to +14:00, inclusive. Time zone names such as 'CET' or 'America/Argentina/Buenos_Aires', including the special value 'SYSTEM', are not supported in datetime literals. In addition, in this context, a leading zero is required for an hour value less than 10, and MySQL rejects the offset '-00:00' as invalid.

Datetime literals with time zone offsets can also be used as parameter values in prepared statements.

As part of this work, the allowed range of numeric values for the time_zone system variable has been changed, so that it is now also -13:59 to +14:00, inclusive.

For additional information and examples, see The DATE, DATETIME, and TIMESTAMP Types, and MySQL Server Time Zone Support. (Bug #83852, Bug #25108148, WL #10828)

From MySQL 8.0.19, compression is supported for messages sent over X Protocol connections. Connections can be compressed if the server and the client agree on a compression algorithm to use. By default, X Protocol announces support for the deflate, lz4, and zstd compression algorithms. You can disallow any of these algorithms by setting the new mysqlx_compression_algorithms system variable to include only the ones you permit. X Protocol always allows uncompressed connections if the client does not request compression during capability negotiation. Note that X Protocol's list of permitted compression algorithms operates independently of the list of compression algorithms announced by MySQL Server, and X Protocol does not fall back to using MySQL Server's compression settings. You can monitor the effects of message compression for X Protocol using new X Plugin status variables. (WL #9252, WL #13442)

For multithreaded slaves (replication slaves on which slave_parallel_workers is set to a value greater than 0), setting slave_preserve_commit_order=1 ensures that transactions are executed and committed on the slave in the same order as they appear in the slave's relay log, preserving the same transaction history on the slave as on the master. Previously, this setting required binary logging and slave update logging to be enabled on the slave, with the associated execution costs and disk space requirements. Now, slave_preserve_commit_order=1 can be set on a slave with no binary log and no slave update logging. This enables you to preserve commit order on the slave, and avoid gaps in the sequence of transactions, without the overhead of binary logging.

A limitation to preserving the commit order on the slave can occur if statement-based replication is in use, and both transactional and non-transactional storage engines participate in a non-XA transaction that is rolled back on the master. Normally, non-XA transactions that are rolled back on the master are not replicated to the slave, but in this particular situation, the transaction might be replicated to the slave. If this does happen, a multithreaded slave without binary logging does not handle the transaction rollback, so the commit order on the slave diverges from the relay log order of the transactions in that case. (WL #7846)

The MySQL 8.0.18 release introduced the ability to specify a PRIVILEGE_CHECKS_USER account for a replication channel (using a CHANGE MASTER TO statement), against which MySQL makes privilege checks when replicated transactions are applied. The use of a PRIVILEGE_CHECKS_USER account helps secure a replication channel against the unauthorized or accidental use of privileged or unwanted operations. The use of row-based binary logging is strongly recommended when replication channels are secured with privilege checks.

In MySQL 8.0.19, a new setting REQUIRE_ROW_FORMAT is added for replication channels, which makes the channel accept only row-based replication events. You can specify REQUIRE_ROW_FORMAT using a CHANGE MASTER TO statement to enforce row-based binary logging for a replication channel that is secured with privilege checks, or to increase the security of a channel that is not secured in this way. By allowing only row-based replication events, REQUIRE_ROW_FORMAT prevents the replication applier from taking actions such as creating temporary tables and executing LOAD DATA INFILE requests, which protects the replication channel against some known attack vectors. Row-based binary logging (binlog_format=ROW) must be used on the replication master when REQUIRE_ROW_FORMAT is set.

Group Replication already requires row-based binary logging, so from MySQL 8.0.19, Group Replication channels are automatically created with REQUIRE_ROW_FORMAT set, and you cannot change the option for those channels. The setting is also applied to all Group Replication channels on upgrade.

mysqlbinlog has a new --require-row-format option, which enforces row-based replication events for mysqlbinlog's output. The stream of events produced with this option would be accepted by a replication channel that is secured using the REQUIRE_ROW_FORMAT option. (WL #12968)

MySQL uses delimiter strings when constructing tablespace names and file names for table partitions. A “#p#” delimiter string precedes partition names, and an “#sp#” delimiter string precedes subpartition names, as shown:

schema_name.table_name#p#partition_name#sp#subpartition_name
table_name#p#partition_name#sp#subpartition_name.ibd

Historically, delimiter strings have been uppercase (#P# and #SP#) on case-sensitive file systems such as Linux, and lowercase (#p# and #sp#) on case-insensitive file systems such as Windows. To avoid issues when migrating data directories between case-sensitive and case-insensitive file systems, delimiter strings are now lowercase on all file systems. Uppercase delimiter strings are no longer used.

Additionally, partition tablespace names and file names generated based on user-specified partition or subpartition names, which can be specified in uppercase or lowercase, are now generated (and stored internally) in lowercase regardless of the lower_case_table_names setting to ensure case-insensitivity. For example, if a table partition is created with the name PART_1, the tablespace name and file name are generated in lowercase:

schema_name.table_name#p#part_1
table_name#p#part_1.ibd

During upgrade, MySQL now checks and modifies if necessary:

During tablespace import operations, partition tablespace file names on disk are checked and modified if necessary to ensure lowercase delimiters and partition names. (WL #13352)

References: See also: Bug #26925260, Bug #29823032, Bug #30012621, Bug #29426720, Bug #30024653.

Support was added for efficient sampling of InnoDB data for the purpose of generating histogram statistics. The default sampling implementation used by MySQL when storage engines do not provide their own requires a full table scan, which is costly for large tables. The InnoDB sampling implementation improves sampling performance by avoiding full table scans. The sampled_pages_read and sampled_pages_skipped INNODB_METRICS counters can be used to monitor sampling of InnoDB data pages. See Histogram Statistics Analysis. (WL #8777)

Important Change: Character set resolution has been changed for the following string functions:

Previously, character set information for all arguments to these functions was aggregated, which could lead to results that were not well formed. This also caused issues with LPAD(), which assumes that both input and output are well formed. Now each of the three listed functions always uses the character set employed by str, and converts all other arguments to this character set at execution time; if any such conversion fails, the function returns an error. (Bug #30114420)

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

Important Change: Subquery materialization no longer requires strict matching of inner and outer types. Different types can now be materialized when one of the following conditions is true:

(Bug #13960580)

NDB Cluster: Password masking was incomplete for some NDB logging options. (Bug #97335, Bug #30453137)

InnoDB: Initialization of certain internal data structures at startup depend on internal variables derived from the max_connections setting. InnoDB failed to resize the internal data structures when the max_connections setting was modified after startup using SET PERSIST. (Bug #30628872)

InnoDB: os_file_get_parent_dir warnings were encountered when compiling MySQL with GCC 9.2.0. (Bug #30499288, Bug #97466)

InnoDB: An attempt to access a large object (LOB) value using a null reference raised an assertion failure. To prevent this issue form occurring, a check was added to determine if LOB references are null before they are accessed. (Bug #30499064)

InnoDB: An assertion failure occurred after upgrading the data directory. Prepared XA transaction were still present, which prevented undo tablespaces from being upgraded. Undo tablespaces containing prepared transaction changes must remain active until all prepared XA transactions are committed or rolled back.

Prepared XA transactions also prevented the completion of an explicit undo tablespace truncation operation after a restart. (Bug #30489497)

InnoDB: Attempting to upgrade a MySQL 5.7 instance on Linux with uppercase table names (partitioned or otherwise) to MySQL 8.0 on macOS raised an assertion failure. Partition file format changes in MySQL 8.0 prevented migration of the data directory to a different platform, and the lower_case_table_names setting was changed at upgrade time, which can cause an upgrade failure. Instead of a failure occurring under these circumstances, an error is now reported. (Bug #30450968, Bug #30450979)

InnoDB: On macOS, a failure occurred when attempting to upgrade a MySQL 5.7 instance with uppercase table names to MySQL 8.0. Uppercase table names were not normalized to lowercase. The following errors were reported: Table is not found in InnoDB dictionary and Error in fixing SE data errors. (Bug #30450944)

InnoDB: On Windows, a failure occurred when attempting to upgrade a MySQL 5.7 instance with uppercase partitioned table names to MySQL 8.0. Opening the table returned a null pointer, which caused a segmentation fault when closing the table. (Bug #30450918)

InnoDB: On Windows, a mysqld exception was raised when attempting to upgrade a MySQL 5.7 instance with uppercase partitioned table names to MySQL 8.0. (Bug #30447790)

InnoDB: On Windows, a failure occurred when attempting to upgrade a MySQL 5.7 instance containing general tablespace defined with an uppercase name to MySQL 8.0. The following errors were reported: Error in fixing SE data and Failed to Populate DD. (Bug #30446798)

InnoDB: Introduction of local minitransactions (mtrs) in LOB-related code resulted in an assertion failure during recovery. (Bug #30417719)

InnoDB: A failure occurred when attempting to upgrade a MySQL 5.7 instance on Windows with uppercase partitioned table names to MySQL 8.0 on Linux. Partition file format changes in MySQL 8.0 prevented migration of the data directory to a different platform. Instead of a failure, an error is now reported. (Bug #30411118)

InnoDB: Updating the same compressed LOB data repeatedly caused the tablespace file to increase in size. (Bug #30353812)

InnoDB: When the temptable_max_ram limit was reached, the TempTable storage engine incorrectly reported an out-of-memory error instead of falling back to disk-based storage. (Bug #30314972, Bug #96893)

InnoDB: After importing an encrypted table and restarting the server, the following error was returned when attempting to access the table: ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully. The tablespace key was not written to disk after it was encrypted with the destination master key. (Bug #30313734)

InnoDB: The internal InnoDB dict_create_foreign_constraints() function that parsed SQL statements and performed foreign key related DDL checks was removed. The function became redundant with introduction of the data dictionary in MySQL 8.0 and the subsequent relocation of foreign key related DDL checks to the SQL layer.

Removal of the dict_create_foreign_constraints() function also addressed the following foreign key issues:

Additionally, a check was added to the SQL layer to detect attempts to create multiple foreign keys of the same name on a table at an early stage in the execution of an ALTER TABLE statement. (Bug #30287895, Bug #22364336, Bug #28486106, Bug #28703793, Bug #16904122, Bug #92567, Bug #11754659, Bug #46293)

InnoDB: A comparison function found two records to be equal when attempting to merge non-leaf pages of a spatial index. The function was unable to handle this unexpected condition, which resulted in a long semaphore wait and an eventual assertion failure. (Bug #30287668)

InnoDB: A locally acquired latch required for freeing a large object (LOB) page could have caused a deadlock if a subsequent caller attempted to acquire a latch for the same page before the page was freed. Similarly, a latch taken on a compressed or uncompressed LOB during a rollback related operation could have caused a deadlock due to a latching order issue. (Bug #30258536)

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

InnoDB: A race condition between a purge thread that was purging a compressed LOB page and an update thread that is using a delete-marked record caused an assertion failure. (Bug #30197056)

InnoDB: A tablespace import operation that failed due to the source and destination tables being defined with different DATA DIRECTORY clauses reported an insufficiently descriptive schema mismatch error. Moreover, if a .cfg file was not present, the same operation would raise an assertion failure. A more informative error message is now reported in both cases before the import operation is terminated due to the data directory mismatch. (Bug #30190199, Bug #30190227, Bug #20644698, Bug #76142)

InnoDB: A purge operation failed when attempting to purge a LOB value larger than the buffer pool. (Bug #30183982)

InnoDB: Update operations that moved externally stored LOB data to inline storage failed to mark the old LOB data as purgeable. (Bug #30178056, Bug #96466)

InnoDB: Index key part sort order information was not stored to the .cfg metadata file used by ALTER TABLE ... IMPORT TABLESPACE operations. The index key part sort order was therefore assumed to be ascending, which is the default. As a result, records could be sorted in an unintended order if one table involved in the import operation is defined with a DESC index key part sort order and the other table is not. To address this issue, the .cfg file format was updated to include index key part sort order information. (Bug #30128418)

InnoDB: Criteria used by the btr_cur_will_modify_tree() function, which detects whether a modifying record needs a modifying tree structure, was insufficient. (Bug #30113362)

InnoDB: Startup was slow on instances with a large number of tables due the tablespace file scan that occurs at startup to retrieve space IDs. A multithreaded scan was only initiated if the number of tablespace files exceed 50,000, and three tablespace pages were read to retrieve a space ID. To improve startup times, additional threads are now allocated for the tablespace file scan, and only the first tablespace page is read to retrieve a space ID. If a space ID is not found on the first page of the tablespace, three pages are read to determine the space ID, as before. (Bug #30108154, Bug #96340)

InnoDB: Startup failed on a case insensitive file system with an error indicating that multiple files were found for the same tablespace ID. A file path comparison did not recognize that innodb_data_home_dir and datadir paths were the same due to the paths having different lettercases. (Bug #30040815)

InnoDB: A storage engine error occurred when accessing the mysql.innodb_index_stats and mysql.innodb_table_stats persistent optimizer statistics tables after upgrading a MySQL 8.0.13 instance on Linux with partitioned tables and a lower_case_table_names=1 setting to MySQL 8.0.14 or MySQL 8.0.15. The persistent optimizer statistics tables contained duplicate entries. (Bug #30012621)

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

InnoDB: CREATE TABLESPACE failed with an error indicating that the tablespace already exists. The error was due to the failure of a preceding CREATE TABLESPACE operation where the DDL failed but related changes were not rolled back due to rollback being disabled prior to transaction commit. Rollback is now disabled after the transaction commits successfully. (Bug #29959193, Bug #95994)

InnoDB: Changed pages belonging to imported tablespaces were not being tracked. (Bug #29917343)

InnoDB: Renaming of full-text search auxiliary tables during upgrade failed due to a tablespace name conflict when upgrading from MySQL 5.7 to MySQL 8.0 on a case-insensitive file system. (Bug #29906115)

InnoDB: Rollback of an INSERT operation that inserted a LOB value larger than a buffer pool caused a deadlock. (Bug #29846292, Bug #95572)

InnoDB: A code regression was addressed by prohibiting unnecessary implicit to explicit secondary index lock conversions for session temporary tables. (Bug #29718243)

InnoDB: A tablespace import operation raised an assertion when the cursor was positioned on a corrupted page while purging delete-marked records. Instead of asserting when encountering a corrupted page, the import operation is now terminated and an error is reported. (Bug #29454828, Bug #94541)

InnoDB: Delete marked rows were able to acquire an external read lock before a partial rollback was completed. The external read lock prevented conversion of an implicit lock to an explicit lock during the partial rollback, causing an assertion failure. (Bug #29195848)

InnoDB: After a server exit that occurred while an undo tablespace truncation operation was in progress, warning messages were printed at startup stating that doublewrite pages could not be restored for undo tablespace pages. The warning messages are no longer printed for undo tablespaces that are being truncated. (Bug #28590016)

InnoDB: In read-only mode (innodb_read_only=ON), SHOW CREATE TABLE output did not include information about foreign key constraints. (Bug #21966795, Bug #78754)

Partitioning: When upgrading a database with a subpartitioned table from MySQL 8.0.16 or lower and then executing ALTER TABLE ADD COLUMN, an assertion or error would occur. (Bug #30360695, Bug #97054)

Partitioning: During upgrade of partitioned tables from MySQL 5.7 to 8.0, when a prefix key was used by the partitioning function, the prefix length was ignored, and the full column length was considered instead. Consequently, the table might incorrectly be rejected from being upgraded because its partition field length was found to be too large. (Bug #29941988, Bug #95921)

Partitioning: ALTER TABLE ... EXCHANGE PARTITION could cause indexes to become corrupted. This was due to the fact that the server assumed that the order in which an index is created in a partitioned table is the same as that of the table which is not partitioned. This led to the wrong index data being exchanged. (Bug #29706669)

Replication: An assertion was raised when privilege checks were carried out for a replication channel if the slave had more columns in the relevant table than the master. The check now references the number of columns in the event, rather than in the table definition. (Bug #30343310)

Replication: Replication connection parameters that are held in the mysql.slave_relay_log_info table are now preserved in the event of a server crash or deliberate restart after issuing RESET SLAVE but before issuing START SLAVE. This action applies to the PRIVILEGE_CHECKS_USER account setting for replication privilege checks (introduced in MySQL 8.0.18) and the REQUIRE_ROW_FORMAT setting (introduced in MySQL 8.0.19). Note that if relay_log_info_repository=FILE is set on the server (which is not the default and is deprecated), replication connection parameters are not preserved in this situation. (Bug #30311908)

Replication: When a replication channel is secured by specifying a PRIVILEGE_CHECKS_USER account, which should not have ACL privileges, a GRANT statement that is replicated to the channel causes the replication applier to stop. In this situation, the behavior was correct but an assertion was being raised. The assertion has now been removed. (Bug #30273684)

Replication: For multithreaded replication slaves, setting slave_preserve_commit_order=1 now preserves the order of statements with an IF EXISTS clause when the object concerned does not exist. Previously, these updates might have committed before transactions that preceded them in the relay log, which might have resulted in gaps in the sequence of transactions that have been executed from the slave's relay log. (Bug #30262096)

Replication: When privilege checks were carried out for a replication channel, the permissions required for setting the session value of the sql_require_primary_key system variable were not being checked. The check is now carried out. (Bug #30254917)

Replication: A memory leak could occur when a failed replication group member tried to rejoin a minority group and was disallowed from doing so. (Bug #30162547, Bug #96471)

Replication: When a group member rejoins a replication group, it begins the distributed recovery process by checking the relay log for its group_replication_applier channel for any transactions that it already received from the group, and applying these. The joining member then initiates state transfer from an existing online member, which might begin with a remote cloning operation. Previously, the group_replication_applier channel was not explicitly stopped when a remote cloning operation was started, so it was possible that the applier might still be applying existing transactions at that time, which might lead to errors. The group_replication_applier channel is now stopped before a remote cloning operation is requested, and restarted when the distributed recovery process moves on to state transfer from a donor's binary log. (Bug #30152028, Bug #96447)

Replication: If STOP GROUP_REPLICATION was issued while the member's XCom port was blocked, the XCom thread hung and the shutdown did not complete. XCom is now terminated in this situation. (Bug #30139794)

Replication: The slave status logs mysql.slave_relay_log_info (the relay log info log) and mysql.slave_worker_info (the slave worker log) are now copied from the donor to the recipient during a local or remote cloning operation. The slave status logs hold information that can be used to resume replication correctly after the cloning operation, including the relay log position from which to restart replication, the PRIVILEGE_CHECKS_USER account setting, and the new REQUIRE_ROW_FORMAT setting. Note that the relay logs themselves are not copied from the donor to the recipient, only the information about them that is held in these tables. Also note that if relay_log_info_repository=FILE is set on the server (which is not the default and is deprecated), the slave status logs are not cloned; they are only cloned if TABLE is set.

Before this patch, the following replication-related behaviors occurred on a replication slave that had been provisioned by a cloning operation:

With this change, the following replication-related behaviors now occur on a replication slave that has been provisioned by a cloning operation:

(Bug #29995256, Bug #30510766)

Replication: An internal deadlock could occur on a multi-threaded replication slave when the relay_log_space_limit system variable was set to limit the size of relay logs on the slave, and the coordinator thread acquired locks related to this limit and to the end position of the log. (Bug #29842426)

Replication: If a replication slave was set up using a CHANGE MASTER TO statement that did not specify the master log file name and master log position, then shut down before START SLAVE was issued, then restarted with the option --relay-log-recovery set, replication did not start. This happened because the receiver thread had not been started before relay log recovery was attempted, so no log rotation event was available in the relay log to provide the master log file name and master log position. In this situation, the slave now skips relay log recovery and logs a warning, then proceeds to start replication. (Bug #28996606, Bug #93397)

Group Replication: When a member is joining or rejoining a replication group, if Group Replication detects an error in the distributed recovery process (during which the joining member receives state transfer from an existing online member), it automatically switches over to a new donor, and retries the state transfer. The number of times the joining member retries before giving up is set by the group_replication_recovery_retry_count system variable. The Performance Schema table replication_applier_status_by_worker displays the error that caused the last retry. Previously, this error was only shown if the group member was configured with parallel replication applier threads (as set by the slave_parallel_workers system variable). If the group member was configured with a single applier thread, the error was cleared after each retry by an internal RESET SLAVE operation, so it could not be viewed. This was also the case for the output of the SHOW SLAVE STATUS command whether there were single or multiple applier threads. The RESET SLAVE operation is now no longer carried out after retrying distributed recovery, so the error that caused the last retry can always be viewed. (Bug #30517160, Bug #30517172, Bug #97540)

Group Replication: When a replication group member leaves a group, either because STOP GROUP_REPLICATION was issued or due to an error, Group Replication now stops the binary log dump thread so that the former group member cannot send unwanted binary log data to the members that have remained in the group. (Bug #30315614)

Group Replication: When Group Replication was started following either provisioning with a cloning operation, execution of RESET MASTER, or removal of a partial transaction from the relay log, RESET SLAVE ALL was used internally to clear any unwanted state on the server. However, in MySQL 8.0.18, this caused any PRIVILEGE_CHECKS_USER account that was specified for a Group Replication channel to be removed. RESET SLAVE is now used instead, which does not remove the account. (Bug #30262225)

Group Replication: When Group Replication is running in single-primary mode, and a new primary server is elected, the messages logged at this time now provide the newly elected primary server's gtid_executed set, and the set of GTIDs retrieved by the replication applier. (Bug #30049310)

Group Replication: If a replication group member stops unexpectedly and is immediately restarted (for example, because it was started with mysqld_safe), it automatically attempts to rejoin the group if group_replication_start_on_boot=on is set. Previously, if the restart and rejoin attempt took place before the member's previous incarnation had been expelled from the group, the member could not rejoin. Now in this scenario, Group Replication automatically uses a Group Communication System (GCS) feature to retry the rejoin attempt for the member 10 times, with a 5-second interval between each retry. This should cover most cases and allow enough time for the previous incarnation to be expelled from the group, letting the member rejoin. Note that if the group_replication_member_expel_timeout system variable is set to specify a longer waiting period before the member is expelled, the automatic rejoin attempts might still not succeed. (Bug #29801773)

macOS: On macOS, configuring MySQL with -DWITH_SSL=system caused mysql_config output to incorrectly include internal CMake names for the static SSL libraries. (Bug #30541879, Bug #97632)

macOS: Builds on macOS with Ninja could fail with an error trying to create a symbolic link multiple times. (Bug #30368985)

Microsoft Windows; JSON: On Windows platforms, memory used for a multi-valued index was not released after the table containing it was dropped. (Bug #30227756)

Microsoft Windows: On Windows, -DWITH_SSL=system failed to find the installed OpenSSL headers if Strawberry Perl was installed. (Bug #30359287)

Microsoft Windows: On Windows, the -DWITH_SSL=system option did not work if the path name leading to the system OpenSSL libraries contained a space. This is now handled. Also, -DWITH_SSL=yes is treated like -DWITH_SSL=system, as on other platforms. (Bug #30261942, Bug #96739)

Microsoft Windows: MSVC 2019 produced garbled source file names for compilation errors. A workaround in the CMake configuration was implemented to correct for this. (Bug #30255096, Bug #96720)

JSON: Updating a value in a JSON column by replacing a character string element with a binary string containing the same byte sequence as the utf8mb4 representation of the character string had no effect.

The root cause of this issue was a change in the behavior of comparisons between JSON strings and JSON opaque values introduced by the implementation of multi-valued indexes in MySQL 8.0.17, previous to which, JSON strings and JSON opaque values were never considered equal. After the change, they were considered equal if their binary data matched.

An analysis of this change showed that it was not needed; in addition, the new behavior conflicted with the existing documentation for comparisons of JSON values. This issue is fixed by restoring the original behavior. (Bug #30348554)

JSON: A view that used JSON_TABLE() did not preserve the character set in which JSON path arguments were encoded. This meant that, if the view was evaluated with a different character set in effect from the one in which it was defined, it could produce wrong results. This is fixed by ensuring that JSON_TABLE() preserves the original character set in such cases. (Bug #30310265)

JSON: Adding a functional index on a JSON column changed the collation used for comparing strings, causing the result returned by the same query selecting the column to differ from that obtained without the index. (Bug #29723353)

JSON: If the first argument to JSON_TABLE() was const during the execution of a stored procedure, but not during preparation, it was not re-evaluated when a statement was subsequently executed again, causing an empty result to be returned each time following the first execution of the procedure. (Bug #97097, Bug #30382156)

JSON: In some cases, such as when a query uses FORCE INDEX, the cost of reading the table is DBL_MAX; this was rounded up to 2e308 when printed, which is too large for the JSON parser, so that it was not possible to extract parts of the optimizer trace using a query such as SELECT JSON_EXTRACT(trace, '$**.table_scan') FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE. Now in such cases, values greater than 1.5e308 are rounded down and printed as 1e308 instead. (Bug #96751, Bug #30226767)

After upgrading from MySQL 5.7 to MySQL 8.0, a CLONE INSTANCE operation failed with the following error: ERROR 3862 (HY000): Clone Donor Error: 1016 : Can't open file: './undo001'. The upgrade process left behind orphaned in-memory undo tablespaces. Thanks to Satya Bodapati for the contribution. (Bug #30602218, Bug #97784, Bug #30239255, Bug #96637)

The MySQL optimizer's hash join algorithm uses the join buffer to store intermediate results. If this buffer overflows, the server uses a spill-to-disk algorithm, which writes one of the hash join operands to a temporary file, to handle this gracefully. If one of the operands was a table that was a member of a pushed join operation, this strategy conflicted with the pushed join requirement for all child result rows to use nested-loop reads whenever one of their pushed join ancestors was the current row in the join evaluation, which could in some cases result in incorrect query results being returned. (Bug #30573733)

Access to the INFORMATION_SCHEMA.VIEWS table was not properly restricted to the correct user. (Bug #30542333)

When creating hash values used for lookups during a hash join, the server did not respect the PAD SPACE attribute, meaning that 'foo' and 'foo ' did not match when using a PAD SPACE collation. This is fixed by padding all strings up to the same length as the longest possible string, where the longest possible string is deduced from the data type length specifier N in CHAR(N) or VARCHAR(N). (Bug #30535541)

When retrieving large result sets containing DECIMAL columns from a secondary engine, conversion of the column values to strings for transport over the text protocol acted as a bottleneck. The performance of the functions responsible for such conversions has been improved in some cases by as much as 50%, as reflected in internal testing. (Bug #30528427)

When the FORMAT_PICO_TIME() function was invoked to process several rows, once a NULL argument was found in a row, every result after that was set to NULL. (Bug #30525561)

When a Performance Schema event was timed, the event duration reported in events_xxx tables could be NULL instead of 0 for events where the timer start and end values are equal. (Bug #30525560)

Adding a LIMIT clause to a parenthesized query suppressed locking clauses within the parentheses. For example, this query would not lock the table:

(SELECT ... FOR UPDATE) LIMIT ...;

Adding a LIMIT clause outside of a parenthesized query is intended to override a LIMIT clause within the parentheses. However, the outer LIMIT suppressed ORDER BY within the parentheses as well. For example, for this query, the ORDER BY was suppressed:

(SELECT ... ORDER BY ... LIMIT a) LIMIT b;

Now inner locking and ORDER BY clauses are not suppressed by an outer LIMIT clause. (Bug #30521098, Bug #30521803)

When optimizer extracts conditions on constant tables for early evaluation, it does not include WHERE conditions that are expensive to evaluate, including conditions involving stored functions. When the extracted condition evaluated to true because it involved only const tables, the entire WHERE condition was incorrectly removed. Now in such cases, a check for expensive conditions is performed prior to any removal of the WHERE condition. (Bug #30520714)

When a lateral materialized derived table used DISTINCT, the derived table was not rematerialized for each outer row as expected. (Bug #30515233)

EXPLAIN ANALYZE did not work correctly with a common table expression using WITH RECURSIVE. (Bug #30509580)

The GNU gold loader could cause memory exhaustion on some platforms. Now it is used by default only on Intel 64-bit platforms. (Bug #30504760, Bug #96698)

Some Linux platforms experienced high overhead with EXPLAIN ANALYZE due to use of a system call by libstdc++ instead of clock_gettime(). (Bug #30483025)

On Solaris 11.4, the LDAP authentication plugins could not be built. (Bug #30482553)

Queries that used the MEMBER OF() operator were not always handled correctly. (Bug #30477993)

Boost compilation failed under Visual Studio due to a Boost workaround for a VC++ 2013 bug that has since been fixed. The workaround is now patched for Boost compilation with MySQL. (Bug #30474056, Bug #97391)

When retrieving large result sets containing many integers from a secondary engine, conversion of the integers to strings for sending over the text protocol could act as a bottleneck. To avoid this problem, the performance of internal functions performing such conversions has been improved. (Bug #30472888)

Docker packages were missing the LDAP authentication plugins. (Bug #30465247)

Corrected a typo in a mysys/my_handler_errors.h error message. Thanks to Nikolai Kostrigin for the contribution. (Bug #30462329, Bug #97361)

A GTID table update while innodb_force_recovery was enabled caused a debug assertion failure. (Bug #30449531, Bug #97312)

MySQL failed to compile against Protobuf 3.10. (Bug #30428543, Bug #97246)

Buffered log lines during system startup could be lost. (Bug #30422941, Bug #97225)

If the mysql.user system table was renamed, the server could exit. (Bug #30418070)

Revoking a role specified with no host name could cause a server exit. (Bug #30416389)

When determining whether to pull out a semijoin table when other tables inside the semijoin depended on this table, only those semijoin tables which were base tables were considered; those in nested joins were ignored. (Bug #30406241)

References: See also: Bug #12714094, Bug #11752543, Bug #43768.

The AppArmor profile on Ubuntu platforms was not able to read the OpenSSL configuration. (Bug #30375723)

Some Fedora 30 packages had missing obsoletes information that could cause problems upgrading an existing MySQL installation. (Bug #30348549, Bug #96969)

Altering only the default encryption in an ALTER SCHEMA statement caused the schema default character set and collation to be reset to the system defaults. (Bug #30344462, Bug #96994)

Columns declared with both AUTO_INCREMENT and DEFAULT value expressions (a nonpermitted combination) could raise an assertion or cause a server exit. (Bug #30331053)

SHOW GRANTS for an anonymous user could result in a server exit under some conditions. (Bug #30329114)

GREATEST() and LEAST() did not always handle time values correctly. (Bug #30326848)

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

The list of subpartitions in partition objects was not serialized and therefore not included in serialized dictionary information (SDI). To address this issue, support was added for serialization and deserialization of subpartition dictionary information. The patch for this bug also includes minor SDI code refactoring and format changes. Due to the format changes, the SDI version number was incremented. (Bug #30326020, Bug #96943)

Following execution of ANALYZE TABLE, the optimizer trace for a given query differed when another query was executed previously to it, but also after the ANALYZE TABLE. (Bug #30321546)

innodb_buffer_pool_instances was not initialized correctly at server startup if it had been set using SET PERSIST or PERSIST_ONLY. (Bug #30318828)

A low max_allowed_packet value caused the following error: ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes. The error message was revised to indicate the minimum required max_allowed_packet value for cloning operations. (Bug #30315486, Bug #96891)

An assertion could be raised when server code tried to send to clients an error code intended to be written to the error log. These instances are fixed by sending a code intended to be sent to clients. (Bug #30312874)

CREATE VIEW did not always succeed when the body of the view definition contained a join and multiple subselects. (Bug #30309982)

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

Dependency information for SLES 12 RPM packages was incorrect, causing MySQL installation failure. (Bug #30308305)

When restoring GEOMETRY data from hash join chunk files to a GEOMETRY column, the server did not copy the data to the column, but instead stored a pointer to the data, which resided in a temporary buffer, meaning that the GEOMETRY column pointed to random data as soon as this buffer was reused. Now, the server always copies the data from this buffer into the GEOMETRY column when executing a hash join. (Bug #30306279)

Some ALTER TABLE operations using the COPY algorithm did not handle columns with expression default values properly. (Bug #30302907, Bug #96864)

The CONV() function did not always handle returning the proper number of characters correctly. (Bug #30301543)

Parser recursion checks were insufficient to prevent stack overflow. (Bug #30299881)

The removal of a subquery because the condition in which it occurred was always false was expected to be performed during resolution, but when the subquery did not involve any tables, the server executed it while resolving it. This resulted in the failure of a subsequent check to confirm that the subquery was only being resolved and not yet optimized. Now in such cases, the server also checks to see whether the subquery was already executed. (Bug #30273827)

For debug builds, attempts to add to an empty temporary table a column with an expression default that was not valid raised an assertion. (Bug #30271792)

Construction of the iterator tree may yield a non-hierarchical structure; this can happen when, for example, b and c from a LEFT JOIN b LEFT JOIN c also make up the right side of a semijoin. The iterator executor solves this by adding a weedout on top of the entire query, which means that is also necessary to iterators interacting with row IDs that they need to store and restore them. This was not done in all such cases, causing wrong results. Now the addition of a top-level weedout is always communicated to the iterators as soon as it is known that this is being done, before any affected iterators are constructed. (Bug #30267889)

Foreign key-handling code duplication between the SQL layer and the data dictionary was eliminated. A side effect is that some error messages now are more informative and clear. (Bug #30267236, Bug #96765)

During startup, the server could handle incorrect option values for persisted variables improperly, resulting in a server exit. (Bug #30263773)

In some queries involving materialized semijoins, when using the iterator executor, conditions were evaluated outside the materialization, causing inefficient query plans to be used and sometimes also producing wrong results. (Bug #30250091)

ALTER TABLE statements that renamed a column used in CHECK constraints could result in an incorrect error message. (Bug #30239721)

For SELECT statements, an INTO var_name clause prior to a locking clause is legal but the parser rejected it. (Bug #30237291, Bug #96677)

FLUSH TABLES WITH READ LOCK caused a deadlock when a LOCK INSTANCE FOR BACKUP statement was previously executed within the same session and there was a concurrent ALTER DATABASE statement running in another session against the same database specified (implicitly or explicitly) for the FLUSH TABLES WITH READ LOCK statement. (Bug #30226264)

Slow query logging could result in a server exit for connections that did not use the classic client/server protocol. (Bug #30221187)

A statement that added a foreign key without an explicit name failed when re-executed as a prepared statement or in a stored program with an unwarranted duplicate foreign key name error. (Bug #30214965, Bug #96611)

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

With multiple sessions executing concurrent INSERT ... ON DUPLICATE KEY UPDATE statements into a table with an AUTO_INCREMENT column but not specifying the AUTO_INCREMENT value, inserts could fail with a unique index violation. (Bug #30194841, Bug #96578)

Client programs could load authentication plugins from outside the plugin library. (Bug #30191834, Bug #30644258)

When switching between table scans and index lookups, AlternativeIterator did not reset the handler, which could lead to assertion failures. (Bug #30191394)

Setting open_files_limit to a large value, or setting it when the operating system rlimit had a value that was large but not equal to RLIM_INF could cause the server to run out of memory. As part of this fix, the server now caps the effective open_files_limit value to the the maximum unsigned integer value. (Bug #30183865, Bug #96525)

References to fully qualified INFORMATION_SCHEMA tables could fail depending on the lettercase in which INFORMATION_SCHEMA was specified. (Bug #30158484)

Slow queries with an execution time greater than 35 days could cause corruption of the mysql.slow_log system table requiring a REPAIR TABLE operation. (Bug #30113119, Bug #96373)

MySQL did not support sending systemd notification messages to a socket specified using the NOTIFY_SOCKET environment variable, if the variable named an abstract namespace socket. (Bug #30102279)

Using SET PERSIST_ONLY to set a boolean system variable to a numeric value resulted in the server being unable to restart. (Bug #30094645, Bug #30298191, Bug #96848)

A fix for a previous issue combined two TABLE_LIST constructors in an unfortunate way. One of these created a TABLE_LIST object from a TABLE object representing a temporary table. Previously, the table name was made the same as the alias; this was changed to copying the name from the TABLE object. Due to the fact that, for a temporary table, the table name is a file path, it was possible to exceed the limit for MDL_key names, leading to a failed assertion. Fixed by reintroducing dedicated constructors which behave in the manner that they did prior to the fix. (Bug #30083125)

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

For UNIX_TIMESTAMP() errors occurring within stored functions, the number of fractional seconds for subsequent function invocations could be incorrect. (Bug #30034972, Bug #96166)

When a common table expression contained a nondeterministic expression (such one that used RAND()) and the common table expression was referenced more than once in the outer query, it was merged in some cases. This caused the common table expression to return a different result for each reference. Now in such cases, the common table expression is not merged, but rather is materialized instead. (Bug #30026353)

In debug build of MySQL started on Linux with a lower_case_table_names=1 setting, discarding a tablespace for a partitioned table after an in-place upgrade from MySQL 8.0.16 caused a serious error. The partition tablespace name stored in the data dictionary was invalid, and the metadata lock key prepared for the partition tablespace in MySQL 8.0.17 did not match the key stored in the mysql.tablespaces table. (Bug #30024653)

KILL QUERY could kill the statement subsequent to the one intended. (Bug #29969769)

With lower_case_table_names=2, SHOW TABLES could fail to display tables with uppercase names. (Bug #29957361)

The error message reported for attempts to upgrade tables with invalid expressions for generated columns did not provide sufficient information. The error message now includes the generated column name and the expression used to create the generated column. (Bug #29941887, Bug #95918)

Attempting to display an unresolvable view could result in a server exit rather than an error. (Bug #29939279)

Incorrect checking of temporal literals for CREATE TABLE statements could lead to a server exit. (Bug #29906966, Bug #95794)

Attempting to spawn a thread for a parallel read operation while system resources were temporary unavailable raised system error. (Bug #29874480)

Writing unexpected values to the mysql.global_grants system table could cause a server exit. (Bug #29873343)

The LAST_EXECUTED value in the INFORMATION_SCHEMA.EVENTS table was incorrectly reported in UTC, not in the event time zone. (Bug #29871530, Bug #95649)

With keyring_encrypted_file_password set on the command line at server startup, the password value could be visible to system utilities. (Bug #29848634)

Changing the lower_case_table_names setting when upgrading from MySQL 5.7 to MySQL 8.0 could cause a failure due to a schema or table name lettercase mismatch. If lower_case_table_names=1, table and schema names are now checked by the upgrade process to ensure that all characters are lowercase. If table or schema names are found to contain uppercase characters, the upgrade process fails with an error. For related information, see Preparing Your Installation for Upgrade. (Bug #29842749, Bug #95559)

With a LOCK TABLES statement in effect, a metadata change for the locked table could cause Performance Schema or SHOW queries for session variables to hang in the opening_tables state. (Bug #29836204, Bug #92387)

A SELECT using a WHERE condition of the form A AND (B OR C [OR ...]) resulting in an impossible range led to an unplanned exit of the server. (Bug #29770705)

For JSON-format audit logging, the id field now may contain values larger than 65535. Previously, with heaving logging activity, more than 65536 queries per second could be executed, exceeding the 16 bits permitted for id values. (Bug #29661920)

An incomplete connection packet could cause clients not to properly initialize the authentication plugin name. (Bug #29630767)

Out-of-memory errors from the parser could be ignored, resulting in a server exit. (Bug #29614521)

On Linux, an assertion could be raised when the Performance Schema file instrumentation was disabled and re-enabled. (Bug #29607570)

For a column defined as a PRIMARY KEY in a CREATE TABLE statement, a default value given as an expression was ignored. (Bug #29596969, Bug #94668)

The TABLE_ENCRYPTION_ADMIN privilege, added in MySQL 8.0.16, was incorrectly granted to the system-defined mysql.session user during upgrade. (Bug #29596053, Bug #94888)

For connections encrypted with OpenSSL, network I/O at the socket level was not reported by the Performance Schema. Also, network I/O performed while the server was in an IDLE state was not reported by the Performance Schema. (Bug #29205129, Bug #30535558, Bug #97600)

When a query used a subquery that was merged into the outer query block (due to a semijoin transformation or merge of a derived table), and the subquery itself contained a subquery with an aggregate function with an aggregation query block that differed from its base query block, the query could sometimes fail to return any rows unless executed a second time or preceded with FLUSH TABLES. This was because, when merging, the information regarded tables used and the aggregation information for the aggregate function was not updated properly. In the case which raised this bug report, this meant that the comparison operation containing a scalar subquery was regarded as const-for-execution and therefore the range optimizer attempted to evaluate it, and the scalar subquery contained a MIN() function referring to an outer reference which had not yet been read. Thus, when the aggregator object was populated, it was based on uninitialized data, leading to unpredictable results. (Bug #28941154)

Changing the mandatory_roles system variable could cause SHOW GRANTS in concurrent sessions to produce incorrect results. (Bug #28699403)

Failure of keyring_aws initialization caused failure of SSL socket initialization. (Bug #28591098)

Under certain conditions, enabling the read_only or super_read_only system variable did not block concurrent DDL statements executed by users without the SUPER privilege. (Bug #28438114, Bug #91852)

The current GROUP BY plan is improved so that every gap attribute is allowed to have a disjunction of equality predicates. Predicates from different attributes must still be conjunctive to each other in order to take advantage of this enhancement.

Our thanks to Facebook for this contribution. (Bug #28056998, Bug #15947433, WL #13066)

In some cases, BIGINT arguments to the FLOOR() and CEILING() functions were resolved as the wrong type. (Bug #27125612)

mysqlpump exits rather than dumping databases that contain an invalid view, by design, but it also failed if an invalid view existed but was not in any of the databases to be dumped. (Bug #27096081)

Foreign key information is now retrieved from the data dictionary, not from InnoDB. (Bug #25583288)

Foreign key definitions used in CREATE TABLE and ALTER TABLE statements for InnoDB tables were ignored if the statements were wrapped in conditional comments (such as /*!50101 ... */ or /*! ... */). (Bug #21919887, Bug #78631)

The --log-raw option is now available at runtime as the log_raw system variable. The system variable is set at startup to the option value, and may be set at runtime to change password masking behavior. (Bug #16636373, Bug #68936)

EXPLAIN ANALYZE did not execute subqueries in the SELECT list, and thus did not take them into account in its calculations of time or cost. (Bug #97296, Bug #30444266)

An inner scalar subquery containing an outer reference did not return the same result using a nested set of SELECT expressions on the right hand side as when using a single SELECT that was equivalent. (Bug #97063, Bug #30381092)

A materialized subquery could yield different results depending on whether it used an index. (Bug #96823, Bug #30289052)

When a query terminated due to exceeding the time specified using the MAX_EXECUTION_TIME hint, the error produced differed depending on the stage of the query. In particular, if the query terminated during a filesort, the error raised was ER_FILSORT_ABORT, even though in such cases the query should always exit with ER_QUERY_TIMEOUT. This made it unnecessarily difficult to trap such errors and to handle them correctly.

This fix removes the error codes ER_FILSORT_ABORT and ER_FILESORT_TERMINATED. (Bug #96537, Bug #30186874)

If a stored procedure had a parameter named member or array, and it had been defined without quoting the parameter names, the database in which it was defined could not be upgraded to 8.0.17 or 8.0.18. (Bug #96288, Bug #30084237)

References: See also: Bug #96350, Bug #30103640.

When a function such as COALESCE() or IFNULL() was passed a BIGINT column value, casting a negative return value from this function to UNSIGNED unexpectedly yielded zero.

Our thanks to Oleksandr Peresypkin for this contribution. (Bug #95954, Bug #29952066)

EXPLAIN output showed Select tables optimized away for a query using MAX() on an indexed column, but if MAX() on the same column was called in a user function, it showed Using index instead. (Bug #94862, Bug #29596977)