Oracle released MySQL Server 8.0.29 on April 26th and this is a quick review of the release notes. I have put my own comments in italics.
So what is in the ’29 release of MySQL Server? Does it come festooned with new, neat features or is it a big bug-fix bonanza?
The TL;DR
While this server release has some interesting stuff, there is no compelling feature that will necessitate an immediate upgrade. Read through the release notes to see if anything in there that is a must for you but for most of us, MySQL 8.0.29 does not require an immediate update. If this was a birthday or holiday present, ’29 is the equivalent of getting a fresh box of dental floss – useful but not thrilling.
The shell is evolving too and the new version for VS Code looks promising.
MySQL Server 8.0.29
UTF8MB3?
The server now makes extensive use of UTF8MB3 (yes 3, not 4) for the output of SHOW commands, reporting on invalid strings, when populating data dictionary tables from built-in character sets
Goodbye Arbitrary Delimiters in DATETIME/TIMESTAMP
The use of any nonstandard or excess delimiter or whitespace characters now triggers a warning. MySQL used to allow you to use arbitrary delimiters and use an arbitrary number of them in TIME, DATE, DATETIME, and TIMESTAMP literals plus an arbitrary number of whitespaces before, after, and between the date and time values in DATETIME and TIMESTAMP literals. Those halcyon days are now over. Expect this ability to be removed in a future release.
Deprecated Variables & Options
The replica_parallel_type, query_prealloc_size, and transaction_prealloc_size variables are deprecated with the mysqld options of –abort-slave-event-count and –disconnect-slave-event-count.
PRIMARY and SECONDARY Engine
The Performance Schema now tracks the PRIMARY and SECONDARY engine used to process a query. Usually, this will be InnoDB and HeatWave. There is now an EXECUTION_ENGINE column to record which engine did the work with a COUNT_SECONDARY column to know how many times that engine was used.
SSL Libraries
Version bumped from1.1.1l o 1.1.1.n.
IF NOT EXITS
We get IF NOT EXISTS checks on function, procedure, and triggers. We needed this for a l-o-n-g time.
Detached XA Transactions
Group replication had a problem where XA transactions prepared on another connection could not be committed. A prepared XA transaction can now be committed or rolled back by another connection. The current session can also then initiate another XA or local transaction without waiting for the prepared XA transaction to complete.
Classic and X Protocol Lock Files
It was possible that the lock file from either the MySQL Classic or X Protocol, which have different formats, could keep a server from starting. But now they use the same format.
Instant Drop Table
ALTER TABLE … DROP COLUMN ALGORITHM=INSTANT is now available.
Secure Persisted Variables
Rather than use a keyring, we can now have secure persisted variables. You still need a keyring component instead of the keyring plugin to hold the encrypted information. So the data stays with the operating system in a file, not the keyring.
Clone Delayed
Cloning data too after dropping the old data is problematic for some operating systems that free things up asynchronously. So the new clone_delay_after_data_drop variable lets you wait up to an hour before your clone arrives. Too bad we don’t get an Uber-like ‘your clone arriving in x minutes’ status update.
MyISAM
The myisam_repair_threads variable and myisamchk –parallel-check option is deprecated.
Isolated Variables
Better code for parsing, resolution, and execution of SQL statements now better enforces the isolation of access to system variables.
Bug Fixes
There are many bug fixes that take care of things from anonymous users with the PROCESS privilege could not select rows from the processlist table to histograms now storing buckets in dynamic arrays instead to reduce space overhead and will speed up selectivity estimation due to reduced indirection when performing a binary search on the buckets. I recommend reading through the bug fixes with each new release because they can trigger an ‘ah-ha’ moment when you find something that is impacting your instances that were on the edge of your awareness or provide a flood of endorphins from the schadenfreude of others’ problems.
MySQL Shell
MySQL Shell has a new diagnostics utility, util.debug.collectDiagnostics, that gathers diagnostic information from the connected MySQL server. You can get reports in TSV and YAML formats before them in a zip archive in the location of your choice. This utility enables you to retrieve diagnostic information from standalone servers, members of replication topologies, MySQL Database Service DB Systems, and so on.
And there is a shell, prompt() function to provide a way for scripts to interact with a user.
Will “ALGORITHM=INSTANT” instantly reclaim space?
Opps! Should be instant drop column not table!
A few tpyos:
– “IF NOT EXITS” -> “IF NOT EXISTS”
– “Instant Drop Table” -> “Instant Drop Column”
I look at the fact that there are no real new features in this release as a GOOD thing! Slowing the cadence of new features so that the bug backlog can be worked on and improve product stability is hugely beneficial. I advocated for various modifications to the 8 release model. This is certainly a bug-fix bonanza and I consider that a good reason to look at upgrading. One notable bug fix has to do with the INSTANT algorithm they introduced in the last release: Bug #33924532. The fact that a bugfix for 8.0.28 appears in 8.0.29 means it was critical and affected many people.
For me the “The derived materialized table condition pushdown optimization can now be used with most unions. This means that an outer WHERE condition can now be pushed down to every query block of the query expression of the materialized derived table or view.” will be a big improvement. In many cases Table Scans on UNION views will now use Indexes on the underlying tables, huge YAY.
I think an important one is missed .
Auto Binlog Purge flag with default ON in 8.0.29.
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_auto_purge
Not sure why they made the default ON but it is worth to mention this in your blog IMO.
I have installed the new mysql version and since then the backup with newest precona xtrabackup is failing with “Corrupt log record found”. Is this maybe related to the new MySQL version?
https://forums.percona.com/t/corrupt-log-record-found-mysql-8-0-29-0ubuntu0-20-04-2-and-xtrabackup-8-0-28-20/15572