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

Short description

Amazon RDS and Amazon Aurora MySQL-Compatible Edition have automatic prechecks that minimize any unplanned downtime during a version upgrade. When you upgrade from MySQL version 5.7 to 8.0 or from 8.0 to 8.4, the prechecks detect any data incompatibility that might affect your upgrade. When you get a preupgrade check failure on your DB instance, your Amazon RDS for MySQL version upgrade stops. Then, Amazon RDS provides the details of the failed precheck in the PrePatchCompatibility log file.

To identify and correct the issue, view the log file under Logs & events in the Amazon RDS console. 

For information about the MySQL prechecks, see Upgrade checker utility on the MySQL website.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Review the preupgrade check failure message

To view your log file, complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases, and then select the database you are trying to upgrade.
3. Select the Logs & events tab, and then search for PrePatchCompatibility in the Recent events.
4. Review the PrePatchCompatibility log file and then resolve the issue.
   Note: In most cases, the log entry includes a link to the Amazon RDS for MySQL documentation on how to correct the incompatibility issue.

Check the message for specific errors, warnings, and notice level messages

In the PrePatchCompatibility log file, you might receive one of the following precheck messages:

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

If you receive an ERROR message, then correct the errors before you try to upgrade. If you receive a WARNING message, then Amazon RDS didn't find any fatal errors, but it did find some potential issues.

If you receive a NOTICE message, then Amazon RDS didn't find any known compatibility errors or issues. However, check the NOTICE in the error logs.

List the log files and download the data

To list the log files and download the data, complete the following steps:

1. Run the describe-db-log-files AWS CLI command to list the log files:

   aws rds describe-db-log-files --db-instance-identifier DB_identifier --query '*[].[LogFileName]' --output text

   Note: Replace DB_identifier with the name of the database that the log file is in.
   Example output:

   [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

2. Run the download-db-log-file-portion command to download a specific log 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>

   Note: Replace DB_identifier with the name of the database that the log file is in.
   Example output:

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

Related Information

Upgrading to MySQL 8.0? Here's what you need to know on the MySQL website

Preparing your installation for upgrade on the MySQL website