The MySQL Development team is very happy to announce that MySQL 8.0.14 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.14 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.14 Release Notes. Here are the highlights. Enjoy!

SQL

Lateral derived tables  (WL#8652)  This work by Guilhem Bichot adds support for SQL:99 feature T491 “lateral derived tables”. LATERAL can be described as the “for each” loop of SQL, see for example Markus Winand’s slides 3-11 here. See also feature request from Daniël van Eeden in Bug#78930.

Derived tables dependent on outer select  (WL#461)  This work by Guilhem Bichot extends MySQL’s support for derived tables. If you have a top query, which contains a subquery, and a derived table is contained in the subquery, this work allows the definition of the derived table to reference the tables used by the top query.  This has been allowed by the SQL standard since SQL92. This work has been designed, described, implemented and documented together with WL#8652 above (LATERAL).

Add JSON_ARRAYAGG and JSON_OBJECTAGG windowing functions (WL#11574)  This work by Chaithra Gopalareddy ensures that the two JSON functions JSON_ARRAYAGG() and JSON_OBJECTAGG() can be used as SQL window functions.

Remove trivial conditions before outer to inner join transformation (WL#9571) This work by Chaithra Gopalareddy removes trivial conditions (expressions over literals) during preparation. This results in better plans for some queries, for example it fixes Bug#69359.

GIS

ST_Distance with unit (WL#11000) This work by Torje Digernes adds a third, optional, parameter to the ST_Distance(g1, g2 [, unit]) function. The new parameter names the [unit] to use in the result, for example “metre” or “foot”. The units of measure is found in MySQL’s INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE table.

X Protocol

X Protocol Prepared Statement execution (WL#9270)  This work by Tomasz Stepniak moves SQL prepare functionality to the X Protocol. This gives the user the possibility to prepare and execute DevAPI CRUD operations. In the case of the Document Store a large part of the queries are simple _id based lookups which can be accelerated by preparing the Crud once and executing it often.

Router

Persist last known set of metadata servers   (WL#11318)  This work by Andrzej Religa makes the router pull meta-data information from the group at regular intervals (default 500ms) and then store the IP-addresses of group members locally in a file. When a router is restarted it will ask one of the group members, found in the local file, for all relevant meta-data about the group. This is needed because the group membership changes over time, nodes might join and leave the group. All group members have a copy of the meta-data for the group, maintained by the group replication protocol. The router will need to know the IP-addresses of current members so it can contact one of them and ask for its meta-data.

Simulating slow networks in the router test environment  (WL#12438) This work by Jan Kneschke makes it possible to simulate slow networks for testing purposes. A tester can delay messages in the Router – Server handshakes by setting “exec_time” and thus simulate slow response from servers.

Parallel read of index

Parallel read of index (WL#11720) This work by Sunny Bains adds the capability to read the the cluster index in parallel for non-locking operations. The number of parallel threads is controlled by a new configuration variable called  –innodb-parallel-read-threads (min 1, default 4, max 256). This new functionality is currently in use for CHECK TABLE t;.

Auto-sizing the Redo Log

Automatically configure default redo log file size for innodb dedicated server (WL#12300) This work by Zheng Lai ensures that the two settings, innodb_log_file_size and innodb_log_files_in_group, are both auto-tuned relative to the innodb_buffer_pool_size setting.  This feature is only for InnoDB dedicated server, which means it works once innodb_dedicated_server is enabled. So, if the user explicitly sets these options in my.cnf, then MySQL will still use the user’s configuration. The purpose is to simplify MySQL configuration by auto-tuning and for large scale deployments.

Tablespaces

Support CREATE/ALTER/DROP UNDO TABLESPACE   (WL#9508) This work by Kevin Lewis adds SQL support for managing InnoDB UNDO tablespaces. With this change users can add and drop UNDO tablespaces at runtime and the location of each tablespace can be chosen individually. For example CREATE UNDO TABLESPACE undo99 ADD DATAFILE 'undo99.ibu';. An existing tablespace can be made inactive by ALTER UNDO TABLESPACE 'undo99' SET INACTIVE; which will cause the tablespace to be (eventually) emptied. An empty tablespace can be deleted by DROP UNDO TABLESPACE undo99;. The old way of creating UNDO tablespaces is no longer supported and the innodb_undo_tablespaces setting is ignored in 8.0.14. For more details see WL#9508.

CREATE TABLESPACE without DATAFILE clause  (WL#12236)  This work by Thayumanavar Sachithanantha introduces a CREATE TABLESPACE variant that does not require a DATAFILE clause, but instead creates a unique filename implicitly. This allow users to create tablespaces without having the FILE privilege. This relieves the user from knowing physical layout of the storage spaces maintained by the storage engine.

With shared tablespaces , tablespaces with multiple tables in them, we now disallow mixing encrypted and unencrypted tables in the same shared tablespace. In 5.7 this has been deprecated and will now issue a warning. In 8.0 it is explicitly disallowed. The encryption attribute is a per tablespace attribute and all tables within that tablespace will inherit the tablespace encryption attribute. This work was done by Mayank Prasad.

Admin Port

Add Admin Port (WL#12138)  This work by Dmitry Shulga adds the capability to specify a separate IP address and TCP port that are ready to accept TCP connection and speak MySQL protocol.  There is a dedicated thread which handles the interaction between a server and a system administrator communicating via this socket. There is also  a separate privilege to restrict network access to a socket specified by this pair of IP address/TCP port. The purpose of this “admin port” is to ensure that critical infrastructure automation and humans can still connect even if the server is overloaded accepting too many connections.  This feature has been contributed by Facebook in Bug#90395.

Error Logging

Slow Query Log Changes: Add new command line option, –log-slow-extra, for richer slow logging  (WL#12393) This work by Tatjana Nurnberg adds a new command-line option, –log-slow-extra, that will turn on richer logging in the slow query log. This feature has been contributed by Facebook in Bug #89637.

Update early “buffered” logging to make full use of the new error logging system (WL#11875) This work by Tatjana Nurnberg ensures that error log messages generated during MySQL startup, before error logging system is operational, will be cached and flushed to the error log as soon as it becomes operational. When flushed to the error log, error messages will follow the new rules for formatting and verbosity introduced by the 8.0 error logging system. Before 8.0.14 these early error messages were flushed in the old format and using old rules.

Character Set Conversion

Alter table with character set conversion as inplace operation (WL#11605) This work by Dyre Tjeldvoll makes it possible handle some conversions as an inplace operation, that is, as a pure meta-data change. There are two pre-conditions for doing conversions as a pure change: First, the target character set must be a superset of the source character set. This ensures that no data needs to change. Second,  the columns to be changed are not part of any primary or secondary indexes. This because the implicit change in collation will cause the sorting order of an index to change. For example, one can safely change the character set of a column of datatype “Text” from UTF8MB3 to UTF8MB4 as long as this column  is not part of any primary or secondary indexes.

Performance Schema

Capture QUERY_ID  (WL#12165) This work by Marc Alff captures the QUERY_ID maintained by the SQL layer for a statement and prints this as STATEMENT_ID in the events_statements tables. The purpose is to correlate the data collected in the performance schema statement instrumentation with the query in the SQL layer.

Security

Support 2 active passwords per user account (WL#11540) This work by Harin Vadodaria allows user accounts to  have dual passwords, by adding an extra option to ALTER USER/SET PASSWORD to keep the old password and then adding another ALTER USER command to retire the old password. The purpose is to avoid downtime while changing passwords in a replicated environment. Clients can use the old password while a new password is being established in a group of servers and retire the old password only when the new password has been established across the whole group.

Cryptographically signed SET statements (WL#12086) This work by Georgi Kodinov makes it possible to use SET PERSIST [ONLY] even for non-persistent system variables by introducing cryptographically signed SET statements for additional security. Extra authentication is needed to avoid privilege escalation via SET PERSIST [ONLY], e.g. to avoid setting sensitive data like secure-file-priv.

SESSION_VARIABLE_ADMIN privilege  (WL#12217)  This work by Georgi Kodinov adds a new global dynamic privilege called SESSION_VARIABLES_ADMIN and requiring this privilege to set certain session variables. Setting session variables is available to everybody. But certain session variables can affect the work of the entire server. So allowing them set in an unsanctioned way can destabilize the work of the running server.

Replication

Replicate original server version (WL#11879) This work by Maria Couceiro implements two new session variables, the original_server_version and the immediate_server_version  session variables. The original_server_version is used to tag each transaction with the version of the server where it was originally executed. This variable will be replicated, unchanged, to the servers in the replication topology. The immediate_server_version is used to tag each transaction with the version of the immediate server in the replication topology. Replication topologies may be composed of servers of different versions. These servers may have some features that may be incompatible between themselves. However, before this change there has been no way to extract the version of either the  original or the immediate server version for a replicated transaction, which creates a lot of difficulties when implementing with cross-version replication approaches.

Binlog encryption at rest (WL#10957)  This work by Libing Song and Joao Gramacho implements the ability to encrypt binary and relay log files online. The encryption algorithm used for the files, the AES (Advanced Encryption Standard) cipher algorithm, is built in to MySQL Server and cannot be configured. Binary log encryption is supported for OpenSSL and wolfSSL builds of MySQL Server. You enable encryption on a MySQL server by setting the binlog_encryption system variable to ON. OFF is the default.  See documentation here.

Turn binlog-row-event-max-size into a sysvar (WL#12385)  This work by Luis Soares makes the binlog-row-event-max-size CLI option into a server read only variable. This makes it possible to use  SET PERSIST_ONLY binlog_row_event_max_size to configure binlog_row_event_max_size for a MySQL  instance without having filesystem access. It also makes it possible to read the value of binlog_row_event_max_size through an SQL interface – SHOW VARIABLES LIKE. See also Bug#74728.

Group Replication

Consistent reads (WL#10379) This work by Nuno Carvalho implements four consistency guarantees on Group Replication, that is, allows the user to configure globally or per transaction the consistency provided by the group. The four consistency guarantees are EVENTUAL (default, current behavior), BEFORE, AFTER, and BEFORE_AND_AFTER. A typical use case is to load balance your reads over the group without having to worry about stale reads. The user can specify the transaction consistency guarantee by setting the system variable group_replication_consistency.

IPv6 support  (WL#11926) This work by Tiago Jorge implements IPv6 support for MySQL Group Replication. The user is now able to fully deploy Group Replication on an IPv6 network and make use of IPv6 features. Implements feature request in Bug#90217. See documentation here.

Change XCom interface to queues instead of sockets (WL#9850) This work by Tiago Vale changes the way requests are sent to XCom, from the sockets used before, to a new, flexible, input mechanism decoupled from XCom itself. The purpose is to reduce the overhead when GCS communicates with its local XCom instance. This work does not introduce any user-visible features to MySQL.

Hold reads and writes when the new primary has replication backlog to apply (WL#11123) This work by Anibal Pinto implements a fencing mechanism when a new primary is being promoted in Group Replication (GR). The fencing will hold connections from writing and reading from the new primary until it has applied all the pending backlog of changes that came from the old primary.  The user can specify the transaction consistency guarantee by setting the system variable group_replication_consistency to BEFORE_ON_PRIMARY_FAILOVER. The purpose is to ensure that an application always read its own writes, even in the event of new primary promotion.

Component Infrastructure

Component interface to the SHUTDOWN command  (WL#12003)  This work by Georgi Kodinov adds a component service to send signals to the hosting binary. One of these signals is the shutdown signal. The immediate use case is for the replication components to be able to shut down the server in a standard and controlled way.

MySQL Test Runner (MTR)

Use InnoDB to store warnings in MTR  (WL#12270)  This work by Malika Agarwal moves the three MTR tables called test_suppressions, global_suppressions and error_log from MyISAM to InnoDB.

Review the Replication and GR warning patterns in mtr_warnings.sql file  (WL#11626) This work by Malika Agarwal reviews the Replication and Group Replication related warnings in “mysql-test/include/mtr_warnings.sql” and moves the test specific patterns over to the corresponding MTR tests. The purpose is to remove global ignore rules and replace them with test specific ignore rules in the cases where they are needed.

Deprecation and Removals

Deprecate Shared tablespaces in partitioned table in  5.7.24 (WL#11571) This work by Mayank Prasad adds a deprecation warning when the user creates or moves a partition into a shared tablespace. In 8.0, partitioned tables cannot reside in shared tablespaces, each partition must be stored as “single partition per file”. This is because of how encryption works in InnoDB, i.e. all or none of the tables in a tablespace must be encrypted.  By requiring a partition to be stored as “single partition per file” we ensure that a partition is never stored together with other tables, which again ensures that we can encrypt a partitioned table without consequences for other tables.

Deprecate (5.7) and remove (8.0) resolve_stack_dump  (WL#12619) This work by Erlend Dahl  deprecates resolve_stack_dump in 5.7.25 and removes it in 8.0.14. This is more of a formal clean up since the resolve_stack_dump tool has not been in use, nor maintained for many years.

Deprecate (5.7) and remove (8.0) resolveip  (WL#12620) This work by Erlend  Dahl deprecates resolveip in 5.7.25 and removes it in 8.0.14. This is more of a formal clean up since the resolveip tool has not been in use, nor maintained for many years.

Thank you for using MySQL !