MySQL Shell 9.2  /  MySQL Shell Utilities  /  Upgrade Checker Utility

12.1 Upgrade Checker Utility

The util.checkForServerUpgrade() function is an upgrade checker utility that enables you to verify whether MySQL server instances are ready for upgrade. 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.

About the Utility

You can use the upgrade checker utility to check MySQL 5.7 server instances, and MySQL 8.x server instances at another GA status release within the MySQL 8.x release series, for compatibility errors and issues for upgrading. 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.

Note
  1. The upgrade checker utility does not support checking MySQL Server instances older than MySQL 5.7.

  2. MySQL Server only supports upgrade between GA releases from 5.7 onwards. Upgrades from non-GA releases are not supported. For more information on supported upgrade paths, see Upgrade Paths.

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

Running the Utility

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.

The user account that is used to run the upgrade checker utility requires RELOAD, PROCESS, and SELECT privileges.

The upgrade checker utility has the following signature:

Press CTRL+C to copy
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. You can specify any release from 8.0.11 (the first MySQL Server 8.0 GA release) up to the MySQL Server release with the same version number as the MySQL Shell release that you are using. If you specify the short form version number, for example 8.0, or omit the targetVersion option, the utility checks for upgrade to the MySQL Server release number that matches the release number for the MySQL Shell release that you are using.

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.1\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 from the Upgrade Checker Utility.

include

Comma-separated list of the upgrade checks to run. Only the specified checks are run. If a check is defined in both the include and exclude list, an error is returned.

For example:

Press CTRL+C to copy
"include": ["invalidPrivileges", "removedSysVars", "sysVarsNewDefaults"]

See Utility Checks.

exclude

Comma-separated list of the upgrade checks to ignore. If a check is defined in both the include and exclude list, an error is returned.

For example:

Press CTRL+C to copy
"exclude": ["invalidPrivileges", "removedSysVars", "sysVarsNewDefaults"]

See Utility Checks.

list

Returns a list of all checks included and excluded from the current configuration, a description of the check, and the versions to which it applies.

For example:

Press CTRL+C to copy
{"list": true, "targetVersion": "8.4.0"}

See Utility Checks.

For example, the following commands verify, then check the MySQL server instance currently connected to the global session, with output in text format:

Press CTRL+C to copy
mysqlsh> \status \status MySQL Shell version 8.1.0-commercial ... Server version: 8.1.0-commercial MySQL Enterprise Server - Commercial ... mysqlsh> util.checkForServerUpgrade()

The following command checks the MySQL server at URI user@example.com:3306 for upgrade to MySQL Server release 8.1.0. 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:

