MySQL 8.0.31 发布

• The audit_log_rotate() function simplifies audit log file rotation. Previously, audit log file rotation required renaming the file manually and setting audit_log_flush = ON to close the file and open a new log file with the original name. The audit_log_rotate() function renames the current file and creates a new one. Manually renaming the audit log file is no longer necessary.

  The audit_log_flush variable is deprecated as of MySQL 8.0.31; expect support for it to be removed in a future version of MySQL.

• Renamed internal Performance Schema functions from _utf8* to _utf8mb4* as they’ve used utf8mb4 since v8.0.11. (Bug #34351301)

  References: See also: Bug #27407745.

• Fixed linker flags for clang/ubsan to workaround a LLVM/Clang related integer issue as described in LLVM bug #16404. (Bug #34311325)
• Updated helper scripts to use ‘utf8mb3’ for charsets/collations instead of ‘utf8’ for future compatibility.

  Renamed functions and data structures used to handle utf8mb3 character sets and collations to help clarify their utf8mb3 usage. (Bug #34263480, Bug #34025412)

• Cleaned up CMake code, and simplified INFO_BIN and INFO_SRC. (Bug #34139084)

• New component services now enable server components and plugins to query within the local server. The new MySQL command services are similar to C API functions in libmysql, except they do not expose protocol internals to the client. For information about these services, see the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html. Search for:
  • s_mysql_mysql_command_factory
  • s_mysql_mysql_command_options
  • s_mysql_mysql_command_query
  • s_mysql_mysql_command_query_result
  • s_mysql_mysql_command_field_info
  • s_mysql_mysql_command_error_info

• MySQL Keyring previously implemented Oracle Cloud Infrastructure Vault Keyring keystore capabilities using a server plugin, but now is transitioning to use the MySQL component infrastructure. The new keyring component has similarities to the existing keyring_oci plugin, but is configured differently and can access keys that were created using the keyring_oci plugin when a similar set of configuration options are used to initialize the new component. For configuration details, see Using the Oracle Cloud Infrastructure Vault Keyring Component.

  component_keyring_oci is not loaded using the --early-plugin-load server option during startup or configured during startup or at runtime using system variables:

  • During startup, the server determines which keyring component to load using a manifest file, and the loaded component consults its own configuration file when it initializes. See Keyring Component Installation.
  • At runtime, the ALTER INSTANCE RELOAD KEYRING statement enables reconfiguring an installed keyring component after changes to its configuration file. See ALTER INSTANCE Statement.

  If a keyring component is installed, the Performance Schema keyring_component_status table provides status information about it. See The keyring_component_status Table.

• Important Change: The MySQL Optimizer’s internal management of set operations has been improved, with the following effects:
  • Bodies of parenthesized query expressions can now be nested to in combination with UNION. For example, the query shown here, previously rejected with error ER_NOT_SUPPORTED_YET, is now allowed:

    ( 
      (SELECT a, b, c FROM t ORDER BY a LIMIT 3) ORDER BY b LIMIT 2
    ) ORDER BY c LIMIT 1;

  • When collapsing parenthesized expression bodies, MySQL now follows the semantics specified in the SQL standard, so that a higher outer limit cannot override an inner lower one. This means that the expression (SELECT ... LIMIT 3) LIMIT 5 can return at most three rows.
  • UNION DISTINCT and UNION ALL can now be nested in arbitrary combinations.

  The maximum nesting level supported in all the cases just listed is 63; this follows any simplifications or merging performed by the parser. See Parenthesized Query Expressions, for more information. (Bug #103954)

• An outer reference in a derived table pointing to a table in a JOIN operation that was not available for the subquery to be reported (incorrectly) as valid, whereas such a reference without a derived table was correctly reported as invalid.

  In a related issue, an outer reference to a derived table placed inside the VALUES clause of an INSERT statement was not recognized as valid, whereas a similar reference without a derived table was so recognized. Now such a reference to a derived table is accepted within VALUES.

  In both cases, we fix the problem by making sure that the correct name resolution context is used for the outer reference. (Bug #32678303, Bug #34131822)

• The Performance and sys schemas now expose metrics for the global and session memory limits introduced in MySQL 8.0.28.

  The following columns were added to tables of the Performance Schema:

  • SETUP_INSTRUMENTS:
    • FLAGS
  • THREADS:
    • CONTROLLED_MEMORY
    • MAX_CONTROLLED_MEMORY
    • TOTAL_MEMORY
    • MAX_TOTAL_MEMORY
  • EVENTS_STATEMENTS_CURRENT, EVENTS_STATEMENTS_HISTORY, and EVENTS_STATEMENTS_HISTORY_LONG:
    • MAX_CONTROLLED_MEMORY
    • MAX_TOTAL_MEMORY
  • Statement Summary Tables:
    • MAX_CONTROLLED_MEMORY
    • MAX_TOTAL_MEMORY
  • Performance Schema Connection Tables:
    • MAX_SESSION_CONTROLLED_MEMORY
    • MAX_SESSION_TOTAL_MEMORY
  • PREPARED_STATEMENTS_INSTANCES:
    • MAX_CONTROLLED_MEMORY
    • MAX_TOTAL_MEMORY

  The following columns were added to the sys schema STATEMENT_ANALYSIS and X$STATEMENT_ANALYSIS views:

  • MAX_CONTROLLED_MEMORY
  • MAX_TOTAL_MEMORY

  The PROPERTIES column of the SETUP_INSTRUMENTS table was updated with the controlled_by_default flag.

  You can also add, or remove, non-global memory instruments to the set of controlled-memory instruments by setting the value of the FLAGS column of SETUP_INSTRUMENTS.For example:

        SQL> UPDATE PERFORMANCE_SCHEMA.SETUP_INTRUMENTS SET FLAGS="controlled" 
             WHERE NAME='memory/sql/NET::buff';

• Important Change: Previously, MySQL supported the use of “full” as the name of a table, column, view, stored procedure, or stored function, as well as for the alias of a table, view, or column. Beginning with this release, using “full” (regardless of letter case) in this fashion as an unquoted identifier is now deprecated, and raises a warning. This is to align more closely with the SQL standard, in which FULL is reserved as a keyword.

  For example, the following CREATE TABLE and DROP TABLE statements now raise warnings, as shown here:

  mysql> CREATE TABLE full (c1 INT, c2 INT);
  Query OK, 0 rows affected, 1 warning (0.03 sec)
  
  mysql> SHOW WARNINGS\G
  *************************** 1. row ***************************
    Level: Warning
     Code: 4119
  Message: Using FULL as unquoted identifier is deprecated, please use quotes or
  rename the identifier. 
  1 row in set (0.00 sec)
  
  mysql> DROP TABLE full;
  Query OK, 0 rows affected, 1 warning (0.01 sec)
  
  mysql> SHOW WARNINGS\G
  *************************** 1. row ***************************
    Level: Warning
     Code: 4119
  Message: Using FULL as unquoted identifier is deprecated, please use quotes or
  rename the identifier. 
  1 row in set (0.00 sec)

  To execute the statements without the warnings, encase the table name in each of them with backtick characters (`), like this:

  mysql> CREATE TABLE `full` (c1 INT, c2 INT);
  Query OK, 0 rows affected (0.03 sec)
  
  mysql> DROP TABLE `full`;
  Query OK, 0 rows affected (0.02 sec)

  The use of FULL in the right hand side of a value assignment is not affected by this change. For example, the SET statement shown here remains valid:

  mysql> SHOW VARIABLES LIKE '%metadata%';
  +---------------------------------------+---------+
  | Variable_name                         | Value   |
  +---------------------------------------+---------+
  | binlog_row_metadata                   | MINIMAL |
  | innodb_stats_on_metadata              | OFF     |
  | performance_schema_max_metadata_locks | -1      |
  | resultset_metadata                    | FULL    |
  +---------------------------------------+---------+
  4 rows in set (0.00 sec)
  
  mysql> SET @@global.binlog_row_metadata=FULL;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> SHOW VARIABLES LIKE '%metadata%';
  +---------------------------------------+-------+
  | Variable_name                         | Value |
  +---------------------------------------+-------+
  | binlog_row_metadata                   | FULL  |
  | innodb_stats_on_metadata              | OFF   |
  | performance_schema_max_metadata_locks | -1    |
  | resultset_metadata                    | FULL  |
  +---------------------------------------+-------+
  4 rows in set (0.00 sec)

  For more information, see Keywords and Reserved Words.

• In this release MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators.

  query_a INTERSECT query_b includes only rows appearing in both result sets.

  query_a EXCEPT query_b returns any rows from the result set of query_a which are not in the result of query_b.

  INTERSECT and EXCEPT both support DISTINCT and ALL, with DISTINCT the default in both cases. (This is the same as for UNION).

  INTERSECT groups before EXCEPT or UNION, so TABLE r EXCEPT TABLE s INTERSECT TABLE t is evaluated as TABLE r EXCEPT (TABLE s INTERSECT TABLE t).

  See INTERSECT Clause, and EXCEPT Clause, for additional information and examples.

• Important Change; Replication: The Rewriter plugin rewrites SQL queries according to a table of rewriter rules. Previously, all queries were subject to rewrites, including those from any user regardless of privileges, as well as internal system queries like those performed by the replication applier thread, by bootstrapping the MySQL Server, and by storage engines. This MySQL release provides a mechanism for allowing the plugin to skip rewrites for certain queries, such as those executed by system threads, and those from designated users.

  We do this by implementing a new privilege SKIP_QUERY_REWRITE, which indicates a user whose queries are not rewritten. A new system variable rewriter_enabled_for_threads_without_privilege_checks controls whether rewrites are skipped for threads executing with privilege checks disabled. Such threads include upgrade threads, initialisation threads, and replication threads which have PRIVILEGE_CHECKS_USER as NULL. To preserve backward compatibility, the default setting for this variable is variable is ON, although the recommended setting is OFF.

  Both SKIP_QUERY_REWRITE and rewriter_enabled_for_threads_without_privilege_checks are available only if the Rewriter plugin is installed.

  For more information, see The Rewriter Query Rewrite Plugin.

• Important Change: For platforms on which OpenSSL libraries are bundled, the linked OpenSSL library for MySQL Server has been updated to version 1.1.1q. Issues fixed in OpenSSL version 1.1.1q are described at https://www.openssl.org/news/cl111.txt and https://www.openssl.org/news/vulnerabilities.html. (Bug #34414695)
• InnoDB: Two new status variables are provided for monitoring online buffer pool resizing operations. The Innodb_buffer_pool_resize_status_code status variable reports a status code indicating the stage of an online buffer pool resizing operation. The Innodb_buffer_pool_resize_status_progress status variable reports a percentage value indicating the progress of each stage.

  For more information, see Configuring InnoDB Buffer Pool Size.

• InnoDB: InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded. Previously, this action was performed by a single thread.
• Replication: When replication filtering is in use, a replica no longer raises replication errors related to privilege checks or require_row_format validation for events which are filtered out. Previously, all privileges were checked in the applier, with some being checked before applying filters and others not until after; with this release, privilege checks are now deferred until after all replication filters have been applied. In addition, prior to this release, checks for require_row_format equal to 1 took place on both the receiver and the applier; now the applier alone performs this check, before any filters are evaluated.

  This makes it possible to filter out any transactions that fail validation, using existing replication filters, and for the replica to accept only that part of the database to which a given user has been granted access, as long as updates to this part of the database are replicated only in row-based format. Another instance in which this might be desirable is when migrating to MySQL Database Service from on-premise or cloud services which use administration tables to which the inbound replication user does not have access.

  Privilege checks protect against updating critical tables on the replica. Checking require_row_format protects against replicating unsafe operations; this is used in conjunction with privilege checks, because the guaranteed absence of unsafe operations removes the need for some session context initialization and cleanup actions, which in turn removes the need to grant privileges for those operations.

  Other behavior changes associated with this work are noted in the following list:

  • The receiver thread no longer errors out when an event that violates require row format validation is received; the applier thread no longer errors out when an event that violates require row format validation is handled and subsequently filtered out due to replication filters.
  • The applier thread no longer errors out for Query log events that are filtered out due to lack of privileges for setting any of the system variables listed here:
    • pseudo_thread_id
    • sql_require_primary_key
    • default_table_encryption

    This is because setting the variable and checking whether the applier is authorized to set it are now performed only after filtering takes place. If an event is filtered out, the applier does not try to set the variable, and thus never fails a check whether it has the necessary privileges to do so.

  • The applier thread no longer errors out on privilege checks when an Execute_load_query event is handled and ends up being filtered out due to replication filters. This is also the case when an Append_block or Begin_load_query event is applied, and a subsequent Execute_load_query event is handled and then filtered out.
  • The applier thread no longer errors out on privilege checks or require_row_format checks for Delete_file events. These events are not filterable, because they do not contain the table name or database name, so we assume they are filtered out. We assume this to be safe because Delete_file events do not alter database state.
  • The applier thread no longer creates or deletes files in the filesystem during the handling of Append_block or Delete_file events, when there is a user account configured by PRIVILEGE_CHECKS_USER and it does not have the FILE privilege, nor if require_row_format is enabled.
  • For a replication event altered by --replicate-rewrite-db, any privilege checks apply to the rewritten database.

  See How Servers Evaluate Replication Filtering Rules, and Replica Errors During Replication, for more information.

• The new Resource_group_supported status variable is added to indicate whether a server supports the resource group feature. (Bug #34264356)
• Binary packages that include curl rather than linking to the system curl library have been upgraded to use curl 7.85.0. (Bug #34138733, Bug #34614578)
• The new thread_pool_transaction_delay system variable permits specifying a delay period, in milliseconds, before worker threads start executing a new transaction. The maximum value is 300000 (5 minutes).

  A transaction delay can be used in cases where parallel transactions affect the performance of other operations due to resource contention. For example, if parallel transactions affect index creation or an online buffer pool resizing operation, you can configure a transaction delay to reduce resource contention while those operations are running.

• The new thread_pool_query_threads_per_group system variable lets you increase the number of query threads in a thread group, from the default of a single query thread. When you are using the high concurrency thread pool algorithm (thread_pool_algorithm=1), consider increasing the value if you experience slower response times due to long-running transactions. It cannot exceed the value of thread_pool_max_transactions_limit.
• Thread pool plugin features previously only available with MySQL Database Service, are now available in MySQL Enterprise Edition.
  • The thread_pool_max_transactions_limit variable defines the maximum number of transactions permitted by the thread pool plugin. Defining a transaction limit binds a thread to a transaction until it commits, which helps stabilize throughput during high concurrency.
  • The thread_pool_dedicated_listeners variable dedicates a listener thread in each thread group to listen for incoming statements from connections assigned to the group. Enabling this variable is necessary when a transaction limit is defined by thread_pool_max_transactions_limit. It ensures that each thread group has an available listener thread.
  • The TP_CONNECTION_ADMIN privilege enables a user to access the server with a privileged connection. When the limit defined by thread_pool_max_transactions_limit has been reached, new connections cannot access the server. A privileged connection ignores the limit defined by thread_pool_max_transactions_limit and permits connecting to the server to increase the transaction limit, remove the limit, or kill running transactions.
• The new field WRITE_CONSENSUS_SINGLE_LEADER_CAPABLE in the Performance Schema table replication_group_communication_information shows whether the replication group supports the use of a single consensus leader, even if group_replication_paxos_single_leader is currently set to OFF on the queried member. The field is set to 1 if the group was started with group_replication_paxos_single_leader set to ON, and its communication protocol version is MySQL 8.0.27 or above.
• Activating MySQL Server’s offline mode, which is done by changing the value of the offline_mode system variable to ON, now requires the CONNECTION_ADMIN privilege in addition to the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege, which covers both these privileges). Sessions whose users have the CONNECTION_ADMIN privilege remain connected when offline mode is activated, and users with that privilege can make new connections to an offline server instance. The new requirement means that administrators who can activate offline mode cannot inadvertently lock themselves out by doing so.
• Added the read-only build_id system variable. On Linux systems, a 160-bit SHA1 signature is generated at compile time; this value, converted to a hexadecimal string, provides a unique identifier for the build. This value is written in the server log at startup.

  This variable is not supported on platforms other than Linux.

• Incompatible Change: The service pfs_plugin_table, deprecated since MySQL 8.0.14, is removed in this release.

  Plugins or components using this service must be updated to use pfs_plugin_table_v1 and pfs_plugin_column_* instead. (Bug #34361827)

• Important Change; Replication: Query log events filtered by the default database whenever --replicate-do-db or --replicate-ignore-db was used included XA START, XA END, XA COMMIT, and XA ROLLBACK (but not XA PREPARE or XA COMMIT ONE_PHASE), regardless of the binary log format.

  This can lead to one of the issues listed here:

  • When XA START or XA END was filtered out, the sequence of XA statements within the first part of the transaction were rendered invalid, which made the replica stop with an error.
  • When XA START and XA END were preserved while XA COMMIT or XA ROLLBACK was filtered out, then a transaction could remain in the prepared state indefinitely on the replica.

  To prevent either of these problems from occurring, we no longer filter XA START, XA END, XA COMMIT, or XA ROLLBACK statements by the default database with --replicate-do-db or --replica-ignore-db. (Bug #106201, Bug #33764808)

• InnoDB: After upgrading to a release that supports row versions for columns added or dropped using ALGORITHM-INSTANT, a failure occurred during an instant ADD COLUMN operation on a table with a nullable column and an instantly added column. (Bug #34488482)
• InnoDB: Adding a virtual column and dropping a column in the same ALTER TABLE statement raised an invalid debug assertion failure. (Bug #34467592)
• InnoDB: The physical position of columns were not set correctly after dropping a column and renaming an existing column to the name of the dropped column. (Bug #34463089)
• InnoDB: A Valgrind error detected in mtr_t::start() was corrected. (Bug #34327575)
• InnoDB: A DDL operation on a corrupted partitioned table raised an assertion failure. (Bug #34293555)
• InnoDB: An index block latch order violation during histogram sampling blocked concurrent inserts and could cause a deadlock failure. (Bug #34282448, Bug #34174927, Bug #107299)
• InnoDB: An ALTER TABLE operation executed while a data load operation was in progress raised an assertion failure. (Bug #34267618)
• InnoDB: InnoDB memory leaks during MySQL server initialization, identified by Address Sanitizer (ASAN) builds, were fixed. (Bug #34156050)
• InnoDB: During recovery, a tablespace object associated with encrypted undo tablespace pages that were fetched from disk did not contain the encryption keys required to decrypt the pages, resulting in a failure. (Bug #34148143)
• InnoDB: In debug builds, a descending b-tree scan raised a debug assertion failure. (Bug #34144951)
• InnoDB: The innodb_redo_log_consumer_advance() function failed to handle an invalid argument. (Bug #34052884)
• InnoDB: A column added using ALGORITHM=INSTANT was visible in read views created before the DDL operation that added the column. (Bug #33937504)
• InnoDB: A failure occurred while upgrading a MySQL instance with a MySQL 5.6 data directory containing user-created table with a particular table ID. Assignment of that table ID resulted in assignment of conflicting data dictionary table IDs while upgrading from MySQL 5.7 to MySQL 8.0.

  Thanks to Rahul Malik for the contribution. (Bug #33919635)

• InnoDB: A buffer block containing intrinsic temporary table page was relocated during page traversal, causing an assertion failure. (Bug #33715694)
• InnoDB: Dropping a table with a discarded tablespace caused an assertion failure. (Bug #33232978)
• InnoDB: Page I/O reads not completed before shutdown reached the flush phase caused an assertion failure in cases where pages had change buffer merges to be completed after the I/O read. (Bug #33192496)
• InnoDB: An index latch order violation in dict_table_x_lock_indexes() caused an assertion failure. (Bug #32912868)
• InnoDB: A TRUNCATE TABLE operation failed to free an acquired mutex in specific cases. (Bug #107858, Bug #34380370)
• InnoDB: In debug builds, importing a tablespace without a .cfg file for table with an instantly added or dropped column raised an assertion failure. (Bug #107517, Bug #34307874)
• InnoDB: A potential memory leak in the trx_undo_prev_version_build() function was corrected.

  Thanks to Alex Xing for the contribution. (Bug #106952, Bug #34051207)

• InnoDB: A debug assertion failure occurred while redoing a space deletion during recovery. (Bug #103482, Bug #32819101)
• InnoDB: An InnoDB startup message that specified the number of object pools was removed to avoid confusion with the number of buffer pool instances. (Bug #80248, Bug #22653060)
• Partitioning: A partitioned table was not repartitioned as expected when an ALTER TABLE statement acted to change the character set of any of its columns. The root cause of this was that, when determining whether a change in character set could be done as an in-place alter operation, we did not consider whether any columns affected by the character set change were part of the table’s partitioning expression. Since such a change might lead to a repartitioning of the table, it could not be done in place. Now when a change in character set is planned, we also check whether this affects any columns that are part of a partitioning expression; if not, then we can perform the operation in place. (Bug #106884, Bug #34031693)
• Replication: The START REPLICA and STOP REPLICA statements were permitted for the Group Replication applier channel (group_replication_applier), but stopping and starting the channel manually can leave ONLINE members of the replication group incapable of committing remote transactions. These statements now cannot be used for the channel when Group Replication is running. (Bug #34231291)
• Replication: Corrected an internal error in replication information handling. (Bug #34214416)
• Replication: In an InnoDB ClusterSet, in the event of a commit order deadlock and subsequent retry, Group Replication could roll back a transaction during certification with an error stating that the GTID for the transaction had already been used. The error is now only returned if the GTID has been used on the server instance as well as in the group. (Bug #34157846)
• Replication: When a single event caused multiple updates to the same row of a table on a replica, the change could not be applied if the replica had a primary key on the table that did not exist in the source. The hash-based row lookup algorithm now operates in this situation even if a primary key is present in the replica’s table. (Bug #34114296)
• Replication: When a single statement generated multiple write events for different tables on a replica, extra auto-increment columns in the replica’s tables were only cleaned up for the last event generated. They were not cleaned up at all if the last event did not involve a table with an extra auto-increment column. The cleanup process now always takes place for multiple write events. (Bug #34095290)
• Replication: A misleading error was returned to the client if the group_replication_force_members system variable was set, and the wait for the view change timed out. The error, and associated errors, have been updated so they do not suggest that the values given for the system variable were incorrect. (Bug #34091087)
• Replication: The FILTER_RULE column in the Performance Schema table replication_applier_filters showed incorrect data for filters whose rule did not exist. The table now displays the empty string in this situation. (Bug #33885484)
• Replication: It was possible in some rare instances of flushing or synchronization errors for the server to attempt an update of the endpoint of the binary log using invalid values for both the file and position within the file, leading to undefined behavior.

  To solve this issue, we now call the internal function update_binlog_end_pos(binlog_file, pos) only when both the file and position parameters are known to be valid. (Bug #33818238)

• Replication: Operations using the SID of a invalid GTID log event sometimes led to undefined behavior.

  We solve this issue by ensuring that the SID is always initialized, even when it belongs to an invalid GTID log event. (Bug #33784022)

• Replication: Calculation of the maximum length of status variables (MAX_SIZE_LOG_EVENT_STATUS) was not performed correctly.

  Issues corrected in this fix are listed here:

  • sql_require_primary_key and default_table_encryption require 2 bytes (one for type ID, one for the variable)
  • The maximum length of time_zone was calculated as 255 instead of MAX_TIME_ZONE_NAME_LENGTH
  • An additional byte (each) is required to save the length of the user variable, the number of database IDs stored in binlog_accessed_db_names, and the type of microseconds

  In addition, master_data_written, which was not actually used, has been removed. (Bug #33713071)

• Replication: On 32-bit platforms, setting binlog_expire_logs_seconds to 2147483648 (231) or more had the effect of causing all binary logs to be purged before the specified time had elapsed. (Bug #33670457)
• Replication: If a group configuration change is in progress, new members cannot join. The error message issued in this scenario now states what operation is in progress, such as a primary election, a swap between single primary mode and multi-primary mode, or a change of group communication protocol. (Bug #32766981)
• Replication: After checking a transaction commit has no conflicts and is in the correct order, Group Replication reports back to the committing session. When the event scheduler thread was started, Group Replication was not able to find the committing session, resulting in the member entering ERROR state and leaving the group. The procedure to locate the committing session was extended to find daemon threads, as used to start the event scheduler thread. Thanks to Lou Shuai for the contribution. (Bug #107635, Bug #34312482)
• Replication: When the binary log sender thread waited for updates with heartbeats enabled, it sometimes missed update signals, so that changes were not replicated until the next signal was issued and noticed by the thread.

  Our thanks to Facebook for the contribution. (Bug #104491, Bug #33176377)

• When the system variable replication_optimize_for_static_plugin_config was enabled to improve performance for semisynchronous replication, the RAPID plugin could not be used in the server due to issues with notification handling. The issue has now been fixed. (Bug #34549470)
• Strings containing many leading zeros were incorrectly marked as overflowing when converting them to decimal, causing the value returned to be the maximum decimal value for the given precision and scale.

  We fix this by replacing all leading zeros in such cases with a single 0, prior to conversion. (Bug #34479194)

• Differences between the WSAPoll function on Windows and the poll() function on Unix-like systems caused thread pool code on Windows to consider invalid file descriptors or sockets as ready for I/O. (Bug #34478731)
• If a MySQL instance at MySQL 8.0.27 or above joined a replication group running at MySQL 8.0.26, the member action mysql_start_failover_channels_if_primary was removed to match the member actions configuration for to the rest of the group. However, if the instances were upgraded to run at MySQL 8.0.27 or above, the member action was not reinstated unless group_replication_reset_member_actions() was issued on a server that then bootstrapped the group. Joining members now check if the member action mysql_start_failover_channels_if_primary is available to them, and add it to their member actions configuration if it is. (Bug #34464526)
• A constant expression used in the SELECT list of a query was not always handled correctly by the server. (Bug #34418412)
• Added a missing error check in Json_table_column::fill_column(). (Bug #34416304)
• Some string functions accepting multiple arguments did not process all of the arguments properly. (Bug #34393009)
• Columns of type YEAR created for temporary tables holding UNION results were not always guaranteed to be of the correct length. (Bug #34370933)
• If a view (v1) accessed another view (v2), and if v2 was recreated, then SHOW COLUMNS FROM v1 reported an invalid view error. This issue could occur only when the user was granted global privileges with roles, but not table-level privileges. It is fixed by checking for global privileges if table-level privileges are absent. (Bug #34341533)
• Queries on the Performance Schema table replication_group_member_actions could fail due to conflicts between threads. The issue has now been fixed. (Bug #34337673)
• Added a missing error check after an item evaluation when reading const tables. (Bug #34327798)
• Reparsing and cloning of derived tables was not always handled correctly in certain cases. (Bug #34317258, Bug #34408241)
• Pushing a condition down to a derived table with set operations when multiple query blocks were present was not always handled correctly. (Bug #34306361)
• A startup failure during RSA key creation resulted in empty or incomplete keys. Keys are now generated in a fail-safe manner. (Bug #34274914)
• When comparing two string values with explicit COLLATE clauses, these should have the same collation, else the comparison should be rejected, but this was not always enforced.

  For more information, see Collation Coercibility in Expressions. (Bug #34274525)

• A deadlock could occur when Group Replication was started on a server instance, but errors such as incompatible configuration caused the member to leave immediately. The service registry now manages the registration and deregistration of services by an alternative process that avoids the issue. (Bug #34194893)
• A CREATE USER operation read from binary log failed to execute on a replica server due to an unexpected syntax error. (Bug #34178823)
• A precision mismatch could sometimes occur in a CAST to FLOAT operation on an integer value, when using SQL_SMALL_RESULT as opposed to SQL_BIG_RESULT (see SELECT Statement).

  For SQL_BIG_RESULT, the value was truncated according to the inherent precision of an IEEE 32 bit floating point value, which is the correct behavior. With SQL_SMALL_RESULT, the precision from the original integer value was preserved; this happened because the value was copied to a temporary table which supported only double precision values, and these values were later copied back to the client.

  We fix this by distinguishing the type of floating point value when copying such values to a temporary table, so that double precision values are stored as type double and single precision values are stored as type float. (Bug #34167787)

• Executed codespell on the majority of source code, and fixed the reported spelling errors in the code comments. (Bug #34163323)
• In certain cases, when checking an internal function pointer before it was called, the wrong pointer was validated. (Bug #34134738)
• Both the access path and the iterator for eq_ref access had a use_order member for determining whether to use an ordered index scan. Since eq_ref always returns at most one row, ordering it is not necessary; thus, use_order serves no purpose here, and has been removed from the access path and the corresponding iterator. (Bug #34122706)
• Improved the ADD_GDB_INDEX CMake option to also function when loading unit tests. (Bug #34117732)

  References: See also: Bug #29925009.

• Removed GNU ‘gold’ linker support, including the associated USE_LD_GOLD CMake option. (Bug #34094324)
• Manually created directories in a MySQL 5.7 data directory (treated as schema directories in MySQL 5.7) with special characters in the directory name or schemas and tables created with special characters in their name using a `#mysql50#` prefix caused DDL failures after upgrading to MySQL 8.0. In MYSQL 8.0, the special characters are replaced with question mark characters (?) when the object names are added to the data dictionary. The name mismatch caused DDL operations on the affected objects to fail. Upgrade now stops with an error if invalid schema and table names are encountered.

  In MySQL 5.7, you can update invalid schema and table names to a valid encoding using mysqlcheck. To identify invalid names, run:

  mysqlcheck --check-upgrade --all-databases

  To fix invalid names, run:

  mysqlcheck --fix-db-names --fix-table-names --all-databases

  In MySQL 5.7, directory names that have special characters can also be updated to use valid encoding using the following SQL syntax:

  ALTER DATABASE `directory_name` UPGRADE DATA DIRECTORY NAME;

  (Bug #34066605)

• Conditions using the null-safe equals operator (<=>) can now considered by the executor as equijoin conditions. Predicates using such conditions are not considered as equijoin predicates, but rather as extra predicates to evaluate following the join. (Bug #34063499)
• Support was added for compiling the MySQL Enterprise Encryption component with OpenSSL 3. (Bug #34043029)
• Executed codespell on the source code and fixed the reported spelling errors in the code comments. (Bug #34006439)
• When one or more arguments to GREATEST() were temporal values, the order in which they were passed to the function could affect the result. (Bug #33996054)
• A SELECT with a LEFT JOIN on a derived table containing a nested subquery as part of a WHERE clause with an IN predicate returned incorrect results. (Bug #33971286)
• Calls to ERR_clear_error() in the vio_ssl_write() and ssl_handshake_loop() functions caused errors when compiling with OpenSSL 3.0. (Bug #33967647)
• The server did not always process nested views as expected. (Bug #33876690)
• mysqlpump might not be given the correct permissions to use derived tables (tables that are generated by a query FROM clause), causing the dump process to stop if these were present. Derived tables are now handled separately and privileges are set for them. (Bug #33866103)
• Repeated execution of a stored routine having as a subquery a SELECT containing multiple AND, OR, or XOR conditions led to excessive consumption and possibly eventual exhaustion of virtual memory. (Bug #33852530)
• EXPLAIN ANALYZE provides information about the estimated cost and elapsed time for each iterator in the query plan. We expect these numbers to be cumulative, so that the cost (or time) for a given iterator includes the cost of any iterators on which this iterator depends. For example, the estimated cost of joining table t1 with table t2, using a table scan of t1 followed by a primary key lookup on t2, should be no less than the cost of scanning t1 plus the the cost of the cardinality lookups on t2.

  For some queries involving materialization or temporary tables, these numbers were not cumulative. In addition, cost estimates were incorrect when materializing the result of a UNION operation and subsequently performing a table scan on the temporary (materialized) table. (Bug #33834146, Bug #34302461)

• Coercion of collations for comparisons of two string values was dependent upon the order of the operands. Given one string using the utf8mb4 character set and one using utf8mb3, the comparison was carried out using the utf8mb4 collation when the left operand was utf8mb4, and utf8mb3 when the left operand was utf8mb3.

  The root of the problem was with the special handling used for coercion of ASCII strings; since both strings were literals, they were scanned for ASCII and non-ASCII characters, and the repertoire property set according to this. This meant that it was possible for one string to be treated as ASCII while the other string was not; when the utf8mb4 string was considered ASCII and thus compatible with the utf8mb3 string it was (wrongly) converted prior to the comparison.

  We fix this by using the internal MY_CS_PUREASCII property rather than MY_REPERTOIRE_ASCII; MY_CS_PUREASCII is derived strictly from the character set, and not the actual string, which makes determination of collation deterministic. Now in such cases, neither string is identified as ASCII, which leads to conversion of the utf8mb3 to utf8mb4 before the comparison is performed, as expected. (Bug #33801312)

• The scope of the connect_timeout limit was extended to full-packet reads. (Bug #33723597)
• New validation was added to ensure that the Event Scheduler restarts automatically on the server as expected. (Bug #33711304)

  References: See also: Bug #33539082.

• When a prepared statement was executed with no cursor, it could return the wrong result if the previous execution used a cursor. This issue is fixed by creating and saving two query result objects, one for use with a cursor and one for use without a cursor. The objects are created on demand, so if a prepared statement is never used with cursors, the cursor-specific query result object is never created. (Bug #33535746)
• A prepared statement with parameters could fail to update a row, but the same statement with the same data did update the row when issued as a query. The fix for the problem is to assign a default data type to the parameters, although this can be inefficient because there is no context available for data type propagation and a character string type is given implicitly. In this case, the best practice is to wrap such parameter declarations in CAST clauses that supply the desired data types. (Bug #33521861)
• For a table using the FEDERATED storage engine, if consecutive queries on the local server had a delay between them that exceeded the remote server’s wait timeout (wait_timeout setting), a packet error was returned on the local server. The timeout error from the remote server is now handled correctly by the local server, and it reconnects and executes the statement again. (Bug #33500956)
• Handled an unexpected condition which could arise during join optimization. (Bug #31401468)

  References: See also: Bug #34350945.

• Following execution of SET @@character_set_client = @@character_set_system, optimizer hints were not parsed correctly. (Bug #107820, Bug #34370403)
• A query performing a full-text search in boolean mode on a column of a MyISAM table led to an assertion in debug builds when the column was not of a string type. This was due to the fact that MyISAM allows MATCH columns without indexes in boolean mode, and therefore also allows integer columns, which are subject to constant propagation. Such queries on tables using any storage engine other than MyISAM were not affected by this issue.

  This is fixed by disabling constant propagation on the MATCH clause when the column is not of a string type, so that we can safely assume that it contains only column references. (Bug #107638, Bug #34311472)

• Corrected errors in comments in my_time.cc. Our thanks to Dimitry Kudryavtsev for the contribution. (Bug #107633, Bug #34308417)
• Following changes to connection error handling in MySQL 8.0.24, if an error such as exceeding max_allowed_packet caused the mysql client to disconnect from the server, the error was not reset afterwards to indicate the disconnect. On subsequent queries, the original error was returned, and automatic reconnection did not take place because the mysql client did not have a disconnection error. The error is now reset to indicate disconnection and allow clients to reconnect. (Bug #107605, Bug #34303841)
• Following a bug fix in MySQL 8.0.29, if the password was provided in an option file, the mysql client did not prompt for a password if the -p option was specified at login. The client now always prompts for a password when the -p option is specified and uses the specified password, even if an alternative was populated from an option file. (Bug #107205, Bug #34134984)
• The alternative for the deprecated --ssl=off server option to disable the use of encrypted connections was not working as documented, or as presented in the deprecation warning. Setting the tls_version system variable to the empty value (tls_version='') now works correctly for this purpose. (Bug #106459, Bug #33858646)
• An aggregate query on a BIT column returned a value that was formatted as a bit string, but also had the BINARY flag added automatically. New validation now checks for and skips setting the BINARY flag for BIT results. (Bug #106241, Bug #33781442)
• SHOW SLAVE STATUS and SHOW SLAVE HOSTS used more CPU following the introduction of SHOW REPLICA STATUS and SHOW REPLICA HOSTS than previously. (Bug #105924, Bug #33684069)
• When a NO_SKIP_SCAN hint referred to a specific index not to be used for a table skip scan, all other possible indexes were also ignored, and, thus, a skip scan was not used for any indexes on the table.

  This occurred because processing was not performed for all possible keys for a skip scan if the NO_SKIP_SCAN hint was not applicable to all indexes. (Bug #104670, Bug #33251616)

• Queries using WHERE column IN (list) took an increasingly excess amount of CPU time as the list of values grew in length. (Bug #102037, Bug #32311183)

转自 MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.31 (2022-10-11, General Availability)