Changes in MySQL 8.0.21 (2020-07-13, General Availability)

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

In the documentation for MySQL 8.0.21, we have started changing the term “master” to “source”, the term “slave” to “replica”, the term “whitelist” to “allowlist”, and the term “blacklist” to “blocklist”. There are currently no changes to the product's syntax, so these terms are still present in the documentation where the current code requires their use. See the blog post MySQL Terminology Updates for more information.

• Account Management Notes

• C API Notes

• Compilation Notes

• Configuration Notes

• Connection Management Notes

• Deprecation and Removal Notes

• JSON Notes

• Optimizer Notes

• Packaging Notes

• Pluggable Authentication

• Security Notes

• Test Suite Notes

• X Plugin Notes

• Functionality Added or Changed

• Bugs Fixed

Account Management Notes

• You can now set per-user comments and attributes when creating or updating MySQL user accounts. A user comment consists of arbitrary text passed as the argument to a COMMENT clause used with a CREATE USER or ALTER USER statement. A user attribute consists of data in the form of a JSON object passed as the argument to an ATTRIBUTE clause used with either of these two statements. The attribute can contain any valid key-value pairs in JSON object notation.

  For example, the first of the following two statements creates a user account bill@localhost with the comment text This is Bill's user account. The second statement adds a user attribute to this account, using the key email, with the value [e-mail].

  CREATE USER 'bill'@'localhost' COMMENT 'This is Bill\'s user account';
  
  ALTER USER 'mary'@'localhost'
      ATTRIBUTE '{"email":"[e-mail]"}';

  Only one of COMMENT or ATTRIBUTE can be used in the same CREATE USER or ALTER USER statement.

  User comments and user attributes are stored together internally as a JSON object, with the comment text as the value of an element with the key comment. You can information retrieve user comments and user attributes from the ATTRIBUTE column of the INFORMATION_SCHEMA.USER_ATTRIBUTES table; since this data is in JSON format, you can work with it using MySQL's JSON function and operators (see JSON Functions). Changes to an existing user attribute are merged with its current value, as you had used JSON_MERGE_PATCH(); new key-value pairs are appended to the attribute, and new values for existing keys overwrite their previous values.

  To remove a given key-value pair from a user attribute, use ALTER USER user ATTRIBUTE '{"key":null}'.

  For more information and examples, see CREATE USER Statement, ALTER USER Statement, and The INFORMATION_SCHEMA USER_ATTRIBUTES Table. (WL #13562)

  References: See also: Bug #31067575.

C API Notes

• Per OpenSSL recommendation, x509_check_host() and X509_check_ip_asc() calls in the C client library were replaced, respectively, with X509_VERIFY_PARAM_set1_host() and X509_VERIFY_PARAM_set1_ip_asc() calls. (Bug #29684791)

• The MySQL C API now supports compression for asynchronous functions. This means that the MYSQL_OPT_COMPRESSION_ALGORITHMS and MYSQL_OPT_ZSTD_COMPRESSION_LEVEL options for the mysql_options() function now affect asynchronous operations, not just synchronous operations. See mysql_options().

  Our thanks to Facebook for the contribution. (Bug #96802, Bug #30284871, WL #13510)

Compilation Notes

• The minimum version of the Boost library for server builds is now 1.72.0. (Bug #30963985)

Configuration Notes

• tcmalloc is no longer a permitted value for the mysqld_safe --malloc-lib option. (Bug #31372027)

Connection Management Notes

• MySQL Server supports a “main” network interface for ordinary client connections, and optionally an administrative network interface for administrative client connections. Previously, the main and administrative interfaces used the same TLS configuration, such as the certificate and key files for encrypted connections. It is now possible to configure TLS material separately for the administrative interface:

  • There are new configuration parameters that apply specifically to the administrative interface.

  • The ALTER INSTANCE RELOAD TLS statement is extended with a FOR CHANNEL clause that enables specifying the channel (interface) for which to reload the TLS context.

  • The new Performance Schema tls_channel_status table exposes TLS context properties for the main and administrative interfaces.

  • For backward compatibility, the administrative interface uses the same TLS context as the main interface unless some nondefault TLS parameter value is configured for the administrative interface.

  For more information, see Administrative Interface Support for Encrypted Connections, ALTER INSTANCE Statement, and The tls_channel_status Table. (WL #13850)

Deprecation and Removal Notes

• Partitioning: Columns with index prefixes are not supported as part of a table's partitioning key; previously such columns were simply omitted by the server when referenced in creating, altering, or upgrading a table that was partitioned by key, with no indication that this omission had taken place, except when the proposed partitioning function used only columns with prefixes, in which case the statement failed with an error message that did not identify the actual source of the problem. This behavior is now deprecated, and subject to removal in a future release in which using any such columns in the proposed partitioning key will cause the CREATE TABLE or ALTER TABLE statement in which they occur to be rejected.

  When one or more columns using index prefixes are specified as part of the partitioning key, a warning is now generated for each such column. In addition, when a CREATE TABLE or ALTER TABLE statement is rejected because all columns specified in the proposed partitioning key employ index prefixes, the error message returned now makes clear the reason the statement did not succeed. This includes cases in which the columns proposed the partitioning function are defined implicitly as those in the table's primary key by employing an empty PARTITION BY KEY() clause.

  For more information and examples, see Column index prefixes not supported for key partitioning, and KEY Partitioning. (Bug #29941932, Bug #29941959, Bug #31100205, WL #13588)

  References: See also: Bug #29942014.

JSON Notes

• Added the JSON_VALUE() function, which simplifies creating indexes on JSON columns. A call to JSON_VALUE(json_doc, path RETURNING type) is equivalent to calling CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type), where json_doc is a JSON document, path is a JSON path expression pointing to a single value within the document, and type is a data type compatible with CAST(). RETURNING type is optional; if no return type is specified, JSON_VALUE() returns VARCHAR(512).

  JSON_VALUE() also supports ON EMPTY and ON ERROR clauses similar to those used with JSON_TABLE().

  You can create indexes on a JSON column using JSON_VALUE() as shown here:

  CREATE TABLE inventory(
      items JSON,
      INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),
      INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
      INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
  );

  Assuming the items column contains values such as '{"name": "hat", "price": "22.95", "quantity": "17"}', you can issue queries, such as the following, that are able to use these indexes:

  SELECT items->"$.price" FROM inventory
      WHERE JSON_VALUE(items, '$.name' RETURNING CHAR(50)) = "hat";
  
  SELECT * FROM inventory
      WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100.01;
  
  SELECT items->"$.name" AS item, items->"$.price" AS amount
      FROM inventory
      WHERE JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED) > 500;

  For more information and examples, see the description of the JSON_VALUE() function. (WL #12228)

Optimizer Notes

• MySQL attempts to use an ordered index for any ORDER BY or GROUP BY query that has a LIMIT clause, overriding any other choices made by the optimizer, whenever it determines that this would result in faster execution. Because the algorithm for making this determination makes certain assumptions about data distribution and other conditions, it may not always be completely correct, and it is possible in some cases that choosing a different optimization for such queries can provide better performance. To handle such occurrences, it is now possible to disable this optimization by setting the optimizer_switch system variable's prefer_ordering_index flag to off.

  For more information about this flag and examples of its use, see Switchable Optimizations, and LIMIT Query Optimization.

  Our thanks to Jeremy Cole for the contribution. (Bug #97001, Bug #30348211, WL #13929)

  References: See also: Bug #31686878.

• A single-table UPDATE or DELETE statement that uses a subquery having a [NOT] IN or [NOT] EXISTS predicate can now in many cases make use of a semijoin transformation or subquery materialization. This can be done when the statement does not use LIMIT or ORDER BY, and when semijoin or subquery materialization is allowed by any optimizer hints used in the subquery, or by the value of the optimizer_switch server system variable.

  You can see when the semijoin optimization or subquery materialization is used for an eligible single-table DELETE or UPDATE due to the presence of a join_optimization object in the optimizer trace. You can also see that the conversion is performed by checking the output of EXPLAIN FORMAT=TREE; if the optimization is not performed, this shows <not executable by iterator executor>, while a multi-table statement reports a full plan.

  As part of this work, semi-consistent reads are now supported by multi-table UPDATE of InnoDB tables, when the transaction isolation level is weaker than REPEATABLE READ. (Bug #35794, Bug #96423, Bug #11748293, Bug #30139244, WL #6507)

• Added the optimizer_switch flag subquery_to_derived. When this flag is set to on, the optimizer transforms eligible scalar subqueries into left outer joins (and in some cases, inner joins) on derived tables. This optimization can be applied to a subquery which meets the following conditions:

  • It uses one or more aggregate functions but no GROUP BY.

  • It is part of a SELECT, WHERE, JOIN, or HAVING clause.

  • It is not a correlated subquery.

  • It does not make use of any nondeterminstic functions.

  ANY and ALL subqueries which can be rewritten to use MIN() or MAX() are also not affected.

  With subquery_to_derived=on, the optimization can also be applied to a table subquery which is the argument to IN, NOT IN, EXISTS, or NOT EXISTS, and which does not contain a GROUP BY clause.

  The subquery_to_derived flag is set to off by default, since it generally does not improve performance, and its intended use for the most part is for testing purposes.

  For more information, see Switchable Optimizations, for more information and examples. See also Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization, and LIMIT Query Optimization. (WL #13851)

• Building on work done in MySQL 8.0.18, the server now performs injection of casts into queries to avoid mismatches when comparing string data types with those of numeric or temporal types; as when comparing numeric and temporal types, the optimizer now adds casting operations in the item tree inside expressions and conditions in which the data type of the argument and the expected data type do not match. This makes queries in which string types are compared with numeric or temporal types equivalent to queries which are compliant with the SQL standard, while maintaining backwards compatibility with previous releases of MySQL. Such casts are now performed whenever string values are compared to numeric or temporal values using any of the standard numeric comparison operators (=, >=, >, <, <=, <>/!=, and <=>).

  Such implicit casts are now performed between a string type (CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, or SET) and a numeric type (SMALLINT, TINYINT, MEDIUMINT, INT/INTEGER, BIGINT; DECIMAL/NUMERIC; FLOAT, DOUBLE, REAL; and BIT) by casting the string value to DOUBLE; if the numeric value is not already of type DOUBLE, FLOAT, or REAL, it is also cast to DOUBLE. A YEAR value is also cast to DOUBLE when compared with a string value (as is the string value). For such comparisons between string types and TIMESTAMP or DATETIME values, the arguments are cast as DATETIME; when a string type is compared with a DATE value, the string is cast to DATE.

  For example, a query such as SELECT * FROM t1 JOIN t2 ON t1.char_col = t2.int_col is rewritten and executed as SELECT * FROM t1 JOIN t2 ON CAST(t1.char_col AS DOUBLE) = CAST(t2.int_col AS DOUBLE), and SELECT * FROM t1 JOIN t2 ON t1.varchar_col = t2.timestamp_col is transformed to SELECT * FROM t1 JOIN t2 ON CAST(t1.varchar_col AS DATETIME) = CAST(t2.timestamp_col AS DATETIME) prior to execution.

  You can see when casts are injected into a given query by viewing the output of EXPLAIN ANALYZE, EXPLAIN FORMAT=JSON, or EXPLAIN FORMAT=TREE. EXPLAIN [FORMAT=TRADITIONAL] can also be used, but in this case it is necessary, following execution of the EXPLAIN statement, to issue SHOW WARNINGS to view the rewritten query.

  This change is not expected to cause any difference in query results or performance. (WL #13456)

Packaging Notes

• For RPM and Debian packages, client-side plugins were moved from the server package to the client package. Additionally, debug versions of client-side plugins were moved to the test package. (Bug #31123564, Bug #31336340)

• MSI packages for Windows no longer include the legacy server data component. (Bug #31060177)

• The bundled Protobuf library was upgraded from version 3.6.1 to version 3.11. (Bug #31000511, Bug #98852)

• The libevent library bundled with MySQL was upgraded to version 2.1.11. In addition, for the WITH_LIBEVENT CMake option, the following two changes were made:

  1. yes is no longer permitted as a synonym for system. Use system instead.

  2. If system is specified but no system libevent is found, the bundled version is no longer used in place of the missing system library, and an error occurs instead.

  (Bug #30926742)

• The ICU (International Components for Unicode) library bundled with MySQL has been upgraded to version 65.1.

Pluggable Authentication

• The MySQL Enterprise Edition authentication_ldap_sasl plugin that implements SASL LDAP authentication supports multiple authentication methods, but depending on host system configuration, they might not all be available. The new Authentication_ldap_sasl_supported_methods status variable provides discoverability for the supported methods. Its value is a string consisting of supported method names separated by spaces. Example: "SCRAM-SHA1 GSSAPI" (WL #13876)

Security Notes

• Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

  This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)

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

• Previously, LOCAL data loading capability for the LOAD DATA statement could be controlled on the client side only by enabling it for all files accessible to the client, or by disabling it altogether. The new MYSQL_OPT_LOAD_DATA_LOCAL_DIR option for the mysql_options() C API function enables clients to restrict LOCAL data loading to files located in a designated directory. See Security Considerations for LOAD DATA LOCAL. (WL #13168)

Test Suite Notes

• mysql-test-run.pl no longer accepts unique prefixes of command options. Complete option names must be given. (Bug #31390127)

• MySQL tests were updated to use googletest 1.10.0. (Bug #31364750)

• The innodb.innodb_mysql test case was updated to avoid nondeterminism of output row order. Thanks to Facebook for the contribution. (Bug #30810572, Bug #98377)

• mysql-test-run.pl now supports an --mtr-port-exclude option for specifying the range of ports to exclude when searching for available port ranges to use. The MTR_PORT_EXCLUDE environment variable may also be set to achieve the same effect. Thanks to Facebook for the contribution. (Bug #30809607)

• In addition to aborting on receipt of CTRL+C (SIGINT), mysql-test-run.pl now also displays a list of test cases that failed up to that point. (Bug #30407014)

X Plugin Notes

• Where a dollar sign ($) was used to reference an entire document, X Plugin handled the reference differently depending on the context in which it was used. This has now been standardized. (Bug #31374713)

• With certain settings for the global SQL mode, X Plugin's authentication process failed to accept a correct user password. The authentication process now operates independently from the global SQL mode's setting to ensure consistency. (Bug #31086109)

Functionality Added or Changed

• Important Change; Group Replication: By default, a replication source server writes a checksum for each event in the binary log, as specified by the system variable binlog_checksum, which defaults to the setting CRC32. Previously, Group Replication did not support the presence of checksums in the binary log, so binlog_checksum had to be set to NONE when configuring a server instance that would become a group member. This requirement is now removed, and the default can be used. The setting for binlog_checksum does not have to be the same for all members of a group.

  Note that Group Replication does not use checksums to verify incoming events on the group_replication_applier channel, because events are written to that relay log from multiple sources and before they are actually written to the originating server's binary log, which is when a checksum is generated. Checksums are used to verify the integrity of events on the group_replication_recovery channel and on any other replication channels on group members. (WL #9038)

• Performance: Improved the implementation of the UNHEX() function by introducing a lookup table for mapping a hexadecimal digit string to its binary representation. This change speeds up execution of the function by a factor of 8 or more in testing. (Bug #31173103)

• InnoDB: Redo logging can now be enabled and disabled using ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG syntax. This functionality is intended for loading data into a new MySQL instance. Disabling redo logging helps speed up data loading by avoiding redo log writes.

  The new INNODB_REDO_LOG_ENABLE privilege permits enabling and disabling redo logging.

  The new Innodb_redo_log_enabled status variable permits monitoring redo logging status.

  See Disabling Redo Logging. (WL #13795)

• InnoDB: Truncating an undo tablespace on a busy system could affect performance due to associated flushing operations that remove old undo tablespace pages from the buffer pool and flush the initial pages of the new undo tablespace to disk. To address this issue, the flushing operations were removed.

  Old undo tablespace pages are now released passively as they become least recently used, or are removed at the next full checkpoint. The initial pages of the new undo tablespace are now redo logged instead of flushed to disk during the truncate operation, which also improves durability of the undo tablespace truncate operation.

  To prevent potential issues caused by an excessive number of undo tablespace truncate operations, truncate operations on the same undo tablespace between checkpoints are now limited to 64. If the limit is exceeded, an undo tablespace can still be made inactive, but it is not truncated until after the next checkpoint.

  INNODB_METRICS counters associated with defunct undo truncate flushing operations were removed. Removed counters include: undo_truncate_sweep_count, undo_truncate_sweep_usec, undo_truncate_flush_count, and undo_truncate_flush_usec.

  See Undo Tablespaces. (WL #11819)

• InnoDB: At startup, InnoDB validates the paths of known tablespace files against tablespace file paths stored in the data dictionary in case tablespace files have been moved to a different location. The new innodb_validate_tablespace_paths variable permits disabling tablespace path validation. This feature is intended for environments where tablespaces files are not moved. Disabling tablespace path validation improves startup time on systems with a large number of tablespace files.

  For more information, see Disabling Tablespace Path Validation. (WL #14008)

• InnoDB: Table and table partition data files created outside of the data directory using the DATA DIRECTORY clause are now restricted to directories known to InnoDB. This change permits database administrators to control where tablespace data files are created and ensures that the data files can be found during recovery.

  General and file-per-table tablespaces data files (.ibd files) can no longer be created in the undo tablespace directory (innodb_undo_directory) unless that directly is known to InnoDB.

  Known directories are those defined by the datadir, innodb_data_home_dir, and innodb_directories variables.

  Truncating an InnoDB table that resides in a file-per-table tablespace drops the existing tablespace and creates a new one. As of MySQL 8.0.21, InnoDB creates the new tablespace in the default location and writes a warning to the error log if the tablespace was created with an earlier version and the current tablespace directory is unknown. To have TRUNCATE TABLE create the tablespace in its current location, add the directory to the innodb_directories setting before running TRUNCATE TABLE. (WL #13065)

• InnoDB: To improve concurrency for operations that require access to lock queues for table and row resources, the lock system mutex (lock_sys->mutex) was replaced by sharded latches, and lock queues were grouped into table and page lock queue shards, with each shard protected by a dedicated mutex. Previously, the single lock system mutex protected all lock queues, which was a point of contention on high-concurrency systems. The new sharded implementation permits more granular access to lock queues.

  The lock system mutex (lock_sys->mutex) was replaced by the following sharded latches:

  • A global latch (lock_sys->latches.global_latch) consisting of 64 read-write lock objects (rw_lock_t). Access to an individual lock queue requires a shared global latch and a latch on the lock queue shard. Operations that require access to all lock queues take an exclusive global latch, which latches all table and page lock queue shards.

  • Table shard latches (lock_sys->latches.table_shards.mutexes), implemented as an array of 512 mutexes, with each mutex dedicated to one of 512 table lock queue shards.

  • Page shard latches (lock_sys->latches.page_shards.mutexes), implemented as an array of 512 mutexes, with each mutex dedicated to one of 512 page lock queue shards.

  The Performance Schema wait/synch/mutex/innodb/lock_mutex instrument for monitoring the single lock system mutex was replaced by instruments for monitoring the new global, table shard, and page shard latches:

  • wait/synch/sxlock/innodb/lock_sys_global_rw_lock

  • wait/synch/mutex/innodb/lock_sys_table_mutex

  • wait/synch/mutex/innodb/lock_sys_page_mutex

  (WL #10314)

• Group Replication: Group Replication group members can now advertise a list of IP addresses that joining members can use to make connections to them for state transfer during distributed recovery. Previously, the existing member's standard SQL client connection was used for this purpose as well as for client traffic. Advertising distributed recovery endpoints instead gives you improved control of distributed recovery traffic (comprising remote cloning operations and state transfer from the binary log) in your network infrastructure. The list of distributed recovery endpoints for a member is specified using the new group_replication_advertise_recovery_endpoints system variable, and the same SSL requirements are applied that would be in place if the SQL client connection was used for distributed recovery. (WL #13767)

• Group Replication: You can now specify user credentials for distributed recovery on the START GROUP_REPLICATION statement using the USER, PASSWORD, and DEFAULT_AUTH options. These credentials are used for distributed recovery on the group_replication_recovery channel. When you specify user credentials on START GROUP_REPLICATION, the credentials are saved in memory only, and are removed by a STOP GROUP_REPLICATION statement or server shutdown. These credentials can replace user credentials set using a CHANGE MASTER TO statement, which are stored in the replication metadata repositories, and can therefore help to secure the Group Replication servers against unauthorized access.

  The new method of providing user credentials is not compatible with starting Group Replication automatically on server start. If user credentials have previously been set using a CHANGE MASTER TO statement, credentials that you specify on START GROUP_REPLICATION take precedence over these. However, the credentials from the replication metadata repositories are used if START GROUP_REPLICATION is specified without user credentials, which happens on automatic starts if the group_replication_start_on_boot system variable is set to ON (including after a remote cloning operation for distributed recovery). To gain the security benefits of specifying user credentials on START GROUP_REPLICATION, ensure that group_replication_start_on_boot is set to OFF (the default is ON), and use a CHANGE MASTER TO statement to clear any user credentials previously set for the group_replication_recovery channel. (WL #13768)

• Group Replication: The minimum setting for the maximum size for the XCom message cache in Group Replication, specified by the group_replication_message_cache_size system variable, has been reduced from approximately 1 GB to 134217728 bytes, or approximately 128 MB. Note that this size limit applies only to the data stored in the cache, and the cache structures require an additional 50 MB of memory. The same cache size limit should be set on all group members. The default XCom message cache size of 1 GB, which was formerly also the minimum setting, is unchanged.

  The smaller message cache size is provided to enable deployment on a host that has a restricted amount of available memory and good network connectivity. Having a very low group_replication_message_cache_size setting is not recommended if the host is on an unstable network, because a smaller message cache makes it harder for group members to reconnect after a transient loss of connectivity. If some messages that were exchanged during a member's temporary absence have been deleted from the other members' XCom message caches because their maximum size limit was reached, the member cannot reconnect using the message cache. It must leave the group and rejoin in order to retrieve the transactions through distributed recovery, which is a slower process than using the message cache, although the member still can rejoin in this way without operator intervention.

  Note that from MySQL 8.0.21, by default an expel timeout of 5 seconds is added before a member is expelled from the group (specified by the group_replication_member_expel_timeout system variable). With this default setting the XCom message cache therefore now needs to store the messages exchanged by the group in a 10-second period (the expel timeout plus the initial 5-second detection period), rather than in a 5-second period as previously (the initial 5-second detection period only). (WL #13979)

• Group Replication: group_replication_member_expel_timeout specifies the period of time in seconds that a Group Replication group member waits after creating a suspicion, before expelling from the group the member suspected of having failed. The initial 5-second detection period before a suspicion is created does not count as part of this time.

  Previously, the waiting period specified by group_replication_member_expel_timeout defaulted to 0, meaning that a suspected member was liable for expulsion immediately after the 5-second detection period ended. Following user feedback, the waiting period now defaults to 5 seconds, giving a member that loses touch with the group 10 seconds in total to reconnect itself to the group. If the member does reconnect in this time, it can recover missed messages from the XCom message cache and return to ONLINE state automatically, rather than being expelled from the group and needing the auto-rejoin procedure or manual operator intervention to rejoin.

  If you previously tuned the size of the XCom message cache with reference to the expected volume of messages in the previous default time before a member was expelled (the 5-second detection period only), increase your group_replication_message_cache_size setting to account for the new expel timeout, which doubles the default time to 10 seconds. With the new default expel timeout you might start to see warning messages from GCS on active group members, stating that a message that is likely to be needed for recovery by a member that is currently unreachable has been removed from the message cache. This message shows that a member has had a need to use the message cache to reconnect, and that the cache size might not be sufficient to support the current waiting period before a member is expelled. (WL #13773)

• Group Replication: The Group Replication auto-rejoin feature is now activated by default. The group_replication_autorejoin_tries system variable, which is available from MySQL 8.0.16, makes a member that has been expelled or reached its unreachable majority timeout try to rejoin the group automatically. This system variable, which originally defaulted to 0 so auto-rejoin was not activated, now defaults to 3, meaning that a member makes three attempts to rejoin the group in the event of its expulsion or unreachable majority timeout. Between each attempt the member waits for 5 minutes. If the specified number of tries is exhausted without the member rejoining or being stopped, the member proceeds to the action specified by the group_replication_exit_state_action system variable.

  The auto-rejoin feature minimizes the need for manual intervention to bring a member back into the group, especially where transient network issues are fairly common. During and between auto-rejoin attempts, a member remains in super read only mode and does not accept writes. However, reads can still be made on the member, with an increasing likelihood of stale reads over time. If you want to intervene to take the member offline, the member can be stopped manually at any time by using a STOP GROUP_REPLICATION statement or shutting down the server. If you cannot tolerate the possibility of stale reads for any period of time, set the group_replication_autorejoin_tries system variable to 0, in which case operator intervention is required whenever a member is expelled from the group or reaches its unreachable majority timeout. (WL #13706)

• Previously, the --disabled-storage-engines option did not ignore spaces around storage engines listed in the option value. Spaces around engine names are now ignored. (Bug #31373361, Bug #99632)

• The new HANDLE_FATAL_SIGNALS CMake option enables configuring whether Address Sanitizer and Undefined Behavior Sanitizer builds use the sanitizer runtime library to handle fatal signals rather than a MySQL-internal function. The option default is ON for non-sanitizer builds, OFF for sanitizer builds. If the option is OFF, the default action is used for SIGBUS, SIGILL and SIGSEGV, rather than the internal function. (Bug #31068443)

• Using a column that is repeated twice or more in GROUP BY (through an alias), combined with ROLLUP, had behavior differing from MySQL 5.7. Example:

  SELECT a, b AS a, COUNT(*) FROM t1 GROUP BY a, b WITH ROLLUP;

  Behavior of such queries has been changed to better match MySQL 5.7. They should be avoided, however, because behavior may change again in the future or such queries may become illegal. (Bug #30921780, Bug #98663)

• comp_err provides better error messages for certain input file issues. Thanks to Facebook for the contribution. (Bug #30810629, Bug #98390)

• MySQL Server Docker containers now support server restart within a client session (which happens, for example, when the RESTART statement is executed by a client or during the configuration of an InnoDB Cluster instance). To enable this important feature, containers should be started with the docker run option --restart set to the value on-failure. See Starting a MySQL Server Instance for details. (Bug #30750730)

• EXPLAIN ANALYZE now supports the FORMAT option. Currently, TREE is the only supported format. (Bug #30315224)

• ALTER INSTANCE ROTATE INNODB MASTER KEY is no longer permitted when read_only or super_read_only are enabled. (Bug #30274240)

• On storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is now logged as one transaction in the binary log when row-based replication is in use. Previously, it was logged as two transactions, one to create the table, and the other to insert data. With this change, CREATE TABLE ... SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication. For more information, see Atomic Data Definition Statement Support. (Bug #11756034, Bug #47899, WL #13355)

• LOAD XML now supports CDATA sections in the XML file to be imported. (Bug #98199, Bug #30753708)

• X Plugin's mysqlx_bind_address system variable now accepts multiple IP addresses like MySQL Server's bind_address system variable does, enabling X Plugin to listen for TCP/IP connections on multiple network sockets.

  An important difference in behavior is that for MySQL Server, any error in the list of addresses prevents the server from starting, but X Plugin (which is not a mandatory plugin) does not do this. With X Plugin, if one of the listed addresses cannot be parsed or if X Plugin cannot bind to it, the address is skipped, an error message is logged, and X Plugin attempts to bind to each of the remaining addresses. X Plugin's Mysqlx_address status variable displays only those addresses from the list for which the bind succeeded. If none of the listed addresses results in a successful bind, X Plugin logs an error message stating that X Protocol cannot be used. (WL #12715)

• ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE options were added to CREATE TABLE, ALTER TABLE, and CREATE INDEX syntax. The ENGINE_ATTRIBUTE option was also added to CREATE TABLESPACE and ALTER TABLESPACE syntax. The new options, which permit defining storage engine attributes for tables, columns, indexes, and tablespaces, are reserved for future use.

  The following INFORMATION_SCHEMA tables were added for querying storage engine attributes for tables, columns, indexes, and tablespaces. Values are stored in the data dictionary. The tables are reserved for future use.

  • INFORMATION_SCHEMA.TABLES_EXTENSIONS

  • INFORMATION_SCHEMA.COLUMNS_EXTENSIONS

  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS_EXTENSIONS

  • INFORMATION_SCHEMA.TABLESPACES_EXTENSIONS

  (WL #13341)

• The default logging level for MySQL Server omits informational log messages, which previously included some significant lifecycle events for Group Replication that were non-error situations, such as a group membership change. Messages about significant events for a replication group have now been reclassified as system messages, so they always appear in the server error log regardless of the server logging level. Operators can therefore review a complete history of the server's membership in a replication group. In addition, socket bind errors on the group communication layer have been reclassified from information to error messages. (WL #13769)

Bugs Fixed

• InnoDB: A GROUP BY operation on a JSON array column caused failures in an UBSan build of MySQL due to incorrect type casting. (Bug #31451475)

• InnoDB: Several InnoDB error log messages were defined without symbolic values. (Bug #31401028)

• InnoDB: The file segment for a single page write was not released after a data file write failure associated with a doublewrite flush and sync operation. (Bug #31370227)

• InnoDB: Code that was accessed when undo tablespace truncation used the same space ID before and after a truncate operation was removed. That scenario no longer occurs. The truncated undo tablespace is replaced by a new undo tablespace datafile with a different space ID. (Bug #31354435)

• InnoDB: The range of reserved space IDs for undo tablespaces was increased from 512 per undo tablespace to 400000. (Bug #31340834)

• InnoDB: An error that occurred while inserting a log into the ddl_log table was not returned making it appear as though the operation was successful, and a transaction was not registered while performing a tablespace encryption operation. (Bug #31236217)

• InnoDB: The lob::purge() function did not free LOBs correctly for an undo log record type (TRX_UNDO_UPD_DEL_REC) that is generated when an insert operation modifies a delete-marked record. (Bug #31222046, Bug #99339)

• InnoDB: A shutdown error occurred following an attempt to rebuild a discarded partition. (Bug #31215415)

• InnoDB: The internal get_real_path() function, responsible for retrieving directory or a file path, was modified to strip trailing separators before determining if a path is a file or directory. Additionally, if a path does not exist or cannot be identified as a file or subdirectory, the function now assumes the path is a file if the basename has a three letter suffix. (Bug #31215160)

• InnoDB: Tablespace-related error messages were revised. (Bug #31205520, Bug #31205441)

• InnoDB: To avoid potential compilation issues, __attribute__((const)) and __attribute__((pure)) attributes were removed from internal InnoDB functions. (Bug #31153123)

• InnoDB: The parallel read thread limit was not observed when spawning read threads for histogram sampling, causing an assertion failure. (Bug #31151218)

• InnoDB: The transaction read view was not checked when sampling records for generation of histogram statistics. (Bug #31151077)

• InnoDB: An I/O completion routine was not able acquire an LRU list mutex due to a latch held by another thread. (Bug #31128739)

• InnoDB: An attachable transaction thread requested an InnoDB ticket that was already reserved by the main thread, causing a deadlock. Additionally, the server failed to respond to KILL statements in this deadlock scenario. (Bug #31090777)

• InnoDB: The INNODB_METRICS table AVG_COUNT_RESET value for a counter defined as a module owner reported NULL. The METRIC_AVG_VALUE_RESET field was incorrectly marked as NULL. Thanks to Fungo Wang for the contribution. (Bug #31084706, Bug #98990)

• InnoDB: At startup, following an unexpected stoppage during an undo tablespace truncate operation, some rollback segment header pages were found to be corrupted. Encryption of rollback segment header pages was initiated while the header pages were being written, resulting in some header pages not being encrypted, as expected. (Bug #31045160)

• InnoDB: Various aspects of the lock system (lock_sys) code were refactored, and issues with lock_sys lock_rec_block_validate() and lock_test_prdt_pacge_lock() functions were fixed. The lock_rec_block_validate() function called another function repeatedly, which could result in locks not being validated under certain circumstances. The implementation also had a potential quadratic time complexity. The lock_test_prdt_page_lock() function did not iterate over all locks as intended. (Bug #31001732)

• InnoDB: Use of memory-mapped files after exceeding the temptable_max_ram threshold caused a performance degradation. (Bug #30952983, Bug #98739)

• InnoDB: In debug mode, a DROP TABLE operation on a table with an incorrectly defined COMPRESSION clause caused a failure. InnoDB did not return an error to the caller for proper handling. (Bug #30899683, Bug #98593)

• InnoDB: Purge thread activity was excessive when the history list length approached zero, wasting CPU resource and causing mutex contention. (Bug #30875956)

• InnoDB: A regression introduced in MySQL 8.0.18 affected INFORMATION_SCHEMA.INNODB_COLUMNS query performance. Schema and table data dictionary objects were fetched repeatedly to retrieve partition column information. (Bug #30837086, Bug #98449)

  References: This issue is a regression of: Bug #93033, Bug #28869903.

• InnoDB: An ALTER TABLE ... IMPORT TABLESPACE operation with a .cfg file failed with an “Incorrect key file for table” error. The row_import::m_flags member was not initialized. (Bug #30830441)

• InnoDB: A DROP TABLE operation performed after discarding a partition did not remove the associated data files, and DROP DATABASE failed with an error indicating that the database directory could not be removed. Upgrade from MySQL 5.7 to MySQL 8.0 also failed if a partitioned table with a discarded partition was present. The DISCARD attribute was applied to the table object instead of the partition object in the data dictionary, which made it appear that all partitions were discarded. (Bug #30818917)

• InnoDB: The server failed intermittently with an “ibuf cursor restoration fails” error. (Bug #30770380, Bug #91033)

• InnoDB: An ALTER TABLE operation that copied data from one table to another returned an “Out of range value for column” error. The counter that tracks the number of AUTO_INCREMENT rows required for a multi-row insert operation was not always set back to zero after a bulk insert operation. (Bug #30765952, Bug #98211)

• InnoDB: The internal TempTable records_in_range() handler function contained a DBUG_ABORT() call that caused assertion failures in debug builds, and empty result sets in regular builds for some queries. (Bug #30716037)

• InnoDB: The btr_cur_pessimistic_update() function failed to handle a cursor position change caused by a lob::purge() call. (Bug #30712878)

• InnoDB: A type conversion failure during a DELETE IGNORE operation caused an assertion failure. A JSON value was not converted to the expected value. (Bug #30664660)

• InnoDB: A purge operation encountered a null LOB reference, causing an assertion failure. (Bug #30658887)

• InnoDB: Chunk size was not calculated correctly when deallocating memory from the TempTable storage engine, causing a regression in SELECT DISTINCT query performance. (Bug #30562964)

• InnoDB: A segmentation fault occurred in the TempTable storage engine while using the thread pool plugin. TempTable thread-local variables were not compatible with the use of different threads for statements issued by a single client connection. Use of thread local variables also lead to excessive memory consumption due to the memory used by thread-local variables remaining allocated for the life of the thread. To address these issues, thread-local variables were replaced by a caching mechanism. (Bug #30050452, Bug #31116036, Bug #99136)

• InnoDB: A fatal “page still fixed or dirty” error occurred during shutdown. (Bug #29759555, Bug #95285)

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

• Partitioning: A query against a partitioned table, which used an ORDER BY, returned unordered results under the following conditions:

  • The table had a composite index with a prefix on one of the columns.

  • The query's WHERE clause contained an equality condition on the prefixed column.

  • The column with the prefix was the leftmost column in the index.

  • The column used in the ORDER BY was the rightmost column in the index.

  • The index was used for handling the ORDER BY.

  Our thanks to Quanan Han for the suggestion. (Bug #84070, Bug #25207522)

• Replication: If a group's consistency level (set by the group_replication_consistency system variable) was set to BEFORE or BEFORE_AND_AFTER, it was possible for a deadlock to occur in the event of a primary failover. The primary failover is now registered differently to avoid this situation. (Bug #31175066, Bug #98643)

• Replication: When a replication source server shuts down and restarts, its MEMORY tables become empty. To replicate this effect to replicas, the first time that the source uses a given MEMORY table after startup, it notifies replicas that the table must be emptied by writing a DELETE statement for that table to the binary log. Previously, the generated DELETE statement was written to the binary log statement cache for the current session, which could result in it being logged together with other statements under the same GTID, or logged without BEGIN and COMMIT statements. Also, in some situations, the generated DELETE statement could consume the GTID intended for the transaction that triggered it. The generated DELETE statement is now logged with accompanying BEGIN and COMMIT statements, and the resulting transaction is flushed to the binary log immediately after it is written to the statement cache, so that it always receives its own GTID and is kept separate from other transactions. (Bug #30527929, Bug #25681518, Bug #77729)

• Replication: Following a patch in MySQL 8.0.14, if a function call contained operations on temporary tables, it could be written to the binary log in statement format when binlog_format = MIXED was set. This led to CREATE TEMPORARY TABLE statements being incorrectly written to the binary log if they contained a function call. Following further analysis, operations on temporary tables in stored functions and triggers are now marked as unsafe for binary logging in statement format, as they have a strong chance of causing issues with replication. When binlog_format = MIXED is set, these operations are now logged in row format. (Bug #30395151, Bug #30320009)

• Replication: A fix made in MySQL 8.0.14 and MySQL 5.7.25 for a deadlock scenario involving the system variables binlog_transaction_dependency_tracking and binlog_transaction_dependency_history_size had the side effect of leaving the writeset history used for transaction dependency tracking unprotected from concurrent update. The writeset history and tracking mode are now locked correctly whenever they are accessed. (Bug #29719364, Bug #95181)

  References: See also: Bug #28511326, Bug #91941.

• Replication: If a CHANGE MASTER TO statement was issued with MASTER_USER specified as empty (MASTER_USER=''), the statement succeeded and cleared any previously specified user name in the replication metadata repositories. However, if information was subsequently read from the repositories, for example during an automatic restart of a Group Replication channel, a default user name could be substituted for the channel. This issue has now been fixed, so from MySQL 8.0.21, it is a valid approach to set an empty MASTER_USER user name if you always provide user credentials using the START SLAVE statement or START GROUP_REPLICATION statement that starts the replication channel. This approach means that the replication channel always needs operator intervention to restart, but the user credentials are not recorded in the replication metadata repositories.

  The documentation for the CHANGE MASTER TO statement has also been corrected to clarify that it is possible to specify MASTER_USER='', and the resulting error occurs only if you attempt to start the replication channel with the empty credentials. (Bug #27357189)

• Group Replication: A global value that is set for the group_replication_consistency system variable, which controls all user connections, is applied on Group Replication's internal connections to MySQL Server modules using the SQL API, which are handled in a similar way to user connections. This could sometimes lead to Group Replication reporting the use of group_replication_consistency as an error, for example when checking the clone plugin status during distributed recovery. Such internal connections using the SQL API now use consistency level EVENTUAL, which matches the behavior before group_replication_consistency was available, and does not cause an error message. (Bug #31303354, Bug #99345)

• Group Replication: On Windows, Group Replication's use of the Windows API function SleepConditionVariableCS to wait for new write events caused noticeably high CPU usage by this function after Group Replication had been running for two days or more, which could be corrected by restarting the MySQL server instance, but then increased again over time as before. This was caused by the use of two clock functions to calculate the timeout after which the SleepConditionVariableCS function was called, which drifted relative to each other over time, making the timeout progressively shorter and the calls to the function more frequent. The issue has been corrected on Windows by using the current time from a single clock to calculate the timeout. (Bug #31117930)

• Group Replication: If Group Replication was stopped while distributed recovery was in progress, memory issues could result from an attempt to access the record of the member that was selected as the donor. This record is now kept locally with the distributed recovery state. (Bug #31069563)

• Group Replication: When distributed recovery for Group Replication involves a remote cloning operation, the flag set on the server to indicate this remains set until the server instance is restarted. Previously, if Group Replication was stopped and restarted on the server, that flag caused Group Replication to purge the relay log files for the group_replication_applier channel, as is required on starting after a remote cloning operation to ensure that there is no mismatch with the cloned data tables. If there were any unapplied transactions in the purged relay log files, the member could not subsequently be used to bootstrap a group, although it could successfully join a group by retrieving the transactions from another member. Group Replication now ignores the flag on its second or subsequent starts, and only purges the relay log files the first time it is started after a remote cloning operation. (Bug #31059680)

• Group Replication: To avoid the possibility of data inconsistency, Group Replication blocks a new incarnation of the same server (with the same address but a new identifier) from joining the group while its old incarnation is still listed as a member. Previously, the Group Replication Group Communication System (GCS) treated the connection to the old incarnation of a server as active while it was attempting to send messages to the server, and only recognized that the connection was inactive when the socket returned an error, which might take a significant amount of time. During that period, the new incarnation of the server was unable to join the group because the existing members did not connect to it, as they were still waiting on the connection to the old incarnation. Now, GCS only treats a connection to a server as active while messages can be sent to it successfully. If the socket is no longer writeable, the server connection is treated as inactive and is proactively closed. The connection close triggers the group member to attempt reconnection to that server address, upon which a connection is established to the new incarnation of the server, enabling the new incarnation to join the group. (Bug #30770577)

• Group Replication: Group Replication did not broadcast a notification when switching from single-primary mode to multi-primary mode. The change is now notified for use in routing. (Bug #30738896)

• Group Replication: Setting the group_replication_force_members system variable to force a specified membership for a group could fail if another member had already requested the expulsion of the member that was driving the group_replication_force_members operation. The operation to implement the configuration specified by the group_replication_force_members system variable forced any pending group reconfigurations to take place first. If one of those successfully expelled the member where the system variable had been set, because the expel timeout that was set on the member had expired, the operation timed out and failed to complete. To avoid this situation, Group Replication now proceeds directly to implementing the new configuration specified by the group_replication_force_members system variable, and ignores any other pending group reconfigurations. (Bug #29820966)

• Group Replication: Group Replication tracking of connections to other group members only took into account the incoming connections, not the outgoing connections. This meant if the outgoing connection from member A to member B was broken, for example by a firewall configuration issue, but the incoming connection from member B to member A was intact, member A would display member B's status as ONLINE, although member A's messages were not reaching member B. Member B would display member A's status as UNREACHABLE. Now, if a group member starts to receive pings from another group member to which it has an active connection (in this case, if member A received pings from member B), this is treated as an indicator of an issue with the connection. If sufficient pings are received, the connection is shut down by the recipient of the pings (in this case, member A), so that the status of the connection is consistent for both members. (Bug #25660161, Bug #84796)

• JSON: When the expression and path passed to JSON_TABLE() yielded a JSON null, the function raised an error instead of returning SQL NULL as required. (Bug #31345503)

• JSON: In MySQL 5.7, and in MySQL 8.0 prior to 8.0.17, the server attempted to convert JSON boolean values to their SQL counterparts when testing them directly with IS TRUE, as shown here:

  mysql> CREATE TABLE test (id INT, col JSON);
  
  mysql> INSERT INTO test VALUES (1, '{"val":true}'), (2, '{"val":false}');
  
  mysql> SELECT id, col, col->"$.val" FROM test WHERE col->"$.val" IS TRUE;
  +------+---------------+--------------+
  | id   | col           | col->"$.val" |
  +------+---------------+--------------+
  |    1 | {"val": true} | true         |
  +------+---------------+--------------+

  As the result of work done in MySQL 8.0.17 to ensure that all predicates in SQL conditions are complete (that is, a condition of the form WHERE value is rewritten as WHERE value <> 0), and that a NOT IN or NOT EXISTS condition in a WHERE or ON clause is converted to an antijoin, evaluation of a JSON value in an SQL boolean context performs an implicit comparison against JSON integer 0. This means that the query shown previously returns the following result in MySQL 8.0.17 and later:

  mysql> SELECT id, col, col->"$.val" FROM test WHERE col->"$.val" IS TRUE;
  +------+----------------+--------------+
  | id   | col            | col->"$.val" |
  +------+----------------+--------------+
  |    1 | {"val": true}  | true         |
  |    2 | {"val": false} | false        |
  +------+----------------+--------------+

  In such cases, the server also now provides a warning: Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING. Thus, the query can now be rewritten using JSON_VALUE() as shown here:

  mysql> SELECT id, col, col->"$.val" FROM test
      ->     WHERE JSON_VALUE(col, "$.val" RETURNING UNSIGNED) IS TRUE;
  +------+---------------+--------------+
  | id   | col           | col->"$.val" |
  +------+---------------+--------------+
  |    1 | {"val": true} | true         |
  +------+---------------+--------------+

  (Bug #31168181)

• JSON: A GROUP BY query against a table having a multi-valued index was not always handled correctly by the server. (Bug #31152942)

• If log_error_services was persisted, in some cases it could take effect at the wrong time during startup. (Bug #31464539)

• SHOW CREATE USER after certain manual grant table modifications could cause a server exit. (Bug #31462844)

• Some in-memory updates of partial revokes could produce incorrect privileges. (Bug #31430086)

• If log_error_verbosity was set using SET PERSIST, it did not take effect early enough during server startup to affect InnoDB initialization. (Bug #31410674)

• The parser incorrectly raised an assertion before rejecting subqueries in generated column expressions. (Bug #31396191)

• This release makes the following two micro-optimizations for degenerate hash joins (that is, those with no join conditions):

  1. For a degenerate hash antijoin or semijoin, add LIMIT 1 when building the hash table, since having more rows than this cannot change the result.

  2. For a degenerate hash antijoin with a nonempty hash table, avoid scanning the outer side.

  Together, these changes handle a performance regression whereby a rewrite to a hash antijoin caused a NOT EXISTS query which was not rewritten to be executed by the optimizer and be replaced with “zero rows found”. To handle the case in which a nested loop is used instead, a non-correlated subquery inside NOT EXISTS is no longer transformed to an antijoin.

  This fix also applies to subqueries using constant NOT IN (non_correlated_subquery). (Bug #31376809)

• Configuring with -DWITH_EDITLINE=system resulted in compilation failures for older library versions. (Bug #31366715)

• The upgrade of the bundled libedit library in the previous MySQL distribution caused a problem for builds using that library such that CTRL+C (SIGINT) in the mysql client required a following Enter to take effect in some circumstances. (Bug #31360025)

• Columns declared with both AUTO_INCREMENT and DEFAULT value expressions are a nonpermitted combination, but ALTER TABLE failed to produce an error for SET DEFAULT (expr) operations on AUTO_INCREMENT columns. (Bug #31331454)

• It was possible to set the protocol_compression_algorithms system variable to the empty string. This is no longer permitted. (Bug #31326231)

• A lookup function used internally in the MySQL server returns integer -1 when the argument is ambiguous; this resulted in undefined behavior when this value was converted to an unsigned value prior to use as an argument in subsequent calculations. Now when the function returns -1, this is handled as an error and the value is not used further. (Bug #31326120)

• Negation of a signed value in certain cases led to undefined behavior; to prevent this from occurring, the value to be negated is now treated as unsigned. (Bug #31325602)

• The WEIGHT_STRING() function did not always return the correct result for an integer argument. (Bug #31321257)

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

• Assigning CONCAT('') or CONCAT_WS('') to a variable set the variable to NULL, not the empty string. (Bug #31320716, Bug #99485, Bug #31413167, Bug #99722)

• Corrected problems where under some circumstances privilege restrictions could be ignored. (Bug #31306814, Bug #31315692)

• Certain SELECT statement privileges to lock rows were not checked properly and could block other users incorrectly. (Bug #31293065)

• When performing a filesort, an internal function could sometimes return NULL on failure, even if the subselect being sorted was not nullable. (Bug #31281602)

• Statement rewriting for the binary log was inefficient on Windows. (Bug #31260698)

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

• An inconsistency in representing anonymous users in memory could cause issues while performing privilege-granting operations. (Bug #31246179)

• If the administrative connection interface was enabled, a race condition could lead to problems accepting Unix socket file connections on the main connection interface. (Bug #31241872)

• When a role was granted with WITH ADMIN OPTION, the grantee was able to manage the role only after activating it. (Bug #31237368)

• Invalid rows in the default_roles or role_edges system tables could cause server misbehavior. (Bug #31217385)

• Component deinitialization failure at runtime could lead to repeated messages written to the error log at shutdown. (Bug #31217037)

• The prohibition on granting roles to anonymous users was incompletely enforced. (Bug #31215017)

• A privilege-escalation issue was corrected. (Bug #31210226)

• The keyring_hashicorp keyring plugin did not perform sufficient validity checking on the values of its configuration parameters. (Bug #31205363)

• The keyring_hashicorp keyring plugin did not permit binary log encryption to be enabled (by setting the binlog_encryption system variable). (Bug #31204841)

• The keyring_hashicorp keyring plugin did not permit an encryption password to be set by the audit_log plugin. (Bug #31197670)

• Some queries using REGEXP_SUBSTR() with an ORDER BY clause were not handled correctly by the server. (Bug #31184858)

• Some instances where pointer arithmetic was applied to nullptr were corrected. (Bug #31172750)

• If the available file descriptors were exhausted, mysql_real_connect() caused the client to exit. (Bug #31151052)

• Using the killall command to initiate a mysqld shutdown resulted in no message being logged to indicate the start of shutdown. This has been corrected. (Bug #31121907)

• Calling mysql_real_connect_nonblocking() with an invalid host could cause the client to exit upon calling mysql_close(). (Bug #31104389, Bug #99112)

• For Debian packages, Python 2 dependencies that could cause installation failures were removed. (Bug #31099324)

• A potential memory leak in lf_hash_insert() was fixed. (Bug #31090258, Bug #99078)

• Within the LDAP SASL authentication plugins, multiple calls to sasl_client_done() could cause undefined behavior in some cases. (Bug #31088206)

• With the thread pool plugin enabled, high concurrency conditions could cause loss of client context resulting in a server exit. (Bug #31085322)

• For result sets processed using mysql_use_result(), mysql_fetch_row_nonblocking() did not increment the number of rows, so that after all the rows were fetched, mysql_num_rows() returned an incorrect number of rows. (Bug #31082201, Bug #99073)

• Removed an unneeded optimization for EXISTS() that was never actually evaluated. (Bug #31069510)

• For a server started with the --skip-grant-tables option, enabling the partial_revokes system variable caused a server exit. (Bug #31066069, Bug #31202963)

• Queries that used a recursive common table expression with an outer reference could return incorrect results. (Bug #31066001, Bug #99025)

• The parser could fail for multibyte character sets with a minimum character length greater than 1 byte. (Bug #31063981)

• In some cases, the LEAST() function could return NULL for non-nullable input. (Bug #31054254)

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

• mysql_real_connect_nonblocking() blocked if the MYSQL_OPT_CONNECT_TIMEOUT option was set. (Bug #31049390, Bug #98980)

• The last call to the mysql_fetch_row_nonblocking() C API function to return the null row was setting an error when it should not have. (Bug #31048553, Bug #98947)

• On Windows, the default connection type uses a named pipe. The nonblocking C API, which is intended for TCP/SSL connections, did not take that into account and caused the client to exit. It now produces an error message indicating the problem. (Bug #31047717)

• X Plugin connections that failed to authenticate due to nonexistence of the user modified the global audit_log_filter_id system variable. (Bug #31025461)

• LOAD DATA did not ignore hidden generated columns when parsing input file rows. (Bug #31024266, Bug #98925)

• Pinbox exhaustion in the metadata locking subsystem could produce a misleading error message. (Bug #31019269, Bug #98911)

• CREATE TABLE ... SELECT failed if it included a functional index. (Bug #31017765, Bug #98896)

• For X Protocol connections, checking the global session mutex was improved to eliminate a minor performance degradation as the number of threads increased. (Bug #31000043)

• In certain cases, executing a query containing multiple subqueries could lead to an unplanned shutdown of the server. (Bug #30975826)

• SHOW CREATE TRIGGER failed if FLUSH TABLES WITH READ LOCK was in effect. (Bug #30964944)

• Excessive access checks were performed on certain of the data dictionary tables that underlie INFORMATION_SCHEMA views, resulting in slow SHOW COLUMNS performance. These checks were reduced to improve performance.

  In addition, several SHOW statements implemented as INFORMATION_SCHEMA queries were found to benefit from enabling the derived_merge flag for the optimizer_switch system variable. Such queries now internally enable that flag temporarily for better performance, regardless of the flag session value. Affected queries are:

  SHOW SCHEMAS
  SHOW TABLES
  SHOW TABLE STATUS
  SHOW COLUMNS
  SHOW KEYS
  SHOW EVENTS
  SHOW TRIGGERS
  SHOW PROCEDURE STATUS
  SHOW FUNCTION STATUS
  SHOW CHARACTER SET
  SHOW COLLATION

  (Bug #30962261, Bug #98750, Bug #30921214)

• Two otherwise identical queries executed separately returned one row when using a case-sensitive collation and two rows with a case-insensitive collation. When the same two predicates were combined in a single query using AND, two rows were returned when only one row should have been. (Bug #30961924)

• ALTER TABLE on a SET column that had a display width greater than 255 was not done in place, even if otherwise possible. (Bug #30943642, Bug #98523)

• The server checked whether a number in yottabytes was too large to print by comparing the value as a double to ULLONG_MAX, which cannot be represented as a double. This caused the double value immediately above ULLONG_MAX yottabytes to be printed as 0Y, the erroneous conversion being reported by Clang 10. (Bug #30927590)

• Resource group SQL statements such as CREATE RESOURCE GROUP did not work over connections that use X Protocol. (Bug #30900411)

• SHOW GRANTS could display function privileges as procedure privileges. (Bug #30896461, Bug #98570)

• The audit_log plugin mishandled connection events when multiple clients connected simultaneously. (Bug #30893593)

• The LOCK_ORDER tool reported a syntax error for empty dependency graphs. Empty graphs are now permitted.

  The LOCK_ORDER tool could exhibit unexpected behavior due to mishandling thread list maintenance. (Bug #30889192)

• Upgrades from MySQL 5.7 did not grant the REPLICATION_APPLIER privilege to root. (Bug #30783149)

• The gen_range() user-defined function could mishandle its arguments, causing a server exit. (Bug #30763294)

• During UPDATE processing, conversion of an internal in-memory table to InnoDB could result in a key-length error. (Bug #30674616)

• Attempts to grant dynamic privileges (which are always global) at the procedure or function level did not produce an error. (Bug #30628160)

• Table value constructors ignored the LIMIT clause. The clause is now taken into account. For example: VALUES ROW(1), ROW(2), ROW(3) LIMIT 2 outputs 1 and 2. (Bug #30602659)

• It is possible to define a column named * (single asterisk character), but SELECT `*` was treated identically to SELECT *, making it impossible to select only this column in a query; in other words, the asterisk character was expanded to a list of all table columns even when it was surrounded by backticks. (Bug #30528450)

• The FROM_DAYS() function could produce results that were out of range (with the year > 9999). (Bug #30455845, Bug #97340)

• For debug builds, altering the mysql.func table to MyISAM (not a recommended operation in any case) caused a server exit. Now this operation is prohibited. (Bug #30248138, Bug #96692)

• Queries on the INFORMATION_SCHEMA KEY_COLUMN_USAGE and TABLE_CONSTRAINTS views could be slow due to UNION use in their definitions. These were rewritten to move the UNION into a LATERAL table to enable the optimizer to better use indexes. (Bug #30216864, Bug #30766181, Bug #98238)

• In certain cases, a LIMIT clause incorrectly caused the optimizer to estimate that zero rows needed to be read from a table. (Bug #30204811)

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

• An internal packet-length function returned values of the wrong integer type. (Bug #30139031)

• Calculations by mysqldump for the length of INSERT statements did not take into account the _binary character set introducer used for VARBINARY strings. (Bug #29998457, Bug #96053)

• The messages printed to the error log during upgrade of partitioned tables defined with prefix keys did not provide sufficient details. Detailed warnings that indicate the schema, table, column, and prefix length are now printed. (Bug #29942014)

  References: See also: Bug #31100205.

• mysql_store_result() could fail to detect invalid data packets. (Bug #29921423)

• An assertion was raised if creating a child table in a foreign key relation caused an engine substitution. (Bug #29899151, Bug #95743)

• mysqltest and mysql-test-run.pl no longer support the --sleep command-line option. mysqltest no longer supports the real_sleep command. (Bug #29770237)

• The server permitted connections for hosts with names longer than the maximum permitted length (255 characters). (Bug #29704941)

• In a multiple-table UPDATE that updated the key of the first table, if a temporary table strategy was used, duplicate entries could be written to the temporary table, followed by occurrence of a Can't find record error. (Bug #28716103)

• The server sometimes mistakenly removed a subquery with a GROUP BY when optimizing a query, even in some cases when this subquery was used by an outer select. This could occur when the subquery also used an aggregate function. (Bug #28240054)

• Coercibility of the NAME_CONST() function was assessed incorrectly. (Bug #26319675)

• When reading rows from a storage engine, errors other than “no more records” could be ignored, leading to problems later. (Bug #20162055)

• When a multi-table update used a temporary table, this was not shown in the output of EXPLAIN FORMAT=TREE, even though such use could have an impact on the performance of the UPDATE statement for which this was done. (Bug #17978975)

• When performing a filesort for removing duplicates, such as when executing SELECT DISTINCT, it may be necessary to perform another sort afterwards to satisfy an ORDER BY. In cases where such an ORDER BY had been pushed down into the first table of a join, as opposed to the join as a whole, this final sort was not actually performed. (Bug #99687, Bug #31397840)

• Refactoring work done in MySQL 8.0.20 caused single-row buffering for GROUP BY of non-nullable columns not to function correctly, not taking into account that such a column could be the inner table for an outer join, and thus would have a NULL flag that would need to be copied. In a GROUP BY without a temporary table, this would cause the NULL flag to come from the next output row instead of the previous one, and the data returned to be inconsistent. (Bug #99398, Bug #31252625)

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

• A logical error in the constant folding code for the case in which a constant of type DECIMAL or FLOAT was the left-hand operand and an integer column value was the right-hand operand yielded an incorrect result. (Bug #99145, Bug #31110614)

• A query whose predicate compared 0 with -0 where at least one of these was a floating-point value returned incorrect results. (Bug #99122, Bug #31102789)

• Reimplemented rollups without using slices. This fixes the following known issues:

  • A repeating column in a GROUP BY ... WITH ROLLUP yielded the wrong result; that is, a GROUP BY of the form GROUP BY a, b, a WITH ROLLUP erroneously produced NULL for some of the column names in the result.

  • A GROUP BY ... WITH ROLLUP that did not require a temporary table to print the result also produced an erroneous NULL in place of at least one of the expected column names in the output.

  (Bug #98768, Bug #99141, Bug #26227613, Bug #29134467, Bug #30967158, Bug #30969045, Bug #31110494)

• SELECT DISTINCT( HEX( WEIGHT_STRING(varchar_column) ) ) returned a truncated result. (Bug #98592, Bug #30898753)

• Problems with error handling in queries with MAX(), MIN(), or both, combined with a GROUP BY clause, meant that such a query continued executing until it went through all possible iterations even when an error should have caused it to terminate immediately. (Bug #98242, Bug #30769515)

• After refactoring the type propagation code for LEAST(), GREATEST(), and other functions, as well as UNION, an adjustment of the result type for data types like ENUM also replaced the calculated integer data type with a type that could not accommodate both signed and unsigned values. (Bug #95148, Bug #29698617)

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