Using MySQL 8.0 “Upgrade Checker” on RDS

Question

The MySQL 8.0 Shell came with an "Upgrade Checker".

Useful to run before upgrading, as you may detect incompatible issues with your table structure / etc.

Running its python version from the shell be like:

util.check_for_server_upgrade()

Unfortunately, when using AWS's RDS, we get the following error:

SystemError: LogicError: Util.check_for_server_upgrade: The upgrade check needs to be performed by user with ALL privileges.

What can be done?

Answer 1

You can run util.check_for_server_upgrade() with a Mysql user who has the ALL PRIVILEGES. As you are using AWS RDS your master MySQL user don't have SUPER user permission, that's why your are getting this error, you can check your user permission with SHOW GRANTS FOR 'user'@'host'. Only rdsadmin user have the ALL PRIVILEGES in AWS RDS MySQL instance. rdsadmin is amazon's own user that is used by Amazon to perform release or version update, taking backups and other maintenance tasks. Amazon's support should be able to advise you better in this topics.

Answer 2

SystemError: LogicError: Util.check_for_server_upgrade: The upgrade check needs to be performed by user with ALL privileges.

As per your error log, the password for the user account that is used to run the upgrade checker utility. A user account with ALL privileges is required.

As per MySQL documentation 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. 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 checks that are relevant for the specified target release.

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 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 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. A user account with ALL privileges is required. 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.13, you can specify release 8.0.11 (the first MySQL Server 8.0 GA release), 8.0.12, or 8.0.13. 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.

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. Do not use the MySQL Shell command line option --json with the upgrade checker utility; that option is not context-aware and simply wraps the text output from the utility in a JSON object.

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.13
...
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 for upgrade to the first MySQL Server 8.0 GA status release (8.0.11). The user password is 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"})

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

For your further ref here and here