The MySQL Development team is very happy to announce that MySQL 8.0.22 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.22 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.22 Release Notes. Here are the highlights. Enjoy!
Prepare each DML statement once (WL#9384) This work by Roy Lyseng improves prepared statements in MySQL. Historically, only the parse stage has been reused for each execution. With this work both the parse stage and the resolve stage will be prepared once and executed many times when prepared statements are executed. The planning stage will still be repeated for each prepared statement execution. The motivation for this work is to improve performance by repeated executions and also to simplify the code base.
Reimplement SHOW PROCESSLIST as a view on top of PERFORMANCE_SCHEMA tables (WL#9090) This work by Chris Powers creates an alternative implementation of SHOW PROCESSLIST where the active thread data is queried from the Performance Schema rather than the thread manager. The current implementation iterates across active threads from within the thread manager while holding a global mutex, which can be prohibitively intrusive on busy systems. Aggregating the same information from performance schema will not affect the user load in any way. The performance_schema_show_processlist variable determines which SHOW PROCESSLIST implementation to use.
Retrieve the UTC time stamp value stored in a table (WL#12535) This work by Martin Hansson adds the AT TIME ZONE operator that can be used for retrieving a TIMESTAMP value in UTC time. For example when the session’s time zone is CET and time is 2020-04-29 16:43:19, the
SELECT cast( a AT TIME ZONE 'UTC' AS DATETIME ) FROM t1; will result in UTC time which is 2020-04-29 14:43:19.
Read Only Schema
Read only option for schema (WL#13369) This work by Sivert Sørumgård provides a mechanism to prohibit writes to entities in a schema. A new schema option ‘read only’ is introduced. It can be set in an ALTER SCHEMA statement, but not in CREATE SCHEMA statements, where its presence will cause a syntax error. To alter this option, the ALTER privilege is needed on the schema. This work is based upon a contribution from Facebook, see Bug#93971.
Make error-log available for queries from a connection (WL#13681) This work by Tatjana Nuernberg makes the error log accessible by clients over a normal MySQL connection. The error-log is made available via the performance schema error_log table. The server reads the error log from file upon startup and maintains the last N entries while executing. This gives users access to error log information without having an account on operating system level, and granting them access via SSH / read privileges on file-system level, or setting up monitoring tools for them.
Avoid locking reads from ACL tables (WL#14087) This work by Gopal Shankar relaxes the isolation semantics for reading the Access Control List (ACL) system tables in some cases. The motivation is to avoid that non-admin users can block ACL DDL by simply reading from these tables, for example by using mysqldump.
Lock improvements for ACL DDLs (WL#14084) This work by Harin Vadodaria modifies the locking mechanism to improve user management DDLs and FLUSH operations in a situation where other connections are holding locks on ACL tables. See also WL#14087 above.
Disable ALTER USER/RENAME for SQL DEFINER users in SPs and views (WL#14073) This work by Satish Bharathy ensures that DROP USER and RENAME USER fail if there are stored programs, views or scheduled tasks that have the user as a definer. Such database objects must be deleted before the definer can be dropped or renamed.
Condition pushdown to derived tables (WL#8084) This work by Chaithra Gopalareddy reduces the number of rows that need to be processed by pushing down the WHERE condition from the outer select down to the materialized derived table whenever possible. For example the query
SELECT * FROM (SELECT i, j FROM t1) as dt WHERE i > 10; can be transformed to
SELECT * FROM (SELECT i, j FROM t1 WHERE i > 10) as dt;
Add CAST to YEAR function (WL#14015) This work by Catalin Besleaga extends the CAST function to allow for the YEAR argument. By allowing the YEAR argument to the CAST function, it will be possible for arguments of any data type (except GEOMETRY) to be correctly converted to the YEAR data type.
Unify the two field lists in SELECT_LEX (WL#13990) This work by Steinar H. Gunderson unifies the code for fields_list (visible fields) and all_fields (visible _and_
invisible fields). This work is internal code refactoring with no user impact.
Make RIGHT JOIN be fully converted to LEFT JOIN (WL#6059) This work by Guilhem Bichot completes the transformation from RIGHT JOIN to LEFT JOIN. With this work the necessary reversals are done at parse time, so that after parsing, the RIGHT JOIN is in all respects a LEFT JOIN. This work is internal code refactoring with no user impact.
Automatic connection failover for Async Replication Channels (WL#12649) This work by Hemant Dangi implements a mechanism, in asynchronous replication, that makes the replica automatically try to re-establish an asynchronous replication connection to a different source when the current source becomes unreachable or fails. The motivation is to make the deployment fault-tolerant by automating the process of re-establishment of an asynchronous replication connection to a different source. The new source is automatically picked from a list of alternative sources in the system.
Replace SLAVE in SQL commands (WL#14171) This work by Luis Soares implements aliases for START SLAVE, STOP SLAVE, SHOW SLAVE STATUS, SHOW SLAVE HOSTS and RESET SLAVE. The aliases are START REPLICA, STOP REPLICA, SHOW REPLICA STATUS, SHOW REPLICAS and RESET REPLICA respectively. This work is part of a broader effort to modernize the terminology used in the product, see MySQL Terminology Updates.
Rename and deprecate group_replication_ip_whitelist (WL#14175) This work by Jaideep Karande deprecates the configuration variable called group_replication_ip_whitelist in favour of a new variable called group_replication_ip_allowlist. This work is part of a broader effort to modernize the terminology used in the product, see MySQL Terminology Updates.
Scale routing core to 50k connections (WL#10703) This work by Jan Kneschke refactors the routing plugin to handle a large number of incoming connections. The design is changed from one OS thread per connection to roughly one OS thread per available CPU core, which improves the efficiency of the MySQL Router.
Enable REST Interface at bootstrap (WL#13906) This work by Tomasz Stepniak configures the Router’s REST API during the Router’s bootstrap step, making it easier to monitor the router.
Synchronize component tests via systemd notify (WL#13707 and WL#13708) This work by Andrzej Religa reduces the time it takes to run the Router testsuite. This is done by removing unnecessary waiting during test startup by utilizing the systemd notify support rather than waiting a fixed amount of time.
Periodic synchronization with storage device for select into outfile/dumpfile controlled by system variables (WL#13926) This work by Sergey Gluhov provides a way to periodically write data to the storage device to prevent write stalls from happening. Three GLOBAL variables with SESSION override are added. The select_into_disk_sync (ON/OFF) enables buffer synchronization of writes to an output file by a long-running
SELECT INTO or
SELECT INTO DUMPFILE statement. The select_into_buffer_size controls the size of the buffer after which a fsync will be done. After each fsync you can optionally sleep for select_into_disk_sync_delay milliseconds. This feature was contributed by Facebook, see Bug#96799.
InnoDB: Add config option to use fallocate() on Linux (WL#13782) This work by Rahul Agarkar introduces a new dynamic option called innodb_extend_and_initialize. If the variable is set to TRUE, then the server will allocate the space and initialize it with NULLs and a log entry will be added to the redo log for recovery purposes. If the variable is set to FALSE, then the server will allocate the space without initializing it with NULLs and a log entry will be added to the redo log for recovery purposes. The motivation is to make inserts faster for data loading by using fallocate().
libmysql support for DNS SRV (WL#13905) This work by Georgi Kodinov adds support for DNS SRV (RFC 2782) in the MySQL client library. In high availability scenarios, data is often mirrored into a cluster of servers. Presently, developers can share the query load across these servers but they must list every server in the connection string. This is cumbersome since changing any of the server hostnames or adding/dropping servers can involve changing the code or configuration file across the entire app farm. DNS SRV records allows a DNS admin to map a single DNS domain to multiple servers and the DNS admin can update this in a central location. DNS SRV records are widely used and are the standard mechanism used to enumerate a list of implementing servers for a given host,domain. See Connecting to the Server Using DNS SRV Records.
Deprecation and Removal
Deprecate INFORMATION_SCHEMA.TABLESPACES (WL#14064) This work by Nischal Tonthanahal deprecates the INFORMATION_SCHEMA.TABLESPACES table since it is not in use. The table will be removed in a future major release.
Thank you for using MySQL!