MySQL Shell 8.0 (part of MySQL 8.0)
The util.checkForServerUpgrade()
function is an
upgrade checker utility that enables you to verify whether MySQL
server instances are ready for upgrade. From MySQL Shell 8.0.13,
you can select a target MySQL Server release to which you plan to
upgrade, ranging from the first MySQL Server 8.0 General
Availability (GA) release (8.0.11), up to the MySQL Server release
number that matches the current MySQL Shell release number. The
upgrade checker utility carries out the automated checks that are
relevant for the specified target release, and advises you of
further relevant checks that you should make manually.
You can use the upgrade checker utility to check MySQL 5.7 server
instances for compatibility errors and issues for upgrading. From
MySQL Shell 8.0.13, you can also use it to check MySQL 8.0 server
instances at another GA status release within the MySQL 8.0
release series. If you invoke
checkForServerUpgrade()
without specifying a
MySQL Server instance, the instance currently connected to the
global session is checked. To see the currently connected
instance, issue the \status
command.
The upgrade checker utility does not support checking MySQL Server instances at a version earlier than MySQL 5.7.
MySQL Server only supports upgrade between GA releases. Upgrades from non-GA releases of MySQL 5.7 or 8.0 are not supported. For more information on supported upgrade paths, see Upgrade Paths.
From MySQL Shell 8.0.16, the upgrade checker utility can check
the configuration file (my.cnf
or
my.ini
) for the server instance. The utility
checks for any system variables that are defined in the
configuration file but have been removed in the target MySQL
Server release, and also for any system variables that are not
defined in the configuration file and will have a different
default value in the target MySQL Server release. For these
checks, when you invoke
checkForServerUpgrade()
, you must provide the
file path to the configuration file.
The upgrade checker utility can operate over either an X Protocol connection or a classic MySQL protocol connection, using either TCP or Unix sockets. You can create the connection beforehand, or specify it as arguments to the function. The utility always creates a new session to connect to the server, so the MySQL Shell global session is not affected.
Up to MySQL Shell 8.0.20, the user account that is used to run
the upgrade checker utility must have ALL
privileges. From MySQL Shell 8.0.21, the user account requires
RELOAD
,
PROCESS
, and
SELECT
privileges.
The upgrade checker utility can generate its output in text format, which is the default, or in JSON format, which might be simpler to parse and process for use in devops automation.
The upgrade checker utility has the following signature:
checkForServerUpgrade (ConnectionData connectionData, Dictionary options)
Both arguments are optional. The first provides connection data if the connection does not already exist, and the second is a dictionary that you can use to specify the following options:
password
The password for the user account that is used to run the upgrade checker utility. You can provide the password using this dictionary option or as part of the connection details. If you do not provide the password, the utility prompts for it when connecting to the server.
targetVersion
The target MySQL Server version to which you plan to
upgrade. In MySQL Shell 8.0.22, you can specify release
8.0.11 (the first MySQL Server 8.0 GA release), 8.0.12,
8.0.13, 8.0.14, 8.0.15, 8.0.16, 8.0.17, 8.0.18, 8.0.19,
8.0.20, 8.0.21, or 8.0.22. If you specify the short form
version number 8.0, or omit the
targetVersion
option, the utility checks
for upgrade to the MySQL Server release number that matches
the current MySQL Shell release number.
configPath
The local path to the my.cnf
or
my.ini
configuration file for the MySQL
server instance that you are checking, for example,
C:\ProgramData\MySQL\MySQL Server
8.0\my.ini
. If you omit the file path and the
upgrade checker utility needs to run a check that requires
the configuration file, that check fails with a message
informing you that you must specify the file path.
outputFormat
The format in which the output from the upgrade checker
utility is returned. The default if you omit the option is
text format (TEXT
). If you specify
JSON
, well-formatted JSON output is
returned instead, in the format listed in
JSON output for the upgrade checker utility.
For example, the following commands verify then check the MySQL server instance currently connected to the global session, with output in text format:
mysqlsh>\status
MySQL Shell version 8.0.22 ... Server version: 5.7.25-log MySQL Community Server (GPL) ... mysqlsh>util.checkForServerUpgrade()
The following command checks the MySQL server at URI
user@example.com:3306
for upgrade to the first
MySQL Server 8.0 GA status release (8.0.11). The user password and
the configuration file path are supplied as part of the options
dictionary, and the output is returned in the default text format:
mysqlsh> util.checkForServerUpgrade('user@example.com:3306', {"password":"password", "targetVersion":"8.0.11", "configPath":"C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"})
The following command checks the same MySQL server for upgrade to the MySQL Server release number that matches the current MySQL Shell release number (the default), and returns JSON output for further processing:
mysqlsh> util.checkForServerUpgrade('user@example.com:3306', {"password":"password", "outputFormat":"JSON", "configPath":"C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"})
From MySQL 8.0.13, you can start the upgrade checker utility from the command line using the mysqlsh command interface. For information on this syntax, see Section 5.8, “API Command Line Interface”. The following example checks a MySQL server for upgrade to release 8.0.21, and returns JSON output:
mysqlsh -- util checkForServerUpgrade user@localhost:3306 --target-version=8.0.21 --output-format=JSON --config-path=/etc/mysql/my.cnf
The connection data can also be specified as named options grouped together by using curly brackets, as in the following example, which also shows that lower case and hyphens can be used for the method name rather than camelCase:
mysqlsh -- util check-for-server-upgrade { --user=user --host=localhost --port=3306 } --target-version=8.0.21 --output-format=JSON --config-path=/etc/mysql/my.cnf
The following example uses a Unix socket connection and shows the older format for invoking the utility from the command line, which is still valid:
./bin/mysqlsh --socket=/tmp/mysql.sock --user=user -e "util.checkForServerUpgrade()"
To get help for the upgrade checker utility, issue:
mysqlsh> util.help("checkForServerUpgrade")
util.checkForServerUpgrade()
no longer returns
a value (before MySQL Shell 8.0.13, the value 0, 1, or 2 was
returned).
When you invoke the upgrade checker utility, MySQL Shell connects to the server instance and tests the settings described at Preparing Your Installation for Upgrade. For example:
The MySQL server at example.com:3306, version 5.7.25-enterprise-commercial-advanced - MySQL Enterprise Server - Advanced Edition (Commercial), will now be checked for compatibility issues for upgrade to MySQL 8.0.22... 1) Usage of old temporal type No issues found 2) Usage of db objects with names conflicting with new reserved keywords Warning: The following objects have names that conflict with new reserved keywords. Ensure queries sent by your applications use `quotes` when referring to them or they will result in errors. More information: https://dev.mysql.com/doc/refman/en/keywords.html dbtest.System - Table name dbtest.System.JSON_TABLE - Column name dbtest.System.cube - Column name 3) Usage of utf8mb3 charset Warning: The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support. More information: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/charset-unicode-utf8mb3.html dbtest.view1.col1 - column's default character set: utf8 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found 5) Partitioned tables using engines with non native partitioning Error: In MySQL 8.0 storage engine is responsible for providing its own partitioning handler, and the MySQL server no longer provides generic partitioning support. InnoDB and NDB are the only storage engines that provide a native partitioning handler that is supported in MySQL 8.0. A partitioned table using any other storage engine must be altered—either to convert it to InnoDB or NDB, or to remove its partitioning—before upgrading the server, else it cannot be used afterwards. More information: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/upgrading-from-previous-series.html#upgrade-configuration-changes dbtest.part1_hash - MyISAM engine does not support native partitioning 6) Foreign key constraint names longer than 64 characters No issues found 7) Usage of obsolete MAXDB sql_mode flag No issues found 8) Usage of obsolete sql_mode flags No issues found 9) ENUM/SET column definitions containing elements longer than 255 characters No issues found 10) Usage of partitioned tables in shared tablespaces Error: The following tables have partitions in shared tablespaces. Before upgrading to 8.0 they need to be moved to file-per-table tablespace. You can do this by running query like 'ALTER TABLE table_name REORGANIZE PARTITION X INTO (PARTITION X VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table);' More information: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/mysql-nutshell.html#mysql-nutshell-removals dbtest.table1 - Partition p0 is in shared tablespace tbsp4 dbtest.table1 - Partition p1 is in shared tablespace tbsp4 11) Circular directory references in tablespace data file paths No issues found 12) Usage of removed functions Error: Following DB objects make use of functions that have been removed in version 8.0. Please make sure to update them to use supported alternatives before upgrade. More information: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/mysql-nutshell.html#mysql-nutshell-removals dbtest.view1 - VIEW uses removed function PASSWORD 13) Usage of removed GROUP BY ASC/DESC syntax Error: The following DB objects use removed GROUP BY ASC/DESC syntax. They need to be altered so that ASC/DESC keyword is removed from GROUP BY clause and placed in appropriate ORDER BY clause. More information: https://docs.oracle.com/cd/E17952_01/mysql-8.0-relnotes-en/news-8-0-13.html#mysqld-8-0-13-sql-syntax dbtest.view1 - VIEW uses removed GROUP BY DESC syntax dbtest.func1 - FUNCTION uses removed GROUP BY ASC syntax 14) Removed system variables for error logging to the system log configuration No issues found 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://docs.oracle.com/cd/E17952_01/mysql-8.0-en/added-deprecated-removed.html#optvars-removed log_builtin_as_identified_by_password - is set and will be removed show_compatibility_56 - is set and will be removed 16) System variables with new default values 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. More information: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ back_log - default value will change character_set_server - default value will change from latin1 to utf8mb4 collation_server - default value will change from latin1_swedish_ci to utf8mb4_0900_ai_ci event_scheduler - default value will change from OFF to ON [...] 17) Zero Date, Datetime, and Timestamp values Warning: By default zero date/datetime/timestamp values are no longer allowed in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in SQL_MODE by default. These modes should be used with strict mode as they will be merged with strict mode in a future release. If you do not include these modes in your SQL_MODE setting, you are able to insert date/datetime/timestamp values that contain zeros. It is strongly advised to replace zero values with valid ones, as they may not work correctly in the future. More information: https://lefred.be/content/mysql-8-0-and-wrong-dates/ global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE which allows insertion of zero dates session.sql_mode - of 2 session(s) does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE which allows insertion of zero dates dbtest.date1.d - column has zero default value: 0000-00-00 18) Schema inconsistencies resulting from file removal or corruption No issues found 19) Tables recognized by InnoDB that belong to a different engine No issues found 20) Issues reported by 'check table x for upgrade' command No issues found 21) New default authentication plugin considerations 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: [mysqld] default_authentication_plugin=mysql_native_password However, 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. If you are using replication please take time to understand how the authentication plugin changes may impact you. More information: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication Errors: 7 Warnings: 36 Notices: 0 7 errors were found. Please correct these issues before upgrading to avoid compatibility issues.
In this example, the checks carried out on the server instance returned some errors for the upgrade scenario that were found on the checked server, so changes are required before the server instance can be upgraded to the target MySQL 8.0 release.
When you have made the required changes to clear the error count for the report, you should also consider making further changes to remove the warnings. Those configuration improvements would make the server instance more compatible with the target release. The server instance can, however, be successfully upgraded without removing the warnings.
As shown in this example, the upgrade checker utility might also provide advice and instructions for further relevant checks that cannot be automated and that you should make manually, which are rated as either warning or notice (informational) level.
When you select JSON output using the
outputFormat
dictionary option, the JSON
object returned by the upgrade checker utility has the following
key-value pairs:
Host name and port number for MySQL Shell's connection to the MySQL server instance that was checked.
Detected MySQL version of the server instance that was checked.
Target MySQL version for the upgrade checks.
Number of errors found by the utility.
Number of warnings found by the utility.
Number of notices found by the utility.
Text of the summary statement that would be provided at the end of the text output (for example, "No known compatibility errors or issues were found.").
An array of JSON objects, one for each individual upgrade issue that was automatically checked (for example, usage of removed functions). Each JSON object has the following key-value pairs:
The ID of the check, which is a unique string.
A short description of the check.
"OK" if the check ran successfully, "ERROR" otherwise.
A long description of the check (if available) incorporating advice, or an error message if the check failed to run.
If available, a link to documentation with further information or advice.
An array (which might be empty) of JSON objects representing the errors, warnings, or notices that were found as a result of the check. Each JSON object has the following key-value pairs:
The message level, one of Error, Warning, or Notice.
A string identifying the database object to which the message relates.
If available, a string with a specific description of the issue with the database object.
An array of JSON objects, one for each individual upgrade issue that is relevant to your upgrade path and needs to be checked manually (for example, the change of default authentication plugin in MySQL 8.0). Each JSON object has the following key-value pairs:
The ID of the manual check, which is a unique string.
A short description of the manual check.
A long description of the manual check, with information and advice.
If available, a link to documentation with further information or advice.