Previously, each MySQL user account was permitted to have a single password. MySQL now permits an account to have dual passwords, designated as primary and secondary passwords. This capability enables phased password changes to be performed seamlessly in complex multiple-server systems, without downtime. To support dual-password capability, the ALTER USER and SET PASSWORD statements now have a RETAIN CURRENT PASSWORD clause that saves the current password as the secondary password when you assign an account a new primary password. ALTER USER also has a DISCARD OLD PASSWORD clause to discard a secondary password that is no longer needed. See Password Management.

(WL #11540)

The audit API now enables applications to add their own message events to the audit log using the new audit_api_message_emit component, which includes an audit_api_message_emit_udf() loadable function. See The Audit Message Component. (WL #11542)

The minimum version of the Boost library for server builds is now 1.68.0. (Bug #28478497)

A new host_application_signal component service is available to enable components to deliver signals to the host application. For example, the service enables replication components to send a shutdown signal to the server. (WL #12003)

Previously, the COMPILATION_COMMENT CMake option was used by the server (for example, to set the version_comment system variable) and by other programs. However, when the value contained the word “server”, it was inappropriate for use by other programs. The server now uses the new COMPILATION_COMMENT_SERVER option. Other programs continue to use COMPILATION_COMMENT. (Bug #28888510)

The content of the .gitignore file has been cleaned up. Much of this file was inherited from its .bzrignore predecessor and was not relevant. One implication of this cleanup is that in-source builds are disallowed. (Bug #28341794, Bug #91626)

MySQL Server now permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections even when max_connections connections are already established. The administrative network interface has these characteristics:

Thanks to Facebook for suggesting the idea (and for contributing code, although it was not used). (Bug #27847672, Bug #90395, WL #12138)

The deprecated resolveip and resolve_stack_dump utilities have been removed and are no longer included in MySQL distributions. nslookup, host, or dig can be used instead of resolveip. Stack traces from official MySQL builds are always symbolized, so there is no need to use resolve_stack_dump. (WL #12619, WL #12620)

The JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregate functions now can be used as window functions, if an OVER clause is present. See Aggregate Function Descriptions. (WL #11574)

A new system variable, log_slow_extra, if enabled, causes the server to write additional fields to slow query log lines that provide information about slow statements. In addition, SET lines written to the log to indicate statement timestamps now use the time from the beginning of statement execution, rather than the time at the end of execution. See The Slow Query Log. Thanks to Facebook for the contribution on which this feature is based. (Bug #27535580, Bug #89637, WL #12393)

Binary log files and relay log files can now be encrypted, helping to protect these files and the potentially sensitive data contained in them from being misused by outside attackers, and also from unauthorized viewing by users of the operating system where they are stored.

You enable encryption on a MySQL server by setting the new binlog_encryption system variable to ON. OFF is the default. The system variable sets encryption on for binary log files and relay log files. When you first start the server with encryption enabled, a new binary log encryption key is generated before the binary log and relay logs are initialized. This key is used to encrypt a file password for each binary log file (if the server has binary logging enabled) and relay log file (if the server has replication channels), and further keys generated from the file passwords are used to encrypt the data in the files.

If you activate encryption while the server is running, a new binary log encryption key is generated at that time, and the binary log files and relay log files are rotated so that the new and subsequent files are encrypted. If you deactivate encryption by changing the binlog_encryption system variable to OFF, the binary log file and relay log files are rotated immediately and all subsequent logging is unencrypted. Previously encrypted files are not automatically decrypted, but the server is still able to read them. (The SHOW BINARY LOGS statement now shows whether each binary log file is encrypted or unencrypted.) SUPER privileges or the new BINLOG_ENCRYPTION_ADMIN privilege are required to activate or deactivate encryption while the server is running.

The encryption algorithm used for the files, the AES (Advanced Encryption Standard) cipher algorithm, is built in to MySQL Server and cannot be configured. The binary log encryption keys used to encrypt the file passwords for the log files are 256-bit keys that are generated specifically for each MySQL server instance using MySQL Server's built-in keyring service. The binary log encryption key that is currently in use on the server is called the binary log master key.

The new binlog_rotate_encryption_master_key_at_startup system variable controls whether the binary log master key is automatically rotated when the server is restarted. If this system variable is set to ON, a new binary log encryption key is generated and used as the new binary log master key whenever the server is restarted. If it is set to OFF, which is the default, the existing binary log master key is used again after the restart.

Note that when encryption is active for a MySQL server instance, only the data at rest that is written to the binary log files and relay log files is encrypted. The data in motion in the replication event stream, which is sent to MySQL clients including mysqlbinlog, is always in unencrypted format, so it must be protected in transit by the use of connection encryption. The data in use that is held in the binary log transaction and statement caches during a transaction, and any data that exceeds the space available in those caches and is therefore stored in a temporary file on disk, is also in unencrypted format. The temporary files and caches are deleted when the thread that handles the transaction ends. (WL #10957)

Server logging behavior has changed with respect to error log messages generated prior to processing startup options that specify logging configuration. Previously, the server generated messages with the default timestamp, format, and verbosity level; buffered them; and then flushed them after the error log configuration became known. Because these early messages used the default logging configuration, they could differ from what is specified by the startup options.

Now, the server buffers log events rather than formatted log messages. This enables it to retroactively apply configuration settings to those events after the settings are known, with the result that flushed messages use the configured settings, not the defaults. For more information, see Error Log Output Format. (WL #11875)

Previously, derived tables and common table expressions could not contain outer references. Outer references are now permitted. (WL #461)

Ubuntu 18.10 and Fedora 29 install OpenSSL 1.1.1 by default, but OpenSSL 1.1.1 is not fully supported by MySQL. To install MySQL, the OpenSSL 1.0.2 compatibility package must be installed. (Bug #28981868)

The Performance Schema statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) now have a STATEMENT_ID column that indicates the query ID maintained by the server at the SQL level. Column values are unique for the server instance because they are generated using a global counter that is incremented atomically. (WL #12165)

If the LDAP port number is configured as 636 or 3269, the plugin now uses LDAPS (LDAP over SSL) instead of LDAP. The port number is settable using the authentication_ldap_sasl_server_port or authentication_ldap_simple_server_port system variable. (LDAPS differs from startTLS.) (Bug #28743563)

Previously, for LDAP authentication with proxying, LDAP authentication plugins used the first group name returned by the LDAP server as the MySQL proxied user account name. The authentication string for a MySQL account now can specify a list of groups to match, in preference order, and can optionally map the matching group name to a specified MySQL proxied user name. See LDAP Pluggable Authentication. (WL #12005)

The OpenSSL libraries bundled with MySQL on some platforms (Windows, macOS, and Generic Linux) have been upgraded to version 1.0.2q. On all other platforms, MySQL uses the system installed OpenSSL. Issues fixed in the new OpenSSL version are described at http://www.openssl.org/news/vulnerabilities.html. (Bug #28988091)

To affect subsequent server restarts, the SET PERSIST and SET PERSIST_ONLY statements enable system variables to be persisted to the mysqld-auto.cnf option file in the data directory. However, some system variables cannot be persisted (for example, because they involve sensitive data). Consequently, they cannot be set at runtime from within a session conducted by a remote administrator, and thus require the administrator to log into the server host and directly modify a my.cnf option file.

MySQL now permits users to perform runtime administration of many previously nonpersistible system variables, so that they can be persisted under certain restrictive conditions. To enable this capability, designate an SSL certificate X.509 Subject value that signifies the ability to persist these restricted system variables, and set the new persist_only_admin_x509_subject system variable to that Subject value. Users who connect to the server using an encrypted connection and supply an SSL certificate with the designated Subject value then can use SET PERSIST_ONLY to persist persist-restricted system variables. For more information, see Nonpersistible and Persist-Restricted System Variables. (WL #12086)

For most system variables, setting the session value requires no special privileges and can be done by any user to affect the current session. For some system variables, setting the session value can have effects outside the current session and thus is a restricted operation that can be done only by users who have a special privilege. Previously, either SYSTEM_VARIABLES_ADMIN or SUPER qualified as such a privilege, but both privileges also permit operations other than setting session variables. The new SESSION_VARIABLES_ADMIN privilege makes it possible to grant users only the ability to set restricted session variables without also enabling other operations.

Any operation permitted by SESSION_VARIABLES_ADMIN is also permitted by SYSTEM_VARIABLES_ADMIN or SUPER, so any user who already has one of the latter privileges effectively has SESSION_VARIABLES_ADMIN by implication and need not be granted SESSION_VARIABLES_ADMIN explicitly. However, if a user has been granted SYSTEM_VARIABLES_ADMIN or SUPER only for the purpose of enabling that user to modify restricted session system variables, an administrator can reduce the user's privilege footprint by revoking SYSTEM_VARIABLES_ADMIN and SUPER, and granting SESSION_VARIABLES_ADMIN instead. For instructions, see System Variable Privileges.

These previously restricted session variables required SYSTEM_VARIABLES_ADMIN or SUPER but now can also be set with SESSION_VARIABLES_ADMIN:

binlog_format
binlog_row_image
binlog_row_value_options
binlog_rows_query_log_events
debug
debug_sync
default_collation_for_utf8mb4
explicit_defaults_for_timestamp
gtid_next
histogram_generation_max_mem_size
original_commit_timestamp
sql_log_bin
sql_log_off
sql_require_primary_key

These previously unrestricted session variables now are restricted and setting them requires at least SESSION_VARIABLES_ADMIN (they can also be set by users who have SYSTEM_VARIABLES_ADMIN or SUPER):

auto_increment_increment
auto_increment_offset
binlog_direct_non_transactional_updates
bulk_insert_buffer_size
character_set_filesystem
character_set_database
collation_database
pseudo_slave_mode
pseudo_thread_id
rbr_exec_mode
transaction_write_set_extraction

(WL #12217)

The ST_Distance() function now takes an optional third argument enabling the unit for the return value to be specified. Permitted units are those listed in the new INFORMATION_SCHEMA ST_UNITS_OF_MEASURE table. See Spatial Relation Functions That Use Object Shapes, and The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table. (WL #11000)

A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause. A derived table specified with LATERAL can occur only in a FROM clause, either in a list of tables separated with commas or in a join specification (JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN, or RIGHT [OUTER] JOIN). Lateral derived tables make possible certain SQL operations that cannot be done with nonlateral derived tables or that require less-efficient workarounds. See Lateral Derived Tables.

(WL #8652)

The INFORMATION_SCHEMA tables that accompany the thread pool plugin have been migrated to be available as Performance Schema tables. The INFORMATION_SCHEMA tables now are deprecated and will be removed in a future MySQL version. Applications should transition away from the old tables to the new tables. For example, if an application uses this query:

SELECT * FROM INFORMATION_SCHEMA.TP_THREAD_STATE;

The application should use this query instead:

SELECT * FROM performance_schema.tp_thread_state;

For more information, see Performance Schema Thread Pool Tables. (WL #11547)

X Plugin now includes the 5-digit SQLSTATE error code in its error handling class. Previously, the SQLSTATE error code was returned to clients for SQL errors, but only the MySQL-specific error number was exposed. (Bug #28735058)

When querying collections of documents, if boolean values were used as arguments for a placeholder in an SQL query, unexpected results were returned. A new translation specialization has now been added for boolean values so that they are handled correctly in this situation. (Bug #28227037)

X Protocol now always converts retrieved data to the utf8mb4 character set (using the utf8mb4_general_ci collation) before returning it. (Bug #28180155)

X Protocol now supports SQL prepare functionality. (WL #9270)

InnoDB: Disabling the innodb_buffer_pool_in_core_file variable reduces the size of core files by excluding InnoDB buffer pool pages. To use this variable, the core_file variable must be enabled and the operating system must support the MADV_DONTDUMP non-POSIX extension to madvise(), which is supported in Linux 3.4 and later. For more information, see Excluding Buffer Pool Pages from Core Files.

Thanks to Facebook for the contribution. (Bug #27724476, Bug #90144)

InnoDB: By default, undo logs reside in two undo tablespaces that are created when the MySQL instance is initialized.

Additional undo tablespaces can be created in a chosen location at runtime using CREATE UNDO TABLESPACE syntax.

CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';

Undo tablespaces created using CREATE UNDO TABLESPACE syntax can be dropped at runtime using DROP UNDO TABLESPACE syntax.

DROP UNDO TABLESPACE tablespace_name;

ALTER UNDO TABLESPACE syntax can be used to mark an undo tablespace as active or inactive.

ALTER UNDO TABLESPACE tablespace_name SET {ACTIVE|INACTIVE};

A STATE column that shows the state of a tablespace was added to the INFORMATION_SCHEMA.INNODB_TABLESPACES table. An undo tablespace must be in an empty state before it can be dropped.

The previously deprecated innodb_undo_tablespaces variable is no longer configurable and will be removed in a future MySQL version.

For more information, see Undo Tablespaces. (WL #9508)

InnoDB: InnoDB now supports parallel clustered index reads, which can improve CHECK TABLE performance. This feature does not apply to secondary index scans. The innodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads to occur. The default value is 4. The actual number of threads used to perform a parallel clustered index read is determined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller. (WL #11720)

InnoDB: The ADD DATAFILE clause of the CREATE TABLESPACE statement is now optional, which permits users without the FILE privilege to create tablespaces. A CREATE TABLESPACE statement executed without an ADD DATAFILE clause implicitly creates a tablespace data file with a unique file name. (WL #12236)

InnoDB: When the server is started with --innodb-dedicated-server, the size and number of log files are now determined by the buffer pool which has been set by this option. Previously, log file size was set according to the amount of memory detected on the server, and the number of log files was not set automatically. See Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server. (WL #12300)

Replication: Two new session variables have been added for internal use by replication. original_server_version and immediate_server_version support cross-version replication by transmitting the MySQL server release numbers associated with a transaction through the replication topology. original_server_version holds the MySQL Server release number of the server where a transaction was originally committed (for example, 80014 for a MySQL 8.0.14 server instance). immediate_server_version holds the MySQL Server release number of the server that is the immediate master in a replication topology. If either of those servers, or another intervening server in the replication topology, is at an older release that does not support these session system variables, their values are set to 0.

With this information the slave can correctly process data originating from a master at an older release, by recognizing where syntax changes or semantic changes have occurred between the releases involved and handling these appropriately. The information can also be used in a Group Replication environment where one or more members of the replication group is at a newer release than the others. The value of the variable can be viewed in the binary log for each transaction (as part of the Gtid_log_event, or Anonymous_gtid_log_event if GTIDs are not in use on the server), and could be helpful in debugging cross-version replication issues. (WL #11879)

Replication: When running a group in single-primary mode, in the event of a new primary being elected while there were transactions held in the backlog to be applied, there was a chance that a read operation against the new primary could return a stale value. Now, you can use the group_replication_consistency variable to control how a group behaves in this situation. When group_replication_consistency is set to EVENTUAL, a new primary responds to read requests even when there is a backlog which has not yet been applied, which matches the previous behavior and comes with the risk that a client could read old values while any backlog is being applied. Writes to the new primary fail during this period because it is has super_read_only mode enabled. When group_replication_consistency is set to BEFORE_ON_PRIMARY_FAILOVER, any new read or write queries against a newly elected primary that is applying backlog from the old primary are held until the backlog is applied. This ensures that clients always read the newest value which they have written, but also means that clients might have to wait until the backlog has been applied before they can read from the new primary. (WL #11123)

References: See also: Bug #26004894.

Group Replication: The Group Communication System (GCS) and group communication engine (XCom, a Paxos variant) for Group Replication now provide full support for IPv6, so replication group members can use IPv6 addresses as an alternative to IPv4 addresses for internal group communications. The localhost address for IPv6, and the private subnetwork addresses for IPv6 (unique-local addresses and link-local unicast addresses), are added to the automatic whitelist for Group Replication for use if no manual whitelist is specified.

If all members of a replication group are at a MySQL server version that supports the use of IPv6 addresses for Group Replication, the group can contain a mix of members using IPv6 addresses and members using IPv4 addresses. Joining members must provide whitelisted IP addresses or host names that match the protocols offered by the seed members for connection, but the joining member's main identifying address or host name (group_replication_local_address) can use either protocol. If a member uses a host name that resolves to both an IPv4 and an IPv6 address, the IPv4 address is always used for Group Replication connections.

If any or all existing members of a replication group use an older MySQL Server version without support for the use of IPv6 addresses for Group Replication, joining members must present an IPv4 address for group communications in the group_replication_local_address option. When every group member has been upgraded, the group can be migrated to IPv6 addresses. (Bug #26088469, Bug #27757729, Bug #90217, WL #11926)

Group Replication: MySQL Group Replication can now communicate using a dedicated input channel as an alternative to using a TCP socket. The new input channel uses shared memory for communication between the Group Replication logic and the local instance of the underlying group communication engine (XCom, a Paxos variant).

Previously, communication with the local XCom instance always took place using a TCP socket, namely the network address that is specified by the group_replication_local_address system variable for each group member. This incurred overheads that were unnecessary for local communication, such as memory copying through the network protocol stack and data serialization. A TCP socket (group_replication_local_address) is still required for each group member to communicate with remote XCom instances. The Group Communication System (GCS) component of Group Replication now selects the most appropriate communication method for each Group Replication task, either the input channel or TCP. For example, the process of joining a group requires communication with a remote XCom instance, so TCP must be used. However, the process of removing a member from a group only requires communication with the local XCom instance, so the input channel is used. The input channel is selected wherever possible to minimize the overheads associated with communication using networking mechanisms. (WL #9850)

Microsoft Windows: The access control granted to clients on the named pipe created by the MySQL server now is set to the minimum necessary for successful communication on Windows. Newer MySQL client software can open named pipe connections without any additional configuration. If older client software cannot be upgraded immediately, the new named_pipe_full_access_group server system variable can be used to give a Windows group the necessary permissions to open a named pipe connection. Membership in the full-access group should be restricted and temporary. (WL #12445)

The minimal server RPM is mostly used for Docker images. For better Docker compatibility, the log-error line has been removed from the rpm-docker configuration file. This way, logging goes to stdout/stderr, enabling use of Docker's own interface. (Bug #28692675)

Error messages relating to creating and dropping foreign keys were improved to be more specific and informative. (Bug #28526309, Bug #92087)

The error message for ALTER TABLE statements that attempted character set conversion but failed was improved to indicate which column produced the error. (Bug #27546306, Bug #88738)

Previously, for command options that take a numeric value, the value could be given with a suffix of K, M, or G to indicate a multiplier of 1024, 1024² or 1024³. Now a suffix can also be T, P, and E to indicate a multiplier of 1024⁴, 1024⁵ or 1024⁶. Thanks to Daniel Black for the patch. (Bug #27306931, Bug #89017)

Resource group locking was revised to improve scalability and performance. (Bug #27148580)

The startup option --binlog-row-event-max-size now has a corresponding system variable binlog_row_event_max_size. The startup option and system variable set a soft limit on the maximum size of a row-based binary log event, with a default setting of 8192 bytes. Where possible, rows stored in the binary log are grouped into events with a size not exceeding the value of this setting. If an event cannot be split, the maximum size can be exceeded.

The binlog_row_event_max_size global system variable is read-only and can be set only at server startup. Its value can therefore only be modified by using the PERSIST_ONLY keyword or the @@persist_only qualifier with the SET statement. The addition of a system variable means that this setting can be viewed using the Performance Schema tables or a SHOW VARIABLES or SELECT statement. (Bug #19985377, Bug #74728, WL #12385)

ALTER TABLE now can be used to change a column character set in place (without a table rebuild), when these conditions apply:

(WL #11605)

The new -DFORCE_INSOURCE_BUILD CMake option defines whether to force an in-source build. Out-of-source builds are recommended, as they permit multiple builds from the same source, and cleanup can be performed quickly by removing the build directory. To force an in-source build, invoke CMake with -DFORCE_INSOURCE_BUILD=ON.

Important Change: Importing a dump from a MySQL 5.7 server to a server running MySQL 8.0 often failed with ER_WRONG_VALUE_FOR_VAR when an SQL mode not supported by the 8.0 server was used. This could happen frequently due to the fact that NO_AUTO_CREATE_USER is enabled by default in MySQL 5.7 but not supported in MySQL 8.0.

The behavior of the server in such circumstances now depends on the setting of the pseudo_slave_mode system variable. If this is false, the server rejects the mode setting with ER_UNSUPPORTED_SQL_MODE. If pseudo_slave_mode is true, the server ignores the unsupported mode and gives a warning. Note that mysqlbinlog sets pseudo_slave_mode to true prior to executing any SQL. (Bug #90337, Bug #27828236)

InnoDB: Global and backup metadata locks were not released after the background purge thread truncated undo logs. (Bug #29215254, Bug #93901)

InnoDB: MySQL would not start on Solaris X86. The static thread-local 'tables' variable in the TempTable storage engine was not properly initialized. (Bug #28987365)

InnoDB: Latching logic used during deadlock detection was simplified. (Bug #28904966)

InnoDB: An invalid record offset for an old version of a clustered index record raised a debug assertion. (Bug #28825617)

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

InnoDB: The minimum DML delay imposed when the length of the history list exceeds innodb_max_purge_lag was decreased from 5000 microseconds to 5 microseconds. (Bug #28813453)

InnoDB: An incorrect lock order caused a deadlock when one thread attempted to drop a table while another created an encrypted tablespace. (Bug #28774259)

InnoDB: ALTER TABLESPACE failed to ignore unsupported tablespace attributes. (Bug #28656611)

InnoDB: Implicit to explicit lock conversion logic was simplified and optimized. (Bug #28637472)

InnoDB: A fragment page allocation failure raised an assertion. (Bug #28615893)

InnoDB: Incorrectly placed debug points caused flushed LOB pages to be considered corrupt. (Bug #28607368)

InnoDB: The TempTable storage engine incorrectly created temporary files in the system temporary directory instead of the directory defined by the tmpdir variable. (Bug #28598943)

InnoDB: Attempting to drop a table with a name similar to that of a full-text search auxiliary table caused an assertion failure. (Bug #28577083)

InnoDB: A function called by an UPDATE query did not account for virtual columns. (Bug #28560650)

InnoDB: An incorrect key was defined for the buffer pool zip hash mutex. (Bug #28556539)

InnoDB: Deadlock handling for background transactions that involve the mysql.innodb_table_stats and mysql.innodb_index_stats tables was modified. The tables were incorrectly included in an assertion that is triggered when internal tables are included in a deadlock cycle. (Bug #28523042, Bug #92069)

InnoDB: Setting innodb_spin_wait_delay to a high value caused an assertion failure when attempting to shut down the server. To prevent this failure from occurring, the innodb_spin_wait_delay maximum value was reduced to 1000. (Bug #28489407, Bug #91973)

InnoDB: An ON DELETE CASCADE operation on table with a foreign key constraint and an indexed virtual column caused the server to exit. (Bug #28470805)

InnoDB: An incorrectly written DML log involving a virtual column value raised an assertion. (Bug #28448853)

InnoDB: A RENAME TABLE operation failed when run on a table created outside of the MySQL data directory using the DATA DIRECTORY clause. (Bug #28341514)

InnoDB: ALTER TABLE ... EXCHANGE PARTITION permitted partitions with different virtual column definitions to be exchanged, which resulted in an assertion when InnoDB later attempted to read from a nonexistent virtual column. (Bug #28235668)

InnoDB: A counter was added for redo log write and flush requests that occur during transaction commit. The counter is used by the log writer thread to compute the average time between consecutive requests. When the average time is greater than 100 microseconds, log writer threads do not use spin delay and instead wait on request events with a 10 microsecond timeout limit.

A log writer thread implementation issue that could cause a hang was also fixed. (Bug #28062382, Bug #28444247, Bug #28616442, Bug #90890)

InnoDB: An assertion was raised when attempting to add rollback segments to newly added undo tablespace that was not fully initialized. (Bug #27914054)

InnoDB: Foreign key constraints were ignored after a RENAME TABLE operation. (Bug #27453180, Bug #89441)

InnoDB: Using the O_DIRECT_NO_FSYNC innodb_flush_method setting could cause the system to hang due to file system metadata becoming unsynchronized. To prevent this issue from occurring in O_DIRECT_NO_FSYNC mode, InnoDB now calls fsync() after creating a new file, after increasing file size, and after closing a file. The fsync() system call is still skipped after each write operation. (Bug #27309336)

InnoDB: Specifying the CREATE TABLE or ALTER TABLE ENCRYPTION option with an empty string failed to raise an error and was interpreted as a default setting, which is ENCRYPTION='N'. Specifying an empty string is now treated as invalid and raises an error. (Bug #27177845)

InnoDB: Partitioned table name delimiters (the #P# or #SP# part of a partitioned table name) were not converted to lowercase when moving tablespace data files from a MySQL instance on Windows to a MySQL instance on Linux where the lower_case_table_names variable was enabled. Failure to fully convert names to lowercase caused errors such as “'InnoDB error' from storage engine” when attempting to alter, rename, or optimize the tables. (Bug #26925260)

InnoDB: An assertion was raised when attempting to write to a tablespace file greater than 4GB in size on a 64-bit Windows system. The failure was due to a narrowing cast. (Bug #26636815, Bug #87423)

InnoDB: After attempting to create a table with a foreign key constraint that referenced a partitioned table, which is an unsupported operation, SHOW ENGINE INNODB STATUS output incorrectly reported a foreign key error indicating that the referenced table name could not be resolved. This error no longer appears, and the error message returned to the client now states that foreign keys are not yet supported in conjunction with partitioning. (Bug #25319071, Bug #84331)

InnoDB: Misleading error messages were reported for unsupported foreign key operations, including creating a foreign key that referenced a partitioned table, and referencing a table that uses a storage engine that does not support foreign keys. The error messages are now more informative. (Bug #11747571, Bug #33027)

Partitioning: Trying to perform an instant add column on a discarded tablespace led to an assert. An error is now returned in such cases. (Bug #28517843)

Partitioning: Repeated ALTER TABLE statements on partitioned tables containing BLOB or TEXT columns were not always handled correctly. (Bug #28491099)

Partitioning: ALTER TABLE ... EXCHANGE PARTITION did not work when the partitioned table had one or more partition definitions using the DATA DIRECTORY option. This fix supports partitioned tables using the InnoDB storage engine only. (Bug #19730200)

Replication: Depending on the value of group_replication_exit_state_action, the behavior of members exiting a group was not consistent. To harmonize the behavior of members exiting the group regardless of the error scenario, now when a member with group_replication_exit_state_action=READ_ONLY exits the group unintentionally, the super_read_only mode that the member had when started is restored. This makes the behavior consistent with that of a member with group_replication_exit_state_action=ABORT_SERVER. (Bug #28971639, Bug #28526591)

Replication: The metadata written to the binary log for CREATE TABLE statements includes character set information for the character columns in the table. Previously, when the mysqlbinlog option --print-table-metadata was specified, a default character set was printed for the table. This default character set was the character set that appeared most frequently in the table columns, and might not match the default character set that had been specified for the table. mysqlbinlog now prints the character set for each column individually. The columns are also printed on separate lines. (Bug #28774144)

Replication: Character set information was not written to the binary log as part of the table metadata for ENUM and SET columns. This information is now added when binlog_row_metadata=FULL is set, which produces extended metadata. (For character columns, character set information is also added with binlog_row_metadata=MINIMAL.) (Bug #28706307)

Replication: A patch to correct the handling of quotes for identifiers in ROLLBACK TO SAVEPOINT statements in the binary log was not correctly applied to subsequent MySQL versions. (Bug #28569645)

Replication: Following a patch in MySQL 5.7.23, LOAD DATA statements stopped statement-based replication from a MySQL 5.7.22 master to a replication slave at a later release. The problem has now been fixed. (Bug #28541204, Bug #92132)

Replication: In some circumstances, the CHANGE MASTER TO statement could not be used on a replication slave if the master info log had been changed from a table (master_info_repository=TABLE) into a file (master_info_repository=FILE). (Bug #28529558)

Replication: mysqlbinlog incorrectly added statements to set the sql_require_primary_key system variable (which was introduced in MySQL 8.0.13) to ON for events involving DML SQL statements. The check carried out when the system variable is set to ON is only relevant for DDL SQL statements that create new tables or alter the structure of existing tables. (Bug #28524803)

Replication: When the system variables binlog_transaction_dependency_tracking and binlog_transaction_dependency_history_size were set or read, the types of lock that were required could result in a deadlock scenario, because the same locks were also required for working with the active binary logs. A new lock type is now used instead for access to the transaction dependency tracking system variables, so that this deadlock cannot occur. (Bug #28511326, Bug #91941, Bug #28537209, Bug #92108)

Replication: An assertion was raised in debug builds if an implicit commit was attempted when the GTID value for the next transaction had not yet been determined (gtid_next=NOT_YET_DETERMINED). The gtid_next system variable has this value immediately after the internal-use statement BINLOG has been issued by mysqlbinlog to execute a format description event. If a statement with an implicit commit was attempted next (such as a CREATE TABLE statement), the gtid_next setting did not transition to AUTOMATIC state, and was left in an unacceptable state. If autocommit was on, the error ER_CANT_SET_GTID_NEXT_TO_ANONYMOUS_WHEN_GTID_MODE_IS_ON was also logged when the statement was attempted.

To fix this issue, the use of the BINLOG statement is now prevented during transactions if it would change the state of gtid_next. The error ER_VARIABLE_NOT_SETTABLE_IN_TRANSACTION is returned if this is attempted. Also, when GTIDs are in use and the value of gtid_next is NOT_YET_DETERMINED, the next statement must either explicitly set gtid_next to a valid value or leave the GTID state unaffected. Otherwise the error ER_CANT_SET_GTID_NEXT_TO_ANONYMOUS_WHEN_GTID_MODE_IS_ON is returned. (Bug #28490793, Bug #91980)

Replication: The PURGE BINARY LOGS TO 'log_name' statement failed for binary log files that had been moved to another location using mysqlbinlogmove. Such files are still listed in the binary log index file, but they are listed using an absolute path, rather than a path relative to the directory where the binary log files are normally stored. MySQL Server can now locate and purge moved binary log files successfully. (Bug #28284624)

Replication: When binlog_format is set to MIXED, if a function contained DML statements that applied to a temporary table, and also a DROP TEMPORARY TABLE statement, the function call was not written to the binary log, which caused replication errors. The function call is now written to the binary log in mixed replication mode if the function contains DML statements that operate on a temporary table. (Bug #28258992)

Replication: If autocommit was set to 0 for a replication slave or Group Replication group member where GTIDs were in use and super_read_only=ON was set, server shutdown was prevented by a transaction that did not complete. The transaction was attempting to save GTIDs to the mysql.gtid_executed table, but the update failed because super_read_only=ON was set. (With autocommit set to 1, the transaction would complete in this situation, and the mysql.gtid_executed table would instead be updated at server startup.) Now, the check for the super_read_only setting is skipped for this task, so the transaction is able to save the GTIDs to the mysql.gtid_executed table and complete regardless of the combination of super_read_only and autocommit settings. (Bug #28183718)

Replication: An assertion was raised in debug builds if an XA ROLLBACK statement was issued for an unknown transaction identifier when the gtid_next value had been set manually. The server now does not attempt to update the GTID state if an XA ROLLBACK statement fails with an error. (Bug #27928837, Bug #90640)

Replication: An assertion was raised in debug builds if a SELECT... FOR UPDATE statement was issued immediately after a transaction was committed or rolled back, and the transaction had been assigned a GTID manually using the gtid_next session system variable. After gtid_next has been used to set a GTID for a transaction, and the transaction has been committed or rolled back, another explicit SET GTID_NEXT statement must be issued before any other statement, otherwise the gtid_next value is left undefined. The SELECT... FOR UPDATE statement caused a GTID consistency violation in this situation because it acquired write locks, although it did not make any changes. SELECT... FOR UPDATE statements that acquire write locks now return an error in this situation. (Bug #27903848, Bug #90547)

Replication: Under heavy loads, a race condition in binary log group commit could cause the server to stop unexpectedly. The tracking of transaction commits has been changed to prevent this situation. (Bug #27556117)

Replication: The value returned by a SHOW SLAVE STATUS statement for the total combined size of all existing relay log files (Relay_Log_Space) could become much larger than the actual disk space used by the relay log files. The I/O thread did not lock the variable while it updated the value, so the SQL thread could automatically delete a relay log file and write a reduced value before the I/O thread finished updating the value. The I/O thread then wrote its original size calculation, ignoring the SQL thread's update and so adding back the space for the deleted file. The Relay_Log_Space value is now locked during updates to prevent concurrent updates and ensure an accurate calculation. (Bug #26997096, Bug #87832)

Replication: If the relay log index file was temporarily locked for viewing by a backup process for a replication slave, and MySQL Server also attempted to access the file at that time for rename or delete operations, the backup completed with warnings, but MySQL Server experienced an unexpected halt. MySQL Server now retries the file access operation a number of times in case this or a similar scenario is the explanation and the file becomes available again before long. (Bug #25839610)

Replication: With sync_binlog=1 set, if the binary log was rotated during a commit before the binary log end position was updated, replication stopped on the slave because the server attempted to use the old binary log end position with the new binary log file. The server now compares the binary log file name with the active binary log file when updating the binary log end position, so that the issue does not occur. (Bug #22252394, Bug #25524203, Bug #84752)

Replication: When adding a new member to a group, if the certification information was too big to transmit, an event was generated that caused failures in all group members. To avoid this situation, now if the certification information is too large an error is generated which makes the joining member leave the group. (Bug #93130, Bug #91870, Bug #28900691, Bug #28443958)

Replication: When you used group_replication_switch_to_single_primary_mode(), if a member which also had an asynchronous channel encountered an error, the asynchronous replication channel was not stopped correctly, and the server could stop unexpectedly. (Bug #91747, Bug #28382590)

Replication: It was possible to use the group coordinator based functions which configure a group, such as group_replication_switch_to_single_primary_mode, while members were in the UNREACHABLE or RECOVERING state, and this caused the operation to wait until all members became ONLINE. This could result in the group coordinator operation never completing successfully. Now, if you invoke any of these functions on a group in this state, an error is returned. Ensure all members are ONLINE before attempting to configure the group using the functions. (Bug #91537, Bug #28284355)

Replication: When a member joined a group that had a constant peak load, the member might not be able to move from the RECOVERING to the ONLINE state. The cause was that:

Now, when the recovery completion policy is waiting for transactions to be applied, the member first waits until one of the following conditions is fulfilled:

Then, the member waits for the currently queued transactions in the group_replication_applier channel to be applied, before the member state changes to ONLINE. (Bug #89582, Bug #27511404)

Group Replication: The maximum timeout setting for the waiting period before expelling a suspect Group Replication group member has been reduced to 3600 seconds (one hour). Previously, the group_replication_member_expel_timeout system variable could be set to a value of up to 31536000 seconds. The new upper limit provides a more reasonable maximum for the removal of inactive members from the group. The default setting for the timeout is zero, meaning that inactive members are liable for expulsion immediately after the 5-second detection period ends. Specifying a timeout value is useful to avoid unnecessary expulsions on slower networks, or in the case of expected transient network failures or machine slowdowns. (Bug #28656750)

Group Replication: On systems where the Group Communication System (GCS) used the systemd-resolved service for network name resolution, if the host name could not be resolved, GCS kept trying indefinitely. Now, if a retry message is returned from any name resolution service, GCS makes a limited number of retries, then concludes that the host name is unresolvable. (Bug #28177861)

Group Replication: When a group was being reconfigured online, for example using group_replication_switch_to_multi_primary_mode or group_replication_set_as_primary, there was a chance that stopping a member could result in an unexpected stop. Now, when you issue STOP GROUP_REPLICATION, if the member is part of an online group that is being reconfigured, the group coordinator is informed that Group Replication is stopping. The member waits for the online configuration process to complete any ongoing actions, but any subsequent actions are cancelled. (Bug #92829, Bug #28807260)

Group Replication: When stopping group replication, any channels with pending transactions could cause a deadlock. (Bug #92376, Bug #28636768, Bug #28365855)

Group Replication: When group_replication_exit_state_action is set to ABORT_SERVER, the Group Replication plugin now uses the new component service added by WL#12003 to shut down MySQL. (Bug #91793, Bug #28401703)

Microsoft Windows: MySQL Installer could fail after failure to remove an existing MySQL service. This is now treated as nonfatal so that installation operations can continue, but might require a system restart to permit service cleanup. (Bug #29016677, Bug #93048)

Microsoft Windows: When multiple instances of mysqld were started with the --no-monitor option on the same host for same user, the SHUTDOWN command shut down the wrong server process. This fix creates a unique shutdown event name for use with --no-monitor by appending the process ID of the process. (Bug #28723675)

X DevAPI: When using the X Protocol, a stored procedure called with a user variable as an OUT parameter did not set the variable's value. (Bug #91907, Bug #28458752)

JSON: Iteration over JSON objects resulted in unnecessary allocation of strings. (Bug #28975640)

JSON: Conversion of JSON values to text caused linear growth of the destination string, resulting in an unnecessarily high number of reallocations. Now this process uses exponential growth instead, to reduce the number of allocations required. (Bug #28949700)

References: See also: Bug #103790, Bug #32919524.

JSON: YEAR values were stored as opaque data in JSON; when JSON documents containing YEAR values were converted to text, the YEAR values were shown as base64-encoded strings. To resolve this issue, YEAR values are now stored as unsigned integers, which are shown as numbers when converted to text. An additional benefit of this fix is that less storage space is now required for YEAR values within JSON documents. (Bug #28947107)

JSON: Hit an assert when attempting to execute UPDATE or DELETE on an ARCHIVE table containing a JSON column. (Bug #28923281)

JSON: When trying to select from a JSON column of a FEDERATED table, the server returned ER_INVALID_JSON_PATH_CHARSET Cannot create a JSON value from a string with CHARACTER SET 'binary'.

In addition, neither DELETE or UPDATE had any effect on a FEDERATED table containing a JSON column. (Bug #28877215)

JSON: A query of the form SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt failed due a permissions error even though the user executing the query had the SELECT privilege on column c. (Bug #23254268)

The code contributed by Facebook for the feature implemented by Bug#27855592 was updated. (Bug #28950397)

References: See also: Bug #27855592.

On SuSE Linux, spurious EBUSY return values from pthread_mutex_destroy() were not handled. (Bug #28948462)

mysqld_safe and mysqld_multi were incorrectly included in client-only packages. (Bug #28942508)

Mishandling of host cache locking could cause a server exit. (Bug #28936159)

MySQL Enterprise Firewall did not work well if the audit_log plugin was installed. (Bug #28930885, Bug #93184)

Corrections were made to enable successful builds under Visual Studio on Windows. (Bug #28892711, Bug #93077)

The server permitted creation of databases with the same name as redo log files, which could result in unexpected server behavior. Such names are no longer permitted as database names. (Bug #28867993)

mysqld_multi could fail to pass the correct datadir value to mysqld. (Bug #28866662, Bug #90801)

A debug assertion that checks parameter schema names during MDL key creation for routines, events, and triggers to ensure that names are lowercase failed when encountering a schema name that included multi-byte character. (Bug #28864244)

The format specifiers for some error messages were improved to avoid displaying incorrect numeric values. (Bug #28860795)

For debug builds on Windows, unused memory leak checks were enabled and could slow down the shutdown process. These checks are now enabled only for specialized builds. (Bug #28857626)

mysql_upgrade could fail to upgrade certain system tables if the sql_require_primary_key system variable was enabled. (Bug #28855207, Bug #92988)

Builds configured with -DWITH_LIBWRAP=ON did not compile. (Bug #28853650, Bug #92983)

For InnoDB tables, the values of stored or indexed virtual generated columns that depended on the DEFAULT() function were not correctly updated by ALTER TABLE, if the default for a column referenced in this function was changed by making column nullable. (Bug #28848265)

Corrections were made to enable successful builds under Visual Studio on Windows with the /permissive flag turned on. (Bug #28842878, Bug #92943)

Builds configured with -DCMAKE_BUILD_TYPE=Release did not compile. (Bug #28841366, Bug #92945)

ALTER TABLE now can use the INPLACE algorithm when these conditions apply:

An example of such a change is a change to the column comment. (Bug #28836543)

Plugin system variables that had been persisted were not applied when the plugin was reinstalled. (Bug #28823972)

EXPLAIN ... FOR CONNECTION could modify another connection's SQL mode. (Bug #28786981)

Removal of Sun RPC and XDR from glibc into a separate libtirpc library caused problems with libasan on some platforms. (Bug #28785835, Bug #92762, Bug #28897799, Bug #93116)

It was possible to hit an assert when comparing two ENUM values while processing a query of the form SELECT a FROM table WHERE b = value and there was an index on column b. (Bug #28769996)

Concurrent read and write access to the offline_mode system variable could result in deadlock. (Bug #28761869)

Triggers were loaded into memory in an incorrect order when upgrading from MySQL 5.7 to MySQL 8.0, causing an assertion failure. (Bug #28760011, Bug #92609)

Joins involving the Performance Schema data_locks table could produce incorrect results. (Bug #28733170)

Some multiply-nested subqueries involving the use of scalar subqueries were not handled correctly. (Bug #28723670)

On Ubuntu, the installed /etc/mysql/mysql.conf.d/default-auth-override.cnf file was mistakenly created with executable mode. Thanks to Evgeniy Patlan for the correction contribution. (Bug #28714840, Bug #92587)

A memory leak was caused by GET_LOCK() calls with a zero timeout that failed due to concurrent connections holding the same user-level lock. (Bug #28714367)

Heap corruption and a server exit could occur when a server hosting a large number of tables was started and stopped repeatedly. (Bug #28705511, Bug #92572)

MySQL Router was missing from MySQL Server MSI packages. (Bug #28685556)

The example stored function GTID_SUBTRACT_UUID has been corrected in the code to match the documented version. (Bug #28670170)

CAP_SYS_NICE capability is no longer enabled for mysqld by MySQL package installers for Linux. (This was done to facilitate use of resource groups thread priorities.) For Linux deployments that require access to thread priorities, consult the MySQL Reference Manual instructions for enabling CAP_SYS_NICE capability at Resource Group Restrictions. (Bug #28670160)

The internal implementation of the <=> operator was simplified. (Bug #28660232)

After a STOP GROUP_REPLICATION statement was issued to remove a server instance from a group, multiple instances of the error message "[GCS] Error pushing message into group communication engine" were logged on the server instance. The error is now ignored when a server is in the process of leaving a group or is no longer a member of a group. (Bug #28658228, Bug #92454)

If the CHARACTER SET attribute of some column was implicit in JSON_TABLE(... COLUMNS ...), the resulting column used the global character_set_results as the default character set. The column now uses the session character_set_connection and collation_connection values. (Bug #28643862)

Adding a functional index on an expression that produced a row value raised an assertion; now it results in an error instead. (Bug #28643252)

In debug builds, creating a trigger after setting sql-mode to TIME_TRUNCATE_FRACTIONAL caused an assertion failure. The SQL mode was not present in the sql_mode column of the mysql.triggers data dictionary table. (Bug #28642918)

When using --log-timestamps=SYSTEM, ISO 8601 timestamps in log messages did not take account of daylight saving time. (Bug #28632725, Bug #32893161)

The arguments for error ER_IB_MSG_720 were computed incorrectly. (Bug #28629175)

The server could exit at startup if the option for specifying a socket file was not specified correctly. (Bug #28609181)

It was possible to create an inconsistent foreign key by adding a parent table with a different storage engine from the child table, then changing the parent table to the same storage engine as the child table. (Bug #28608460, Bug #92317)

When a server is joining a replication group, it attempts to connect to the first seed member listed in its group_replication_group_seeds system variable. If the connection is refused, the joining member tries to connect to each of the other seed members in the list in order. Previously, If the joining member connected to a seed member but did not get added to the replication group as a result, the joining member did not make any further connection attempts. This situation could occur if the seed member failed after the connection was made, or if the seed member did not have the joining member's address in its whitelist and closed the connection, or if the seed member rejected the joining member's request to join the group. Now, if the joining member connects to a seed member but does not manage to join the group, the joining member continues to try the remaining seed members in the list in order. (Bug #28602835)

Given certain patterns of allocations, copies with rebinds of the allocator, and deallocations, it was possible for temptable::Allocator to reuse a freed memory block. This led to failures in the test suite on Windows platforms. (Bug #28595557)

Setting time_zone to a negative offset and timestamp to a low value triggered an assertion when altering routines and views. (Bug #28590623, Bug #92273)

Persisting the pid_file system variable to DEFAULT could result in a value of NULL for subsequent server startups. (Bug #28589736)

Incorrect privilege checking could produce an error for SELECT ... FOR UPDATE statements that executed successfully in MySQL 5.7. (Bug #28581664, Bug #92254)

Attempting to rename the parent column of a foreign key with ALTER TABLE could fail. (Bug #28581468)

Privileges for RESET PERSIST were not checked correctly. (Bug #28564239)

An overflow occurred when calculating AVG(YEAR(datetime_column)). (Bug #28562930)

After a server restart, path names of persisted system variables in the Performance Schema variables_info table could be calculated incorrectly. (Bug #28561584)

A partitioned table name check raised an invalid assertion. (Bug #28556942)

The handler::create() function could be called with an error in the condition list, which could prevent an error in the handler::create() function from being reported properly. (Bug #28556264)

For ALTER TABLE, ALGORITHM=INSTANT was incorrectly rejected on tables created in a MySQL version prior to 8.0.12. (Bug #28554157, Bug #92194)

mysqlpump did not free all allocated resources when it encountered an error, resulting in memory leaks. (Bug #28538971, Bug #92131)

The COLLATE attribute was rejected for data types in the COLUMNS clause of the JSON_TABLE() function. (Bug #28538315)

For debug builds, the server could exit when attempting to roll back CREATE USER statements. (Bug #28536312)

Plugin variables with signed values were displayed incorrectly. (Bug #28534414, Bug #92107)

Mishandling of deprecated system variables could cause output from queries on the Performance Schema variables_by_thread table to be incorrect. (Bug #28515475, Bug #92049)

Data races discovered by Thread Sanitizer in Event_queue::lock_data and the SAFE_MUTEX implementation were fixed. (Bug #28510721, Bug #92041, Bug #28510691, Bug #92040)

No ER_NEED_REPREPARE diagnostic was pushed to the diagnostics area when a reprepare failed for prepared statements. (Bug #28509306, Bug #92029)

When a subquery contained a UNION, the count of the number of subquery columns was calculated incorrectly. (Bug #28499924)

When evaluating an expression using WITH ROLLUP, we now write the result of the expression into a temporary table only when it has a temporary table column. (Bug #28493849, Bug #28523014)

For debug builds, incorrect foreign key error checking for ALTER TABLE on a TEMPORARY table could result in a server exit. (Bug #28493257, Bug #91990)

For some system variables, SET PERSIST persisted the default value rather than the specified value. (Bug #28466045)

SET RESOURCE GROUP could not be executed as a prepared statement. (Bug #28448258, Bug #91876)

Restored a call to Item_field::fix_fields() that was inadvertently removed during work done to implement window functions. (Bug #28431783)

Data races reported by Thread Sanitizer during X Plugin startup and shutdown were corrected. (Bug #28407294)

Creating a table with a partition description containing illegal utf8 characters raised an assertion. (Bug #28387488, Bug #91763)

mysqldump output could include SQL mode values that have been removed. (Bug #28373001, Bug #91714)

A potential lock order cycle was corrected. (Bug #28366531)

On a GTID-enabled server, concurrent statements on the INFORMATION_SCHEMA.COLUMNS table could deadlock. (Bug #28293047, Bug #91548)

CREATE TABLE statements for tables with the utf32 table character set and literal strings in the table definition raised an assertion. (Bug #28275881)

Internal functions were added to support updating the server version number upon the successful completion of a server upgrade. (Bug #28211486, Bug #91323)

Comparing log file names as strings using the memcmp() function resulted in uninitialized memory read errors. The comparison now uses the strncmp() function. Thanks to Zsolt Parragi and Laurynas Biveinis for their contributions. (Bug #28178776, Bug #90238)

The server mishandled stored program and resource group names that differed only in accents. (Bug #28122841)

The optimizer skipped the second column in a composite index when executing an inner join with a LIKE clause against the second column. (Bug #28086754)

CREATE TABLE ... SELECT could create date columns with “zero” date default values when it should have created them without a default value. (Bug #28022129)

The transformation of IN subquery predicates into semijoins was not handled correctly for a very large number of tables. (Bug #28004674)

The bitmap used for reading fields from the storage engine was not enabled correctly when performing the filesort which is added to the last of any temporary tables created for window functions. In the case where there was no need for a temporary table, the server added a filesort to the output from the select table, but the removed reference (the WHERE condition) was not added. Now in such cases, the reference is added to the select table when the first window function needs sorting and no temporary table was created before processing this window function. (Bug #27975193)

After seeing a row in the range frame, if another row was later determined to be appearing before this range frame, the server continued to check for new rows. This led to the next frame calculation being done incorrectly. (Bug #27973860)

Server mishandling of SIGHUP signals could result in a server exit. (Bug #27966483, Bug #90742)

DELETE WHERE a=constant from a table with column a and partitioned by the value of a generated column b led to an assertion in debug builds. (Bug #27954073)

INFORMATION_SCHEMA queries could cause a server exit when updating dynamic table statistics. (Bug #27898108)

A metadata locking deadlock could occur when opening a foreign key parent table. (Bug #27859086)

Improper memory handling by account management statements could result in server misbehavior. (Bug #27820277)

In certain cases, window functions did not handle ORDER BY and PARTITION BY correctly. (Bug #27816506)

The MySQL query optimizer identifies each predicate to be pushed down to a table as a table condition; as part of this process it checks to see whether a given predicate among the table conditions is already known to be true in virtue of the selected access path for the table, in which case the predicate can safely be eliminated.

For example, when executing SELECT * FROM t1 WHERE pk=1, where pk is the primary key of table t1, the ref access method is selected. Since we know that this already returns only rows for which pk=1, further evaluation of this condition as a filter (Using where) should be eliminated.

When optimizing a query which includes a GROUP BY or ORDER BY, a late optimizer check is performed to discover whether sorting can be skiped by using a sorted index instead. Since this is done after another index may have been chosen for accessing the table to be sorted, some predicates thought to be redundant (due to the previous access path selected) could be removed prematurely. To compensate for this, the following actions were performed:

Issues arose because the following actions also intended to remedy early predicate removal were not performed correctly:

A further problem existed for storage engines implementing condition pushdown, such as NDBCLUSTER: The conditions pushed down were generated from the table condition prior to analysis, such that, if the access path was later changed, the pushed condition did not contain the predicates already removed, making condition pushdown less efficient.

The root cause of this issue was that part_of_refkey() analysis was performed on table predicates before the access method for the table had been completely decided. This is fixed by removing such early analysis. (Bug #27808758, Bug #27814026)

A windowing function that included an ORDER BY column clause failed with Unknown field in window order by even when the column was found in the table being queried. (Bug #27808099)

Executing a prepared statement to do a multiple-row insert with large number of placeholders consumed excessive memory and could execute slowly. (Bug #27703912)

On Windows, if the Visual C++ Redistributable for Visual Studio had been removed, MySQL uninstallation using the MSI installer failed. (Bug #27621546)

The parser accepted invalid SET statement syntax in trigger definitions that could result in a server exit. (Bug #27595603)

The server failed to start if the keyring_encrypted_file plugin keyring file was invalid. (Bug #27588064)

Keyring migration failed with source and destination keyring plugins of keyring_okv and keyring_encrypted_file, respectively. (Bug #27493970)

It was possible in debug builds for a windowing function using a signed integer to mishandle a frame that included FOLLOWING. (Bug #27452365)

When executing a prepared statement with a procedure call with the CURSOR_TYPE_READ_ONLY flag set, the client library hung if the procedure performed a SELECT that returned an empty result set. (Bug #27443252, Bug #89214)

Names of referenced columns of foreign keys were always shown in lowercase in SHOW CREATE TABLE output and the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. (Bug #27353767, Bug #88718)

Loading and unloading the audit_log plugin while performing other concurrent activity could cause the server to become unresponsive. (Bug #27325622)

The data dictionary properties interface (dd::Properties) and implementation was revised to provide a new method of defining valid keys for property objects. (Bug #27309072, Bug #89031, Bug #27309082, Bug #89032)

Installing and uninstalling the validate_password component concurrently with SET PASSWORD could cause component failure. (Bug #27020979)

Some typos in server source code were fixed. Thanks to Hyunwoo Park for the contribution. (Bug #26189673, Bug #86565)

After column privileges were granted to a table, a HANDLER READ call asserted during privilege checking. (Bug #25987758)

A check that ensures compatibility of referencing and referenced column types in a foreign key definition was moved from the storage engine layer to the SQL layer. In addition, a better error message is produced when columns are not compatible, and when a foreign key constraint references a table that does not exist. (Bug #25722927, Bug #28371394, Bug #91712, Bug #21308781, Bug #77467, Bug #11746132, Bug #23693)

The parser performed some out-of-memory checks incorrectly. (Bug #25633994)

A race condition between user-management statements and other statements that tried to access grant tables directly could result in deadlock and transaction rollback. (Bug #24481240)

When the server was started with the skip_name_resolve system variable enabled, spurious warnings could be written to the error log about ignoring accounts with a host name part of localhost. (The accounts in fact were used and not ignored.) (Bug #23329861, Bug #81441)

DML statements using IGNORE were not always handled correctly on tables having generated columns. (Bug #22990029)

MySQL now removes trivial WHERE conditions arising from constant literal expressions during preparation, rather than at a later stage in optimization. This should result in improved plans for queries with outer joins containing trivial conditions, such as this one:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1

After removing the redundant OR 0 = 1 condition the optimizer can rewrite the query as an inner join, as shown here:

SELECT * FROM t1 LEFT JOIN t2 WHERE condition_1 AND condition_2

For more information, see What Is New in MySQL 8.0, and Outer Join Optimization. (Bug #16893426, Bug #28237111, Bug #28239008, Bug #28341790, WL #9571)

References: See also: Bug #28197977, Bug #28240054.

Updates for BLOB columns in FEDERATED tables did not work. (Bug #11748067, Bug #34997)

Each of the functions REGEXP_REPLACE(), REGEXP_SUBSTR(), REGEXP_LIKE(), and REGEXP_INSTR() returned a DOUBLE instead of a value of the function's specified return type. (Bug #90039, Bug #27682225)

A query employing a dynamic range and an index merge could use more memory than expected. (Bug #89953, Bug #27659490)

Selecting from a table having a CHAR column with a NO_PAD collation yielded inconsistent results. (Bug #89753, Bug #27578340)