https://www.percona.com/blog/wp-content/uploads/2022/08/MySQL-Upgrade-Testing.png
My last blog, Percona Utilities That Make Major MySQL Version Upgrades Easier, detailed the tools available from the Percona toolkit that assists us with major MySQL version upgrades. The pt-upgrade tool aids in testing application queries and generates reports on how each question performs on servers running various versions of MySQL.
MySQL Shell Upgrade Checker is a utility that helps in compatibility tests between MySQL 5.7 instances and MySQL 8.0 upgrades, which is part of the mysql-shell-utilities. The util.checkForServerUpgrade() function checks whether the MySQL 5.7 instance is ready for the MySQL 8.0 upgrade and generates a report with warnings, errors, and notices for preparing the current MySQL 5.7 setup for upgrading to MySQL 8.0.
We can run this Upgrade Checker Utility in the current MySQL 5.7 environment to generate the report; I would recommend running it on any of the replica instances that have the same configuration as the production.
The user account used to execute the upgrade checker tool must have ALL rights up to MySQL Shell 8.0.20. The user account requires RELOAD, PROCESS, and SELECT capabilities as of MySQL Shell 8.0.21.
How to generate a report using Upgrade Checker Utility
To generate a report using Upgrade Checker Utility we may either login to the shell prompt or execute directly from the command prompt.
mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf > CheckForServerUpgrade_Report.txt Please provide the password for 'mysqluser@localhost:3306':
$ mysqlsh
MySQL JS > util.checkForServerUpgrade('root@localhost:3306', { "targetVersion":"8.0.29", "configPath":"/etc/my.cnf"})
Please provide the password for 'mysqluser@localhost:3306':
To quit the mysqlsh command prompt, type \exit.
MySQL JS > \exit Bye!
Do pt-upgrade and Upgrade Checker Utility do the same tests? No!
Don’t confuse the Upgrade Checker Utility with the pt-upgrade tool since they are used for different kinds of major version upgrade testing. The Upgrade Checker Utility performs a variety of tests on the selected MySQL server to ascertain whether the upgrade will be successful; however, the tool does not confirm whether the upgrade is compatible with the application queries or routines.
Does it check both my.cnf file and the MySQL server variables?
The utility can look for system variables declared in the configuration file (my.cnf) but removed in the target MySQL Server release, as well as system variables not defined in the configuration file but with a different default value in the target MySQL Server release. You must give the file path to the configuration file when executing checkForServerUpgrade() for these checks. However, the tool is unable to identify the variables that have been deleted in the my.cnf file but are set in the MySQL server.
Let us remove query_cache_type from /etc/percona-server.conf.d/mysqld.cnf and run the command.
]# mysql -uroot -p -e "SHOW VARIABLES WHERE Variable_Name IN ('query_cache_type','query_cache_size')"
Enter password:
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| query_cache_size | 1048576 |
| query_cache_type | ON |
+------------------+---------+
]# cat /etc/my.cnf
#
# The Percona Server 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
# Please make any edits and changes to the appropriate sectional files
# included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-server.conf.d/
]#
Remove query_cache_type variable from mysqld.cnf:
]# sed -i '/query_cache_type/d' /etc/percona-server.conf.d/mysqld.cnf
]#
]# grep -i query /etc/my.cnf /etc/percona-server.conf.d/mysqld.cnf
/etc/percona-server.conf.d/mysqld.cnf:query_cache_size=5058320
]#
As the query cache type variable has been deleted from my.cnf, the tool is unable to detect it.
# mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf | grep -B 6 -i "query_cache"
15) Removed system variables
Error: Following system variables that were detected as being used will be
removed. Please update your system to not rely on them before the upgrade.
More information:
https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
query_cache_size - is set and will be removed
ERROR: 1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.
In JSON format, the report looks like this:
Note: To make the blog more readable, I shortened the report.
# mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf --output-format=JSON
{
"serverAddress": "localhost:3306",
"serverVersion": "5.7.39-42 - Percona Server (GPL), Release 42, Revision b0a7dc2da2e",
"targetVersion": "8.0.29",
"errorCount": 1,
"warningCount": 27,
"noticeCount": 1,
"summary": "1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.",
"checksPerformed": [
{
"id": "oldTemporalCheck",
"title": "Usage of old temporal type",
"status": "OK",
"detectedProblems": []
},
{
"id": "reservedKeywordsCheck",
"title": "Usage of db objects with names conflicting with new reserved keywords",
"status": "OK",
"detectedProblems": []
},
…
{
"id": "sqlModeFlagCheck",
"title": "Usage of obsolete sql_mode flags",
"status": "OK",
"description": "Notice: The following DB objects have obsolete options persisted for sql_mode, which will be cleared during upgrade to 8.0.",
"documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals",
"detectedProblems": [
{
"level": "Notice",
"dbObject": "global system variable sql_mode",
"description": "defined using obsolete NO_AUTO_CREATE_USER option"
}
]
},
{
"id": "enumSetElementLenghtCheck",
"title": "ENUM/SET column definitions containing elements longer than 255 characters",
"status": "OK",
"detectedProblems": []
},
…
{
"id": "removedSysVars",
"title": "Removed system variables",
"status": "OK",
"description": "Error: Following system variables that were detected as being used will be removed. Please update your system to not rely on them before the upgrade.",
"documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed",
"detectedProblems": [
{
"level": "Error",
"dbObject": "query_cache_size",
"description": "is set and will be removed"
}
]
},
{
"id": "sysVarsNewDefaults",
"title": "System variables with new default values",
"status": "OK",
"description": "Warning: Following system variables that are not defined in your configuration file will have new default values. Please review if you rely on their current values and if so define them before performing upgrade.",
"documentationLink": "https://mysqlserverteam.com/new-defaults-in-mysql-8-0/",
"detectedProblems": [
{
"level": "Warning",
"dbObject": "back_log",
"description": "default value will change"
},
{
"level": "Warning",
"dbObject": "innodb_max_dirty_pages_pct",
"description": "default value will change from 75 (%) 90 (%)"
}
]
},
{
"id": "zeroDatesCheck",
"title": "Zero Date, Datetime, and Timestamp values",
"status": "OK",
"detectedProblems": []
},
…
],
"manualChecks": [
{
"id": "defaultAuthenticationPlugin",
"title": "New default authentication plugin considerations",
"description": "Warning: The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file:\n\n[mysqld]\ndefault_authentication_plugin=mysql_native_password\n\nHowever, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security.\nIf you are using replication please take time to understand how the authentication plugin changes may impact you.",
"documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues\nhttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication"
}
]
}
Please read Daniel Guzmán Burgos’ blog post to find out more about the Upgrade Checker Utility and click the link to learn more about the pt-upgrade testing.
Prior to a major version upgrade, application query testing and configuration checks are an inevitable task, and the pt-upgrade and “Upgrade Checker Utility” are quite helpful.
Planet MySQL