MySQL Forums
Forum List  »  Announcements

MySQL Community Server 8.0.20 has been released (part 1/2)
Posted by: Bjørn Munch
Date: April 27, 2020 12:55PM

[ Due to size limitation, this announcement is split in two.
This is part 1 ]

Dear MySQL users,

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

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

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

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

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

Windows packages are available via the Installer for Windows:

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

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

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.

Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to
a previous MySQL 8.0 release, is not supported. The only supported
alternative is to restore a backup taken before upgrading. It is
therefore imperative that you backup your data before starting the
upgrade process.

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

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


Changes in MySQL 8.0.20 (2020-04-27, General Availability)

     * Account Management Notes

     * Compilation Notes

     * Deprecation and Removal Notes

     * JSON Notes

     * Logging Notes

     * Optimizer Notes

     * Packaging Notes

     * Performance Schema Notes

     * Pluggable Authentication

     * SQL Syntax Notes

     * Test Suite Notes

     * X Plugin Notes

     * Functionality Added or Changed

     * Bugs Fixed

Account Management Notes

     * Previously, for a user to access definitions of routines
       the user did not define, the user was required to have
       the global SELECT privilege, which is very broad. The new
       SHOW_ROUTINE privilege may be granted instead as a
       privilege with a more restricted scope that permits
       access to routine definitions. (That is, an administrator
       can rescind global SELECT from users that do not
       otherwise require it and grant SHOW_ROUTINE instead.)
       This enables an account to back up stored routines
       without requiring a broad privilege.
       SHOW_ROUTINE provides access to:

          + The contents of the INFORMATION_SCHEMA.ROUTINES



       For upgrades from an older version of MySQL, any user who
       has the global SELECT privilege is granted SHOW_ROUTINE,
       if there is not already some user who has SHOW_ROUTINE.

