So first up, the things they do tell you.. and there are some pretty good resources that you can find with some simple googline so I’m not going to repeat everything in those. To save you some time, the following article from the MySQL blog was as good and comprehensive as any I found.
What you need to know
- https://dev.mysql.com/blog-archive/upgrading-to-mysql-8-0-here-is-what-you-need-to-know/
- https://dev.mysql.com/doc/refman/8.0/en/upgrading.html
Now what they don’t tell you…
Assuming you are upgrading to the latest version of 8 (0.32 at time of writing), you should read ALL the release notes for ALL of the minor revisions. And read them carefully. This is a lesson I learnt during our MySQL8 upgrade journey because they have made some pretty big changes which just aren’t mentioned in the upgrade notes. I’ll note a few of the highlights below, but note this is just the things that were relevant to us and some that seem they will be relevant to most. Actually, having been through this process, you should definitely be reading the minor revision notes on any minor revision upgrade as well. There was some stuff hidden in these which actually significantly broke our web application.
Changes to how date queries work
This ones an absolute doozy! Buried away in the bug fixes section of the notes for 8.0.16:
When comparing
DATEvalues with constant strings, MySQL first tries to convert the string to aDATEand then to perform the comparison. When the conversion failed, MySQL executed the comparison treating theDATEas a string, which could lead to unpredictable behavior. Now in such cases, if the conversion of the string to aDATEfails, the comparison fails withER_WRONG_VALUE. (Bug #29025656)
What does this actually mean? If anything in your application can result in a date comparison with a string which isn’t in an actual date format, whereas previously MySQL would have tried to do a comparison, it now won’t – the query will just fail.
This is kind of fine in the following scenario where you wouldn’t really expect to get many results:
where aDateField > 'something'But where it really caught us out was where we had queries such as this:
where aDateField > '' and aDateField < '2023-12-12'The above is a pretty plausible scenario if you have a web app which allows the user to input criteria to find results between two date fields. Or if it allows them to enter criteria across multiple fields, one of which happens to be a date field, where they decide to leave blank criteria.
This will cause you further problems if your planned upgrade route to MySQL 8 is via replication (upgrade your read replica and then switch your master server to your replica). This is actually how we found this problem in the first place. We had a query something along the lines of this:
update table set aDateField='2023-01-10' where aDateField is null or aDateField='' or aDateField='0000-00-00' Now – ok – granted – this is bad code. However, because this now causes a query error – this caused replication to our 8 server to fail. We had to figure out a way around this without a pretty major re-write to our web app. I’ll save details on how we did this for another blog post!
Some upgrade warnings/advisories debunked:
UTF8MB3 Deprecated – upgrade to UTF8MB4 – They spend a lot of time trying to convince you to upgrade your databases from UTF8MB3 to the new UTF8 format UTF8MB4. And whilst this probably should be in your plans at some stage, it’s important to note that UTF8MB3 is only deprecated, it is not yet removed, and they do eventually admit somewhere buried in the release notes, that it’s likely to be some time before it is actually removed. If you do choose to upgrade, bear in mind this will be a full table rebuild which, if you are NOT using innodb_file_per_table, is likely to eat up some disc space that you’ll never get back (room for a blog post on that I think…). We have seen no issues with data that we left on UTF8. Also worth noting, that “USE NAMES UTF-8” in queries still uses UTF8-MB3 if that is what you set your server default character set to.
COMPACT row format – There’s some complicated jazz talked about here with indexes, row sizes and COMPACT row format. Generally, COMPACT row format is old, and shouldn’t be used anymore. The upgrade guide and check process will check your tables for COMPACT row format and likely complain (it lists these in warnings). There is a separate list of tables it thinks you NEED to upgrade in order for the upgrade to complete successfully. The warnings won’t stop you completing the upgrade, however, it’s worth mentioning that we did start to get some errors logged in the error log pertaining to tables with COMPACT row format, and row size, so it may be advisable to change any tables with COMPACT row format to DYNAMIC prior to upgrade. However, to not again, this needs a full table rebuild so you might want to check and try and avoid doing this on any particularly huge tables.
Other notable changes (that aren’t noted)
Just a few other things I picked out of the release notes which aren’t mentioned in either the MySQL 8 upgrade notes or guide. Some things which feel likely to cause application issues, and a couple of cool new features added. I’ll leave them here unedited. Note that quite a few of these are deprecations so won’t cause you issues on upgrade, but are things you may wish to prepare for.
You’ll find them all in full, in the minor release version notes when you read through them all 🙂
Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.
IDENTIFIED BY PASSWORD ‘hash_string’ syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITH auth_plugin AS ‘hash_string’ for CREATE USER and ALTER USER, where the ‘hash_string’ value is in a format compatible with the named plugin.
Additionally, because IDENTIFIED BY PASSWORD syntax has been removed, the log_builtin_as_identified_by_password system variable is superfluous and has been removed.
The PASSWORD() function. Additionally, PASSWORD() removal means that SET PASSWORD … = PASSWORD(‘auth_string’) syntax is no longer available.
= Check provisioning and DB access code.
These deprecated query cache items remain deprecated, but have no effect, and will be removed in a future MySQL version:
The SQL_NO_CACHE SELECT modifier.
= look for queries using NO_CACHEIn MySQL 8.0, the default authentication plugin was changed from mysql_native_password. to caching_sha2_password. Because caching_sha2_password provides a superset of the capabilities of the sha256_password authentication plugin, sha256_password is now deprecated and will be removed in a future MySQL version. MySQL accounts that authenticate using sha256_password should be migrated to use caching_sha2_password instead. (WL #12694)
From MySQL 8.0.22, the group_replication_ip_whitelist system variable is deprecated, and the system variable group_replication_ip_allowlist has been added to replace it. The system variable works in the same way as before, only the terminology has changed.For both system variables, the default value is AUTOMATIC. If either one of the system variables has been set to a user-defined value and the other has not, the changed value is used. If both of the system variables have been set to a user-defined value, the value of group_replication_ip_allowlist is used. (WL #14175)
From MySQL 8.0.22, the statements START SLAVE, STOP SLAVE, SHOW SLAVE STATUS, SHOW SLAVE HOSTS and RESET SLAVE are deprecated. The following aliases should be used instead:
Instead of START SLAVE use START REPLICA
Instead of STOP SLAVE use STOP REPLICA
Instead of SHOW SLAVE STATUS use SHOW REPLICA STATUS
Instead of SHOW SLAVE HOSTS use SHOW REPLICAS
Instead of RESET SLAVE use RESET REPLICA
From MySQL 8.0.23, the statement CHANGE MASTER TO is deprecated. The alias CHANGE REPLICATION SOURCE TO should be used instead. The parameters for the statement also have aliases that replace the term MASTER with the term SOURCE. For example, MASTER_HOST and MASTER_PORT can now be entered as SOURCE_HOST and SOURCE_PORT. The START REPLICA | SLAVE statement’s parameters MASTER_LOG_POS and MASTER_LOG_FILE now have aliases SOURCE_LOG_POS and SOURCE_LOG_FILE. The statements work in the same way as before, only the terminology used for each statement has changed. A deprecation warning is issued if the old versions are used.
8.0.26 – changes to peformance schema names / replica status information field names
If the incompatible changes do have an impact for you, you can set the new system variable terminology_use_previous to BEFORE_8_0_26 to make MySQL Server use the old versions of the names for the objects specified in the previous list. This enables monitoring tools that rely on the old names to continue working until they can be updated to use the new names. The system variable can be set with session scope to support individual functions, or global scope to be a default for all new sessions. When global scope is used, the slow query log contains the old versions of the names.8.0.27:
Deprecation: Important Change: The default_authentication_plugin variable is deprecated as of MySQL 8.0.27; expect support for it to be removed in a future version of MySQL.The default_authentication_plugin variable is still used in MySQL 8.0.27, but in conjunction with and at a lower precedence than the new authentication_policy system variable, which is introduced in MySQL 8.0.27 with the multifactor authentication feature. For details, see The Default Authentication Plugin. (Bug #27515356, WL #14138)
Deprecation: Important Change: The BINARY operator is now deprecated, and subject to removal in a future release of MySQL. Use of BINARY now causes a warning. Use CAST(… AS BINARY) instead. (WL #13619)
8.0.28
Support for the TLS v1.0 and TLS v1.1 connection protocols is removed as of MySQL 8.0.28. The protocols were deprecated from MySQL 8.0.26. For background, refer to the IETF memo Deprecating TLS v1.0 and TLSv 1.1. Make connections using the more-secure TLSv1.2 and TLSv1.3 protocols. TLSv1.3 requires that both the MySQL Server software and the client application were compiled with OpenSSL 1.1.1 or higher.From MySQL 8.0.28, client programs, including MySQL Shell, that support a –tls-version option for specifying TLS protocols for connections to the MySQL server cannot make a TLS/SSL connection with the protocol set to TLSv1 or TLSv1.1. If a client attempts to connect using these protocols, for TCP connections, the connection fails, and an error is returned to the client. For socket connections, if –ssl-mode is set to REQUIRED, the connection fails, otherwise the connection is made but with TLS/SSL disabled.
On the server side, the following settings are changed from MySQL 8.0.28:
Permitted values for tls_version and admin_tls_version no longer include TLSv1 or TLSv1.1.
Permitted values for group_replication_recovery_tls_version no longer include TLSv1 or TLSv1.1.
For asynchronous replication, replicas can no longer set the protocol for connections to the source server (SOURCE_TLS_VERSION option for CHANGE REPLICATION SOURCE TO) to TLSv1 or TLSv1.1.
8.0.29
Deprecated – Important Change: Previously, MySQL allowed arbitrary delimiters and an arbitrary number of them in TIME, DATE, DATETIME, and TIMESTAMP literals, as well as an arbitrary number of whitespaces before, after, and between the date and time values in DATETIME and TIMESTAMP literals. This behavior is now deprecated, and you should expect it to be removed in a future version of MySQL. With this release, the use of any nonstandard or excess delimiter or whitespace characters now triggers a warning of the form Delimiter ‘char’ in position pos in datetime value ‘value’ at row rownum is superfluous and is deprecated, followed by Please remove, or in cases in which a suitable replacement can be suggested, Prefer the standard ‘replacementchar’.A deprecation warning is returned only for the first nonstandard delimiter or whitespace character encountered in the literal value. An example is shown here:
mysql> SELECT DATE”2020/02/20″;
+——————+
| DATE”2020/02/20″ |
+——————+
| 2020-02-20 |
+——————+
1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 4095
Message: Delimiter ‘/’ in position 4 in datetime value ‘2020/02/20’ at row 1 is
deprecated. Prefer the standard ‘-‘.
1 row in set (0.00 sec)
Such warnings are not elevated to errors in strict mode.8.0.29
InnoDB: When using COMPACT or REDUNDANT row format, it was possible to create a table that exceeded the maximum row size, which could eventually result in ‘Row size too large’ errors when inserting data. BLOB prefixes were not included in the record size check performed during the CREATE TABLE operation. (Bug #33399379)8.0.30
Not an issue, but interesting:
In this release MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators.query_a INTERSECT query_b includes only rows appearing in both result sets.
query_a EXCEPT query_b returns any rows from the result set of query_a which are not in the result of query_b.
INTERSECT and EXCEPT both support DISTINCT and ALL, with DISTINCT the default in both cases. (This is the same as for UNION).
INTERSECT groups before EXCEPT or UNION, so TABLE r EXCEPT TABLE s INTERSECT TABLE t is evaluated as TABLE r EXCEPT (TABLE s INTERSECT TABLE t).
See INTERSECT Clause, and EXCEPT Clause, for additional information and examples.
0 Comments