MySQL Community Server 8.0.23 has been released [ part 1/2 ]

[Bjørn Munch]

[ Due to size limitations, the post is split in two. This is part 1. ]

Dear MySQL users,

MySQL Server 8.0.23, a new version of the popular Open Source
Database Management System, has been released. MySQL 8.0.23 is
recommended for use on production systems.

For an overview of what's new in MySQL 8.0, please see

http://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

For information on installing MySQL 8.0.23 on new servers, please see
the MySQL installation documentation at

http://dev.mysql.com/doc/refman/8.0/en/installing.html

MySQL Server 8.0.23 is available in source and binary form for a number of
platforms from our download pages at

http://dev.mysql.com/downloads/mysql/

MySQL Server 8.0.23 is also available from our repository for Linux
platforms, go here for details:

http://dev.mysql.com/downloads/repo/

Windows packages are available via the Installer for Windows:

http://dev.mysql.com/downloads/installer/

along with .ZIP (no-install) packages for more advanced needs.

8.0.23 also comes with a web installer as an alternative to the full
installer.

The web installer doesn't come bundled with any actual products
and instead relies on download-on-demand to fetch only the
products you choose to install. This makes the initial download
much smaller but increases install time as the individual products
will need to be downloaded.

We welcome and appreciate your feedback, bug reports, bug fixes,
patches, etc.:

http://bugs.mysql.com/report.php

The following link lists the changes in the MySQL 8.0 since
the release of MySQL 8.0.22. It may also be viewed
online at

http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html

Enjoy!

Changes in MySQL 8.0.23 (2021-01-18, General Availability)

   This release adds support for macOS 11 (Big Sur).

     * Account Management Notes

     * C API Notes

     * Compilation Notes

     * Connection Management Notes

     * Deprecation and Removal Notes

     * Optimizer Notes

     * Performance Schema Notes

     * Pluggable Authentication

     * Security Notes

     * Spatial Data Support

     * SQL Syntax Notes

     * X Plugin Notes

     * Functionality Added or Changed

     * Bugs Fixed

