MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
The MySQL 8.0.19 Maintenance Release is Generally Available

The MySQL Development team is very happy to announce that MySQL 8.0.19 is now available for download at dev.mysql.com. In addition to bug fixes there are a few new features added in this release.  Please download 8.0.19 from dev.mysql.com or from the MySQL  YumAPT, or SUSE repositories. The source code is available at GitHub. You can find the full list of changes and bug fixes in the 8.0.19 Release Notes. Here are the highlights. Enjoy!

InnoDB ReplicaSet

Following InnoDB Cluster as our first, fully integrated MySQL HA solution based on Group Replication, InnoDB ReplicaSet delivers another complete solution, this time based on MySQL Replication. The basic idea for InnoDB ReplicaSet is to do the same for classic MySQL Replication as InnoDB Cluster did for Group Replication. We take a strong technology that is very powerful but can be complex, and provide an easy-to-use AdminAPI for it in the MySQL Shell.

In just a few easy to use Shell commands, a MySQL Replication database architecture can be configured from scratch, including data provisioning using CLONE, setting up replication and performing manual switchover/failover. MySQL Router understands the topology and will automatically load balance/redirect traffic.

Router

Add support for ReplicaSet in Router (WL#13188) This work by Andrzej Religa extends the MySQL Router to understand the concept of InnoDB ReplicaSets. This means loading and caching the topology information from the metadata schema as well as regularly updating the status information of the setup in order to perform the correct routing operations.

Handle the metadata upgrade in the Router (WL#13417) This work by Andrzej Religa  extends the Router to understand and properly handle the “0.0.0” version and the “metadata_upgrade_in_progress” lock.

New bootstrap option (–account) to allow reuse of an existing account  (WL#13177) This work by Pawel Mroszczyk adds a startup option to specify the account the router should use when talking to the server. The router needs a server account to be able to talk to the InnoDB Cluster. Until now, this account is always automatically created during bootstrapping.

SQL

Implement ALTER TABLE … DROP/ALTER CONSTRAINT syntax (WL#12798) This work by Praveenkumar Hulakund adds the SQL DROP CONSTRAINT and ALTER CONSTRAINT clauses to the ALTER TABLE statement. This work supplements WL#929 where the SQL  CHECK CONSTRAINT clauses were added to the CREATE TABLE and ALTER TABLE statements and fixes Bug#3742.

Table with JSON Schema validation constraint should return error for concrete row (WL#13195) This work by Praveen Hulakund adds an SQL condition in the Diagnostics area with an error code pinpointing the schema violation in the form of “The JSON document location X failed requirement Y at JSON Schema location Z”. See also WL#13196 below.

TIMESTAMP/DATETIME values can include timezone details (WL#10828) This work by Martin Hansson makes it possible to include time zone details along with the TIMESTAMP value.  Without explicit time zone information or offset, there might exist date/times which in some time zones that are not well defined, like when Daylight Saving Time changes the time backwards. This is a feature request from Booking.com (Bug#83852).

Support LIMIT in recursive common table expression (WL#12534) This work by Guilhem Bichot adds support for LIMIT in CTEs. The LIMIT is on the total number of rows in the CTE, not on the number of rows that each iteration may produce. See also Bug#92857.

Implement table value constructors: VALUES  (WL#10358) This work by Thomas Aven implements standard SQL syntax for table value constructors. But due to a conflict with the VALUES() function, a MySQL non-standard feature, we decided to use the SQL standard verbose form of the table value constructors:  VALUES ROW(1, 2),… See also Bug#77639.

Referencing old/new row in INSERT … ON DUPLICATE KEY UPDATE  (WL#6312) This work by Thomas Aven extends the INSERT … VALUES/SET … ON DUPLICATE KEY UPDATE syntax to make it possible to declare an alias for the new row and columns in that row, and refer to those aliases in the UPDATE expression. The intention with this new feature is to be able to replace VALUES(<expression>) clauses with row and column alias names.

Optimizer

Enhance group-by loose index scan  (WL#13066) This work by Sergey Gluhov improves the loose index scan (LIS) used for GROUP BY queries by lifting the current limitation on  the number of infix ranges (range on non-grouping column) that can be present in the query. This improvement will make the optimizer choose LIS for more queries. This feature is based on a contribution by Facebook (Bug#90951).

BKA in iterator executor (WL#13002) This work by Steinar H. Gunderson implements BKA (Batch Key Access) in the iterator executor. For now,  only inner joins are supported, other join types will be added later.

Information Schema

INFORMATION_SCHEMA views for roles (WL#10895) This work by Gopal Shankar implements SQL Standard Information Schema views for SQL Roles. The APPLICABLE_ROLES view shows the roles that are applicable for the current user. The ADMINISTRABLE_ROLE_AUTHORIZATIONS view shows the roles that are applicable for the current user, which can also be granted to other users. The ENABLED_ROLES view shows the roles that are enabled for the current user. The ROLE_TABLE_GRANTS view shows the table grants for the roles that are enabled for the current user. The ROLE_COLUMN_GRANTS view shows the column grants for the roles that are enabled for the current user. And finally, the ROLE_ROUTINE_GRANTS view shows the routine grants for the roles that are enabled for the current user.

Security

FAILED_LOGIN_ATTEMPTS/PASSWORD_LOCK_TIME counters per user  (WL#13515) This work by Georgi Kodinov 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 CREATE USER Password-Management Options.

Store secret data in keyring  (WL#12859) This work by Harin Vadodaria enhances the existing keyring plugins to support a new type of data called SECRET. This allows users to store, retrieve, and manage any opaque data in the keyring (max 16K). The keyring backend will not interpret the secret but simply treat it as a BLOB.  This functionality will typically be used to store secret data such as passwords and certificates.

InnoDB

InnoDB: Support for sampling table data for generating histograms (WL#8777) This work by Darshan M N implements the handler API defined in WL#9127 by sampling data pages from the clustered index and providing the records stored in these pages.

Innodb: Case insensitive names for partition tables (WL#13352) This work by Debarun Banerjee solves user issues related to alphabetical case differences in partition name and separators. The solution is to always handle partition name, partition separator (#p#), sub-partition name and sub-partition separator (#sp#) in a case insensitive manner so that the difference in case doesn’t matter for file names on disk and metadata stored in the data dictionary. This includes handling of partitioned tablespace file names during import.

Replication

Configure replication applier to require row-based replication (WL#12968)  This work by Pedro Gomes ensures that a replication channel only accepts row-based replication. This allows restricting the type of instruction executed on the slave, and as a side effect, limits the number of privileges a user needs when associated to the replication applier.

MTS: slave-preserve-commit-order when log-slave-updates/binlog is disabled  (WL#7846) This work by Hemant Dangi implements applier commit order, even when the worker threads are not logging the changes to the replicas binary log (–log-slave-updates=off). The main motivation for this is to be able to turn on parallel replication on a replica that has no binary log enabled without having different commit histories between the master and the slave. In other words, the transaction commit history, as observed in the replication stream, is preserved because the applier executes incoming transactions in parallel but commits them in the order that they appear in the stream.

Support for TLS 1.3 in Asynchronous Replication  (WL#13392) This work by Nuno Carvalho supplements previous work to support TLS 1.3 in the connections between MySQL clients and servers (WL#12361) by adding support for TLS 1.3 to Replication as well. This work implements the MASTER_TLS_CIPHERSUITES option on CHANGE MASTER command, the group_replication_recovery_tls_version plugin option and the group_replication_recovery_tls_ciphersuites plugin option.

X Protocol

Connection Compression  (WL#9252) This work by Lukasz Kotula implements client-server data compression over the X Protocol. The compression algorithm is negotiable based on server capabilities.

Zstd compression (WL#13442) This work by Grzegorz Szwarc implements zstd compression in the X Protocol. WL#9252 added deflate and lz4 compression support to xprotocol. The X Protocol now supports three compression algorithms: deflate, lz4, and zstd. The zstd algorithm has been found to perform better than deflate and has a compression ratio much better than lz4.

Add schema validation to the create collection method (WL#12965) This work by Tomasz Stepniak implements a document schema validator in the X Plugin. From the DevAPI perspective,  the user calls “var coll = schema.createCollection(“mycollection”, {validator: validationDoc})”.  The createCollection() is implemented in the X Plugin and this implementation is extended with a call to the JSON_SCHEMA_VALID(<json_schema>, <json_document>) function introduced by WL#11999.

Collection with json-schema validation must return error for concrete row (WL#13196) This work by Tomasz Stepniak extends WL#12965 by identifying the exact location where a document violates a schema constraint. See WL#13195 above.

MTR testsuite

Move testcases in binlog, sys_vars, and funcs_1 suites that needs MyISAM to a separate .test file (WL#13410 and WL#13241) This work by Pooja Lamba moves the sections that need MyISAM to a separate .test file. This allows the MTR test suite to run on a server that is built without the MyISAM engine.

Other

Make mysql command line tool’s –binary-as-hex be on by default for interactive terminals  (WL#13038) This work by Georgi Kodinov enables the option –binary-as-hex by default for command line tools running in “interactive mode”. The current default of –binary-as-hex (OFF) is kept for non-interactive runs. Historically, the MySQL command line tools have asked the server to convert all the data it receives to text so it can display them. But for some data (e.g. GEOMETRY data types) the conversion spawns binary strings that might contain non-printable symbols which can garble certain terminals. This is avoided by printing the hex dump instead of text.

Specifying Character Sets in the UDF API (WL#12370) This work by Rahul Sisondia extends the User Defined Functions (UDF) API to let the UDF creator specify which character sets to use in the arguments and the return value. Previously the UDF API simply assumed ASCII for both string arguments and the return value.

Sys Schema, Replace Stored Functions With Native Functions (WL#13439) This work by Chris Powers replaces stored functions with performance schema native functions in SYS Schema stored programs. The performance schema native functions are: format_bytes(), format_pico_time(), ps_current_thread_id(), and ps_thread_id(connection_id). The original SYS Schema functions are deprecated and will be removed in some future release.

Enable the “system” mysql command line command for windows  (WL#13391) This work by Georgi Kodinov enables the system command in the MySQL command line tool on Windows.  The system command  executes its arguments as an OS command and displays the result. It has been working for Linux in the past and since the MSVC CRT now supports all the necessary APIs it is time to enable it for Windows as well. This fixes Bug#58680.

Component service to add “admin” session (WL#13378) This work by Georgi Kodinov adds a new component service to allow its caller to create a special kind of SQL session that is not a subject to the max_connection limit. This is done to ensure that internal mechanisms are not limited by max_connections.  For example, group replication cannot operate when the server hits max_connections limit (see Bug#94987).

Add more information to Duplicate Key Error (WL#12589) This work by Jens Even Blomsøy extends the error information given in cases of DUP_ENTRY_KEY to include the table name along with the key name and key value in the error message. This work is based on a contribution from Facebook (Bug#92530). See also Bug#47207.

Use signal SIGUSR1 to flush logs  (WL#13689) This work by Jens Even Blomsøy redefines signal SIGUSR1 to be a light version of SIGHUP. The SIGUSR1 will cause the server to flush error log, general log and slow query log, but not send the MySQL status report.

Split the errmgs-utf8.txt file  (WL#13423) This work by Tatjana Nurnberg splits the errmgs-utf8.txt file into one file for messages to clients and another for messages to the error log (messages_to_clients.txt and messages_to_error_log.txt). This is a code refactoring helping developers to distinguish between client messages and error log messages.

Deprecation and Removal

Deprecate display width and UNSIGNED for the YEAR datatype  (WL#13537)  This work by Jon Olav Hauglid deprecates the syntax YEAR(4). Use the equivalent YEAR instead. This work also deprecates the use of UNSIGNED with YEAR. UNSIGNED is not documented to work with YEAR and has no effect.

Remove integer display width from SHOW CREATE output  (WL#13528)   This work by Jon Olav Hauglid changes SHOW CREATE to not output integer display width unless ZEROFILL is also used. Without ZEROFILL, integer display width has no effect. This work is a logical consequence of deprecating the display width attribute for integer types (WL#13127).

XProtocol: Removal of the deprecated namespace of Mysqlx.Sql.StmtExecute message (WL#13057) This work by Grzegorz Szwarc removes the namespace xplugin from the list of supported namespaces. Thus, the xplugin namespace becomes an unknown identifier and any administrative commands sent within this namespace will not be handled. If the user continues to use this namespace, they will be notified by a standard error message. The xplugin namespace in the Mysqlx.Sql.StmtExecute message has been deprecated since 5.7.14.

Thank you for using MySQL!