MySQL Shell 8.0  /  MySQL Shell Utilities

Chapter 5 MySQL Shell Utilities

MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which currently provides the single function checkForServerUpgrade(). This function is an upgrade checker utility that enables you to verify whether MySQL server instances are ready for upgrading to MySQL 8.

From MySQL Shell 8.0.12, the upgrade checker utility has the following signature:

Integer 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 of options, consisting of a password and an output format.

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

A user account with ALL privileges must be used to run the upgrade checker utility. You can provide the password for the user either as part of the connection details or as one of the options specified in the dictionary. If you do not provide the password, the utility prompts for it when connecting to the server.

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.11
...
Server version:               5.7.21-log MySQL Community Server (GPL)
...
mysqlsh> util.checkForServerUpgrade()

The following command checks the MySQL server at URI user@example.com:3306 and supplies the user password as part of the options dictionary. The output is returned in text format:

mysqlsh> util.checkForServerUpgrade('user@example.com:3306', {"password":"password"})

The following command checks the same MySQL server but returns JSON output for further processing:

mysqlsh> util.checkForServerUpgrade('user@example.com:3306', {"password":"password", "outputFormat":"JSON"})

The outputFormat dictionary option returns well-formatted JSON output from the utility, in the format listed in JSON output for the upgrade checker utility. Do not use the MySQL Shell command line option --json here, which is not context-aware and simply wraps the text output from the utility in a JSON object.

You can start the upgrade checker utility from the command line, for example:

mysqlsh root:@localhost:3306 -e "util.checkForServerUpgrade({\"outputFormat\":\"JSON\"})"

Or using a Unix socket connection:

./bin/mysqlsh --socket=/tmp/mysql.sock --user=root -e "util.checkForServerUpgrade()"

To get help for the upgrade checker utility, issue:

mysqlsh> util.help("checkForServerUpgrade")

The return value of util.checkForServerUpgrade() is:

  • 0 if no issues were found

  • 1 if warnings were found

  • 2 if errors were found

If you execute the utility from the command line (for example, using the -e argument), the return value is not printed.

MySQL Shell connects to the server instance and tests the settings described at Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation. For example:

The MySQL server at example.com:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.21 - MySQL Community Server (GPL)

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with reserved keywords in 8.0
  Warning: The following objects have names that conflict with reserved keywords that are new to 8.0. 
  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

  uc.System - Table name
  uc.System.JSON_TABLE - Column name
  uc.System.cube - Column name

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Foreign key constraint names longer than 64 characters
  No issues found

6) Usage of obsolete MAXDB sql_mode flag
  No issues found

7) Usage of obsolete sql_mode flags
  No issues found

8) Usage of partitioned tables in shared tablespaces
  No issues found

9) Usage of removed functions
  No issues found

10) Issues reported by 'check table x for upgrade' command
  No issues found

Errors:   0
Warnings: 3
Notices:  0

No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. 
Please ensure that the reported issues are not significant before upgrading.

In this case the server instance being checked generated some warnings, so it can be upgraded to MySQL 8, but the configuration could be improved to make the server more compatible with MySQL 8. A server instance that passes all of the tests can be upgraded to MySQL 8 with no changes. A server instance that fails any of the tests requires changes before it can be upgraded to MySQL 8.

JSON output for 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

Address of the server instance that was checked.

serverVersion

Detected MySQL version of the server instance that was checked.

targetVersion

Target MySQL version for the upgrade.

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 summary statement that would be provided at the end of the text output (for example, "No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.").

checksPerformed

An array of JSON objects, one for each individual upgrade issue 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 did not run successfully.

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 Notice, Warning, or Error.

dbObject

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

description

If available, a string with a further description of the issue.