Account Management Notes


     * Granting the RELOAD privilege enables a user to perform a
       wide variety of operations. In some cases, it may be
       desirable for a user to be able to perform only some of
       these operations. To enable DBAs to avoid granting RELOAD
       and tailor user privileges more closely to the operations
       permitted, these new privileges of more limited scope are
       available:

          + FLUSH_OPTIMIZER_COSTS: Enables use of the FLUSH
            OPTIMIZER_COSTS statement.

          + FLUSH_STATUS: Enables use of the FLUSH STATUS
            statement.

          + FLUSH_TABLES: Enables use of the FLUSH TABLES
            statement.

          + FLUSH_USER_RESOURCES: Enables use of the FLUSH
            USER_RESOURCES statement.
       The new privileges apply only at the global level. For
       more information, see Privileges Provided by MySQL
       (https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html),
       and FLUSH Statement
       (https://dev.mysql.com/doc/refman/8.0/en/flush.html).
       The mysql_refresh() C API function performs operations
       similar to those of various FLUSH statements, but is
       unaffected by this change. It still requires the RELOAD
       privilege regardless of the operation for which it is
       invoked.

C API Notes


     * For some applications, it may be useful to define
       metadata on a per-query basis. Examples include the URL
       of the page that produced a query, or extra processing
       information to be passed with a query for use by a plugin
       such as an audit plugin or query rewrite plugin. MySQL
       now supports this capability without the use of
       workarounds such as specially formatted comments included
       in query strings:

          + On the client side, the mysql_bind_param() C API
            function enables defining query attributes. These
            attributes apply to the next SQL statement sent to
            the server for execution. Additionally, the mysql
            and mysqltest clients have a query_attributes
            command that enables defining query attributes.

          + On the server side, a component service provides
            access to query attributes. A component named
            query_attributes uses this service to implement a
            mysql_query_attribute_string() user-defined function
            (UDF) that enables obtaining attribute values within
            SQL statements. The query_attributes component is
            optional but must be installed for the UDF to be
            available.
       For more information, see Query Attributes
       (https://dev.mysql.com/doc/refman/8.0/en/query-attributes.html).
       Thanks to Facebook for suggesting the idea (and for
       contributing code, although it was not used). (Bug
       #27855905, Bug #28686334)

Compilation Notes


     * Thanks to Tzachi Zidenberg, who contributed a patch for
       compiling MySQL on aarch64 (ARM64). (Bug #31815236, Bug
       #100664)

Connection Management Notes


     * Selection of the account that matches incoming TCP client
       connections could be affected by account creation order.
       To make the matching algorithm more deterministic,
       matching the host name part of accounts now checks
       accounts specified using host IP addresses, in a specific
       order, before attempting to match accounts specified
       using host names. Host name matching remains unchanged.
       See Access Control, Stage 1: Connection Verification
       (https://dev.mysql.com/doc/refman/8.0/en/connection-access.html).

Deprecation and Removal Notes


     * The gen_blacklist() user-defined function is deprecated.
       Use gen_blocklist() instead, which performs the same
       term-replacement operation.

     * Flushing the host cache can be done using any of these
       methods:

          + Execute a TRUNCATE TABLE statement that truncates
            the Performance Schema host_cache table. This
            requires the DROP privilege for the table.

          + Execute a FLUSH HOSTS statement. This requires the
            RELOAD privilege.

          + Execute a mysqladmin flush-hosts command. This
            requires the RELOAD privilege.
       Although those methods are equivalent in effect, granting
       the RELOAD privilege enables a number of other operations
       in addition to host cache flushing, which is undesirable
       from a security standpoint. Granting the DROP privilege
       for the host_cache table is preferable because it has a
       more limited scope. Therefore, the FLUSH HOSTS statement
       is deprecated and will be removed in a future MySQL
       release. Instead, truncate the host_cache table.
       mysqladmin flush-hosts previously executed a FLUSH HOSTS
       statement. Now it attempts to truncate the host_cache
       table, falling back to FLUSH HOSTS only if the truncate
       operation fails.

Optimizer Notes


     * Switched the hash table used for hash joins from an
       unordered multimap to an unordered flat map implemented
       with a multimap adapter. This change yields the following
       improvements:

          + A faster hash table

          + Less memory usage due to less hash table overhead,
            less space used for alignment and key/value lengths,
            and better memory usage with many equal keys; this
            should also reduce the frequency at which it is
            necessary to spill to disk

          + Better memory control by approaching the allowed
            join buffer size more closely rather than being
            effectively limited to approximately 2/3 of
            join_buffer_size, and by making it possible to free
            old memory when the hash table grows
       (Bug #99933, Bug #31516149)

Performance Schema Notes


     * Performance Schema macros that previously expanded to
       dynamic calls now expand to static calls when possible to
       reduce processing overhead. (Bug #32028160)

     * Performance overhead of timer code was reduced. This
       should be of most benefit to workloads with high
       concurrency using the Performance Schema. Thanks to
       Georgy Kirichenko for the contribution. (Bug #31960377,
       Bug #101018)

Pluggable Authentication


     * The MySQL Enterprise Edition SASL LDAP authentication
       plugin now supports SCRAM-SHA-256 as an authentication
       method for MySQL clients and servers. SCRAM-SHA-256 is
       similar to SCRAM-SHA-1 but is more secure. Use of
       SCRAM-SHA-256 requires an OpenLDAP server built using
       Cyrus SASL 2.1.27 or higher. See LDAP Authentication
       Methods
(https://dev.mysql.com/doc/refman/8.0/en/ldap-pluggable-authentication.html#ldap-pluggable-authentication-auth-methods).

Security Notes


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

Spatial Data Support


     * The new ST_HausdorffDistance() and ST_FrechetDistance()
       functions return the discrete Fréchet and Hausdorff
       distances between two geometries, reflecting how similar
       the geometries are. See Spatial Relation Functions That
       Use Object Shapes
(https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html).

SQL Syntax Notes


     * MySQL now supports invisible columns, which normally are
       hidden to queries, but can be accessed if explicitly
       referenced. See Invisible Columns
       (https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html).

X Plugin Notes


     * For X Protocol connections using the MYSQL41
       authentication method, if the nonce sent by the server
       was shorter than 20 bytes, the connection logic did not
       handle it correctly. (Bug #32036194)

     * If a query that was building up a resultset was killed, X
       Plugin interpreted this as meaning the server session had
       been killed, and dropped the connection. The status of a
       query is now checked separately from the status of the
       server session. (Bug #31954296)

     * A deadlock could occur if an X Protocol session attempted
       to display X Plugin status variables or settings at the
       same time as another X Protocol session was being
       released and reset. The situation is now handled
       appropriately. (Bug #31931873)

     * If an X Protocol client with a connection to a server
       remains idle (not sending to the server) for longer than
       the relevant X Plugin timeout setting (read, write, or
       wait timeout), X Plugin closes the connection. In the
       case of a read timeout, the plugin returns a warning
       notice with the error code ER_IO_READ_ERROR to the client
       application.
       From MySQL 8.0.23, X Plugin now also sends a warning
       notice if a connection is actively closed due to a server
       shutdown, or by the connection being killed from another
       client session. In the case of a server shutdown, the
       warning notice is sent to all authenticated X Protocol
       clients with open connections, with the
       ER_SERVER_SHUTDOWN error code. In the case of a killed
       connection, the warning notice is sent to the relevant
       client with the ER_SESSION_WAS_KILLED error code, unless
       the connection was killed during SQL execution, in which
       case a fatal error is returned with the
       ER_QUERY_INTERRUPTED error code.
       Client applications can use the warning notices to
       display to users, or to analyze the reason for
       disconnection and decide whether to attempt reconnection
       to the same server, or to a different server.

     * For classic MySQL protocol, if an SQL query is using
       metadata locking or the sleep function, the connection to
       the server is checked periodically to verify that it is
       still alive. If not, the query can be stopped so that it
       does not continue to consume resources. Previously, X
       Protocol did not carry out these checks, and assumed that
       the connection was still alive. The check has now been
       added for X Protocol.

Functionality Added or Changed


     * InnoDB: Performance was improved for the following
       operations:

          + Dropping a large tablespace on a MySQL instance with
            a large buffer pool (>32GBs).

          + Dropping a tablespace with a significant number of
            pages referenced from the adaptive hash index.

          + Truncating temporary tablespaces.
       The pages of dropped or truncated tablespaces and
       associated AHI entries are now removed from the buffer
       pool passively as pages are encountered during normal
       operations. Previously, dropping or truncating
       tablespaces initiated a full list scan to remove pages
       from the buffer pool immediately, which negatively
       impacted performance. (Bug #31008942, Bug #98869)

     * InnoDB: The new AUTOEXTEND_SIZE option defines the amount
       by which InnoDB extends the size of a tablespace when it
       becomes full, making it possible to extend tablespace
       size in larger increments. Allocating space in larger
       increments helps to avoid fragmentation and facilitates
       ingestion of large amounts of data. The AUTOEXTEND_SIZE
       option is supported with the CREATE TABLE, ALTER TABLE,
       CREATE TABLESPACE, and ALTER TABLESPACE statements. For
       more information, see Tablespace AUTOEXTEND_SIZE
       Configuration
(https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-autoextend-size.html).
       An AUTOEXTEND_SIZE size column was added to the
       INFORMATION_SCHEMA.INNODB_TABLESPACES table.

     * InnoDB: InnoDB now supports encryption of doublewrite
       file pages belonging to encrypted tablespaces. The pages
       are encrypted using the encryption key of the associated
       tablespace. For more information, see InnoDB Data-at-Rest
       Encryption
       (https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html).

     * InnoDB: InnoDB atomics code was revised to use C++
       std::atomic.

     * From MySQL 8.0.23, the statement CHANGE MASTER TO is
       deprecated. The alias CHANGE REPLICATION SOURCE TO should
       be used instead. The parameters for the statement also
       have aliases that replace the term MASTER with the term
       SOURCE. For example, MASTER_HOST and MASTER_PORT can now
       be entered as SOURCE_HOST and SOURCE_PORT. The START
       REPLICA | SLAVE statement's parameters MASTER_LOG_POS and
       MASTER_LOG_FILE now have aliases SOURCE_LOG_POS and
       SOURCE_LOG_FILE. The statements work in the same way as
       before, only the terminology used for each statement has
       changed. A deprecation warning is issued if the old
       versions are used.
       A new status variable, Com_change_replication_source, has
       been added as an alias for the Com_change_master status
       variable. Both the old and new version of the statement
       update both the old and new version of the status
       variable.
       The server rewrites all CHANGE MASTER TO statements as
       CHANGE REPLICATION SOURCE TO statements in the query log.
       The same is done for the statements START SLAVE, STOP
       SLAVE, SHOW SLAVE STATUS, SHOW SLAVE HOSTS and RESET
       SLAVE. The event name for the CHANGE MASTER TO statement
       is set to statement/sql/change_replication_source in the
       statement history table. (Bug #32145023)

     * When invoked with the --all-databases option, mysqldump
       now dumps the mysql database first, so that when the dump
       file is reloaded, any accounts named in the DEFINER
       clause of other objects will already have been created.
       (Bug #32141046)

     * Some overhead for disabled Performance Schema and
       LOCK_ORDER tool instrumentation was identified and
       eliminated. (Bug #32105698)

     * For BLOB and TEXT columns that have a default value
       expression, the INFORMATION_SCHEMA.COLUMNS table and SHOW
       COLUMNS statement now display the expression. (Bug
       #31856459)

     * CRC calculations for binlog checksums are faster on ARM
       platforms. Thanks to Krunal Bauskar for the contribution.
       (Bug #99118, Bug #31101633, Bug #32163391)

     * MySQL Server's asynchronous connection failover mechanism
       now supports Group Replication topologies, by
       automatically monitoring changes to group membership and
       distinguishing between primary and secondary servers.
       When you add a group member to the source list and define
       it as part of a managed group, the asynchronous
       connection failover mechanism updates the source list to
       keep it in line with membership changes, adding and
       removing group members automatically as they join or
       leave. The new
       asynchronous_connection_failover_add_managed() and
       asynchronous_connection_failover_add_managed() UDFs are
       used to add and remove managed sources.
       The connection is failed over to another group member if
       the currently connected source goes offline, leaves the
       group, or is no longer in the majority, and also if the
       currently connected source does not have the highest
       weighted priority in the group. For a managed group, a
       source's weight is assigned depending on whether it is a
       primary or a secondary server. So assuming that you set
       up the managed group to give a higher weight to a primary
       and a lower weight to a secondary, when the primary
       changes, the higher weight is assigned to the new
       primary, so the replica changes over the connection to
       it. This function also applies to single (non- managed)
       servers, so the connection is now failed over if another
       source server is available that has a higher weighted
       priority.

     * Replication channels can now be set to assign a GTID to
       replicated transactions that do not already have one,
       using the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS option
       of the CHANGE REPLICATION SOURCE TO statement. This
       feature enables replication from a source that does not
       use GTID-based replication, to a replica that does. For a
       multi-source replica, you can have a mix of channels that
       use ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS, and channels
       that do not. The GTID can include the replica's own
       server UUID or a server UUID that you assign to identify
       transactions from different sources.
       Note that a replica set up with
       ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS on any channel
       cannot be promoted to replace the replication source
       server in the event that a failover is required, and a
       backup taken from the replica cannot be used to restore
       the replication source server. The same restriction
       applies to replacing or restoring other replicas that use
       ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS on any channel.
       The GTID set (gtid_executed) from a replica set up with
       ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS is nonstandard and
       should not be transferred to another server, or compared
       with another server's gtid_executed set.

     * For a multithreaded replica (where slave_parallel_workers
       is greater than 0), setting slave_preserve_commit_order=1
       ensures that transactions are executed and committed on
       the replica in the same order as they appear in the
       replica's relay log. Each executing worker thread waits
       until all previous transactions are committed before
       committing. If a worker thread fails to execute a
       transaction because a possible deadlock was detected, or
       because the transaction's execution time exceeded a
       relevant wait timeout, it automatically retries the
       number of times specified by slave_transaction_retries
       before stopping with an error. Transactions with a
       non-temporary error are not retried.
       The replication applier on a multithreaded replica has
       always handled data access deadlocks that were identified
       by the storage engines involved. However, some other
       types of lock were not detected by the replication
       applier, such as locks involving access control lists
       (ACLs) or metadata locking (for example, FLUSH TABLES
       WITH READ LOCK statements). This could lead to
       three-actor deadlocks with the commit order locking,
       which could not be resolved by the replication applier,
       and caused replication to hang indefinitely. From MySQL
       8.0.23, deadlock handling on multithreaded replicas that
       preserve the commit order has been enhanced to mitigate
       these types of deadlocks. The deadlocks are not
       specifically resolved by the replication applier, but the
       applier is aware of them and initiates automatic retries
       for the transaction, rather than hanging. If the retries
       are exhausted, replication stops in a controlled manner
       so that the deadlock can be resolved manually.

     * The use of the system variables master_info_repository
       and relay_log_info_repository is now deprecated, and a
       warning message is issued if you attempt to set them or
       read their values. The system variables will be removed
       in a future release. These system variables were used to
       specify whether the replica's connection metadata
       repository and applier metadata repository were written
       to an InnoDB table in the mysql system database, or to a
       file in the data directory. The FILE setting was already
       deprecated in a previous release, and tables are the
       default for the replication metadata repositories in
       MySQL 8.0.

     * The new temptable_max_mmap variable defines the maximum
       amount of memory the TempTable storage engine is
       permitted to allocate from memory-mapped temporary files
       before it starts storing data to InnoDB internal
       temporary tables on disk. A setting of 0 disables
       allocation of memory from memory-mapped temporary files.
       For more information, see Internal Temporary Table Use in
       MySQL
(https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html).

[ To be continued ]