PHP Developer News

A Quick Peek At MySQL 8.0.29

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?
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
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.
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.
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
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.
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.

Most Popular in Database