The MySQL Development team is very happy to announce that MySQL 8.0.16 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.16 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.16 Release Notes. Here are the highlights. Enjoy!
CHECK constraints (WL#929) This work by Praveenkumar Hulakund implements the CHECK constraint, i.e. the clause
[CONSTRAINT [constraint_name]] CHECK (condition) [[NOT] ENFORCED] in CREATE TABLE and ALTER TABLE statements. A check constraint is satisfied if and only if the specified condition evaluates to TRUE or UNKNOWN (for NULL column value) for a row of the table. The constraint is violated otherwise. For example:
CREATE TABLE t (s1 INT, CHECK (s1 > 0))
INSERT INTO t VALUES (-1) will fail since the condition is FALSE and
INSERT INTO t VALUES (NULL) will succeed since the condition is UNKNOWN. This work implements a commonly requested feature, see for example Bug#3465 and Bug#22759.
Move actions from mysql_upgrade client into mysqld (WL#12413) This work by Amitabh Das moves the actions executed by the MySQL upgrade script to the mysqld itself. The MySQL server now has control of when these actions are executed, and does not rely on the user to remember to run the
mysql_upgrade script. I.e. starting with 8.0.16, MySQL will upgrade itself. The goal is to make upgrade simpler and faster by removing the
mysql_upgrade step. The
mysql_upgrade is deprecated and will be removed in a future MySQL version. Even though
mysql_upgrade no longer performs upgrade tasks, it still exits with status 0 (EXIT_SUCCESS) to avoid failures in existing user scripts. See MySQL Upgrade.
ST_Length with unit (WL#12657) This work by Torje Digernes implements a second, optional, parameter for ST_Length(ls [, unit]) that names the unit of length to use in the result. Supported units are listed in the Information Schema ST_UNITS_OF_MEASURE table.
Add Chinese collation for utf8mb4 (WL#11825) This work by Xing Zhang adds the Chinese collation for utf8mb4. The collation is named
utf8mb4_zh_0900_as_cs, ‘utf8mb4’ is the character set, ‘zh’ is the ISO code for Chinese, ‘0900’ means this collation follows the Unicode standard 9.0, ‘as_cs’ means accent sensitive and case sensitive.
Add metrics for undo truncation (WL#12600) This work by Kevin Lewis adds metrics to the INFORMATION_SCHEMA.INNODB_METRICS table for the background activities during undo tablespace truncation. These metrics are available by
SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'undo'; Examples include the number of times the purge thread attempted to truncate undo history and the time the purge thread spent truncating undo history. See WL#12600 for the full list.
Native Functions (WL#7803) This work by Chris Powers implements native functions to format or retrieve data from the Performance Schema. The implemented functions are format_pico_time(), format_bytes(), PS_current_thread_id(), and PS_thread_id(). These native functions replace the corresponding stored functions in the MySQL sys schema. The purpose is to reduce overhead by having a C++ implementation rather than a stored function.
Add folding of constants when compared to fields (WL#11935) This work by Dag Wanvik is about internal optimizer improvement. The goal is to speed up execution at the cost of a little more analysis at optimize time. Always true and false comparisons are detected and eliminated. In other cases, the type of the constant is adjusted to match that of the field if they are not the same, avoiding type conversion at execution time.
Subquery optimizations: Make IN optimizations also handle EXISTS (WL#4389) This work by Roy Lyseng extends the IN semi-join optimizations to handle EXISTS subqueries as well. EXISTS subqueries are handled with the same semi-join strategies as IN, including first-match, materialization, duplicate weedout and loose index scan. In addition, the WHERE condition attached to the subquery is decorrelated into IN-like expression lists, further expanding the possible semi-join strategies. Decorrelation is done for both EXISTS and IN subqueries. The overall goal is to transform more queries into a form where we can do cost based optimizations (e.g. semi-join).
Volcano iterator design (WL#11785 and WL#12074) This work by Steinar H. Gunderson implements the beginning of a new SQL executor. 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.
Add Partition Information into the Binary Log (WL#12168) This work by Neha Kumari injects table partitioning information into the binary log. This information will also be available by tools such as mysqlbinlog. Among other possibilities, this extra information can be used to make the replication applier schedule more transactions concurrently.
Add support to binary log encryption key rotation and cleanup (WL#12080) This work by Daogang Qu makes it easier for the user to rotate the binary log master key used to encrypt file passwords of binary and relay log files. For example, as the database owner you might want to rotate binlog encryption master key periodically without the need to restart the server in order to comply with security rules.
MYSQL GCS: Improve XCom Cache Management (WL#11615) This work by Andre Negrao makes the XCom Cache configurable and dynamic. The user can set the maximum group_replication_message_cache_size for each individual node. In addition, the cache will no longer be bound by a fixed number of entries; instead, it will grow dynamically, so long as the size limit is respected.
Auto-rejoin member to group after an expulsion (WL#11284) This work by Ricardo Ferreira ensures that expelled nodes can auto-rejoin the group if they are out of it for a reasonably short time. This feature automates rejoin operations for servers that leave the group due to transient communication failures between the peers.
Support Data Fragmentation (WL#11610) This work by Tiago Vale enables packet fragmentation which makes the group communication layer handle large messages much more efficiently, and by doing that avoid false suspicions that could lead to servers being evicted from the group in some cases.
Change exit state action option to READ_ONLY (WL#12659) This work by Jaideep Karande changes the default value of group_replication_exit_state_action from ABORT_SERVER to READ_ONLY. We decided to change this default back to READ_ONLY based on consistent customer and community feedback.
Control (enforce and disable) table encryption (WL#12261) This work by Gopal Shankar enables the DBA to have global control over table encryption. With this feature the DBA can grant CREATE TABLE to users while still retaining some control over encryption usage, e.g. enforce that all tables in a certain database are encrypted. The DBA can also make sure people don’t do double encryption in cases where the underlying storage engine is encrypted. See also Tablespace Encryption.
Users and Privileges
MySQL system users (WL#12098) This work by Rahul Sisondia adds two new features, the “SYSTEM_USER privilege” and “partial_revokes”. The users granted the ‘SYSTEM_USER’ privilege can be seen as power users, and in effect there will two categories of users, power users and regular users. Regular users can not modify any properties of power users – even if DDL privileges are granted. The purpose of this (optional) feature is to enable a strong separation between those who administrate a hosted environment and those that are using the same environment. The partial_revoke feature simplifies the administration of privileges. With partial_revoke, the DBA can restrict DDL/DML operations on one or more databases even if the user has the required global privileges. Alternatively, the DBA must avoid using global privileges if she wants to protect certain resources, which are more cumbersome and error prone.
Extend GRANT/REVOKE syntax to cover partial revokes information (WL#12820) This work by Harin Vadodaria extends the GRANT/REVOKE syntax with the [AS user [WITH ROLE..]] clause. The purpose is to be able to specify the grantor and its active roles. Normally this does not need to be specified since it is the grantor with its active roles who does the GRANT/REVOKE. However, when the GRANT/REVOKE information is read from the binlog by the mysqlbinlog tool and is to be replayed by the Server it is replayed by a user who lacks this context. This problem is solved by extending the syntax to cover the missing contextual information.
Separate KILL administration from CONNECTION_ADMIN (WL#12364) This work by Rahul Sisondia ensures that only users with the SYSTEM_USER privilege can kill sessions/queries for other users with the SYSTEM_USER privilege. Without this change, users with CONNECTION_ADMIN and/or SUPER privileges could kill connections belonging to users with the SYSTEM_USER privilege.
Support TLS 1.3 in the server and libmysql (WL#12361) This work by Ramil Kalimullin adds support for TLS 1.3 in the –tls-version option and enables it by default. MySQL 8.0 with OpenSSL 1.1.1 now supports the TLSv1.0, TLSv1.1, TLSv1.2 and TLSv1.3 protocols.
Allow switching the SSL options for a running server (WL#11541) This work by Georgi Kodinov makes all of the SSL options dynamic by preparing a new SSL context for the listening socket and then replacing the old one. The purpose is to be able to update the certificates without restarting the running server. This work is based on the Facebook contribution found in Bug#90782. See also ALTER INSTANCE RELOAD TLS.
Instrument the keyring into performance schema (WL#11543) This work by Ivo Roylev adds Performance Schema tables which exposes the metadata of the keys (key IDs, key owner), provided that the user has enough privileges to see these. When using an external key vault it is useful to match the internal key ID used by the keyring infrastructure, to the ID that this key is stored in the key vault. See also the MySQL Keyring.
MySQL Classic Protocol
Add asynchronous, non-blocking C APIs (WL#11381) This work by Bharathy Satish extends the historical C APIs with new non-blocking C APIs. The non blocking version of C APIs does not wait for data to be available on the socket to be read. These APIs will return immediately with a return status. Return status is used to decide if the API has completed its operation or needs to be called again at some later point in time to complete the operation. This work is based on the Facebook contribution found in Bug#89327.
MySQL X Protocol
Error message when connecting with wrong protocol (WL#12240) This work by Lukasz Kotula ensures that a Classic Protocol Client gets a useful error message (invalid protocol) if it tries to connect to the server at the X Protocol port number.
Session connect attributes (WL#12376) This work by Tomasz Stepniak implements support for session connection attributes in the X Protocol. Clients can send custom attributes such as the name and version of the application, OS and PID to the server, which can be queried from the performance schema session_connect_attrs table. Such client information can help server administrators to find faulty clients and target potential issues.
Reset connection state (WL#12375) This work by Grzegorz Szwarc fixes wrong behavior in X Protocol session-reset, session-close, and connection-close messages. The session-reset message will reset a session without reauthenticating or reopening the connection. The session-close message keeps the connection open and but the session must re-authenticate. The connection-close message will close the current connection.
HTTP component for Router (WL#11891, WL#12524, WL#12503) This work by Jan Kneschke adds a tiny HTTP server library to MySQL Router to allow it to expose REST endpoints and a web-interface. The purpose is to ensure observability of the Router e.g. integration with external tools for healthcheck, monitoring, and management purposes. It supports TLS/HTTPS and basic authentication against a secure, file based password storage.
Support for dynamic changes between single and multi master mode (WL#12089) This work by Pawel Mroszczyk adapts the Router configuration so that it can seamlessly move between single and multi master modes. The configuration file now has RW and RO Routing sections for all transports regardless of how many PRIMARY or SECONDARY nodes the cluster has.
Allow MySQL Router to log to multiple log sinks (WL#12733) This work by Andrzej Religa allows each log sink to be configured independently. The MySQL Router supports logging to different console, file, syslog (unix), and eventlog (windows). As an example, this work allows the user to log all the messages to file (level=debug) and only errors to the syslog (level=error).
Send log messages to windows eventlog (WL#9552) This work by Andrzej Religa implements a new eventlog plugin that logs events to the windows event log. Events are logged with the correct event type. The DEBUG level messages are logged as INFORMATION since Windows EventLog does not support DEBUG. Message source for all the messages is “MySQL Router”. In order to correctly display the log source as MySQL Router in the Windows event log, the MySQLRouter binary needs to be added in the Windows registry. For that proper privileges are needed (system admin) when launching the Router for the first time after the eventlog logging has been added to the configuration.
Log rotation on unix-system (WL#8988) This work by Andrzej Religa enables log rotation on demand while the Router is running. The router will reopen the logfile when it receives the SIGHUP signal.
Emphasis output of router executables for better readability (WL#12604 and WL#12598) This work by Jan Kneschke applies colors and emphasis to all –help output for the mysqlrouter and the mysqlrouter_* tools as well as the mysqlrouter –bootstrap output. The purpose is to make it easier for humans to read the information output.
Component service interface of my_error API (WL#12544) This work by Murthy Sidagam adds a new component service
mysql_runtime_error to allow components to report errors to the client session (if called from a session) or the server error log. It also adds a set of convenience functions to allow easy migration of existing server API symbols to the new service. The service’s default implementation resides in the server component.
Make number of PAUSES in spin loops configurable (WL#12616) This work by Jakub Lopuszanski introduces a new system variable called innodb_spin_wait_pause_multiplier which defaults to 50 (range 0..100). The purpose is to let the system administrator user adapt the PAUSE to the hardware at hand. For example, Skylake processors have a much slower (~15x) PAUSE instruction than earlier processors.
Pre-parse plugin for DDL statement clause filtering (WL#12668) This work by Sivert Sørumgård implements a plugin which does simple string replacement of DDL statements from dump files based on regular expression matching. This will be an audit type plugin catching and handling pre-parse events. In various situations when restoring output from a dump file, it is relevant to filter out certain clauses from DDL statements. This is useful, e.g. when restoring a dump file into an environment where encryption will not be applied. In such a case, we would like to filter out the encryption clauses from the CREATE TABLE statements.
Add an option to allow server quick settings validation (WL#12360) This work by Nisha Gopalakrishnan introduces a validate-config command line option which allows users to validate their server configuration settings during server start up. The validate-config option will only validate the server options (i.e the output of “–help –verbose”) and does not validate plugin options. The server will exit with an error(1) for first occurrence of invalid configuration. The server will exit with error(0) when it has successfully validated all the configuration options.
Enable use of C++14 in MySQL Server (WL#12424) This work by Jon Olav Hauglid enables the use of C++14 in the 8.0 version of MySQL server. C++14 is a small extension over C++11 with mostly bug fixes and minor improvements. Descriptions and code examples of new C++14 features can be found here. This means that the minimal compiler versions are GCC 5.3 (Linux), Clang 4.0 (FreeBSD), XCode 9 (MacOS), Developer Studio 12.6 (Solaris), and Visual Studio 2017 (Windows)
Deprecation and Removals
Deprecate mysql_upgrade client (WL#12918) This work by Amitabh Das adds a deprecation warning to the mysql_upgrade client in 8.0. The mysql_upgrade is no longer needed since the upgrade code is folded into the server by WL#12413.
Deprecate the non-caching sha256 authentication plugin (WL#12694) This work by Yashwant Sahu deprecates the sha256_password plugin both as a server side authentication plugin and as a client side authentication plugin. Use the default caching_sha2_password instead. See SHA-256 Pluggable Authentication.
Remove MyISAM as engines used for on-disk internal temporary tables (WL#11974) This work by Sergey Gluhov removes the internal_tmp_disk_storage_engine system variable and thus the option to use MyISAM for internal temporary tables. MySQL will now always use the TempTable storage engine introduced in 8.0 for overflow to disk.
Thank you for using MySQL !