Compilation Notes

     * Solaris: Clang and GCC now can be used for compiling
       MySQL on Solaris, although both are experimental and
       cannot currently be used for production code. (Bug

     * On EL7 and EL8, CMake configuration was adjusted to look
       for GCC 9 before GCC 8. Because libmysqlclient ships with
       MySQL distributions, client applications built against
       libmysqlclient on those platforms are affected and may
       need to be recompiled. (Bug #30722756)

     * On Windows, the CMake compiler-version check for Visual
       Studio was updated to indicate that Visual Studio 2019 is
       the currently supported version. (The version check can
       be bypassed by running CMake with
       -DFORCE_UNSUPPORTED_COMPILER=1.) (Bug #30688403)

Deprecation and Removal Notes

     * JSON: Previously, it was possible to specify ON EMPTY and
       ON ERROR clauses in either order when invoking the
       JSON_TABLE() function. This runs counter to the SQL
       standard, which stipulates that when ON EMPTY is
       specified, it must always come before any ON ERROR
       clause. For this reason, specifying ON ERROR before ON
       EMPTY is now deprecated, and trying to do so causes the
       server to issue a warning. Support for the nonstandard
       syntax will be removed in a future version of MySQL.

     * The max_length_for_sort_data system variable is now
       deprecated due to optimizer changes that make it obsolete
       and of no effect.
       References: See also: Bug #30473261.

     * The use of VALUES() to access new row values in INSERT
       ... ON DUPLICATE KEY UPDATE statements is now deprecated,
       and is subject to removal in a future MySQL release.
       Instead, you should use aliases for the new row and its
       columns as implemented in MySQL 8.0.19 and later.
       For example, the statement shown here uses VALUES() to
       access new row values:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
       Henceforth, you should instead use a statement similar to
       the following, which uses an alias for the new row:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;
       Alternatively, you can employ aliases for both the new
       row and each of its columns, as shown here:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
       For more information and examples, see INSERT ... ON

JSON Notes

     * The rapidjson library included with MySQL has been
       upgraded to the GitHub snapshot of 16 January 2020. A fix
       for a compiler error encountered when building from the
       snapshot on Mac OS X has been added. (Bug #30898701)

Logging Notes

     * Sending a SIGHUP signal to the server no longer causes it
       to write a status report to the error log. Other actions
       performed by the server in response to SIGHUP continue to
       be done. See Unix Signal Handling in MySQL
       (Bug #30578923)

     * The JSON-format error log writer now includes a ts
       (timestamp) in log messages. The value is an integer
       indicating milliseconds since the epoch ('1970-01-01
       00:00:00' UTC). See Error Log Message Format

Optimizer Notes

     * Hash joins are now used any time a nested block loop
       would be employed. This means that hash joins can be used
       for the following types of queries:

          + Inner non-equi-joins

          + Semijoins

          + Antijoins

          + Left outer joins

          + Right outer joins
       This builds on work done for MySQL 8.0.18, and removes a
       limitation in the implementation such that a hash join
       could be used only with a query having at least one
       equi-join condition. In addition, both inner and outer
       joins (including semijoins and antijoins) can now employ
       batched key access (BKA), which allocates join buffer
       memory incrementally so that individual queries need not
       use up large amounts of resources that they do not
       actually require for resolution. For more information,
       see Batched Key Access Joins
       This fix completes the task of replacing the executor
       used in previous versions of MySQL with the iterator
       executor, including replacement of the old index subquery
       engines that governed queries of the form WHERE value IN
       (SELECT column FROM table WHERE condition) for those IN
       queries which have not been converted into semijoins, as
       well as queries materialized into the same form, which
       depended on internals from the old executor.
       For more information and examples, see Hash Join
       (Bug #30528604, Bug #30473261, Bug #30912972)

     * This release implements several new index-level optimizer
       hints, which function much like existing index hints that
       employ SQL keywords such as FORCE INDEX and IGNORE INDEX.
       These are intended to replace the equivalent index hints,
       which will be deprecated in a future MySQL release (and
       eventually removed). The new hints are listed here, along
       with a brief description of each:

          + JOIN_INDEX: Forces MySQL to use the specified index
            or indexes for any available access method, such as
            ref, range, index_merge, and so on. This is
            equivalent to the FORCE INDEX FOR JOIN index hint.
            NO_JOIN_INDEX: Causes the server to ignore the
            specified index or indexes for any access method.
            The equivalent index hint is IGNORE INDEX FOR JOIN.

          + GROUP_INDEX: Makes the server use the specified
            index or indexes for index scans for GROUP BY
            operations. Equivalent to FORCE INDEX FOR GROUP BY.
            NO_GROUP_INDEX: Forces MySQL to ignore the specified
            index or indexes for index scans for GROUP BY
            operations. The equivalent index hint is IGNORE
            INDEX FOR GROUP BY.

          + ORDER_INDEX: Causes MySQL to use the specified index
            or indexes for sorting rows. It is equivalent to
            NO_ORDER_INDEX: Keeps the server from using the
            specified index or indexes for performing row sorts.
            Equivalent to IGNORE INDEX FOR ORDER BY.

          + INDEX: Acts as the combination of JOIN_INDEX,
            GROUP_INDEX, and ORDER_INDEX, forcing the server to
            use the specified index or indexes for any and all
            scopes. Equivalent to FORCE INDEX.
            NO_INDEX: Acts as the combination of NO_JOIN_INDEX,
            NO_GROUP_INDEX, and NO_ORDER_INDEX; that is, it
            forces MySQL to ignore the specified index or
            indexes for any and all scopes. It is equivalent to
            the index hint IGNORE INDEX.
       Consider the following query using index hints on a table
       having the indicated columns and index:
       Using the index-level optimizer hints introduced in this
       release, this query can be rewritten as shown here:
SELECT /*+ ORDER_INDEX(t1 i_ab) */ a,b FROM t1 ORDER BY a;
       The new index-level optimizer hints can be used with
       SELECT, UPDATE, and DELETE statements. (This is unlike
       index hints using FORCE INDEX or IGNORE INDEX, which can
       be used only with SELECT and UPDATE.) Thus, statements
       like the following are possible:
UPDATE /*+ INDEX(t1 i_ab) */ t1 SET d = 1
    WHERE a = 1 AND b = 2 AND c = 3;

DELETE /*+ INDEX(t1 i_a,i_c) */ FROM t1
    WHERE a = 1 AND b = 2 AND c = 3;
       Multiple hints can be specified within the same comment,
       like this:
DELETE /*+ INDEX(t1 i_a) JOIN_INDEX(t1 i_c) */
    FROM t1 WHERE a = 1 AND b = 2 AND c = 3;
       Index-level optimizer hints can be used concurrently with
       other optimizer hints. When you do so, the index-level
       hints apply first; the effects of any other optimizer
       hints are limited to the set of indexes permitted by the
       index-level hints.
       Index-level hints can also be used when creating views,
       as shown here:
    SELECT /*+ NO_INDEX(t1 i_a,i_b) */ a FROM t1
    WHERE b IN
        (SELECT /*+ NO_INDEX(t1 i_ab,i_b) */ a FROM t1 WHERE a > 3)
    ORDER BY a;
       If these index-level optimizer hints are used in the same
       statement as index hints, the index hints are ignored.
       The new index-level optimizer hints are equivalent to
       FORCE INDEX rather than USE INDEX; in other words, using
       one or more of the index-level optimizer hints means that
       a table scan is used only if there is no way to use one
       of the named indexes to find rows in the table. To cause
       MySQL to use the same index or set of indexes as with a
       given instance of USE INDEX, you can use NO_INDEX,
       combination of these.
       For more information and examples, see Index-Level
       Optimizer Hints

Packaging Notes

     * Binary packages that include curl rather than linking to
       the system curl library have been upgraded to use curl
       7.69.0. (Bug #30866333)

     * For RPM packages, the comp_err utility has been moved to
       the -test subpackage and marked as a test component. (Bug

     * The bundled LZ4 library was upgraded to version 1.9.2.
       This fixes certain issues such as Bug #30369643 producing
       a mysqlpump runtime error.
       References: See also: Bug #30369643.

     * The bundled libedit library was upgraded to version 3.1.

Performance Schema Notes

     * The Performance Schema collected session-related
       statistics for errors that can occur only globally and
       not per session. This is no longer done, reducing memory
       overhead for error instrumentation. Additionally, rows
       for global errors are no longer included in error
       summaries reported per thread, account, user, or host.
       (Bug #30311574)

Pluggable Authentication

     * An LDAP server can be configured to delegate LDAP
       searches to another LDAP server, a functionality known as
       LDAP referral. However, enabling LDAP referral can cause
       searches to fail with LDAP operation errors under certain
       conditions. To enable the MySQL Enterprise Edition LDAP
       authentication plugins to avoid referral errors, the new
       authentication_ldap_simple_referral and
       authentication_ldap_sasl_referral system variables are
       available. These variables enable each plugin to control
       whether the LDAP server should use referral during MySQL
       authentication. See LDAP Search Referral

     * The MySQL Enterprise Edition SASL LDAP authentication
       plugin now supports GSSAPI/Kerberos as an authentication
       method for MySQL clients and servers on Linux. This is
       useful in Linux environments where applications access
       LDAP using Microsoft Active Directory, which has Kerberos
       enabled by default. See LDAP Authentication Methods
       This feature is available for all RPM and DEB packages
       for Linux, but not for the TAR archive packages.

SQL Syntax Notes

     * Previously, the INTO clause for SELECT statements could
       appear at either of two positions:

          + Before FROM:
SELECT * INTO OUTFILE 'file_name' FROM table_name;

          + Before a trailing locking clause:
SELECT * FROM table_name INTO OUTFILE 'file_name' FOR UPDATE;

       INTO now can appear in a third position, at the end of
       SELECT statements:
SELECT * FROM table_name FOR UPDATE INTO OUTFILE 'file_name';

       Placing INTO at the end is the preferred position. The
       position before a locking clause is now deprecated and
       support for it will be removed in a future MySQL version.
       In other words, INTO after FROM but not at the end of the
       SELECT produces a warning.
       Additionally, some changes have been made for UNION with
       respect to INTO. These UNION variants containing INTO are
       syntactically correct and produce the same result:
... UNION SELECT * FROM table_name INTO OUTFILE 'file_name';
... UNION (SELECT * FROM table_name) INTO OUTFILE 'file_name';
... UNION SELECT * INTO OUTFILE 'file_name' FROM table_name;
... UNION (SELECT * INTO OUTFILE 'file_name' FROM table_name);

       However, the last two variants are confusing, as if they
       collect information from the named table rather than the
       entire query expression (the UNION). Those two UNION
       variants containing INTO now are deprecated and support
       for them will be removed in a future MySQL version. Thus:

          + In the trailing query block of a query expression,
            use of INTO before FROM produces a warning.

          + In a parenthesized trailing block of a query
            expression, use of INTO (regardless of its position
            relative to FROM) produces a warning.
       The deprecations apply to all INTO forms: INTO OUTFILE,
       INTO DUMPFILE, and INTO var_list.

Test Suite Notes

     * The perfschema.idx_compare_replication_applier_status
       test case was updated to store the old value of number of
       transaction retries and compare it with the new value of
       number of transaction retries. Thanks to Facebook for the
       contribution. (Bug #30810627, Bug #98389)

X Plugin Notes

     * If the MySQL Server instance's client connections limit,
       as specified by the max_connections server system
       variable, was reached while X Plugin was starting up, X
       Plugin was unable to create a session to get the server
       configuration, so failed to start. X Plugin now creates
       an administrative session (using the mysql_admin_session
       service) during startup, which is not subject to the
       client connections limit. (Bug #30894981)

     * When an X Protocol session could not be initialized
       because there were too many X Protocol connections
       already, the error code 5011 Could not open session was
       returned. The more relevant error code 1040 Too many
       connections is now returned in this situation. (Bug

     * An issue with validating JSON references caused an error
       when creating a collection with a validation schema. (Bug

     * During shutdown of a MySQL Server instance with X
       Protocol connections to clients, a race condition in X
       Plugin could cause invalid client connections to be
       accepted for processing. Because invalid clients were
       ignored for client timeout verification during shutdown,
       these clients blocked shutdown until the timeout set by
       the mysqlx_wait_timeout system variable was reached,
       which defaults to 8 hours. To prevent this issue, client
       timeout verification now includes clients that are in an
       invalid state. (Bug #30702685)

     * When connecting to a MySQL 8.0 server, X Plugin set a
       different collation for the session to that used by the
       mysql client, which could cause issues with queries that
       depended on the collation. X Plugin now uses the
       utf8mb4_0900_ai_ci collation, which is the default for
       the utf8mb4 characterset. (Bug #30516849)

     * The worker threads for X Protocol connections were
       identified as system threads on creation, and assigned to
       the SYS_default resource group. This identification meant
       they could not be assigned to user resource groups for
       resource management purposes. They are now identified as
       user threads and assigned to the USR_default resource
       group. Note that X Protocol does not currently support
       CREATE, ALTER, DROP, and SET RESOURCE GROUP statements,
       but these statements can operate on X Protocol connection
       threads using classic MySQL protocol connections. (Bug

     * X Plugin can now access the MySQL system variables as
       soon as initialization starts, so the plugin install
       thread can set up the required connectivity itself rather
       than starting a separate thread. (Bug #29127302)

Functionality Added or Changed

     * Important Change: Previously, including any column of a
       blob type larger than TINYBLOB or BLOB as the payload in
       an ordering operation caused the server to revert to
       sorting row IDs only, rather than complete rows; this
       resulted in a second pass to fetch the rows themselves
       from disk after the sort was completed. Since JSON and
       GEOMETRY columns are implemented internally as LONGBLOB,
       this caused the same behavior with these types of columns
       even though they are almost always much shorter than the
       4GB maximum for LONGBLOB (or even the 16 MB maximum for
       MEDIUMBLOB). The server now converts columns of these
       types into packed addons in such cases, just as it does
       TINYBLOB and BLOB columns, which in testing showed a
       significant performance increase. The handling of
       MEDIUMBLOB and LONGBLOB columns in this regard remains
       One effect of this enhancement is that it is now possible
       for Out of memory errors to occur when trying to sort
       rows containing very large (multi-megabtye) JSON or
       GEOMETRY column values if the sort buffers are of
       insufficient size; this can be compensated for in the
       usual fashion by increasing the value of the
       sort_buffer_size system variable. (Bug #30400985, Bug

     * InnoDB: The Contention-Aware Transaction Scheduling
       (CATS) algorithm, which prioritizes transactions that are
       waiting for locks, was improved. Transaction scheduling
       weight computation is now performed a separate thread
       entirely, which improves computation performance and
       The First In First Out (FIFO) algorithm, which had also
       been used for transaction scheduling, was removed. The
       FIFO algorithm was rendered redundant by CATS algorithm
       enhancements. Transaction scheduling previously performed
       by the FIFO algorithm is now performed by the CATS
       A TRX_SCHEDULE_WEIGHT column was added to the
       INFORMATION_SCHEMA.INNODB_TRX table, which permits
       querying transaction scheduling weights assigned by the
       CATS algorithm.
       The following INNODB_METRICS counters were added for
       monitoring code-level transaction scheduling events:

          + lock_rec_release_attempts
            The number of attempts to release record locks.

          + lock_rec_grant_attempts
            The number of attempts to grant record locks.

          + lock_schedule_refreshes
            The number of times the wait-for graph was analyzed
            to update transaction schedule weights.

     * InnoDB: The storage area for the doublewrite buffer was
       moved from the system tablespace to doublewrite files.
       Moving the doublewrite buffer storage area out of the
       system tablespace reduces write latency, increases
       throughput, and provides flexibility with respect to
       placement of doublewrite buffer pages. The following
       system variables were introduced for advanced doublewrite
       buffer configuration:

          + innodb_doublewrite_dir
            Defines the doublewrite buffer file directory.

          + innodb_doublewrite_files
            Defines the number of doublewrite files.

          + innodb_doublewrite_pages
            Defines the maximum number of doublewrite pages per
            thread for a batch write.

          + innodb_doublewrite_batch_size
            Defines the number of doublewrite pages to write in
            a batch.
       For more information, see Doublewrite Buffer

     * EXPLAIN ANALYZE can now be stopped during execution using
       KILL QUERY or CTRL-C. (Bug #30787515)

     * EXPLAIN FORMAT=TREE now displays inversion information
       for windowing functions. (Bug #30770631)

     * EXPLAIN FORMAT=TREE output has been improved to provide
       more information about evaluated window functions, and to
       match that supplied for regular aggregates. (Bug
       #30573446, Bug #30582782)

     * EXPLAIN ANALYZE now supports the FORMAT option.
       Currently, TREE is the only supported format. (Bug

     * Configuring with the -DWITH_LTO=1 CMake option now works
       on macOS. (Bug #30125902)

     * From MySQL 8.0.20, you can enable binary log transaction
       compression on a MySQL server instance. When binary log
       transaction compression is enabled, transaction payloads
       are compressed using the zstd algorithm, and then written
       to the server's binary log file as a single event (a
       Transaction_payload_event). Compressed transaction
       payloads remain in a compressed state while they are sent
       in the replication stream to replication slaves, other
       Group Replication group members, or clients such as
       mysqlbinlog. They are not decompressed by receiver
       threads, and are written to the relay log still in their
       compressed state. Binary log transaction compression
       therefore saves storage space both on the originator of
       the transaction and on the recipient (and for their
       backups), and saves network bandwidth when the
       transactions are sent between server instances.
       You can enable binary log transaction compression on a
       MySQL server instance using the
       binlog_transaction_compression system variable, which
       defaults to OFF. You can also use the
       binlog_transaction_compression_level_zstd system variable
       to set the level for the zstd algorithm that is used for
       compression. This value determines the compression
       effort, from 1 (the lowest effort) to 22 (the highest

     * A new option for the CHANGE MASTER TO statement,
       REQUIRE_TABLE_PRIMARY_KEY_CHECK, enables a replication
       slave to select its own policy for primary key checks.
       When the option is set to ON for a replication channel,
       the slave always uses the value ON for the
       sql_require_primary_key system variable in replication
       operations, requiring a primary key. When the option is
       set to OFF, the slave always uses the value OFF for the
       sql_require_primary_key system variable in replication
       operations, so that a primary key is never required, even
       if the master required one. When the
       which is the default, the slave uses whatever value is
       replicated from the master for each transaction.

          + For multisource replication, setting
            REQUIRE_TABLE_PRIMARY_KEY_CHECK to ON or OFF enables
            a slave to normalize behavior across the replication
            channels for different masters, and keep a
            consistent setting for the sql_require_primary_key
            system variable. Using ON safeguards against the
            accidental loss of primary keys when multiple
            masters update the same set of tables. Using OFF
            allows masters that can manipulate primary keys to
            work alongside masters that cannot.

          + When PRIVILEGE_CHECKS_USER is set to apply
            replication privilege checks to the channel, setting
            that the user account does not need session
            administration level privileges to set restricted
            session variables, which are required to change the
            value of sql_require_primary_key to match the
            master's setting for each transaction.

     * Since MySQL 8.0.19, compression has been supported for
       messages sent over X Protocol connections. Connections
       can be compressed if the server and the client agree on a
       compression algorithm to use. By default, the server
       permits the Deflate, LZ4, and zstd compression
       algorithms, or you can set the
       mysqlx_compression_algorithms system variable to include
       only the ones you permit. In MySQL 8.0.19, X Protocol
       uses the library default compression level for each
       algorithm, and the client cannot negotiate this.
       From MySQL 8.0.20, the client can request a specific
       compression level during capability negotiations for an X
       Protocol connection. X Protocol sets a maximum
       compression level for each algorithm, which prevents the
       server from agreeing to high compression levels that are
       requested by clients if that would consume too much
       resource on the server. The maximum compression levels
       are initially set to 5 for Deflate, 8 for LZ4, and 11 for
       zstd. You can adjust these settings using the new
       mysqlx_lz4_max_client_compression_level, and
       mysqlx_zstd_max_client_compression_level system
       New default compression levels for X Protocol have also
       been selected through performance testing as being a good
       trade-off between compression time and network transit
       time. These defaults are not necessarily the same as the
       library default for each algorithm. They are applied if
       the client does not request a compression level for the
       algorithm. The default compression levels are initially
       set to 3 for Deflate, 2 for LZ4, and 3 for zstd. You can
       adjust these settings using the new
       mysqlx_lz4_default_compression_level, and
       mysqlx_zstd_default_compression_level system variables.

Options: ReplyQuote

Written By
MySQL Community Server 8.0.20 has been released (part 1/2)
April 27, 2020 12:55PM

Sorry, you do not have permission to post/reply in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.