The MySQL Development team is very happy to announce that MySQL 8.0.17 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.17 from dev.mysql.com or from the MySQL Yum, APT, 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.17 Release Notes. Here are the highlights. Enjoy!
Provisioning by Cloning
We have implemented native provisioning in the server. For example, a newly created, thus empty server instance can be told to clone its state from another running server. Previously you had to use mysqldump or backup to create the initial state. The cloning process is fully automated and easy to use from the MySQL Shell. For example, if you want to add a new server to a running MySQL InnoDB Cluster, you can simply start a new server and tell it to join the cluster.
Clone local replica (WL#9209) This work by Debarun Banerjee creates a server plugin that can be used to retrieve a snapshot of the running system. This work adds syntax for taking a physical snapshot of the database and store it on the machine where the database server is running.
Clone remote replica (WL#9210) This work by Debarun Banerjee enhances the server clone plugin created in WL#9209 to connect to a remote server and transfer the snapshot over the network, i.e. to the machine where the replica needs to be provisioned.
Clone Remote provisioning (WL#11636) This work by Debarun Banerjee simplifies provisioning by allowing cloning directly into the recipient’s current data directory, and furthermore, allows the cloning process to be completely driven via SQL command after a server is started. This work also implements pre-condition checks before cloning and refuses to clone if pre-conditions are not satisfied.
Clone Replication Coordinates (WL#9211) This work by Debarun Banerjee implements support for extracting, propagating and storing consistent replication positions during the process of cloning a server as depicted by WL#9209 and WL#9210. The donor server extracts and sends consistent replication positions to the recipient server. The recipient server stores and uses them to start replicating from a consistent logical point in time with respect to the data it copied from the donor.
Support cloning encrypted database (WL#9682) This work by Debarun Banerjee adds support for cloning of encrypted tables, general tablespaces, undo tablespaces and redo logs. It works both with the local key management service and with a centralized key management solution, i.e. MySQL Enterprise Transparent Data Encryption (TDE).
Multi-valued indexes
Multi-valued indexes make it possible to index JSON arrays. A multi-valued index is an index where multiple index records can point to the same data record. Take the following JSON document as an example: {user: John, user_id: 1, addr: [ {zip:94582} , {zip: 94536} ] }. Here, if we’d like to search all zip codes, we’d have to have two records in the index, one for each zip code in the document, both pointing to the same document.
Such an index is created by the statement CREATE INDEX zips ON t1((CAST(data-> '$.addr[*].zip' AS UNSIGNED ARRAY))); Effectively it’s a functional index which uses the CAST() function to cast JSON arrays to an array of SQL type. At least for now, multi-valued indexes can only be created for JSON arrays.
As soon as a multi-valued index has been created it will be used automatically by the optimizer, like any single-valued index. Multi-valued indexes will typically be used in queries involving MEMBER OF(), JSON_CONTAINS() and JSON_OVERLAPS(). For example: SELECT * FROM t1 WHERE 123 MEMBER OF (data->'$.addr[*].zip'); passes all documents that contain zip code 123. JSON_CONTAINS() searches multiple keys but passes only those documents in which all keys are present. JSON_OVERLAPS() also searches for multiple keys, but passes when at least one key is present in the document.
The JSON_OVERLAPS() function is a new JSON function added in this release. The MEMBER OF() function is standard SQL syntax added in this release. At least for now, the only valid input for MEMBER OF() is a JSON array.
The work on multi-valued indexes includes changes in the Server layer by Evgeny Potemkin (WL#8955) and in InnoDB by Bin Su (WL#8763). The Document Store (XPlugin) integration has been done by Grzegorz Szwarc (WL#10604).
JSON Schema
Add support for JSON Schema (WL#11999) This work by Erik Froseth implements the function JSON_SCHEMA_VALID(<json schema>, <json doc>) which validates a JSON document against a JSON Schema. The first argument to JSON_SCHEMA_VALID is the JSON Schema definition, and the second argument is the JSON document the user wants to validate. JSON_SCHEMA_VALID() can be very useful as a CHECK constraint.
Implement JSON_SCHEMA_VALIDATION_REPORT (WL#13005) This work by Erik Froseth implements the function JSON_SCHEMA_VALIDATION_REPORT(<json schema>, <json doc>) that prints out a structured JSON object giving a more detailed report of the JSON Schema validation in case of errors.
Optimizer improvements
Subquery optimisation: Transform NOT EXISTS and NOT IN to anti-semi-join (WL#4245) This work by Guilhem Bichot converts NOT IN and NOT EXISTS into anti-joins, which makes the subquery disappear. The transformation provides for better cost planning, i.e. by bringing subquery tables into the top query’s plan, and also by merging semi-joins and anti-joins together, we will gain more freedom to re-order tables in the execution plan, and thus sometimes find better plans.
Ensure that all predicates in SQL conditions are complete (WL#12358) This work by Roy Lyseng ensures that incomplete predicates are substituted for non-equalities during the contextualization phase, thus the resolver, the optimizer and the executor will only have to deal with complete predicates.
Add CAST to FLOAT/DOUBLE/REAL (WL#529) This work by Catalin Besleaga extends the CAST function to support cast operations to FLOATING point data types according to the SQL Standard. This aligns the explicit CAST support with implicit CASTs which have had a greater variety of cast possibilities.
Volcano iterator
The work is based on the Volcano model, see the original Volcano paper here. The goal of this activity is to simplify the code base, enable new features such as hash join, and enable a better EXPLAIN and EXPLAIN ANALYZE.
Volcano iterator semijoin (WL#12470) This work by Steinar H. Gunderson implement all forms of semijoins in the iterator executor. This is a continuation of the work described in WL #12074.
Iterator executor analytics queries (WL#12788) This work by Steinar H. Gunderson broadens the scope of the analytics queries the iterator executor can handle, by supporting window functions, rollup, and final deduplication. This is a continuation of the work described in WL#12074 and WL#12470.
Character Sets
Add new binary collation for utf8mb4 (WL#13054) This work by Xing Zhang implements a new utf8mb4_0900_bin collation. The new collation is similar to the utf8mb4_bin collation with the difference that utf8mb4_0900_bin uses the utf8mb4 encoding bytes and does not add pad space.
Replication
Encrypt binary log caches at rest (WL#12079) This work by Daogang Qu ensures that, when encrypting binary log files, we also encrypt temporary files created in cases when binary log caches spill to disk.
Allow compression when using mysqlbinlog against remote server (WL#2726) This work by Luís Soares enables protocol compression for mysqlbinlog. The user is now able to connect to a remote server using mysqlbinlog and request protocol compression support while transfering binary logs over the network.
Group Replication
Clone plugin integration on distributed recovery (WL#12827) This work by Pedro Gomes ensures that the user can start the group replication process in a new server and automatically clone the data from a donor and get up to speed without further intervention. This work makes use of the MySQL clone plugin and integrates it with Group Replication.
Cross-version policies (WL#12826) This work by Jaideep Karande defines the behavior and functional changes needed to maintain replication safety during group reconfigurations.
Router
MySQL 8.0.17 adds monitoring infrastructure and a monitoring REST interface to the MySQL Router. Applications and users who want to monitor the Router get structured access to configuration data, performance information, and resource usage. In addition, the MySQL Router has been further integrated with the MySQL Group Replication as it now handles view change notifications issued by the group replication protocol.
REST interface for Monitoring (WL#8965) This work by Jan Kneschke exposes data as REST endpoints via HTTP methods as JSON payload.
REST endpoints for service health (WL#11890) This work by Jan Kneschke adds REST endpoints for healthcheck, i.e. to check whether the router has backends available and that they are ready to accept connections. The GET <base>/routes/{routeName}/health returns an object with { “isActive”: true } if a route is able to handle client connections, { “isActive”: false } otherwise.
REST endpoints for metadata-cache (WL#12441) This work by Jan Kneschke adds REST endpoints for the metadata-cache that expose current known cluster nodes and their state, success and failure counters, time of last fetch, and current configuration.
REST endpoints for routing (WL#12816) This work by Jan Kneschke adds REST endpoints for routing such as the names of the *routes* the MySQL Router supports, the *configuration* of the route, the *status* information about the named route, blocked hosts, destinations, and connections.
REST endpoints for router application (WL#12817) This work by Jan Kneschke adds REST endpoints for router status, i.e. the hostname, processId, productEdition, timeStarted and version.
Metadata cache invalidation via Group Replication Notification (WL#10719) This work by Andrzej Religa extends the router to handle GR view change notifications. On reception of a GR view change notification from the xplugin the metadata cache will invalidate its cache for that cluster and trigger a refresh of the group status.
Basic xprotocol support for mysql_server_mock (WL#12861) This work by Andrzej Religa adds x protocol support to the mysql_server_mock program used as a dummy replacement for the mysqld server during component testing of the MySQL Router.
Notifications for xprotocol support for mysql_server_mock (WL#12905) This work by Andrzej Religa implements a way to mimic the GR notifications in mysql_server_mock for testing purposes. The mysql_server_mock is now able to mimic InnoDB Cluster nodes sending Notices to the Router.
MTR testsuite
Move testcases that need MyISAM to a separate .test file (WL#7407) This work by Mohit Joshi and 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
Support host names longer than 60 characters (WL#12571) This work by Gopal Shankar ensures that the server will be able to run with host names up to 255 characters. This work fixes Bug#63814 and Bug#90601.
SHOW CREATE USER and CREATE USER to work with HEX STRINGS for AUTH DATA (WL#12803) This work by Georgi Kodinov implements a new server option –print_identified_with_as_hex that causes SHOW CREATE USER to print hex chars for the password hash if the string is not printable (OFF by default). The CREATE USER IDENTIFIED WITH AS and ALTER USER IDENTIFIED WITH AS will take hex literals for the password hash in addition to the string literals it currently takes regardless of the flag. See also Bug#90947.
Add mutex lock order checking to the server (WL#3262) This work by Marc Alff provides a methodology and tooling to enforce that the runtime execution is free of deadlocks.
Fix imbalance during parallel scan (WL#12978) This work by Sunny Bains improves the parallel scan by further splitting of remaining partitions in cases where there are more partitions than there are worker threads. This is follow up work to WL#11720.
Control what plugins can be passed to –early-plugin-load (WL#12935) This work by Georgi Kodinov adds a new plugin flag to the PLUGIN_OPT_ALLOW_EARLY_LOAD flagset so that plugin authors can enable their plugin for –early-plugin-load. For pre-existing plugins this flag will be 0 (off), thus it is not an incompatible change. Also, all keyring plugins that we produce will be marked with this new flag as they must be loadable with –early-plugin-load.
Add OS User as Connection attribute in MySQL Client (WL#12955) This work by Georgi Kodinov adds a new connection attribute for “mysql” clients which adds information about the OS account mysql is executing as. This lets DBAs more easily notify people about who is running time consuming queries on the server. This is a contribution from Daniël van Eeden, see Bug#93916.
Added optional commenting of the @@GLOBAL.GTID_PURGED by dump (WL#12959) This work by Georgi Kodinov adds a new allowed value for the –set-gtid-purged command line argument to mysqldump. The –set-gtid-purged=COMMENTED will output the SET @GLOBAL.GTID_PURGED information in a comment. This is a contribution from Facebook, see Bug#94332.
A component service for the current_thd() (WL#12727) This work by Georgi Kodinov makes it possible to call current_thd() from a component and in this way obtain an
opaque pointer to the THD that we can pass to other services. Calling current_thd() instead of using the global current_thd symbol from mysqld in plugins will contribute to cleaner plugins.
Deprecation and Removal
MySQL 8.0.17 does not remove any features but marks some features as deprecated in 8.0. Deprecated features will be removed in a future major release.
Deprecate/warn when using ‘everyone’ for named_pipe_full_access_group (WL#12670) This work by Dan Blanchard ensures that the server raises and logs a warning message when the named_pipe_full_access_group system variable is set to a value that maps to the built in Windows Everyone group (SID S-1-1-0). We expect that in the future we will change the default value of the named_pipe_full_access_group system variable from ‘*everyone*’ to ” (i.e. no-one). See also WL#12445.
Deprecate BINARY keyword for specifying _bin collations (WL#13068) This work by Guilhem Bichot deprecates the BINARY keyword to specify that you want the *_bin
collation of a character set. This is not a standard SQL feature, just syntactic sugar that adds to the confusion between the BINARY data type and the binary “charset” or *_bin collations.
Deprecate integer display width and ZEROFILL option (WL#13127) This work by Knut Anders Hatlen deprecates the ZEROFILL attribute for numeric data types and the display width
attribute for integer types. See also Proposal to deprecate MySQL INTEGER display width and ZEROFILL by Morgan Tocker.
Deprecate unsigned attribute for DECIMAL and FLOAT data types (WL#12391) This work by Jon Olav Hauglid deprecates the UNSIGNED attribute for DECIMAL, DOUBLE and FLOAT data types. Unlike for the integer data types, the UNSIGNED attribute does not change the range for these data types, it simply means that it is impossible to insert negative values into the columns. As such, it is only a very simple check constraint, and using a general check constraint would be more consistent.
Deprecate && as synonym for AND and || as synonym for OR in SQL statements (WL#13070) This work by Guilhem Bichot adds a deprecation warning when && is used as a synonym for AND and || is used as a synonym for OR in SQL statements.
Deprecate AUTO_INCREMENT on DOUBLE and FLOAT (WL#12575) This work by Jon Olav Hauglid adds a deprecation warning when AUTO_INCREMENT is specified for DOUBLE and FLOAT columns.
Deprecate the ability to specify number of digits for floating point types (WL#12595) This work by Jon Olav Hauglid adds a deprecation warning when the non-standard FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D) is specified.
Deprecate SQL_CALC_FOUND_ROWS and FOUND_ROWS (WL#12615) This work by Steinar H. Gunderson adds a deprecation warning when the non-standard syntax SQL_CALC_FOUND_ROWS and FOUND_ROWS() is used.
Thank you for using MySQL!