MySQL Community Server 8.0.19 has been released, part 1/2
Posted by: Bjørn Munch
Date: January 13, 2020 06:57AM
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
http://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
For information on installing MySQL 8.0.19 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.19 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.19 is also available from our repository for Linux
platforms, go here for details:
https://dev.mysql.com/downloads/
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.19 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.18. It may also be viewed
online at
http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html
Enjoy!
Edited 2 time(s). Last edit at 01/13/2020 08:02AM by Bjørn Munch.
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
http://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
For information on installing MySQL 8.0.19 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.19 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.19 is also available from our repository for Linux
platforms, go here for details:
https://dev.mysql.com/downloads/
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.19 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.18. It may also be viewed
online at
http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html
Enjoy!
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 * INFORMATION_SCHEMA 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 FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements. See Password Management (https://dev.mysql.com/doc/refman/8.0/en/password-management.html). (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 (https://dev.mysql.com/doc/refman/8.0/en/audit-log-file-formats.html). 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 #30471809) * 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 #97743) * 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 (https://dev.mysql.com/doc/refman/8.0/en/adding-udf.html#udf-charset-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 https://dev.mysql.com/doc/index-other.html (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. INFORMATION_SCHEMA Notes * The INFORMATION_SCHEMA contains several new tables that expose role information: + ADMINISTRABLE_ROLE_AUTHORIZATIONS: Roles the current user can grant; see The INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS Table (https://dev.mysql.com/doc/refman/8.0/en/administrable-role-authorizations-table.html). + APPLICABLE_ROLES: Roles applicable for the current user; see The INFORMATION_SCHEMA APPLICABLE_ROLES Table (https://dev.mysql.com/doc/refman/8.0/en/applicable-roles-table.html). + ENABLED_ROLES: Roles enabled within the current session; see The INFORMATION_SCHEMA ENABLED_ROLES Table (https://dev.mysql.com/doc/refman/8.0/en/enabled-roles-table.html). + ROLE_COLUMN_GRANTS: Column privileges for roles for the current user; see The INFORMATION_SCHEMA ROLE_COLUMN_GRANTS Table (https://dev.mysql.com/doc/refman/8.0/en/role-column-grants-table.html). + ROLE_ROUTINE_GRANTS: Routine privileges for roles for the current user; see The INFORMATION_SCHEMA ROLE_ROUTINE_GRANTS Table (https://dev.mysql.com/doc/refman/8.0/en/role-routine-grants-table.html). + ROLE_TABLE_GRANTS: Table privileges for roles for the current user; see The INFORMATION_SCHEMA ROLE_TABLE_GRANTS Table (https://dev.mysql.com/doc/refman/8.0/en/role-table-grants-table.html). 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 (https://dev.mysql.com/doc/refman/8.0/en/keyring-key-types.html). 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 (https://dev.mysql.com/doc/refman/8.0/en/unix-signal-response.html). 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 statements. For more information and examples, see TABLE Statement (https://dev.mysql.com/doc/refman/8.0/en/table.html), and VALUES Statement (https://dev.mysql.com/doc/refman/8.0/en/values.html), as well as INSERT ... SELECT Statement (https://dev.mysql.com/doc/refman/8.0/en/insert-select.html), CREATE TABLE ... SELECT Statement (https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html), JOIN Clause (https://dev.mysql.com/doc/refman/8.0/en/join.html), UNION Clause (https://dev.mysql.com/doc/refman/8.0/en/union.html), and Subqueries (https://dev.mysql.com/doc/refman/8.0/en/subqueries.html). (Bug #77639) * Previously, it was not possible to use LIMIT in the recursive SELECT (https://dev.mysql.com/doc/refman/8.0/en/select.html) 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: WITH RECURSIVE cte AS ( SELECT CAST("x" AS CHAR(100)) AS a FROM DUAL UNION ALL SELECT CONCAT("x",cte.a) FROM qn WHERE LENGTH(cte.a) < 10 LIMIT 3 OFFSET 2; ) SELECT * FROM cte; 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 generated. For more information, see Recursive Common Table Expressions (https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive). (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: INSERT INTO t VALUES (9,5), (7,7), (11,-1) ON DUPLICATE KEY UPDATE a = a + VALUES(a) - VALUES(b); 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: INSERT INTO t 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: INSERT INTO t 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. See INSERT ... ON DUPLICATE KEY UPDATE Statement (https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html), 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 Availability)). 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 (https://dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html). 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 (https://dev.mysql.com/doc/refman/8.0/en/thread-pool-operation.html). 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 8.0.19. 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 ER_JSON_SCHEMA_VALIDATION_ERROR_WITH_DETAILED_REPORT is 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 (https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html#json-validation-functions-constraints). For more general information, see also CHECK Constraints (https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html). * 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 exceptions: + 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 (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_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 (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_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 explicitly: 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 (https://dev.mysql.com/doc/refman/8.0/en/datetime.html), and MySQL Server Time Zone Support (https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html). (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 variables. * 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: schema_name.table_name#p#partition_name#sp#subpartition_name table_name#p#partition_name#sp#subpartition_name.ibd 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: schema_name.table_name#p#part_1 table_name#p#part_1.ibd During upgrade, MySQL now checks and modifies if necessary: + 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 (https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html#analyze-table-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] str) 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 #97466) * 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 #30353812) * 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 ignored. 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 #30190227) * 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 #30113362) * 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 #29029294) * 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.
Subject
Views
Written By
Posted
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.