A Quick Peek At MySQL 8.0.30

MySQL 8.0 is now over four years old and Oracle released the latest quarterly offering today with MySQL 8.0.30, which comes with lots of bug fixes and some interesting additions.  This is my take on the release notes for those who do not have the time to wade through them, comments in italics are my comments and reflect the views of only me. 

This is an interesting release with a good many bug fixes and I urge those who need fixes to upgrade as soon as they can. For everyone else, the TL;DR is that unless you are on the Oracle Cloud Infrastructure or one of the fixed bugs is causing you problems, then upgrade at your leisure. 

Deprecation and removals

Setting the  replica_parallel_workers system variable to 0 is now deprecated and to use single threading set replica_parallel_workers=1 instead.  So 1 is the new zero for this command?!

The –skip_host_cache server option is deprecated and will be removed in a future release. Use SET GLOBAL host_cache_size = 0 or set host_cache_size = 0.

New stuff

Tables without primary keys are a big problem for Oracle’s MySQL Database Service and their cloud expects them.  A previous version of the server added support for invisible primary keys to help with that issue.  Now we get Generated Invisible Primary Keys (GIPK) to automatically add an invisible primary key to any InnoDB tables without a primary key. 

The GIPK column is defined as:

The generated primary key is always named my_row_id; you cannot change this while GIPK mode is turned on. Nor can you use this as a column name in a  CREATE TABLE statement that creates a new InnoDB table unless it includes an explicit primary key. These GIPKs are not enabled by default. Enable this new feature by setting the sql_generate_invisible_primary_key  server system variable to ON. Replication is not affected as this setting has no effect on replication applier threads as a replica never generates a primary key for a replicated table that was not created on the source with a primary key.

GIPKs can only be altered to toggle the visibility of the GIPK using ALTER TABLE tbl CHANGE COLUMN my_row_id SET VISIBLE and ALTER TABLE tbl CHANGE COLUMN my_row_id SET INVISIBLE. GIPKs are visible to SHOW CREATE TABLE and SHOW INDEX plus the information_schema but can be hidden by setting show_gipk_in_create_table_and_information_schema to OFF. I will have to try a generated invisible primary key based on a generated column jsut on general principles.

And mysqldump and mysqlpump will skip GIPKs by setting the –skip-generated-invisible-primary-key option. Are these GIPKs skipped on restore and, if not, how much extra load is this going to generate?

AWS keyring

An updated keyring_aws plugin is now available to use the latest AWS Encryption SDK for C version 1.9.186.

SQL syntax notes

There are two new options for REVOKE that let you determine whether a REVOKE statement with issues raises an error or a warning. IF EXISTS causes REVOKE to raise a warning rather than an error as long as the target user or role does not exist while IGNORE UNKNOWN USER causes REVOKE to raise a warning instead of an error if the target user or role is not known. There are lots of areas where it would be nice to be able to set the error or warning issue.

XA transaction fix

Previously, Replication recovery was not guaranteed when a server node in a replication topology unexpectedly halted while executing XA_PREPARE, XA COMMIT, or XA ROLLBACK. MySQL now maintains a consistent XA transaction state across a topology using either MySQL “classic” Replication or MySQL Group Replication when a server node is lost from the topology and then rejoins. This also means that the XA transaction state is now propagated so that nodes do not diverge while doing work within a given transaction in the event that a server node halts, recovers, and rejoins the topology. Who does not like better transactions!  

InnoDB changes

The innodb_doublewrite  system variable gains a pair of two new settings, DETECT_ONLY and DETECT_AND_RECOVER. With the DETECT_ONLY setting, database page content is not written to the doublewrite buffer, and recovery does not use the doublewrite buffer to fix incomplete page writes. This lightweight setting is intended for detecting incomplete page writes only. The DETECT_AND_RECOVER setting is equivalent to the existing ON setting.

Redo logs now have the ability for dynamic configuration capacity, using the innodb_redo_log_capacity system variable to set at runtime to increase or decrease the total amount of disk space occupied by redo log files. And redo logs get a new home!  InnoDB maintains 32 redo log files in an #innodb_redo directory in the data directory instead of two files in the data directory. I wonder how moving these 32 files to another drive would impact performance. And do we really get the disk space back?

22.04 support

Ubuntu 22.04 is now supported. 

MySQL schema

Host name and user name columns are together now in the start of primary keys to avoid full table scans, The tables are mysql.db, mysql.tables_priv, mysql.columns_priv, and mysql.procs_priv are being rearranged, and, when you upgrade, these tables are modified in the second step of the MySQL upgrade process.

MyISAM

The myisqm_repair_threads system variable and myisamchk –parallel-recover option were removed. The future is InnoDB but I still have some fond memories of MyISAM and hate to see tools stripped of features.

Who is on first

One of the reasons I write these quick peeks is to keep you from having to read the curious wordings found in the release notes where technical writing collides with work log notes to create items such as the following that I quote directly.

• Connections whose users have the CONNECTION_ADMIN privilege are not terminated when MySQL Server is set to offline mode, which is done by changing the value of the offline_mode system variable to ON. Previously, checking for connections that had the CONNECTION_ADMIN privilege could cause a race condition because it involved accessing other threads. Now, a flag for each thread caches whether or not the user for the thread has the CONNECTION_ADMIN privilege. The flag is updated if the user privilege changes. When offline mode is activated for the server, this flag is checked for each thread, rather than the security context of another thread. This change makes the operation threadsafe.In addition, when offline mode is activated, connections whose users have the SYSTEM_USER privilege are now only terminated if the user that runs the operation also has the SYSTEM_USER privilege. Users that only have the SYSTEM_VARIABLES_ADMIN privilege, and do not have the SYSTEM_USER privilege, can set the offline_mode system variable to ON to activate offline mode. However, when they run the operation, any sessions whose users have the SYSTEM_USER privilege remain connected, in addition to any sessions whose users have the CONNECTION_ADMIN privilege. This only applies to existing connections at the time of the operation; users with the SYSTEM_USER privilege but without the CONNECTION_ADMIN privilege cannot make new connections to a system in offline mode.

The above is a hybrid of the nightmares I had after business law, symbolic logic, and English class assignments from my distant past. I had to resist making a flow chart when reading it.

Performance Schema

Performance Schema provides instrumentation for performance monitoring of Group Replication memory usage. To get a peek, use:

Mainframe news

Added a cycle timer for the s390x architecture.

MySQL 8.0.31 should be out in late October.