Tuesday, July 23, 2019

MySQL 8.0.17 Release and The Clone Plugin

MySQL 8.0.17 is officially released Yesterday. The most talked feature is the clone plugin, which enables the automatic node provisioning from an existing node (a Donor). This also closes the gap between MySQL Group Replication and Galera Cluster on usability.

Congrats to MySQL engineering team on the excellent work! Specially expose the visibility of the operations:

mysql> SELECT STATE FROM performance_schema.clone_status;
+-----------+
| STATE     |
+-----------+
| Completed |
+-----------+
1 row in set (0.02 sec)

mysql> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE     | STATE       | END_TIME                   |
+-----------+-------------+----------------------------+
| DROP DATA | Completed   | 2019-07-23 12:27:36.059325 |
| FILE COPY | Completed   | 2019-07-23 12:27:36.258573 |
| PAGE COPY | Completed   | 2019-07-23 12:27:36.265563 |
| REDO COPY | Completed   | 2019-07-23 12:27:36.270240 |
| FILE SYNC | Completed   | 2019-07-23 12:27:36.377404 |
| RESTART   | Not Started | NULL                       |
| RECOVERY  | Not Started | NULL                       |
+-----------+-------------+----------------------------+
7 rows in set (0.02 sec)

There is an excellent blog by Miguel Araújo - A Breakthrough in Usability – Automatic Node Provisioning! The article describes why the clone plugin is needed; and the difference between the binlog-based incremental recovery and the clone-based recovery. 

Other articles on the same subject are:
If you like to learn the design and how does it work internally, suggest to read the related worklogs:
  • WL#9209: InnoDB: Clone local replica
  • WL#9210: InnoDB: Clone remote replica
  • WL#9211: InnoDB: Clone Replication Coordinates
  • WL#9682: InnoDB: Support cloning encrypted and compressed database
  • WL#11636: InnoDB: Clone Remote provisioning
Most of other features in MySQL 8.0.17 are discussed in my previous blog. The more accurate list of changes are in Geir's blog, and 8.0.17 release notes.

Monday, July 8, 2019

Preview of the upcoming new features in MySQL 8.0.17

Note: I am no longer working for Oracle/MySQL. This post is based on public information.

The MySQL 8.0.17 release is around the corner. Let's take a look at the new features in the upcoming release.

InnoDB redo log archive

InnoDB added new parameter “--innodb-redo-log-archive-dirs” in 8.0.17, according to the doc page. The implementation of both page archive and redo log archive has been in the code for a while, under arch directory. This feature can be used by backup and database clone.


Multi-Valued Indexes

As of MySQL 8.0.17, InnoDB supports multi-valued indexes. A multi-valued index is a secondary index defined on a column that stores an array of values. A “normal” index has one index record for each data record (1:1). A multi-valued index can have multiple index records for a single data record (N:1). Multi-valued indexes are intended for indexing JSON arrays. For example, a multi-valued index defined on the array of zip codes in the following JSON document creates an index record for each zip code, with each index record referencing the same data record.

It has some good examples on the page. Also listed Limitations and Restrictions on Multi-valued Indexes.

The LOCK_ORDER Tool

As of MySQL 8.0.17, to enable detection of lock-acquisition deadlocks and enforcement that runtime execution is free of them, MySQL supports LOCK_ORDER tooling. This enables a lock-order dependency graph to be defined as part of server design, and server runtime checking to ensure that lock acquisition is acyclic and that execution paths comply with the graph.

The LOCK_ORDER tool is intended for debugging the server, not for production use.

Table 29.4 LOCK_ORDER System Variable Summary
Variable Name
Variable Type
Variable Scope
Boolean
Global
Boolean
Global
Boolean
Global
Boolean
Global
Boolean
Global
File name
Global
File name
Global
Directory name
Global
Boolean
Global
Boolean
Global
Boolean
Global
Boolean
Global
Boolean
Global

Default the tool is disabled in source, according to the source configuration options:
-DWITH_LOCK_ORDER=bool

Whether to enable LOCK_ORDER tooling. By default, this option is disabled and server builds contain no tooling. If tooling is enabled, the LOCK_ORDER tool is available and can be used as described in Section 29.5.3, “The LOCK_ORDER Tool”.

Other Changes

Early plugin loading
From 8.0.17, all early loading plugins must specify the following flag:
#define PLUGIN_OPT_ALLOW_EARLY  4UL   /* allow --early-plugin-load */
This flag was added in MySQL 8.0.17. All plugins compiled using MySQL distributions prior to 8.0.17 do not have this flag set. When loading these into pre-8.0.17 servers this does not matter, but attempts to use --early-plugin-load to load plugin binaries compiled using pre-8.0.17 MySQL distributions into a 8.0.17 or higher server will fail. The plugins must be recompiled against MySQL 8.0.17 or higher.

Additional Target Types for Casts.
The functions CAST() and CONVERT() now support conversions to types DOUBLEFLOAT, and REAL

JSON Schema Validation
MySQL 8.0.17 adds two functions JSON_SCHEMA_VALID() and JSON_SCHEMA_VALIDATION_REPORT() for validating JSON documents again JSON schemas. 

hintable time_zone
As of MySQL 8.0.17, the time_zone session variable is hintable using SET_VAR.

From MySQL 8.0.17, when binary log encryption is active on the server, temporary files used to hold the binary log cache are encrypted using AES-CTR (AES Counter mode) for stream encryption. Because the temporary files are volatile and tied to a single process, they are encrypted using single-tier encryption, using a randomly generated file password and initialization vector that exist only in memory and are never stored on disk or in the keyring.

For SHOW CREATE USER, print hash values containing unprintable characters in hex. Added in MySQL 8.0.17.

Upgrade on case sensitive file system
Due to a regression introduced in MySQL 8.0.14, in-place upgrade on a case sensitive file system from MySQL 5.7 or a MySQL 8.0 release prior to MySQL 8.0.14 to MySQL 8.0.16 failed for instances with partitioned tables and lower_case_table_names=1.

Reserved Keyword
ARRAY (R); added in 8.0.17 (reserved)
MEMBER (R); added in 8.0.17 (reserved)

Compress option
--compress is only used in one release (8.0.17).
Option Name
Description
Introduced
Deprecated
Removed
Compress all information sent between client and server
8.0.17
8.0.18
Permitted compression algorithms for connections to server
8.0.18
This option was added in MySQL 8.0.17. As of MySQL 8.0.18 it is deprecated. It will be removed in a future MySQL version.