Oracle released MySQL 8.0.28 on January 18th with little fanfare, as part of their four times a year release cycle. So what is included in the new release? Over the past few years, there have been some cool new features included in these ‘dot’ releases that some in the community say would have been better off being labeled as a major release. But what is in .28? Below are the more interesting changes in the database server and the shell, as there was not a whole lot changed in the other products such as router or MEM… I put my asides in italics and my views probably do not reflect the views of anyone else.
And remember, 8.0.29 is due in April.
The TL;DR synopsis is that a lot of excellent work went into MySQL Server & shell 8.0.28 but the pressing question is do you really need to install it right away? On the scale from ‘you probably should wait to upgrade’ to ‘update ASAP’ it falls into the “you can wait” category. If this was a holiday or birthday present, it would be the equivalent of receiving a twelve-pack of tube socks.
The biggest part of the release notes details over 130 fixed bugs. These fixes range from fixing memory leaks to new instrumentation in the Performance Schema.
New or Changed Functionality
Windowing functions can now have up to 127 distinct windows.
InnoDB now supports ALTER TABLE … RENAME COLUMN operations using ALGORITHM=INSTANT.
The functions FROM_UNIXTIME(), UNIX_TIMESTAMP(), and CONVERT_TZ() now handle 64-bit values on platforms that support them, including 64-bit versions of Linux, MacOS, and Windows. Maybe the 2038 bug won’t have an impact but I ain’t canceling my trip to Cabo just in case.
You can now monitor and limit the memory allocation on a global and per-user basis. You can now observe the total memory consumed by all user connections by checking the value of the global_connection_memory status variable. You can specify limits on resource consumption for each user connection by the setting connection_memory_limit variable. A user whose memory usage exceeds this amount cannot issue additional queries. You can also impose a global memory limit by setting global_connection_memory_limit. Whenever global_connection_memory exceeds that global limit, no regular users can issue new queries requiring memory usage. System users such as the MySQL root account are not bound by these limits. I like the observability strategy but worry about draconian use to throttle users and see the possibility of too many queries being run as root to circumvent the new limits.
The GnuPG key used to build the packages expired in mid-February and many trying to update their servers from an earlier version may have seen nastygrams when their package management software tried to verify the software. The fix is to download the YUM or APT repo again or download the MySQL GnuPG public key and add it to your system’s GPG keyring.
Deprecation and Removal Notes
The shortcuts ASCII, for CHARACTER SET latin1, and UNICODE, for CHARACTER SET ucs2, are now deprecated. Also, the character sets ucs2, macroman, macce, dec, and hp8 with all of their collations are now deprecated. Time to shift to UTF8MB4, if you haven’t already.
Sorting Out The Sorting
Sorts of some column types (not listed), including JSON and TEXT, sometimes exhausted the sort buffer if its size was not at least 15 times that of the largest row in the sort. Now the sort buffer need only be only 15 times as large as the largest sort key. Hopefully, this improves sorting times.
The MySQL Shell seems to be gaining new features and functions every release at an impressive rate. What started a few years ago as an alternative to the old CLI program has become a Swiss Army Knife of MySQL use.
Moving an InnoDB cluster node from one cluster to another is now facilitated by dropping and re-creating the cluster metadata schema on the instance to ensure that it corresponds exactly to that for the new cluster. This is a “nice to have” change when playing musical chairs with nodes.
MySQL Shell now supports SSH tunneling for connections to MySQL server instances. An SSH tunnel can be shared between connections to the same host from the same user connecting from the same remote server instance. MySQL Shell’s Secret Store can store passwords and passphrases for connection to an SSH server and for the identity file, to be automatically retrieved for future connections. A new shell.listSshConnections() function returns a list of the active SSH tunnels. This should be handy for cloud situations where security is a big concern.
MySQL 8.0.27 and higher includes support for multi-factor authentication. This capability includes forms of MFA that require up to three authentication values. The authentication_policy system variable defines how many authentication factors accounts may have (or are required to have) and the authentication methods that can be used for each factor. To enable authentication to the MySQL server using accounts that require multiple passwords, MySQL Shell now supports the –password1, –password2, and –password3 options for command-line connections, permitting up to three passwords to be specified. The existing –password option and the –password1 option are treated as equivalent. You can insecurely specify a password value following the option on the command line (please do not do this), or if the options are given without a password value, the MySQL Shell will prompt the user for each password in turn. These options are only supported for classic MySQL protocol connections made using command-line arguments. So not in the X Protocol?!
Skip Grant Tables and util.dumpX
MySQL Shell’s instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() could not be used with a MySQL server instance started with the –skip-grant-tables option, meaning that the utilities could not be used to extract data in this emergency situation. The utilities now detect the special account used for all connections in this situation and assume that account has all available privileges. The users option is also automatically set to false because some grant information cannot be collected in this situation. This should be handy in dire situations!
Delayed Index Generation
MySQL Shell’s dump loading utility util.loadDump() can defer the creation of secondary indexes until after the table data is loaded, with the deferTableIndexes option. Previously, the deferred indexes could not be created on tables with a secondary engine defined, because DDL statements cannot be executed on these tables. The utility now removes the SECONDARY_ENGINE clause from the table if indexes are being deferred, and adds it back in after the indexes have been created. This does make restoration of dumps much quicker.
Comments and questions should be sent to the author.