Press CTRL+C to copy
mysqlsh> util.checkForServerUpgrade('user@example.com:3306', {"password":"password", "targetVersion":"8.1.0", "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:

Press CTRL+C to copy
mysqlsh> util.checkForServerUpgrade('user@example.com:3306', {"password":"password", "outputFormat":"JSON", "configPath":"C:/ProgramData/MySQL/MySQL Server 8.0/my.ini"})

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 Integration”. The following example checks a MySQL server for upgrade to release 8.0.27, and returns JSON output:

Press CTRL+C to copy
mysqlsh -- util checkForServerUpgrade user@localhost:3306 --target-version=8.0.27 --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:

Press CTRL+C to copy
mysqlsh -- util check-for-server-upgrade { --user=user --host=localhost --port=3306 } --target-version=8.0.27 --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:

Press CTRL+C to copy
./bin/mysqlsh --socket=/tmp/mysql.sock --user=user -e "util.checkForServerUpgrade()" --js
Note

MySQL Shell's default language mode is SQL. To run a JavaScript operation from the command line, you must add --js to the command. To use the Python version of an operation, such as util.check_for_server_upgrade(), you must add --py.

To get help for the upgrade checker utility, issue:

Press CTRL+C to copy
mysqlsh> util.help("checkForServerUpgrade")

util.checkForServerUpgrade() does not return a value.

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. The output is similar to the following:

Press CTRL+C to copy
The MySQL server at example.com:3306, version 5.7.33-enterprise-commercial-advanced - MySQL Enterprise Server - Advanced Edition (Commercial), will now be checked for compatibility issues for upgrade to MySQL 8.0.29... 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://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html dbtest.view1.col1 - column's default character set: utf8 ............................ ............................ 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: links... 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.

Utility Checks

The upgrade checker performs the following checks:

  • oldTemporal: Checks for usage of deprecated temporal types.

  • syntax: Checks syntax of routines, triggers, and events using the SQL language corresponding to the target server version.

    Run this check if upgrading the following:

    • from an LTS series to the next LTS or Innovation series. For example: from 8.0 to 8.4.

    • from an Innovation series to the next LTS series. For example: from 9.1 to 9.7.

    • within an Innovation series. For example: from 9.1 to 9.2.

  • reservedKeywords: Checks database object names for conflicts with reserved keywords. See Keywords and Reserved Words.

  • utf8mb3: Checks for usage of the utf8mb3 character set. While utf8mb3 is supported, utf8mb4 is recommended for improved Unicode support. See The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding).

  • mysqlSchema: Checks for table names in the mysql schema which conflict with tables in the target version.

  • nonNativePartitioning: Checks for partitioned tables using non-native partitioning.

  • foreignKeyLength: Checks for foreign key constraint names longer than 64 characters. See Preparing Your Installation for Upgrade.

  • maxdbSqlModeFlags: Checks for usage of the obsolete sql_mode flag, MAXDB.

  • obsoleteSqlModeFlags: Checks for usage of obsolete sql_mode flags.

  • enumSetElementLength: Checks for ENUM/SET column definitions containing elements longer than 255 characters.

  • partitionedTablesInSharedTablespaces: Checks for partitioned tables in shared tablespaces.

  • circularDirectory: Checks for circular directory references in tablespace data file paths.

  • removedFunctions: Checks for functions which were removed in the target version of MySQL.

  • groupbyAscSyntax: Checks for GROUP BY ASC or DESC syntax.

  • removedSysLogVars: Checks for old system variables used to configure system logging.

  • sysvar: Performs the following checks:

    • Checks for system variables with different default values in the target version.

    • Checks system variables for valid values.

    • Checks for system variables which are in use in the source but were deprecated or removed in the target version. Meaning the system variables are set on the source with non-default values.

  • zeroDates: Checks for zero date, datetime, and timestamp values.

  • schemaInconsistency: Checks for schema inconsistencies resulting from file removal or corruption.

  • ftsInTablename: Checks for tablenames containing FTS, which is not supported in MySQL 8.0 or higher.

  • engineMixup: Checks for tables recognized by InnoDB but belonging to a different engine.

  • oldGeometryTypes: Checks for spatial data columns created in MySQL 5.6.

  • checkTableCommand: Checks for issues reported by the CHECK TABLE command.

  • defaultAuthenticationPlugin: Checks for older authentication plugins, such as mysql_native_password.

  • defaultAuthenticationPluginMds: Checks for older authentication plugins, such as mysql_native_password.

  • changedFunctionsInGeneratedColumns: Checks for indexes on functions whose semantics have changed in the target version.

  • columnsWhichCannotHaveDefaults: Checks for columns which cannot have default values (BLOB, TEXT, GEOMETRY, and JSON.)

  • invalid57Names: Checks for invalid table names and schema names used in MySQL 5.7.

  • orphanedObjects: Checks for orphaned routines and events in MySQL 5.7.

  • dollarSignName: Checks for deprecated usage of single dollar signs ($) in object names.

  • indexTooLarge: Check for large indexes which are not supported by MySQL 8.0, or higher.

  • emptyDotTableSyntax: Checks for deprecated .tableName syntax used in routines.

  • invalidEngineForeignKey: Checks for columns with foreign keys pointing to tables from a different database engine.

  • deprecatedDefaultAuth: Checks for deprecated or invalid default authentication methods in system variables.

  • deprecatedRouterAuthMethod: Check for deprecated or invalid authentication methods in use by MySQL Router internal accounts.

  • deprecatedTemporalDelimiter: Checks for deprecated temporal delimiters in table partitions.

  • innodbRowFormat: Checks for InnoDB tables with non-default row format.

  • authMethodUsage: Checks for deprecated or invalid user authentication methods.

  • pluginUsage: Checks for deprecated or removed plugins.

  • columnDefinition: Checks for errors in column definitions.

  • invalidPrivileges: Checks for user privileges that will be removed.

  • partitionsWithPrefixKeys: Checks for partitions by key using columns with prefix key indexes. See Restrictions and Limitations on Partitioning.

  • foreignKeyReferences: Checks for foreign keys referencing non-unique and partial indexes.

JSON Output from the Upgrade Checker Utility

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:

serverAddress

Host name and port number for MySQL Shell's connection to the MySQL server instance that was checked.

serverVersion

Detected MySQL version of the server instance that was checked.

targetVersion

Target MySQL version for the upgrade checks.

errorCount

Number of errors found by the utility.

warningCount

Number of warnings found by the utility.

noticeCount

Number of notices found by the utility.

summary

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.").

checksPerformed

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:

id

The ID of the check, which is a unique string.

title

A short description of the check.

status

"OK" if the check ran successfully, "ERROR" otherwise.

description

A long description of the check (if available) incorporating advice, or an error message if the check failed to run.

documentationLink

If available, a link to documentation with further information or advice.

detectedProblems

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:

level

The message level, one of Error, Warning, or Notice.

dbObject

A string identifying the database object to which the message relates.

description

If available, a string with a specific description of the issue with the database object.

dbObjectType

The type of dbObject. This can be one of the following: Schema, Table, View, Column, Index, ForeignKey, Routine, Event, Trigger, SystemVariable, User, Tablespace, or Plugin.

manualChecks

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:

id

The ID of the manual check, which is a unique string.

title

A short description of the manual check.

description

A long description of the manual check, with information and advice.

documentationLink

If available, a link to documentation with further information or advice.