MySQL Forums
Forum List  »  Announcements

MySQL Community Server 8.0.19 has been released, part 1/2
Posted by: Bjørn Munch
Date: January 13, 2020 06:57AM

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

Dear MySQL users,

MySQL Server 8.0.19, a new version of the popular Open Source
Database Management System, has been released. MySQL 8.0.19 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.19 on new servers, please see
the MySQL installation documentation at

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

MySQL Server 8.0.19 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.19 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.

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.18. It may also be viewed
online at


Changes in MySQL 8.0.19 (2020-01-13)

     * Account Management Notes

     * Audit Log Notes

     * Compilation Notes

     * Configuration Notes

     * Deprecation and Removal Notes

     * Error Handling

     * Function and Operator Notes


     * Keyring Notes

     * Logging Notes

     * Packaging Notes

     * SQL Syntax Notes

     * sys Schema Notes

     * Thread Pool Notes

     * X Plugin Notes

     * Functionality Added or Changed

     * Bugs Fixed

Account Management Notes

     * MySQL now enables administrators to configure user
       accounts such that too many consecutive login failures
       due to incorrect passwords cause temporary account
       locking. The required number of failures and the lock
       time are configurable per account, using the
       the CREATE USER and ALTER USER statements. See Password
       (Bug #27733694, Bug #90169)

Audit Log Notes

     * ANALYZE TABLE statements now produce read audit events.
       (Bug #29625461)

     * Audit log connect events now include any connection
       attributes passed by the client. Connection attribute
       logging is supported for new-style XML log file format
       and JSON format, but not old-style XML format. See Audit
       Log File Formats

Compilation Notes

     * Microsoft Windows: On Windows, the minimum version of
       CMake for builds from the command line is now 3.15. (Bug
       #30332632, Bug #96954)

Configuration Notes

     * New FPROFILE_GENERATE and FPROFILE_USE CMake options are
       available for experimenting with profile guided
       optimization (PGO) with GCC. See the cmake/fprofile.cmake
       in a MySQL source distribution for information about
       using them. These options have been tested with GCC 8 and
       9, and with Clang.
       Enabling FPROFILE_USE also enables WITH_LTO (link time
       optimization). (Bug #30089834, Bug #96314, Bug #30133324,
       Bug #96410, Bug #30164113, Bug #96486)

     * Innodb_system_rows_read, Innodb_system_rows_inserted,
       Innodb_system_rows_deleted status variables were added
       for counting row operations on InnoDB tables that belong
       to system-created schemas. The new status variables are
       similar to the existing Innodb_rows_read,
       Innodb_rows_inserted, Innodb_rows_deleted status
       variables, which count operations on InnoDB tables that
       belong to both user-created and system-created schemas.
       The new status variables are useful in replication
       environments where relay_log_info_repository and
       master_info_repository variables are set to TABLE,
       resulting in higher row operation counts on slaves due to
       operations performed on the slave_master_info,
       slave_replay_log_info, and slave_worker_info tables,
       which belong to the system-created mysql schema. For a
       valid comparison of master and slave row operation
       counts, operations on tables in system-created schemas
       can now be excluded using the count data provided by the
       new status variables.
       Thanks to Facebook for the contribution. (Bug #27724674)

Deprecation and Removal Notes

     * Setting the hash_join optimizer switch (see
       optimizer_switch system variable) no longer has any
       effect. The same applies with respect to the HASH_JOIN
       and NO_HASH_JOIN optimizer hints. Both the optimizer
       switch and the optimizer hint are now deprecated, and
       subject to removal in a future release of MySQL. (Bug

     * Support for the YEAR(2) data type was removed in MySQL
       5.7.5, leaving only YEAR and YEAR(4) as valid
       specifications for year-valued data. Because YEAR and
       YEAR(4) are semantically identical, specifying a display
       width is unnecessary, so YEAR(4) is now deprecated and
       support for it will be removed in a future MySQL version.
       Statements that include data type definitions in their
       output no longer show the display width for YEAR. This
       change applies to tables, views, and stored routines, and
       affects the output from SHOW CREATE and DESCRIBE
       statements, and from INFORMATION_SCHEMA tables.
       For DESCRIBE statements and INFORMATION_SCHEMA queries,
       output is unaffected for objects created in previous
       MySQL 8.0 versions because information already stored in
       the data dictionary remains unchanged. This exception
       does not apply for upgrades from MySQL 5.7 to 8.0, for
       which all data dictionary information is re-created such
       that data type definitions do not include display width.
       The (undocumented) UNSIGNED attribute for YEAR is also
       now deprecated and support for it will be removed in a
       future MySQL version.

Error Handling

     * Error messages regarding crash recovery for XA were
       revised to indicate XA context to distinguish them from
       non-XA crash recovery messages. (Bug #30578290, Bug

     * Previously, the server returned this error message for
       attempts to use LOAD DATA LOCAL with LOCAL capability
       disabled: The used command is not allowed with this MySQL
       version. This was misleading because the error condition
       is not related to the MySQL version. The server now
       returns an error code of ER_CLIENT_LOCAL_FILES_DISABLED
       and this message: Loading local data is disabled; this
       must be enabled on both the client and server side. (Bug
       #30375698, Bug #29377985, Bug #94396)

Function and Operator Notes

     * Previously, user-defined functions (UDFs) took no account
       of the character set or collation of string arguments or
       return values. In effect, string arguments and return
       values were treated as binary strings, with the
       implication that only string arguments containing
       single-byte characters could be handled reliably.
       UDF behavior is still the same by default, but the
       interface for writing UDFs has been extended to enable
       UDFs to determine the character set and collation of
       string arguments, and to return strings that have a
       particular character set and collation. These
       capabilities are optional for UDF writers, who may take
       advantage of them as desired. See User-Defined Function
       Character Set Handling
       Of the UDFs distributed with MySQL, those associated with
       the following features and extensions have been modified
       to take advantage of the new capabilities: MySQL
       Enterprise Audit, MySQL Enterprise Firewall, MySQL
       Enterprise Data Masking and De-Identification, MySQL
       Keyring (the general-purpose keyring UDFs only), and
       Group Replication. The modification applies only where it
       make sense. For example, a UDF that returns encrypted
       data is intended to return a binary string, not a
       character string.
       Character-set capabilities for UDFs are implemented using
       the mysql_udf_metadata server component service. For
       information about this service, see the MySQL Server
       Doxygen documentation, available at (search for
       s_mysql_mysql_udf_metadata and udf_metadata_imp). Source
       code for the MySQL Keyring UDFs is available in Community
       source distributions and may be examined as examples for
       third-party UDF writers who wish to modify their own UDFs
       to be character set-aware.


     * The INFORMATION_SCHEMA contains several new tables that
       expose role information:

          + ADMINISTRABLE_ROLE_AUTHORIZATIONS: Roles the current
            user can grant; see The INFORMATION_SCHEMA

          + APPLICABLE_ROLES: Roles applicable for the current

          + ENABLED_ROLES: Roles enabled within the current
            session; see The INFORMATION_SCHEMA ENABLED_ROLES

          + ROLE_COLUMN_GRANTS: Column privileges for roles for
            the current user; see The INFORMATION_SCHEMA
            ROLE_COLUMN_GRANTS Table

          + ROLE_ROUTINE_GRANTS: Routine privileges for roles
            for the current user; see The INFORMATION_SCHEMA
            ROLE_ROUTINE_GRANTS Table

          + ROLE_TABLE_GRANTS: Table privileges for roles for
            the current user; see The INFORMATION_SCHEMA
            ROLE_TABLE_GRANTS Table

Keyring Notes

     * A new SECRET key type is available that is intended for
       general-purpose storage of sensitive data using the MySQL
       keyring. The keyring encrypts and decrypts SECRET data as
       a byte stream upon storage and retrieval. The SECRET key
       type is supported by all keyring plugins. See Supported
       Keyring Key Types and Lengths

Logging Notes

     * The SIGUSR1 signal now causes the server to flush the
       error log, general query log, and slow query log. One use
       for SIGUSR1 is to implement log rotation without having
       to connect to the server (which to flush logs requires an
       account that has the RELOAD privilege). The server
       response to SIGUSR1 is a subset of the response to
       SIGHUP, enabling SIGUSR1 to be used as a more
       "lightweight" signal that flushes certain logs without
       the other SIGHUP effects such as flushing the thread and
       host caches and writing a status report to the error log.
       See Unix Signal Handling in MySQL

Packaging Notes

     * The zstd library bundled with MySQL has been upgraded
       from version 1.3.3 to 1.4.3. MySQL uses the zstd library
       to support connection compression. (Bug #30236685)

     * For package types for which OpenSSL shared libraries are
       included, they are now also included under lib/private if
       the package has private-to-MySQL libraries located there
       that need OpenSSL. (Bug #29966296)

SQL Syntax Notes

     * Important Change: MySQL now supports explicit table
       clauses and table value constructors according to the SQL
       standard. These have now been implemented, respectively,
       as the TABLE statement and the VALUES statement, each
       described in brief here:

          + TABLE table_name is equivalent to SELECT * FROM
            table_name, and can be used anywhere that the
            equivalent SELECT statement would be accepted; this
            includes joins, unions, INSERT ... SELECT
            statements, REPLACE statements, CREATE TABLE ...
            SELECT statements, and subqueries. TABLE also
            supports ORDER BY and LIMIT.

          + VALUES consists of the VALUES keyword followed by a
            series of row constructors (ROW()), separated by
            commas. It can be used to supply row values in an
            SQL-compliant fashion to an INSERT statement or
            REPLACE statement. For example, the following two
            statements are equivalent:
INSERT INTO t1 VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);

INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9);
            You can also select from a VALUES table value
            constructor just as you would a table, bearing in
            mind that you must supply a table alias when doing
            so. Using column aliases, you can also select
            individual columns, like this:
mysql> SELECT a,c FROM (VALUES ROW(1,2,3), ROW(4,5,6)) AS t(a,b,c);
| a | c |
| 1 | 3 |
| 4 | 6 |

            You can employ such SELECT statements in joins,
            unions, subqueries, and other constructs in which
            you normally expect to be able to use such
       For more information and examples, see TABLE Statement
       (, and
       VALUES Statement
       (, as
       well as INSERT ... SELECT Statement
       CREATE TABLE ... SELECT Statement
       JOIN Clause
       UNION Clause
       (, and
       (Bug #77639)

     * Previously, it was not possible to use LIMIT in the
       recursive SELECT
       part of a recursive common table expression (CTE). LIMIT
       is now supported in such cases, along with an optional
       OFFSET clause. An example of such a recursive CTE is
       shown here:
  SELECT CONCAT("x",cte.a) FROM qn
    WHERE LENGTH(cte.a) < 10
       Specifying LIMIT in this fashion can make execution of
       the CTE more efficient than doing so in the outermost
       SELECT, since only the requested number of rows is
       For more information, see Recursive Common Table
       (Bug #92857, Bug #28816906)

     * When CHECK constraints were implemented in MySQL 8.0.16,
       ALTER TABLE supported DROP CHECK and ALTER CHECK syntax
       as MySQL extensions to standard SQL for modifying check
       constraints, but did not support the more general (and
       SQL standard) DROP CONSTRAINT and ALTER CONSTRAINT syntax
       for modifying existing constraints of any type. That
       syntax is now supported; the constraint type is
       determined from the constraint name.

     * MySQL now supports aliases in the VALUES and SET clauses
       of INSERT INTO ... ON DUPLICATE KEY UPDATE statement for
       the row to be inserted and its columns. Consider a
       statement such as this one:
    VALUES (9,5), (7,7), (11,-1)
       Using the alias new for the inserted row, you can now
       rewrite the statement, referring back to the row alias in
       the ON DUPLICATE KEY UPDATE clause, like this:
    VALUES (9,5), (7,7), (11,-1) AS new
    ON DUPLICATE KEY UPDATE a = a + new.a - new.b;
       Using the same row alias, and, additionally, the column
       aliases m and n for the columns of the inserted row, you
       can omit the row alias and use only the column aliases,
       as shown here:
    VALUES (9,5), (7,7), (11,-1) AS new(m,n)
    ON DUPLICATE KEY UPDATE a = a + m - n;
       The row alias must be distinct from the table name;
       column aliases must be distinct from one another.
       for more information and examples.

sys Schema Notes

     * sys schema objects have been reimplemented not to invoke
       the deprecated sys.format_bytes(), sys.format_time(), and
       sys.ps_thread_id() stored functions. Instead, they invoke
       the equivalent built-in SQL functions implemented in
       MySQL 8.0.16 that format or retrieve Performance Schema
       data (see Changes in MySQL 8.0.16 (2019-04-25, General
       sys.format_bytes(), sys.format_time(), and
       sys.ps_thread_id() will be removed in a future MySQL
       version, so applications that use them should be adjusted
       to use the built-in functions instead, keeping in mind
       some minor differences between the sys functions and the
       built-in functions. See Performance Schema Functions

Thread Pool Notes

     * By default, the thread pool plugin tries to ensure a
       maximum of one thread executing in each group at any
       time. The default algorithm takes stalled threads into
       account and may temporarily permit more active threads.
       The plugin now implements a new
       thread_pool_max_active_query_threads system variable for
       controlling number of active threads per group. If
       thread_pool_max_active_query_threads is 0, the default
       algorithm applies. If
       thread_pool_max_active_query_threads is greater than 0,
       it places a limit on the number of active threads per
       group. See Thread Pool Operation

X Plugin Notes

     * X Plugin could not be compiled on Debian with GCC 9. The
       --no-as-needed linker option was added to provide a
       workaround for the issue. (Bug #30445201)

     * Using X Protocol to query the Information Schema table
       TRIGGERS could result in errors being returned or some
       rows not being returned. (Bug #30318917)

     * In MySQL 5.7.14, the mysqlx namespace parameter was
       introduced for X Protocol's StmtExecute request,
       replacing the xplugin parameter, which was therefore
       deprecated. X Plugin continued to support the deprecated
       xplugin namespace for backward compatibility. In MySQL
       8.0.19, the xplugin namespace has now been removed. If
       the xplugin namespace is used from this release on, an
       error message is returned as for an unknown namespace. X
       Plugin's Mysqlx_stmt_execute_xplugin status variable,
       which counted the number of StmtExecute requests received
       for the xplugin namespace, is no longer used from MySQL

Functionality Added or Changed

     * Microsoft Windows: Previously, the system (\!) command
       for the mysql command-line client worked only for Unix
       systems. It now works on Windows as well. For example,
       system cls or \! cls may be used to clear the screen.
       (Bug #11765690, Bug #58680)

     * JSON: When using JSON_SCHEMA_VALID() to specify a CHECK
       constraint on a table containing one or more JSON columns
       and experiencing a validation failure, MySQL now provides
       detailed information about the reasons for such failures.
       A new error
       implemented containing this information, which can be
       viewed in the mysql client by issuing SHOW WARNINGS when
       an INSERT statement is rejected by the server.
       For more information and examples, see
       JSON_SCHEMA_VALID() and CHECK constraints
       For more general information, see also CHECK Constraints

     * Display width specification for integer data types was
       deprecated in MySQL 8.0.17, and now statements that
       include data type definitions in their output no longer
       show the display width for integer types, with these

          + The type is TINYINT(1). MySQL Connectors make the
            assumption that TINYINT(1) columns originated as
            BOOLEAN columns; this exception enables them to
            continue to make that assumption.

          + The type includes the ZEROFILL attribute.
       This change applies to tables, views, and stored
       routines, and affects the output from SHOW CREATE and
       DESCRIBE statements, and from INFORMATION_SCHEMA tables.
       For DESCRIBE statements and INFORMATION_SCHEMA queries,
       output is unaffected for objects created in previous
       MySQL 8.0 versions because information already stored in
       the data dictionary remains unchanged. This exception
       does not apply for upgrades from MySQL 5.7 to 8.0, for
       which all data dictionary information is re-created such
       that data type definitions do not include display width.
       (Bug #30556657, Bug #97680)

     * Replication connections to a replication slave, and Group
       Replication connections for distributed recovery, now
       have full client side configuration options for the
       TLSv1.3 protocol. In MySQL releases where TLSv1.3 support
       was available but these configuration options were not
       available, if TLSv1.3 was used for these connection
       types, the client in the connection (the replication
       slave or the Group Replication joining member that
       initiated distributed recovery) could not be configured.
       This meant that the server in the connection (the
       replication master or the Group Replication existing
       member that was the donor for distributed recovery) had
       to permit the use of at least one TLSv1.3 ciphersuite
       that is enabled by default. From MySQL 8.0.19, you can
       use the configuration options to specify any selection of
       ciphersuites for these connections, including only
       non-default ciphersuites if you want.
       The new configuration options are as follows:

          + Group Replication system variables
            group_replication_recovery_tls_version and
            group_replication_recovery_tls ciphersuites.
            group_replication_recovery_tls_version specifies a
            list of permitted TLS protocols for connection
            encryption for the client instance (the joining
            member) in the distributed recovery connection.
            group_replication_recovery_tls ciphersuites
            specifies a list of permitted ciphersuites when
            TLSv1.3 is used for that connection.

          + A MASTER_TLS_CIPHERSUITES option on the CHANGE
            MASTER TO command, to specify a list of TLSv1.3
            ciphersuites permitted by the replication slave for
            the connection to the replication master. (The
            CHANGE MASTER TO command already had a
            MASTER_TLS_VERSION option to specify the permitted
            TLS protocol versions for the connection.)
       (Bug #29960735)

     * Debian packages now contain more general systemd support
       that better supports manual mysqld execution. (Bug
       #29702050, Bug #95163)

     * The Group Replication plugin interacts with MySQL Server
       using internal sessions to perform SQL API operations.
       Previously, these sessions counted towards the client
       connections limit specified by the max_connections
       server system
       variable. If the server had reached this limit when Group
       Replication was started or attempted to perform an
       operation, the operation was unsuccessful and Group
       Replication or the server itself might stop. From MySQL
       8.0.19, Group Replication's interactions with MySQL
       Server use a new component service that handles the
       internal sessions separately, which means that they do
       not count towards the max_connections
       limit and are not refused if the server has reached this
       limit. (Bug #29635001)

     * Duplicate key error information was extended to include
       the table name of the key. Previously, duplicate key
       error information included only the key value and key
       name. Thanks to Facebook for the contribution. (Bug
       #28686224, Bug #925308)

     * When the mysql client operates in interactive mode, the
       --binary-as-hex option now is enabled by default. In
       addition, output from the status (or \s) command includes
       this line when the option is enabled implicitly or
Binary data as: Hexadecimal
       To disable hexadecimal notation, use --skip-binary-as-hex
       (Bug #24432545)

     * MySQL now supports datetime literals with time zone
       offsets, such as '2019-12-11 10:40:30-05:00', '2003-04-14
       03:30:00+10:00', and '2020-01-01 15:35:45+05:30'; these
       offsets are respected but not stored when inserting such
       values into TIMESTAMP and DATETIME columns; that is,
       offsets are not displayed when retrieving the values.
       The supported range for a timezone offset is -14:00 to
       +14:00, inclusive. Time zone names such as 'CET' or
       'America/Argentina/Buenos_Aires', including the special
       value 'SYSTEM', are not supported in datetime literals.
       In addition, in this context, a leading zero is required
       for an hour value less than 10, and MySQL rejects the
       offset '-00:00' as invalid.
       Datetime literals with time zone offsets can also be used
       as parameter values in prepared statements.
       As part of this work, the allowed range of numeric values
       for the time_zone system variable has been changed, so
       that it is now also -14:00 to +14:00, inclusive.
       For additional information and examples, see The DATE,
       DATETIME, and TIMESTAMP Types
       and MySQL Server Time Zone Support
       (Bug #83852, Bug #25108148)

     * From MySQL 8.0.19, compression is 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, X Protocol
       announces support for the deflate, lz4, and zstd
       compression algorithms. You can disallow any of these
       algorithms by setting the new
       mysqlx_compression_algorithms system variable to include
       only the ones you permit. X Protocol always allows
       uncompressed connections if the client does not request
       compression during capability negotiation. Note that X
       Protocol's list of permitted compression algorithms
       operates independently of the list of compression
       algorithms announced by MySQL Server, and X Protocol does
       not fall back to using MySQL Server's compression
       settings. You can monitor the effects of message
       compression for X Protocol using new X Plugin status

     * For multithreaded slaves (replication slaves on which
       slave_parallel_workers is set to a value greater than 0),
       setting slave_preserve_commit_order=1 ensures that
       transactions are executed and committed on the slave in
       the same order as they appear in the slave's relay log,
       preserving the same transaction history on the slave as
       on the master. Previously, this setting required binary
       logging and slave update logging to be enabled on the
       slave, with the associated execution costs and disk space
       requirements. Now, slave_preserve_commit_order=1 can be
       set on a slave with no binary log and no slave update
       logging. This enables you to preserve commit order on the
       slave, and avoid gaps in the sequence of transactions,
       without the overhead of binary logging.
       A limitation to preserving the commit order on the slave
       can occur if statement-based replication is in use, and
       both transactional and non-transactional storage engines
       participate in a non-XA transaction that is rolled back
       on the master. Normally, non-XA transactions that are
       rolled back on the master are not replicated to the
       slave, but in this particular situation, the transaction
       might be replicated to the slave. If this does happen, a
       multithreaded slave without binary logging does not
       handle the transaction rollback, so the commit order on
       the slave diverges from the relay log order of the
       transactions in that case.

     * The MySQL 8.0.18 release introduced the ability to
       specify a PRIVILEGE_CHECKS_USER account for a replication
       channel (using a CHANGE MASTER TO statement), against
       which MySQL makes privilege checks when replicated
       transactions are applied. The use of a
       PRIVILEGE_CHECKS_USER account helps secure a replication
       channel against the unauthorized or accidental use of
       privileged or unwanted operations. The use of row-based
       binary logging is strongly recommended when replication
       channels are secured with privilege checks.
       In MySQL 8.0.19, a new setting REQUIRE_ROW_FORMAT is
       added for replication channels, which makes the channel
       accept only row-based replication events. You can specify
       REQUIRE_ROW_FORMAT using a CHANGE MASTER TO statement to
       enforce row-based binary logging for a replication
       channel that is secured with privilege checks, or to
       increase the security of a channel that is not secured in
       this way. By allowing only row-based replication events,
       REQUIRE_ROW_FORMAT prevents the replication applier from
       taking actions such as creating temporary tables and
       executing LOAD DATA INFILE requests, which protects the
       replication channel against some known attack vectors.
       Row-based binary logging (binlog_format=ROW) must be used
       on the replication master when REQUIRE_ROW_FORMAT is set.
       Group Replication already requires row-based binary
       logging, so from MySQL 8.0.19, Group Replication's
       channels are automatically created with
       REQUIRE_ROW_FORMAT set, and you cannot change the option
       for those channels. The setting is also applied to all
       Group Replication channels on upgrade.
       mysqlbinlog has a new --require-row-format option, which
       enforces row-based replication events for mysqlbinlog's
       output. The stream of events produced with this option
       would be accepted by a replication channel that is
       secured using the REQUIRE_ROW_FORMAT option.

     * MySQL uses delimiter strings when constructing tablespace
       names and file names for table partitions. A "#p#"
       delimiter string precedes partition names, and an "#sp#"
       delimiter string precedes subpartition names, as shown:

       Historically, delimiter strings have been uppercase (#P#
       and #SP#) on case-sensitive file systems such as Linux,
       and lowercase (#p# and #sp#) on case-insensitive file
       systems such as Windows. To avoid issues when migrating
       data directories between case-sensitive and
       case-insensitive file systems, delimiter strings are now
       lowercase on all file systems. Uppercase delimiter
       strings are no longer used.
       Additionally, partition tablespace names and file names
       generated based on user-specified partition or
       subpartition names, which can be specified in uppercase
       or lowercase, are now generated (and stored internally)
       in lowercase regardless of the lower_case_table_names
       setting to ensure case-insensitivity. For example, if a
       table partition is created with the name PART_1, the
       tablespace name and file name are generated in lowercase:

       During upgrade, MySQL now checks and modifies if

          + Partition file names on disk and in the data
            dictionary to ensure lowercase delimiters and
            partition names.

          + Partition metadata in the data dictionary for
            related issues introduced by previous bug fixes.

          + InnoDB statistics data for related issues introduced
            by previous bug fixes.
       During tablespace import operations, partition tablespace
       file names on disk are checked and modified if necessary
       to ensure lowercase delimiters and partition names.
       References: See also: Bug #26925260, Bug #29823032, Bug
       #30012621, Bug #29426720, Bug #30024653.

     * Support was added for efficient sampling of InnoDB data
       for the purpose of generating histogram statistics. The
       default sampling implementation used by MySQL when
       storage engines do not provide their own requires a full
       table scan, which is costly for large tables. The InnoDB
       sampling implementation improves sampling performance by
       avoiding full table scans. The sampled_pages_read and
       sampled_pages_skipped INNODB_METRICS counters can be used
       to monitor sampling of InnoDB data pages. See Histogram
       Statistics Analysis

Bugs Fixed

     * Important Change: Character set resolution has been
       changed for the following string functions:

          + REPLACE(str, from_str, to_str)

          + SUBSTRING_INDEX(str, delim, count)

          + TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM]
       Previously, character set information for all arguments
       to these functions was aggregated, which could lead to
       results that were not well formed. This also caused
       issues with LPAD(), which assumes that both input and
       output are well formed. Now each of the three listed
       functions always uses the character set employed by str,
       and converts all other arguments to this character set at
       execution time; if any such conversion fails, the
       function returns an error. (Bug #30114420)
       References: This issue is a regression of: Bug #28197977.

     * Important Change: Subquery materialization no longer
       requires strict matching of inner and outer types.
       Different types can now be materialized when one of the
       following conditions is true:

          + The inner type is numeric (since there is always a
            way to cast the outer type to a number)

          + The inner type is temporal (since there is always a
            way to cast the outer type to a temporal)

          + Both types are strings
       (Bug #13960580)

     * NDB Cluster: Password masking was incomplete for some NDB
       logging options. (Bug #97335, Bug #30453137)

     * InnoDB: Initialization of certain internal data
       structures at startup depend on internal variables
       derived from the max_connections setting. InnoDB failed
       to resize the internal data structures when the
       max_connections setting was modified after startup using
       SET PERSIST. (Bug #30628872)

     * InnoDB: os_file_get_parent_dir warnings were encountered
       when compiling MySQL with GCC 9.2.0. (Bug #30499288, Bug

     * InnoDB: An attempt to access a large object (LOB) value
       using a null reference raised an assertion failure. To
       prevent this issue form occurring, a check was added to
       determine if LOB references are null before they are
       accessed. (Bug #30499064)

     * InnoDB: An assertion failure occurred after upgrading the
       data directory. Prepared XA transaction were still
       present, which prevented undo tablespaces from being
       upgraded. Undo tablespaces containing prepared
       transaction changes must remain active until all prepared
       XA transactions are committed or rolled back.
       Prepared XA transactions also prevented the completion of
       an explicit undo tablespace truncation operation after a
       restart. (Bug #30489497)

     * InnoDB: Attempting to upgrade a MySQL 5.7 instance on
       Linux with uppercase table names (partitioned or
       otherwise) to MySQL 8.0 on macOS raised an assertion
       failure. Partition file format changes in MySQL 8.0
       prevented migration of the data directory to a different
       platform, and the lower_case_table_names setting was
       changed at upgrade time, which can cause an upgrade
       failure. Instead of a failure occurring under these
       circumstances, an error is now reported. (Bug #30450968,
       Bug #30450979)

     * InnoDB: On macOS, a failure occurred when attempting to
       upgrade a MySQL 5.7 instance with uppercase table names
       to MySQL 8.0. Uppercase table names were not normalized
       to lowercase. The following errors were reported: Table
       is not found in InnoDB dictionary and Error in fixing SE
       data errors. (Bug #30450944)

     * InnoDB: On Windows, a failure occurred when attempting to
       upgrade a MySQL 5.7 instance with uppercase partitioned
       table names to MySQL 8.0. Opening the table returned a
       null pointer, which caused a segmentation fault when
       closing the table. (Bug #30450918)

     * InnoDB: On Windows, a mysqld exception was raised when
       attempting to upgrade a MySQL 5.7 instance with uppercase
       partitioned table names to MySQL 8.0. (Bug #30447790)

     * InnoDB: On Windows, a failure occurred when attempting to
       upgrade a MySQL 5.7 instance containing general
       tablespace defined with an uppercase name to MySQL 8.0.
       The following errors were reported: Error in fixing SE
       data and Failed to Populate DD. (Bug #30446798)

     * InnoDB: Introduction of local minitransactions (mtrs) in
       LOB-related code resulted in an assertion failure during
       recovery. (Bug #30417719)

     * InnoDB: A failure occurred when attempting to upgrade a
       MySQL 5.7 instance on Windows with uppercase partitioned
       table names to MySQL 8.0 on Linux. Partition file format
       changes in MySQL 8.0 prevented migration of the data
       directory to a different platform. Instead of a failure,
       an error is now reported. (Bug #30411118)

     * InnoDB: Updating the same compressed LOB data repeatedly
       caused the tablespace file to increase in size. (Bug

     * InnoDB: When the temptable_max_ram limit was reached, the
       TempTable storage engine incorrectly reported an
       out-of-memory error instead of falling back to disk-based
       storage. (Bug #30314972, Bug #96893)

     * InnoDB: After importing an encrypted table and restarting
       the server, the following error was returned when
       attempting to access the table: ERROR 3185 (HY000): Can't
       find master key from keyring, please check in the server
       log if a keyring plugin is loaded and initialized
       successfully. The tablespace key was not written to disk
       after it was encrypted with the destination master key.
       (Bug #30313734)

     * InnoDB: The internal InnoDB
       dict_create_foreign_constraints() function that parsed
       SQL statements and performed foreign key related DDL
       checks was removed. The function became redundant with
       introduction of the data dictionary in MySQL 8.0 and the
       subsequent relocation of foreign key related DDL checks
       to the SQL layer.
       Removal of the dict_create_foreign_constraints() function
       also addressed the following foreign key issues:

          + Spaces around dots (".") in a fully qualified
            referenced table name were not permitted by the
            InnoDB parser.

          + Adding a foreign key and removing partitioning in
            the same ALTER TABLE statement was not permitted.
            The InnoDB parser did not detect that the new table
            version was no longer partitioned.

          + A foreign key constraint could not reference a table
            inside a schema named "AUX". The function that
            parsed referenced table names did not recognize that
            special names such as AUX are encoded.

          + Conditional comments in foreign key definitions were
       Additionally, a check was added to the SQL layer to
       detect attempts to create multiple foreign keys of the
       same name on a table at an early stage in the execution
       of an ALTER TABLE statement. (Bug #30287895, Bug
       #22364336, Bug #28486106, Bug #28703793, Bug #16904122,
       Bug #92567, Bug #11754659, Bug #46293)

     * InnoDB: A comparison function found two records to be
       equal when attempting to merge non-leaf pages of a
       spatial index. The function was unable to handle this
       unexpected condition, which resulted in a long semaphore
       wait and an eventual assertion failure. (Bug #30287668)

     * InnoDB: A locally acquired latch required for freeing a
       large object (LOB) page could have caused a deadlock if a
       subsequent caller attempted to acquire a latch for the
       same page before the page was freed. Similarly, a latch
       taken on a compressed or uncompressed LOB during a
       rollback related operation could have caused a deadlock
       due to a latching order issue. (Bug #30258536)
       References: This issue is a regression of: Bug #29846292.

     * InnoDB: A race condition between a purge thread that was
       purging a compressed LOB page and an update thread that
       is using a delete-marked record caused an assertion
       failure. (Bug #30197056)

     * InnoDB: Importing a tablespace from a location outside of
       the data directory raised an assertion failure. (Bug

     * InnoDB: Importing a tablespace with a data directory
       definition that did not match the destination data
       directory returned an insufficiently descriptive schema
       mismatch error. (Bug #30190199)

     * InnoDB: A purge operation failed when attempting to purge
       a LOB value larger than the buffer pool. (Bug #30183982)

     * InnoDB: Update operations that moved externally stored
       LOB data to inline storage failed to mark the old LOB
       data as purgeable. (Bug #30178056, Bug #96466)

     * InnoDB: Index key part sort order information was not
       stored to the .cfg metadata file used by ALTER TABLE ...
       IMPORT TABLESPACE operations. The index key part sort
       order was therefore assumed to be ascending, which is the
       default. As a result, records could be sorted in an
       unintended order if one table involved in the import
       operation is defined with a DESC index key part sort
       order and the other table is not. To address this issue,
       the .cfg file format was updated to include index key
       part sort order information. (Bug #30128418)

     * InnoDB: Criteria used by the btr_cur_will_modify_tree()
       function, which detects whether a modifying record needs
       a modifying tree structure, was insufficient. (Bug

     * InnoDB: Startup was slow on instances with a large number
       of tables due the tablespace file scan that occurs at
       startup to retrieve space IDs. A multithreaded scan was
       only initiated if the number of tablespace files exceed
       50,000, and three tablespace pages were read to retrieve
       a space ID. To improve startup times, additional threads
       are now allocated for the tablespace file scan, and only
       the first tablespace page is read to retrieve a space ID.
       If a space ID is not found on the first page of the
       tablespace, three pages are read to determine the space
       ID, as before. (Bug #30108154, Bug #96340)

     * InnoDB: Startup failed on a case insensitive file system
       with an error indicating that multiple files were found
       for the same tablespace ID. A file path comparison did
       not recognize that innodb_data_home_dir and datadir paths
       were the same due to the paths having different
       lettercases. (Bug #30040815)

     * InnoDB: A storage engine error occurred when accessing
       the mysql.innodb_index_stats and mysql.innodb_table_stats
       persistent optimizer statistics tables after upgrading a
       MySQL 8.0.13 instance on Linux with partitioned tables
       and a lower_case_table_name=1 setting to MySQL 8.0.14 or
       MySQL 8.0.15. The persistent optimizer statistics tables
       contained duplicate entries. (Bug #30012621)
       References: This issue is a regression of: Bug #26925260.

     * InnoDB: CREATE TABLESPACE failed with an error indicating
       that the tablespace already exists. The error was due to
       the failure of a preceding CREATE TABLESPACE operation
       where the DDL failed but related changes were not rolled
       back due to rollback being disabled prior to transaction
       commit. Rollback is now disabled after the transaction
       commits successfully. (Bug #29959193, Bug #95994)

     * InnoDB: Changed pages belonging to imported tablespaces
       were not being tracked. (Bug #29917343)

     * InnoDB: Renaming of full-text search auxiliary tables
       during upgrade failed due to a tablespace name conflict
       when upgrading from MySQL 5.7 to MySQL 8.0 on a
       case-insensitive file system. (Bug #29906115)

     * InnoDB: Rollback of an INSERT operation that inserted a
       LOB value larger than a buffer pool caused a deadlock.
       (Bug #29846292)

     * InnoDB: A code regression was addressed by prohibiting
       unnecessary implicit to explicit secondary index lock
       conversions for session temporary tables. (Bug #29718243)

     * InnoDB: A tablespace import operation raised an assertion
       when the cursor was positioned on a corrupted page while
       purging delete-marked records. Instead of asserting when
       encountering a corrupted page, the import operation is
       now terminated and an error is reported. (Bug #29454828,
       Bug #94541)

     * InnoDB: Delete marked rows were able to acquire an
       external read lock before a partial rollback was
       completed. The external read lock prevented conversion of
       an implicit lock to an explicit lock during the partial
       rollback, causing an assertion failure. (Bug #29195848)

     * InnoDB: Throughput stalled under a heavy workload with a
       small max_io_capacity setting, a single page cleaner
       thread, and multiple buffer pool instances. (Bug

     * InnoDB: After a server exit that occurred while an undo
       tablespace truncation operation was in progress, warning
       messages were printed at startup stating that doublewrite
       pages could not be restored for undo tablespace pages.
       The warning messages are no longer printed for undo
       tablespaces that are being truncated. (Bug #28590016)

     * InnoDB: In read-only mode (innodb_read_only=ON), SHOW
       CREATE TABLE output did not include information about
       foreign key constraints. (Bug #21966795, Bug #78754)

Edited 2 time(s). Last edit at 01/13/2020 08:02AM by Bjørn Munch.

Options: ReplyQuote

Written By
MySQL Community Server 8.0.19 has been released, part 1/2
January 13, 2020 06:57AM

Sorry, you can't reply to this topic. It has been closed.

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.