How do I resolve a preupgrade check failure in Amazon RDS for MySQL?

5 minute read
2

I get a preupgrade check failure when I try to upgrade from Amazon Relational Database Service (Amazon RDS) for MySQL 5.7 to MySQL 8.0.

Short description

If you get a preupgrade check failure on your DB instance, then your Amazon RDS for MySQL version upgrade stops. When Amazon RDS detects incompatibilities, it provides a log file for you to review the details of the failed precheck.

Note: A number of different prechecks are built into both the MySQL database and Amazon RDS. Prechecks don't run for upgrades on versions lower than MySQL 5.7. For upgrades from MySQL 5.6 to MySQL 5.7, prechecks are limited to detecting orphan tables and sufficient storage space to rebuild tables.

When you perform an upgrade from MySQL 5.7.x to MySQL 8.0.x, Amazon RDS and Amazon Aurora MySQL-Compatible Edition automatically run prechecks. The prechecks detect any data incompatibilities that might affect your upgrade. Prechecks are designed to minimize any unplanned downtime during a version upgrade.

If you experience a preupgrade check failure during one of these version upgrades, review the PrePatchCompatibility log file that Amazon RDS generates. You can view the PrePatchCompatibility log file under Logs & events in the Amazon RDS console. Then, review the details of the listed incompatibility issue and correct the issue. In most cases, the log entry includes a link to the Amazon RDS for MySQL documentation on how to correct the incompatibility.

Resolution

Note: If you receive errors when running AWS Command Line Interface (AWS CLI) commands, make sure that you're using the most recent AWS CLI version.

Review the preupgrade check failure message to check why the upgrade failed. See the following example:

Executing Compatibility Checks for the MySQL server at localhost:3306.
Source Version: 5.7.40-log - Source distribution, Target Version: 8.0.32.

1) Usage of old temporal type
No issues found.
2) Usage of db objects with names conflicting with new reserved keywords
No issues found.
....
Errors: 1
Warnings: 0
Database Objects Affected: 14
----------------------- END OF LOG ----------------------

Check the message for specific errors, warnings, and notice level messages. Refer to the following list to identify the error, warning, or notice you encountered, and then troubleshoot accordingly:

ERROR : Correct these issues before upgrading to avoid compatibility issues.
WARNING : No fatal errors were found that would prevent an upgrade, but some potential issues were detected.
NOTICE : No known compatibility errors or issues were found. But you can check the NOTICE Level error logs.

1) Usage of old temporal type : ERROR 
2) Usage of db objects with names conflicting with new reserved keywords : WARNING
3) Usage of utf8mb3 charset : NOTICE
4) Table names in the mysql schema conflicting with new tables in 8.0 : ERROR
5) Partitioned tables using engines with non native partitioning : ERROR
6) Foreign key constraint names longer than 64 characters : ERROR
7) Usage of obsolete MAXDB sql_mode flag : WARNING
8) Usage of obsolete sql_mode flags : NOTICE
9)  ENUM/SET column definitions containing elements longer than 255 characters : ERROR
10) Usage of partitioned tables in shared tablespaces : ERROR
11) Circular directory references in tablespace data file paths : ERROR
12) Usage of removed functions : ERROR
13) Usage of removed GROUP BY ASC/DESC syntax : ERROR
14) Removed system variables for error logging to the system log configuration : ERROR
15) Removed system variables : ERROR
16) System variables with new default values : WARNING
17) Schema inconsistencies resulting from file removal or corruption : ERROR
18) Issues reported by 'check table x for upgrade' command : ERROR or WARNING or NOTICE
19) The definer column for mysql.events cannot be null or blank. : ERROR
20) Tables with dangling FULLTEXT index reference : ERROR
21) Routines with deprecated keywords in definition : ERROR
22) DB instance must have enough free disk space : ERROR
23) Creating indexes larger than 767 bytes on tables with redundant row format might cause the tables to be inaccessible. : WARNING
24) The tables with redundant row format can't have an index larger than 767 bytes. : ERROR
25) Column definition mismatch between InnoDB Data Dictionary and actual table definition. : ERROR

Use the AWS CLI to run a command similar to the following to list the log files and download the data:

$ aws rds describe-db-log-files --db-instance-identifier <DB identifier> --query '*[].[LogFileName]' --output text

Sample:
[root@ip-x-x-x-x ec2-user]# aws rds describe-db-log-files --db-instance-identifier testinstance --query '*[].[LogFileName]' --output text
PrePatchCompatibility.log
error/mysql-error.log
error/mysql-error-running.log
error/mysql-error-running.log.2023-05-06.3
error/mysql-error-running.log.2023-05-09.4
error/mysql-error-running.log.2023-05-10.3
error/mysql-error-running.log.2023-05-12.19
mysqlUpgrade

Command to download the file:
$ aws rds download-db-log-file-portion --db-instance-identifier <DB identifier> \
--log-file-name PrePatchCompatibility.log --starting-token 0 \
--output text > <LogFileName to save a copy>

Sample:
$ aws rds download-db-log-file-portion --db-instance-identifier testinstance \
--log-file-name PrePatchCompatibility.log --starting-token 0 \
--output text > PrePatchCompatibilityCopy.log

For more information, see the MySQL documentation for Upgrading to MySQL 8.0? Here's what you need to know.

Note: It's a best practice to test your DB instance before performing a major version upgrade.

AWS OFFICIAL
AWS OFFICIALUpdated 3 years ago