Changes in MySQL 8.0.30 (2022-07-26, General Availability)

• Character Set Support

• Compilation Notes

• Deprecation and Removal Notes

• Generated Invisible Primary Keys (GIPKs)

• Keyring Notes

• Performance Schema Notes

• Pluggable Authentication

• Security Notes

• Spatial Data Support

• SQL Syntax Notes

• XA Transaction Notes

• Functionality Added or Changed

• Bugs Fixed

Character Set Support

• Important Change: A previous change renamed character sets having deprecated names prefixed with utf8_ to use utf8mb3_ instead. In this release, we rename the utf8_ collations as well, using the utf8mb3_ prefix; this is to make the collation names consistent with those of the character sets, not to rely any longer on the deprecated collation names, and to clarify the distinction between utf8mb3 and utf8mb4. The names using the utf8mb3_ prefix are now used exclusively for these collations in the output of SHOW statements such as SHOW CREATE TABLE, as well as in the values displayed in the columns of Information Schema tables including the COLLATIONS and COLUMNS tables. (Bug #33787300)

  References: See also: Bug #30624990.

• Important Change: When more than one language had the same collation definition, MySQL implemented collations for only one of the languages. This meant that some languages were covered only by utf8mb4 Unicode 9.0 collations that are specific to other languages. This release fixes such issues by adding language-specific collations for those languages that were previously covered only by language-specific collations for other languages. The new collations are listed here:

  • Norwegian

    • (Bokmål) utf8mb4_nb_0900_ai_ci

    • (Bokmål) utf8mb4_nb_0900_as_cs

    • (Nynorsk) utf8mb4_nn_0900_ai_ci

    • (Nynorsk) utf8mb4_nn_0900_as_cs

  • Serbian with Latin characters:

    • utf8mb4_sr_latn_0900_ai_ci

    • utf8mb4_sr_latn_0900_as_cs

  • Bosnian with Latin characters:

    • utf8mb4_bs_0900_ai_ci

    • utf8mb4_bs_0900_as_cs

  • Bulgarian:

    • utf8mb4_bg_0900_ai_ci

    • utf8mb4_bg_0900_as_cs

  • Galician:

    • utf8mb4_gl_0900_ai_ci

    • utf8mb4_gl_0900_as_cs

  • Mongolian with Cyrillic letters:

    • utf8mb4_mn_cyrl_0900_ai_ci

    • utf8mb4_mn_cyrl_0900_as_cs

  For more information, see Language-Specific Collations. (Bug #31885256, WL #14307)

Compilation Notes

• On Enterprise Linux, fixed ADD_LINUX_RPM_FLAGS so that the initial values of CMAKE_C_FLAGS and CMAKE_CXX_FLAGS are used before modifying them. (Bug #34131794)

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

• Added a new SHOW_SUPPRESSED_COMPILER_WARNINGS CMake option. Enable it to show suppressed compiler warnings, and do so without failing with -Werror. It defaults to OFF. (Bug #34046748)

• Added macOS/ARM support. (Bug #34017614)

• On Windows, deprecation warnings (C4996) were globally disabled with the /wd4996 command-line option; now deprecation warnings are disabled on the localized level where appropriate. (Bug #33975638)

• On Windows, improved the generated INFO_BIN and INFO_SRC files. (Bug #33972317, Bug #34052301)

• Improved GCC 8 support to include -lstdc++fs in order to use std::filesystem. (Bug #33939798)

Deprecation and Removal Notes

• Replication: Setting the replica_parallel_workers system variable (or the equivalent server option --replica-parallel-workers) to 0 is now deprecated, and doing so now raises a warning.

  To achieve the same result (that is, use single threading) without the warning, set replica_parallel_workers=1 instead. (WL #13956)

• The --skip-host-cache server option is now deprecated, and subject to removal in a future release.

  Use a statement such as SET GLOBAL host_cache_size = 0, or set host_cache_size in the my.cnf file, instead. (WL #14359)

• The --old-style-user-limits option causes the server to enforce user limits as they were prior to MySQL 5.0.3, and is intended for backwards compatibility with very old releases. This option is now deprecated, and using it now raises a warning. You should expect this option to be removed in a future release of MySQL, and so you are advised to begin now to remove any dependency your MySQL applications might have on this option. (WL #13228)

Generated Invisible Primary Keys (GIPKs)

• MySQL 8.0.30 now supports GIPK mode, which causes a generated invisible primary key (GIPK) to be added to any InnoDB table that is created without an explicit primary key. This enhancement applies to InnoDB tables only.

  The definition of the generated key column added to an InnoDB table by GIPK mode is shown here:

  my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

  The name of the generated primary key is always my_row_id; you cannot, while GIPK mode is in effect, use this as a column name in a CREATE TABLE statement that creates a new InnoDB table unless it includes an explicit primary key.

  GIPKs are not enabled by default. To enable them, set the sql_generate_invisible_primary_key server system variable (also introduced in this release) to ON. This setting has no effect on replication applier threads; this means that a replica never generates a primary key for a replicated table that was not created on the source with a primary key.

  You cannot alter a generated invisible primary key while GIPKs are in effect, with one exception: You can toggle the visibility of the GIPK using ALTER TABLE tbl CHANGE COLUMN my_row_id SET VISIBLE and ALTER TABLE tbl CHANGE COLUMN my_row_id SET INVISIBLE.

  By default, generated invisible primary keys can be seen in the output of SHOW CREATE TABLE and SHOW INDEX; they are also visible in MySQL Information Schema tables such as the COLUMNS and STATISTICS tables. You can make them hidden instead by setting show_gipk_in_create_table_and_information_schema to OFF.

  You can exclude generated invisible primary keys from the output of mysqldump using the --skip-generated-invisible-primary-key option added in this release. mysqlpump also now supports a --skip-generated-invisible-primary-key option which excludes GIPKs from its output.

  For more information and examples, see Generated Invisible Primary Keys. For general information on invisible column support in MySQL, see Invisible Columns. (Bug #34092605, WL #13784)

Keyring Notes

• The keyring_aws plugin has been updated to use the latest AWS Encryption SDK for C (version 1.9.186).

  The keyring_aws_region variable supports the additional AWS regions supported by the new SDK. Refer to the variable description for a list of supported AWS regions. (WL #14547)

Performance Schema Notes

• InnoDB: In the Performance Schema instrumentation for InnoDB read-write locks, lock acquisition failures and successes for TRY (no-wait) operations were instrumented incorrectly. (Bug #34131395)

• Group Replication: Performance Schema now provides instrumentation for performance monitoring of Group Replication memory usage.

  See Monitoring Group Replication Memory Usage with Performance Schema Memory Instrumentation. (WL #14726)

Pluggable Authentication

• The SASL LDAP plugin failed to properly parse Kerberos Key Distribution Center (KDC) host information read from the Kerberos configuration file, resulting in SASL authentication error. (Bug #31862170)

Security Notes

• It is now possible to compile the MySQL server package (mysqld + libmysql + client tools) using OpenSSL 3.0 on supported platforms, which should not change the behavior of the server or client programs. For additional information, see https://wiki.openssl.org/index.php/OpenSSL_3.0. (WL #14683)

Spatial Data Support

• Previously, the ST_TRANSFORM() function added in MySQL 8.0.13 did not support Cartesian Spatial Reference Systems. Beginning with this release, support is provided by this function for the Popular Visualisation Pseudo Mercator (EPSG 1024) projection method, used for WGS 84 Pseudo-Mercator (SRID 3857). (WL #11961)

SQL Syntax Notes

• It is now possible to determine whether a REVOKE statement which cannot be executed raises an error or a warning. This is implemented with the addition of two new statement options, listed here with brief descriptions:

  • IF EXISTS causes REVOKE to raise a warning rather than an error as long as the target user or role does not exist.

  • IGNORE UNKNOWN USER causes REVOKE to raise a warning instead of an error if the target user or role is not known, but the statement would otherwise succeed.

  For a single target user or role and a given privilege or role to be removed, using the IF EXISTS and IGNORE UNKNOWN USER options together in the same REVOKE statement means that the statement succeeds (albeit doing nothing, and with a warning), even if both the target user or role and the privilege or role to be removed are unknown, as long as the statement is otherwise valid. In the case of multiple targets, multiple privileges or roles to be removed, or both, the statement succeeds, performing those removals which are valid, and issuing warnings for those which are not.

  For more information, see REVOKE Statement. (Bug #102232, Bug #32495441, WL #14690)

XA Transaction Notes

• Replication; Group Replication: Previously, recovery was not guaranteed when a server node in a replication topology unexpectedly halted while executing XA PREPARE, XA COMMIT, or XA ROLLBACK. To address this problem, MySQL now maintains consistent XA transaction state across a topology using either MySQL “classic” Replication or MySQL Group Replication when a server node is lost from the topology and then regained. This also means that XA transaction state is now propagated so that nodes do not diverge while doing work within a given transaction in the event that a server node halts, recovers, and rejoins the topology.

  For any multi-server replication topology (including one using Group Replication), the XA transaction state propagates consistently, so that all servers remain in the same state at all times. For any such topology of any size (including a single server, as long as binary logging is enabled), it is now possible to recover any server to a consistent state after it has halted unexpectedly and been made to rejoin the topology after dropping out.

  This enhancement is implemented for the case of a single server by adding support for a two-phase XA prepare between the storage engine and the server's internal transaction coordinator (ITC), with the state of the prepare retained by both. This means that the ITC can purge its internal logs safely, without the risk of losing state, should the server halt following the purge. In the single-node case, imposing order of execution between the storage engine and the binary log prevents externalization of GTIDs before the corresponding changes become visible to the storage engine; in a topology comprising multiple servers, this keeps the transaction state from being broadcast to the topology before it is guaranteed to be locally consistent and persistent. In all cases, the state of the XA transaction is extracted from the last binary log file to be written and synchronized with the transaction state obtained from the storage engine.

  A known issue in this release can be encountered when the same transaction XID has been used to execute XA transactions sequentially. If there a disruption in operation occurs while the server is processing XA COMMIT ... ONE PHASE using this same XID, after the transaction has been prepared in the storage engine, the state between the binary log and the storage engine can no longer be reliably synchronized.

  For more information, see XA Transactions. (WL #11300)

Functionality Added or Changed

• Important Change: Binary packages that include curl rather than linking to the system curl library have been upgraded to use curl 7.83.1. (Bug #34138733)

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

• Important Change: The fido2 library included with MySQL, used with the authentication_fido plugin, has been upgraded to version 1.8.0. (Previously, version 1.5.0 was included with MySQL.)

  For more information, see FIDO Pluggable Authentication. (WL #15111)

• InnoDB: The innodb_doublewrite system variable, which enables or disables the doublewrite buffer, has two new settings, DETECT_ONLY and DETECT_AND_RECOVER. With the DETECT_ONLY setting, database page content is not written to the doublewrite buffer, and recovery does not use the doublewrite buffer to fix incomplete page writes. This lightweight setting is intended for detecting incomplete page writes only. The DETECT_AND_RECOVER setting is equivalent to the existing ON setting. For more information, see Doublewrite Buffer.

  Thanks to Facebook for the contribution. (Bug #32727919, Bug #103211, WL #14719)

• InnoDB: InnoDB now supports dynamic configuration of redo log capacity. The innodb_redo_log_capacity system variable can be set at runtime to increase or decrease the total amount of disk space occupied by redo log files.

  With this change, the number of redo log files and their default location has also changed. From MySQL 8.0.30, InnoDB maintains 32 redo log files in the #innodb_redo directory in the data directory. Previously, InnoDB created two redo log files in the data directory by default, and the number and size of redo log files were controlled by the innodb_log_files_in_group and innodb_log_file_size variables. These two variables are now deprecated.

  When the innodb_redo_log_capacity setting is defined, innodb_log_files_in_group and innodb_log_file_size settings are ignored; otherwise, those settings are used to compute the innodb_redo_log_capacity setting (innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity). If none of those variables are set, redo log capacity is set to the innodb_redo_log_capacity default value, which is 104857600 bytes (100MB).

  Several status variables are provided for monitoring the redo log and redo log capacity resize operations.

  As is generally required for any upgrade, this change requires a clean shutdown before upgrading.

  For more information about this feature, see Redo Log. (WL #12527)

• Added Ubuntu 22.04 support. (Bug #34123545)

• The order of the columns in the primary key definition for a few tables in the mysql schema has been changed, so that the columns containing the host name and user name are together in sequence at the start of the primary key. ACL queries on these tables are performed using only the host name and user name, and if those columns are not together in sequence, a full table scan must be performed to identify the relevant record. Placing the host name and user name together means that index lookup can be used, which improves performance for CREATE USER, DROP USER, and RENAME USER statements, and for ACL checks for multiple users with multiple privileges.

  The changed tables are mysql.db, mysql.tables_priv, mysql.columns_priv and mysql.procs_priv. When you upgrade to MySQL 8.0.30 or later, these tables are modified in the second step of the MySQL upgrade process. Use the --upgrade=FORCE option when performing logical upgrades using a backup or export utility such as mysqldump or mysqlpump, which ensures that the table structures are checked and rebuilt with the new column order. (Bug #33644645, Bug #33637244, WL #14965)

• The myisam_repair_threads system variable and myisamchk --parallel-recover option were removed. (Bug #31052408, WL #14938)

• A new mysqldump option --mysqld-long-query-time lets you set a custom value of the long_query_time system variable for mysqldump’s session. Use the new option to increase the elapsed time allowed for mysqldump’s queries before they are written to the slow query log file, in order to avoid unnecessary logging. Thanks to Facebook for the contribution. (Bug #96369, Bug #96369, Bug #30110717, WL #13447)

• Error log components can now be loaded implicitly at startup before the InnoDB storage engine is available. This new method of loading error log components loads and enables the components defined by the log_error_services variable.

  Previously, error log components had to be installed first using INSTALL COMPONENT and were only loaded after InnoDB was fully available, as the list of components to load was read from the mysql.components table, which is an InnoDB table.

  Implicit load of error log components has these advantages:

  • Log components are loaded early in the startup sequence, making logged information available sooner.

  • It helps avoid loss of buffered log information should a failure occur during startup.

  • Loading log components using INSTALL COMPONENT is not required, simplifying error log configuration.

  For more information about this feature, see Error Log Configuration.

  If you have previously installed loadable log components using INSTALL COMPONENT and you list those components in a log_error_services setting that is read at startup (from an option file, for example), your configuration should be updated to avoid startup warnings. For more information, see Error Log Configuration Methods. (WL #14793)

• MySQL Enterprise Audit’s audit log file can now be extended with optional data fields to show the query time, the number of bytes sent and received, the number of rows returned to the client, and the number of rows examined. This data is available in the slow query log for qualifying queries, and in the context of the audit log it similarly helps to detect outliers for activity analysis. It is delivered to the audit log through new component services that you set up as an audit log filtering function. The extended data fields can only be added when the audit log is in JSON format (audit_log_format=JSON), which is not the default setting. (WL #14921)

• MySQL Server’s AES_ENCRYPT() and AES_DECRYPT() functions now support the use of a key derivation function (KDF) to create a cryptographically strong secret key from information such as a password or a passphrase that you pass to the function. The derived key is used to encrypt and decrypt the data, and it remains in the MySQL Server instance and is not accessible to users. Using a KDF is highly recommended, as it provides better security than specifying your own premade key or deriving it by a simpler method when you use the function. The functions support HKDF (available from OpenSSL 1.1.0), for which you can specify an optional salt and context-specific information to include in the keying material, and PBKDF2 (available from OpenSSL 1.0.2), for which you can specify an optional salt and set the number of iterations used to produce the key. (WL #12669, WL #15188)

• A new system status variable Tls_library_version shows the runtime version of the OpenSSL library that is in use for the MySQL instance. The version of OpenSSL affects features such as support for TLSv1.3. (WL #13407)

• From MySQL 8.0.30, MySQL Enterprise Encryption’s functions are provided by a component, rather than being installed from the openssl_udf shared library. The new functions provided by the component use only the generally preferred RSA algorithm, not the DSA algorithm or the Diffie-Hellman key exchange method, and they follow current best practice on minimum key size. The component functions also add support for SHA3 for digests (provided that OpenSSL 1.1.1 is in use), and do not require digests for signatures, although they support them.

  If you upgrade to MySQL 8.0.30 from an earlier release where the functions are installed manually from the openssl_udf shared library file, the functions you created remain available and are supported. However, these legacy functions are deprecated from this release, and it is recommended that you install the component instead. The component functions are backward compatible, so RSA public and private keys, encrypted data, and signatures that were produced by the legacy functions can be used with the component functions. For the component functions to support decryption and verification for content produced by the legacy functions, you must set the new system variable enterprise_encryption.rsa_support_legacy_padding to ON (the default is OFF).

  The component functions generate public and private RSA keys in PKCS #8 format. They allow a minimum key size of 2048 bits, which is a suitable minimum RSA key length for current best practice. You can set a maximum key size up to 16384 bits using the system variable enterprise_encryption.maximum_rsa_key_size, which defaults to a maximum key size of 4096 bits. (WL #15024)

• Connections whose users have the CONNECTION_ADMIN privilege are not terminated when MySQL Server is set to offline mode, which is done by changing the value of the offline_mode system variable to ON. Previously, checking for connections that had the CONNECTION_ADMIN privilege could cause a race condition because it involved accessing other threads. Now, a flag for each thread caches whether or not the user for the thread has the CONNECTION_ADMIN privilege. The flag is updated if the user privilege changes. When offline mode is activated for the server, this flag is checked for each thread, rather than the security context of another thread. This change makes the operation threadsafe.

  In addition, when offline mode is activated, connections whose users have the SYSTEM_USER privilege are now only terminated if the user that runs the operation also has the SYSTEM_USER privilege. Users that only have the SYSTEM_VARIABLES_ADMIN privilege, and do not have the SYSTEM_USER privilege, can set the offline_mode system variable to ON to activate offline mode. However, when they run the operation, any sessions whose users have the SYSTEM_USER privilege remain connected, in addition to any sessions whose users have the CONNECTION_ADMIN privilege. This only applies to existing connections at the time of the operation; users with the SYSTEM_USER privilege but without the CONNECTION_ADMIN privilege cannot make new connections to a system in offline mode. (WL #14317)

Bugs Fixed

• InnoDB: A TRUNCATE TABLE operation failed to remove data dictionary entries for columns that were dropped using ALGORITHM=INSTANT.

  Thanks to Marcelo Altmann for the contribution. (Bug #34302445)

• InnoDB: An incorrect nullable column calculation on tables with instantly added columns caused data to be interpreted incorrectly. (Bug #34243694)

• InnoDB: After upgrading to MySQL 8.0.29, a failure occurred when attempting to access a table with an instantly added column. (Bug #34233264)

• InnoDB: Only the physical position of instantly added columns was logged, which was not sufficient for index recovery. The logical position of columns was also required. (Bug #34181432)

• InnoDB: The field_phy_pos debug variable in the InnoDB sources was not updated for child tables during a cascading update operation. (Bug #34181419)

• InnoDB: Some instances of the rec_get_instant_row_version_old() function in the InnoDB sources did not check for row versioning. (Bug #34173616)

• InnoDB: The read_2_bytes() function in the InnoDB sources, which reads bytes from the log buffer, returned a null pointer. (Bug #34173425)

• InnoDB: In a specific locking scenario, an implicit lock was not converted to an explicit lock as expected, triggering a lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP, block, heap_no, trx) debug assertion failure. (Bug #34123159)

• InnoDB: A check that determines if a table has instantly added columns was performed for each column, which affected the performance of ADD and DROP COLUMN operations on tables with numerous columns. The check is now performed once per table. (Bug #34112147)

• InnoDB: A workload that generated a large number of lock requests and numerous timeouts caused a long semaphore wait failure. To address this issue, optimizations were implemented to reduce the number of exclusive global lock system latches. (Bug #34097862)

• InnoDB: The m_flush_bit in the redo log block header, which was set for the first block of multiple blocks written in a single log write call, provided no benefit and has been removed. (Bug #34091444)

• InnoDB: Fixed clang-tidy and cppcheck warnings, which included the removal of unused code and unnecessary checks. (Bug #33957087)

• InnoDB: Recovery of a redo log file mini-transaction (mtr) caused a debug assertion failure on a MySQL Server instance with a small innodb_log_buffer_size setting.

  Thanks to Mengchu Shi for the contribution. (Bug #33945602)

• InnoDB: Compiling with the WITH_VALGRIND source configuration option produced Wunused-variable warnings. (Bug #33899862)

• InnoDB: Multiple issues with the lock-free hash table (ut_lock_free_hash_t) were addressed. (Bug #33830934)

• InnoDB: A query on a generated column with a secondary index caused a failure. The field number representing the position of the generated column was not valid. (Bug #33825077)

• InnoDB: Memory consumption was greater than expected when updating and inserting rows with multi-valued index columns. The memory allocated for multi-valued columns for each row update was held until the file handle was released. (Bug #33766482)

• InnoDB: The UT_LOCATION_HERE structure in the InnoDB sources was not used consistently. (Bug #33436161)

• InnoDB: A table object needed to retrieve an array of values from a multi-valued index column when computing the value of a generated column was unavailable. (Bug #32725063)

• InnoDB: A 4GB tablespace file size limit on Windows 32-bit systems has been removed. The limit was due to an incorrect calculation performed while extending the tablespace. (Bug #28934351)

• InnoDB: Hash and random generator functions in the InnoDB sources were improved. (Bug #16739204, Bug #23584861)

• InnoDB: A DROP TABLE operation on a table with a discarded tablespace caused an unnecessary assertion failure. (Bug #107207, Bug #34135187)

• InnoDB: A query on a table with a JSON column returned only a partial result set after adding a multi-valued index. (Bug #106621, Bug #33917625)

• InnoDB: Purging a record with multiple binary large object values raised an insertion failure due to a mini-transaction (mtr) conflict. (Bug #105592, Bug #33574272)

• InnoDB: Enabling the adaptive hash index (AHI) on a high-concurrency instance caused temporary AHI search latch contention while the hash index was being built.

  Thanks to Zhou Xinjing from CDB Team at Tencent for the patch. (Bug #100512, Bug #31750840)

• Packaging: The SASL LDAP clientside plugin was missing from the MySQL Community packages for Windows.

• Replication: When a table definition diverged between the source and the replica because the replica had an extra primary key, updates and deletes on the replica would fail if that table had an index that was present both on the source and the replica. Primary keys for an InnoDB table are automatically included in all indexes, and the replication applier needs values for all parts of the key to be included in an event in order to search the index. Previously, the applier checked that all the user-defined key parts were present, but the check did not cover hidden primary keys that were automatically included. The applier now validates that both user-defined and automatically included key parts are present in an event before using the index to search the data. (Bug #34122738)

• Replication: The write sets extracted by MySQL Replication from transactions when the transaction_write_set_extraction system variable is enabled (which is the default) are extracted from primary keys, unique keys, and foreign keys. They are used to detect dependencies and conflicts between transactions. Previously, write sets involving multi-column foreign keys were incorrectly identifying each column as a separate foreign key. The issue has now been fixed and foreign key write sets include all referenced key columns. (Bug #34095747, Bug #34144531)

• Replication: When row-based replication was in use, a replica could sometimes override the SQL mode value that was sent by the source, in an attempt to avoid issues with additional columns on the slave. In extreme cases this could lead to data divergence. The problem has been corrected so the replica now preserves the source’s SQL mode wherever possible. (Bug #33945038)

• Replication: MySQL’s semisynchronous replication did not respect the value of the net_read_timeout system variable and forced a read timeout of one millisecond. This could result in the function experiencing partial reads of acknowledgment messages and packets arriving out of order, while other connections in the MySQL system were functioning correctly. The value of the net_read_timeout system variable is now applied to connections for semisynchronous replication. (Bug #101056, Bug #31976209)

• Replication: When the --replicate-same-server-id option was used to make the replica not skip events that have its own server ID, if the log file was rotated, replication stopped with an error. The log rotation event now checks and applies the current value of the option. (Bug #89375, Bug #27492990)

• Group Replication: The COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE column in the Performance Schema table replication_group_member_stats could persistently show transactions related to view change events (View_change_log_event) that had already been applied. These events are queued in the Group Replication applier channel but applied in the Group Replication recovery channel, causing a race condition that could result in the counter decrement being lost. The increment of the count now takes place at a more suitable point, and the counter for COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE is also now set to zero when the applier is not busy. (Bug #33602354, Bug #33674059)

• Group Replication: A deadlock could occur in Group Replication when a member was interacting with the service infrastructure, such as a joining member checking for incompatible configuration and then leaving the group due to it. The issue has now been fixed. (Bug #32688091)

• Group Replication: The message logged when a member tries to rejoin a Group Replication topology when there is an old incarnation of the same server still present has been upgraded from an informational note to a warning message. (Bug #32651024)

• API: Applications that previously used the MySQL client library to perform an automatic reconnection to the server received the following mysql_query error after the server was upgraded:

  [4031] The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior. (Bug #105229, Bug #34007830)

• Pushing a condition down to a derived table was not handled correctly in all cases. (Bug #34311090)

• After pushing down a condition to a derived table having a set operation, while folding an always true boolean condition, the rewrite was not correct due to not setting abort_on_null to true for the cloned condition when making a copy during condition pushdown to a derived table with a set operation. (Bug #34298238)

• A missing error return when processing an invalid ORDER BY expression in a view definition led to an assert in debug builds. (Bug #34239456)

• MySQL Server would not compile with the latest version of Visual Studio 2022. (Bug #34231639)

• While attempting to clone a system variable during condition pushdown, the server sometimes could not determine the correct context of the cloned expression.

  To prevent this, we disallow condition pushdown to derived tables when they use system variables, or if the underlying expressions in the derived table contain system variables. (Bug #34205559)

• Added Enterprise Linux 9 (EL9) support. (Bug #34190004)

• On macOS 11, MySQL Server did not have the correct entitlement to generate a core dump in the event of an unexpected server halt. A build option WITH_DEVELOPER_ENTITLEMENTS has been added to allow a build to generate core dumps. (Bug #34163987)

• Improved error handling for '-DWITH_LIBEVENT=system' and '-DWITH_EDITLINE=system' on systems missing libevent-devel or libedit-devel. (Bug #34131334, Bug #34123545)

• The fix for Bug #33830493 in MySQL 8.0.29 addressed the situation where if a MySQL instance stopped unexpectedly or was restarted shortly after a SET PERSIST statement was used to record system variable settings, the configuration file mysqld-auto.cnf could be left empty, in which case the server restart could not proceed. The persisted system variables are now written to a backup file, which is only renamed to mysqld-auto.cnf after the success of the write has been verified, leaving the original mysqld-auto.cnf file still available. On a restart, if a backup file with valid contents is found, the server reads from that file. Otherwise the mysqld-auto.cnf file is used and the backup file is deleted. The file was not flushed to disk by this fix, so it was still possible for the issue to occur. This patch adds those operations. (Bug #34122866)

• Fixed the -DENABLE_GCOV CMake option. (Bug #34113243)

• The SENSITIVE_VARIABLES_OBSERVER privilege, introduced in MySQL 8.0.29, is now granted to users with the SYSTEM_VARIABLES_ADMIN privilege during upgrade. Previously, the privilege was not granted to any database user during upgrade. (Bug #34068378)

• A select from a view that used left joins did not return any results. (Bug #34060289)

• Under certain circumstances TRUNCATE performance_schema.accounts caused duplicated counts in global_status.

  This occurred if some hosts were not instrumented. For example, if performance_schema_hosts_size was set to a low value.

  Our thanks to Yuxiang Jiang and the Tencent team for the contribution. (Bug #34057013, Bug #106939)

• It was possible under certain conditions for EXPLAIN ANALYZE to attempt access of an iterator that did not exist. (Bug #34051681)

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

• Support was added for compiling the keyring_oci plugin with OpenSSL 3. (Bug #34043013)

• Corrected two issues found with EXPLAIN ANALYZE:

  • Timings displayed as actual time were not cumulative.

  • For a materialized table, the output now show the number of rows materialized, rather than the number of rows read from the materialized table.

  (Bug #34025798, Bug #34135465)

  References: See also: Bug #33834146, Bug #34678179.

• Events recorded in the Performance Schema tables for thread creation and deletion were retained until server shutdown, instead of being removed when the client connection ended. Thread creation and deletion now takes place after the Performance Schema instrumentation is created for the user session, so it is cleaned up when the session ends. (Bug #34019988)

• Upgraded the bundled zlib library to zlib 1.2.12. Also made zlib 1.2.12 the minimum zlib version supported, and removed WITH_ZLIB from the WITH_SYSTEM_LIBS CMake option. (Bug #34015600)

• The CONNECTION_ID() function, since it returns a session ID which remains constant for the lifetime of the session, was treated as a constant function. This caused issues when CONNECTION_ID() was used inside a trigger attached to a table which might be reused by other sessions. We fix this by making the function const for execution, and returning the actual session ID when the function is evaluated. (Bug #34009876)

• Executed codespell on the source code and fixed the reported spelling errors in the code comments. (Bug #34006439)

• The MySQL Enterprise Encryption openssl_udf function library plugin was reimplemented to use OpenSSL 3 APIs. (Bug #33992115)

• FEDERATED storage engine code was revised to address NULL pointer and variable access issues. (Bug #33962357)

• Histograms in MySQL returned a selectivity estimate of 0 for values that outside buckets. This meant that values might be missing from the histogram because they were missed during sampling, or because the histogram had grown stale. To prevent this, we introduce a constant lower bound of 0.001 on the selectivity estimates produced by histograms. This choice of lower bound corresponds to the selectivity of a value or range that we are likely to miss during sampling.

  Using a constant lower bound rather than a statistical estimate for the selectivity of a missing value has the advantage of simplicity and predictability, and provides some protection against underestimating the selectivity due to stale histograms and within-bucket heuristics.

  For more information about histograms in MySQL, see Optimizer Statistics. (Bug #33935417)

• For certain queries using a common table expression (CTE), EXPLAIN ANALYZE did not provide any profiling data for the CTE even when the CTE was known to be executed. This happened when the following conditions were met:

  • The CTE was referenced more than once in the query plan.

  • The first reference to the CTE (in the order of the output of EXPLAIN FORMAT=TREE) was never executed.

  • At least one of the subsequent references was executed at least once.

  The problem was that the CTE plan was always printed when encountering the first reference to the CTE; if that reference was never executed, the CTE was not materialized there; and thus there was no profiling data to print.

  The fix for this issue ensures that we print the CTE plan when it is first executed, that is, the point at which it is materialized. The output then includes profiling data. If the CTE is never executed, we print the plan at the last reference, when there is no profiling data. (Bug #33905399)

• The output from the command mysqld --verbose --help previously showed plugin load options as ON even when they were off by default, or turned off using an option. The output now shows the current value for the plugin. (Bug #33870892)

• The Server now bundles curl (7.83.1) and only uses it when alternative SSL systems are used, such as openssl11 on EL7. (Bug #33859507, Bug #34154806)

• Debug MySQL binaries can now be built using -0g and -fno-inline. (Bug #33855533)

• The FIREWALL_EXEMPT privilege, introduced in MySQL 8.0.27, is now granted to users with the SYSTEM_USER during upgrade. Previously, the privilege was not granted to any database user during upgrade. (Bug #33854409)

• A correlated subquery did not use a functional index as expected. This occurred when an outer column reference used inside the subquery was not considered as constant for subquery execution, which allowed consideration of the functional index to be skipped.

  We fix this problem by making sure to consider the outer column reference as constant while executing the subquery. (Bug #33851055)

• Added alternate OpenSSL system package support by passing in openssl11 on EL7 or openssl3 on EL8 to the WITH_SSL Cmake option. Authentication plugins, such as LDAP and Kerberos, are disabled as they do not support these alternative versions of OpenSSL. (Bug #33835934)

• Prepared statements with subqueries that accessed no tables, but the subquery evaluation raised an error, triggered an assert failure in debug builds. (Bug #33773799)

• Some stored functions were not executed correctly following the first invocation. (Bug #33754993)

• When performing a query using a recursive common table expression (CTE) with a removal of a query expression after constant predicate elimination, it is expected that when the reference count of table objects for the CTE temporary table reaches zero, it should be possible once again to recreate the table, but in certain cases one of the table references was not properly recorded as attached to the CTE. (Bug #33725503)

  References: See also: Bug #32962511.

• Added a missing error return to the parser. (Bug #33725502)

• A number of issues with pushdown of conditions making use of outer references, relating to work done in MySQL 8.0.22 to implement condition pushdown for materialized derived tables, have been identified and resolved. (Bug #33725403, Bug #33725500, Bug #33725508, Bug #33725534, Bug #33725545)

• The plan generated for a SELECT using a common table expression involves table materialization and an index scan on the materialized table. Because the temptable engine does not yet support all index scan methods, such queries might not always execute correctly.

  With other MySQL engines, the materialization access path has special handling when the access path is not considered basic; for temptable, an index scan was not considered basic, which led to undefined behavior.

  We fix this issue by considering the index scan access path basic, and thus avoiding use of any index scan access methods on temptable tables. (Bug #33700735)

• The Data_free column in the INFORMATION_SCHEMA.FILES table was not updated after adding a new data file to the InnoDB system tablespace. (Bug #33508534)

• If a plugin attempted to register a system variable with a name that duplicated that of an existing system variable, the existing static system variable might be overwritten, and uninstalling the plugin might leave pointers to the freed memory. The issues have now been fixed. (Bug #33451101)

• SHOW TABLES and SELECT * FROM INFORMATION_SCHEMA.TABLES did not return any results from the Performance Schema if the user had access privileges on individual Performance Schema tables, only. (Bug #33283709)

• Calling a function relating to the data_masking plugin without first installing the plugin led to an unplanned server shutdown. Functions relating to this plugin are initialized by calling init functions which in turn access the UDF metadata service, but this is valid only when the data masking plugin is installed. We fix this problem by adding a check to verify that the plugin is installed before initializing such functions, and to return an appropriate error message if the plugin providing them is not installed. (Bug #33234046)

• Under certain conditions, the server did not handle the expiration of max_execution_time or the execution of a KILL statement correctly. (Bug #33218625)

• mysqlslap, which uses multiple threads to connect to the server, could not run with a user account that used FIDO authentication. The issue has been fixed by an update to the FIDO library allowing the authentication to be performed on multiple threads. (Bug #33067183)

• If an incorrect value was set for the binlog_checksum system variable during a session, a COM_BINLOG_DUMP command made in the same session to request a binary log stream from a source failed. The server now validates the specified checksum value before starting the checksum algorithm setup process. (Bug #32442749)

• For slow query logging, the Slow_queries status variable was not implemented unless the slow query log was enabled, contrary to the documentation. (Bug #28268680, Bug #91496)

• New parameters (examined_row_count, affected_row_count, and return_row_count) were added to the audit plugin. Our thanks to J D for the contribution. (Bug #110628, Bug #35267239)

• A prepared statement could accept an empty string as a valid float value, which represents a regression from 8.0.27 behavior. This fix explicitly checks that the length of an interpreted string is non-empty and fully interpreted as a (float) number. In addition, new verification now ensures that:

  • All numeric values are supported with empty strings and strings that are all spaces.

  • Regular numeric values are supported, as well as numeric values with leading and trailing spaces.

  (Bug #107399, Bug #34213338)

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

• Upgrading to MySQL 8.0.29 led to issues with existing spatial indexes (see Creating Spatial Indexes). The root cause of the problem was a change in how geographic area computations were performed by the included Boost library, which was upgraded to version 1.77.0 in MySQL 8.0.29. We fix this by ensuring that we accommodate the new method whenever such computations are performed. (Bug #107320, Bug #34184111)

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

• When pushing a condition down to derived table for prepared statements, we clone a condition which also includes parameters when a derived table contains unions. When a statement needed to be reprepared during execution—for example, when the signedness of the value specified did not match that of the actual datatype—the parameter was not cloned correctly resulting in errors. This occurred because the value specified for the parameter was used to print the string for reparsing, instead of a literal ? placeholder character.

  Now in such cases we set a flag QT_NO_DATA_EXPANSION for printing parameters for reparsing which, when enabled, causes the ? placeholder to be printed, rather than the actual value. (Bug #107230, Bug #34148712)

• On MacOS, improved Boost library detection logic for Homebrew as a potentially incompatible system's Boost version could get used even with -DWITH_BOOST set. (Bug #107151, Bug #34121866)

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

• On RHEL 7.x, fetching the CPU cache line size returned 0 on s390x RHEL 7.x which caused rpl_commit_order_queue and integrals_lockfree_queue to fail.

  Our thanks to Namrata Bhave for the contribution. (Bug #107081, Bug #34095278)

• When the mysql client was unable to reconnect to the server following an unexpected server halt, the process of building the completion hash allocated memory that was not freed. The reconnection operation now does not build the completion hash if the client fails to reconnect, and the memory concerned is freed if the client is disconnected. (Bug #106864, Bug #34019571)

• Added a cycle timer for the s390x architecture.

  Our thanks to Namrata Bhave for the contribution. (Bug #106824, Bug #33997819)

• In certain cases, incorrect results could result from execution of a semijoin with materialization, when the WHERE clause of the subquery contained an equality. In some cases, such as when one side of such an equality was an IN or NOT IN subquery, the equality was neither pushed down to the materialized subquery, nor evaluated as part of the semijoin. This also caused issues with some inner hash joins. (Bug #106710, Bug #106718, Bug #33952115, Bug #33957233)

  References: See also: Bug #84705, Bug #25466100.

• Comparator functions for queries like (<date column> <non-date column>) IN ((val1, val2), (val3, val4), …) could return the wrong results. (Bug #106567, Bug #33897969)

• Fixed an assert definition in SetOsLimitMaxOpenFiles; our thanks to hongyuan li for the contribution. (Bug #106555, Bug #33893197)

• Previously, it was assumed that, when the same non-nullable expression was used as both the first and second arguments to LIKE, the result was always true, and so could be optimized away. This assumption turns out not to be valid, due to the fact that LIKE treats the backslash (\) as an escape character, even when ESCAPE is not specified. This led to different results when the condition was used in the SELECT list as opposed to the WHERE clause. To fix the problem, we no longer perform this optimization with LIKE, with or without an ESCAPE clause. (Bug #106444, Bug #33852756)

• In some cases, when arguments other than global transaction IDs (such as column values) were passed to GTID_SUBSET(), the function returned values other than the expected NULL. (Bug #106298, Bug #33793942)

• A problem with evaluation of general quantified comparison predicates occurred when the left-hand side of the predicate was NULL. In such cases, the value of the subquery evaluation from the last current row is saved, so that it does not need re-evaluation, but the cached value (result_for_null_param) was not cleared between executions, so that the next execution could re-use the result from the previous execution. One consequence of this was that, when a subquery execution first caused zero rows to match from the subquery—which for an ALL predicate should return TRUE—a subsequent execution causing at least one row to match also returned TRUE, even though FALSE was expected.

  To solve this issue, we now make sure to clear result_for_null_param while cleaning up the subquery predicate following execution. (Bug #106164, Bug #33755139)

• Test cases executed with the --async-client option and shutdown commands caused mysqltest to halt unexpectedly. (Bug #105797, Bug #33643149)

• MySQL supports the use of equiheight histograms to improve selectivity estimates. Each bucket in an equiheight histogram for a column should contain roughly the same number of values (rows); keeping the buckets small helps minimize any error.

  When constructing an equiheight histogram, too many values were sometime placed in the same bucket, which could result in substantial errors in selectivity estimation. We fix this by introducing a new equiheight construction algorithm that guarantees low error, and adapts to the distribution of the data to make efficient use of its buckets. In addition, a new estimator for the number of distinct values in histogram buckets provides improved worst-case error guarantees.

  See The INFORMATION_SCHEMA COLUMN_STATISTICS Table, and Optimizer Statistics, for more information. (Bug #104789, Bug #33302021)

• Deprecation warnings returned to client programs were sent to stdout rather than stderr, which in the case of mysqldump could mean that the dump file no longer worked because the warnings were included in it. The issue has now been fixed and the warnings are sent to stderr. (Bug #104769, Bug #33733529)

• Extended support for chained SSL certificates. (Bug #54158, Bug #27491518)