The MySQL Development team is very happy to announce that MySQL 8.0.21 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.21 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.21 Release Notes. Here are the highlights. Enjoy!
InnoDB
Add config option to disable redo log globally (WL#13795) This work by Debarun Banerjee adds support for disabling and enabling the Innodb redo log dynamically (ALTER INSTANCE ENABLE|DISABLE INNODB REDO_LOG;). Disabling the redo log (WAL) makes all write operation to the database faster. It also makes the server vulnerable to a crash and the entire instance data could be lost. The primary use case for disabling redo log is while loading initial data to a mysqld instance: First disable redo logging, then load data and finally enable redo logging again. In this model data loading is fast. However, If node goes down during load then all data is lost and one needs to create a fresh instance and start all over again.
Make tablespace filename validation optional via –innodb-validate-tablespace-paths (WL#14008) This work by Niksa Skeledzija adds the option to disable the conservative approach to validate the tablespace files upon startup. The tablespace validation normally checks that all tablespaces listed in the MySQL data dictionary are found in the file system. This can be costly on low end systems with HDD and when there are many tablespace files to scan. In situations where we know that the user does not move files around we can reduce startup time by skipping the validation. The new variable –innodb-validate-tablespace-paths := (ON | OFF) defaults to ON to preserve existing behavior. Users can set it to OFF if they know that tablespace files are never moved around manually at the file system level. Users can still safely move files around using the ALTER TABLESPACE syntax, even when the option is set to OFF.
Lock-sys optimization: sharded lock_sys mutex (WL#10314) This work by Jakub Lopuszanski introduces a granular approach to latching in the InnoDB lock-sys module. The purpose is to improve the throughput for high concurrency scenarios such as with 128 – 1024 Sysbench OLTP RW clients. The lock-sys orchestrates access to tables and rows. Each table, and each row, can be thought of as a resource, and a transaction may request an access right for a resource. Two transactions operating on a single resource can lead to problems if the two operations conflict with each other, lock-sys therefore maintains lists of already GRANTED lock requests and checks new requests for conflicts. If there is a conflict they have to start WAITING for their turn. Lock-sys stores both GRANTED and WAITING lock requests in lists known as queues. To allow concurrent operations on these queues, there is a mechanism to latch these queues safely and quickly. In the past a single latch protected access to all of these queues. This scaled poorly, and the management of queues became a bottleneck, so we have introduced a more granular approach to latching.
Restrict all InnoDB tablespaces to known directories (WL#13065) This work by Kevin Lewis ensures that the placement of tablespace files are restricted to known directories (datadir, innodb_data_home_dir, innodb_directories, and innodb_undo_directory). The purpose is to allow the DBA to restrict where files can be created as well as to avoid surprises during recovery. Recovery is terminated by InnoDB if a file referenced in the redo log has not previously been discovered.
Support ACID Undo DDL (WL#11819) This work by Kevin Lewis improves the handling of undo tablespaces in terms of both reliability and performance. With this feature users can enable the automatic truncation of empty UNDO table spaces. The CREATE/TRUNCATE of undo tablespaces is now redo logged. The advantage of using REDO logging is to avoid the two hard checkpoints that were required during undo truncation in the previous solution, i.e. these hard checkpoints could lead to a stall on a busy system. This change also fixes several issues that impacted durability of UNDO, CREATE, DROP and TRUNCATE.
JSON
Add JSON_VALUE function (WL#12228) This work by Knut Anders Hatlen adds the JSON_VALUE function (SQL 2016, chapter 6.27). The function extracts the value at the specified path from the given JSON document and returns it as the specified type. For example: SELECT JSON_VALUE('{"name": "Evgen"}', '$.name') will return unquoted string ‘Evgen’ as VARCHAR(512) with JSON’s default collation, while SELECT JSON_VALUE('{"price": 123.45}', '$.price' RETURNING DECIMAL(5,2)) will return 123.45 as decimal(5,2). The main motivation is to ease index creation of JSON values.
SQL DDL
Make CREATE TABLE…SELECT atomic and crash-safe (WL#13355) This work by Gopal Shankar makes the CREATE TABLE ... AS SELECT statement atomic. Historically, MySQL has handled this statement as two different statements (CREATE TABLE and SELECT INTO) executed in two different transactions (since DDL is auto committed in MySQL). The consequence is that there have been failure scenarios where the CREATE TABLE has been committed and SELECT INTO rolled back. This problem goes away with WL#13355, the CREATE TABLE … AS SELECT will now either be committed or rolled back. This work enables the CREATE TABLE … AS SELECT in group replication. This also fixes Bug#47899 reported by Sven Sandberg.
Optimizer
Introduce new optimizer switch to disable limit optimization (WL#13929) This work by Chaithra Gopalareddy adds a new optimizer switch called “prefer_ordering_index” (ON by default). The new switch controls the optimization to switch from a non-ordering index to an ordering index for group by and order by when there is a limit clause. While this optimization is beneficial for some queries, it can produce undesirable results for others. This addition mitigates the problem reported by Jeremy Cole in Bug#97001.
Make semijoin work with single-table UPDATE/DELETE (WL#6057) This work by Guilhem Bichot makes single-table UPDATE/DELETE statements, such as UPDATE t1 SET x=y WHERE z IN (SELECT * FROM t2); and DELETE FROM t1 WHERE z IN (SELECT * FROM t2); go through the query optimizer and executor like other queries. Earlier, a shortcut bypassed optimization and went straight for a hard coded execution plan, preventing these statements from benefiting from more advanced optimization, e.g., semijoin. The old trick of adding an irrelevant table to the UDPATE/DELETE statement in order to make it a multi-table statement that is passed through the optimizer is no longer necessary. In addition, EXPLAIN FORMAT=TREE and EXPLAIN ANALYZE can now be used on these statements. This work fixes Bug#35794 reported by Alejandro Cusumano and Bug#96423 reported by Meiji Kimura.
Inject CAST nodes for comparisons with STRING non-const expressions (WL#13456) This work by Catalin Besleaga extends earlier work (WL#12108) around injecting CAST nodes in the query plan. The purpose is to make data type casting explicit and consistent within the generated query plan. WL#12108 created the infrastructure around injecting CAST nodes in comparisons between non-const expressions of different data types, but string-based non-const expressions were excluded. WL#13456 now implements the missing CAST nodes for the string-based non-const expressions.
Group Replication
Reduce minimum value of group_replication_message_cache_size (WL#13979) This work by Luís Soares lowers the minimum value of group_replication_message_cache_size from 1 GB to 128 MB. This allows the DBA to reduce the size of the XCom cache so that InnoDB Cluster can be deployed successfully on a host with a small amount of memory (like 16GB) and a good network connectivity.
Specify through which endpoints can recovery traffic flow (WL#13767) This work by Anibal Pinto implements a mechanism to specify which ips and ports are used during distributed recovery for group replication. The purpose is to control the flow of the recovery traffic in the network infrastructure, e.g. for stability or security reasons.
Compile XCom as C++ (WL#13842) This work by Tiago Vale initiates the transition from C to C++ for the XCom component. The purpose is to accelerate the development of future
XCom work due to C++’s higher-level features and richer standard library.
Classify important GR log messages as system messages (WL#13769) This work by Nuno Carvalho reclassifies some Group Replication log messages as system messages. System messages are always logged, independently of the server log level. The purpose is to ensure that the DBA can observe the main events in the group.
START GROUP_REPLICATION to support credentials as parameters (WL#13768) This work by Jaideep Karandee extends the START GROUP_REPLICATION command to accept USER, PASSWORD, DEFAULT_AUTH and PLUGIN_DIR as optional parameters for the recovery channel. The purpose is to avoid storing credentials in a file which can be a security concern in some environments.
Increase default group_replication_autorejoin_tries (WL#13706) This work by Parveez Baig increases the default group_replication_autorejoin_tries from 0 to 3. With group_replication_autorejoin_tries=0, a group replication network partition for longer than 5 seconds causes the member to exit the group and not come back, which results in the need to do a manual operation to bring the member back. The goal is to provide ‘automatic network partition handling’, including recovering from network partitions, which is most effectively achieved by setting group_replication_autorejoin_tries > 0.
Increase default group_replication_member_expel_timeout (WL#13773) This work by Pedro A Ribeiro increases the default group_replication_member_expel_timeout from 0 to 5 seconds. The default group_replication_member_expel_timeout is set to 5 seconds to decrease the likelihood of unnecessary expulsions and primary failovers on slower networks or in the presence of transient network failures. The new value for the default implies that the member will be expelled 10 seconds after becoming unreachable: 5 seconds are spent waiting before creating the suspicion that the member has left the group, then a further 5 seconds are waited before expelling the member.
Support binary log checksums (WL#9038) This work by Nuno Carvalho implements support for binlog checksums in Group Replication. Before this change the Group Replication plugin required that binlog-checksum was disabled, this restriction is now lifted. The purpose of binlog checksums is to ensure data integrity by automatically computing and validating checksums of binary logs events.
X Plugin
Support multiple –mysqlx-bind-address (WL#12715) This work by Grzegorz Szwarc allows the user to configure X Plugin bind address with multiple IP address (interfaces) where the user can skip unwanted interfaces of the host machine. The MySQL Server introduced binding to multiple addresses in 8.0.13 (WL#11652).
Router
User configurable log filename (WL#13838) This work by Thomas Nielsen adds an option to write log to filenames other than the “mysqlrouter.log” and to redirect console messages to stdout instead of stderr.
Support hiding nodes from applications (WL#13787) This work by Andrzej Religa adds support for a per-instance metadata attribute indicating that a given instance is hidden and should not be used as a destination candidate. The MySQL Router supports distributing connections across the nodes of InnoDB cluster. In general, distributing the load to all nodes is a good default and expected, but the user may have reasons to exclude a node from receiving load. For example, the user might want to exclude a given server instance from application traffic, so that it can be maintained without disrupting the incoming traffic.
Other
CREATE/ALTER USER COMMENT ‘JSON’ (WL#13562) This work by Kristofer Älvring adds a “metadata” JSON object to the User_attributes column in the mysql.user table. The “metadata” JSON object allows users to also store their user account metadata into the column, for example ALTER USER foo ATTRIBUTE '{ "free_text" : "This is a free form text" }'; will be stored as {"metadata": {"free_text": "This is a free form text"}}. The user metadata is exposed in the information schema tables for users.
Support separate set of TLS certificates for admin connection port (WL#13850) This work by Harin Vadodaria makes it possible to have different TLS certificates for the user port and the admin port. MySQL supports a dedicated port for administrative connections. Previously, both the admin connection port and regular client-server connection port shared the same set of TLS certificates. In a managed hosted environment, this poses challenges because: 1. Customers may want to bring their own certificates 2. Policy for certificate rotation may be different for internal and external certificates. We have now introduced a separate set of TLS certificates and other related configuration and status parameters specifically for the admin connection port and use a separate SSL context for connections coming from these two different ports.
Compression protocol for async client (WL#13510) This work by Rahul Sisondia adds support for protocol compression for asynchronous clients. Support for asynchronous clients was added by WL#11381 in 8.0.16. Support for protocol compression for synchronous clients was added by WL#12475 in 8.0.18. This final step ensures that protocol compression is also supported for asynchronous clients. The purpose is to reduce the network traffic across data-centers. This work is based on a contribution from Facebook (BUG#88567).
Client library safe LOAD DATA LOCAL INFILE paths/directories (WL#13168) This work by Georgi Kodinov fixes a usability issue with the LOAD DATA INFILE statement at the client side without compromising on the security aspects. The client side configuration will specify what is allowed and not allowed. Then when the server asks for a file, the client will check the specification and either accept or reject the request.
Deprecation and Removal
Deprecate support for prefix keys in partition functions (WL#13588) This work by Nischal Tonthanahal adds deprecation warnings in cases when a table includes a column having prefix key index in the PARTITION BY KEY clause. As of today the syntax is supported but has no effect on the partition calculation and in the future the syntax will give an error message.
Thank you for using MySQL!