Amazon Aurora MySQL version 2 (with MySQL 5.7 compatibility) to version 3 (with MySQL 8.0 compatibility) upgrade checklist, Part 1

Amazon Aurora MySQL-Compatible Edition version 2 (with MySQL 5.7 compatibility) is planned to reach the end of standard support on October 31, 2024. The Amazon Aurora MySQL version 2 end of standard support timeline is discussed in our public documentation. We recommend that you upgrade your databases to the default minor version of Amazon Aurora MySQL 3 or higher, at your earliest convenience before October 31, 2024. Amazon Aurora MySQL version 3 (with MySQL 8.0 compatibility) provides many benefits, such as community enhancements, Amazon Aurora Serverless V2, Amazon Aurora Zero-ETL, Amazon Aurora I/O Optimized, enhanced binary log (binlog), and AWS Graviton3 support.

The upgrade action requires downtime for your applications while the upgrade is ongoing. Amazon Aurora MySQL upgrades the engine version of the entire cluster; therefore, the upgrade is performed on the writer and reader DB instances together. The duration of the upgrade depends on multiple factors, like the properties of your schema including number of tables and indexes, size of database metadata, and how busy the cluster is. You can determine the amount of time it takes to upgrade by performing a testing your upgrade with a database clone. Note that creating test environments incurs additional cost.

Upgrades may be performed by in-place upgrade, snapshot and restore, or by using Amazon RDS Blue/Green Deployments, which is the preferred method to minimize application downtime during the upgrade. Upgrading between major versions requires extensive and careful planning and testing, both before and after the upgrade. In this post, we discuss the most common causes of the upgrade and upgrade prechecks failure. These issues need to be addressed prior to performing the upgrade.

Upgrade prechecks

Amazon Aurora MySQL performs a major version upgrade as a multistage process, and upgrade prechecks are the first step in the process. MySQL 8.0 brings numerous enhancements, though it’s important to note some incompatibilities with MySQL 5.7, which can cause potential issues during an upgrade from Amazon Aurora MySQL version 2 to version 3. When you start an upgrade, Amazon Aurora MySQL runs prechecks automatically to detect these incompatibilities. In an in-place upgrade, the prechecks run before the DB instance is shut down for the upgrade, meaning that they don’t cause downtime when they run. If the prechecks find an incompatibility, Aurora automatically cancels the upgrade before the DB instance is shut down. In a snapshot restore and Amazon RDS Blue/Green deployment method, if the upgrade process to Amazon Aurora MySQL version 3 fails, the problem is detected while creating and then upgrading the writer instance. Aurora will keep the original 5.7-compatible writer instance. That way, you can examine the log from the prechecks that Amazon Aurora MySQL runs before performing the upgrade. Aurora records detailed information about each incompatibility in the log file upgrade-prechecks.log. You can download this file using the download-db-log-file-portion CLI command.

Before upgrading your production databases, it’s best practice to create a clone of the production database and perform an in-place upgrade of the cloned cluster to see if your clusters have incompatibility issues. When the detectedProblems field in the upgrade-prechecks.log contains entries with a level value of Error, that means that the upgrade can’t succeed until you correct those issues. To summarize the errors and display the associated object and description fields, you can run the command grep -A 2 '"level": "Error"' on the contents of the upgrade-prechecks.log file. Doing so displays each error line and the two lines after it. These contain the name of the corresponding database object and guidance about how to correct the problem. The following is an example:

grep -A 2 '"level": "Error"' upgrade-prechecks.log

"level": "Error",

"dbObject": "test.testtable1",

"description": "Table test.testtable1 contains one or more capital letters in name while lower_case_table_names = 1"

"level": "Error",

"dbObject": "test.testtable2",

"description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"

The end of the upgrade-prechecks.log file summarizes how many checks encountered each type of minor or severe problem. A non-zero errorCount indicates that the upgrade will fail. warningCount won’t directly impact the upgrade process but it is recommended to rectify them whenever possible to avoid possible future issues post upgrade.

"errorCount": 2,

"warningCount": 58,

"noticeCount": 0,

"Summary": "2 errors were found. Please correct these issues before upgrading to avoid compatibility issues."

In the next sections, we show the most common causes of the upgrade prechecks failure.

Cluster has an inconsistent data dictionary or intermediate tables in the user schemas

The data dictionary is a collection of metadata that keeps track of objects such as tables and indexes. MySQL 8 and Amazon Aurora MySQL version 3 support atomic Data Definition Language (DDL) statements but not MySQL 5.7 and Amazon Aurora MySQL version 2. Therefore, in MySQL 5.7 and Amazon Aurora MySQL version 2, an abruptly interrupted DDL can lead to tables with inconsistent data dictionaries. This issue is due to MySQL design and is not caused by Amazon Aurora MySQL or Amazon Relational Database (Amazon RDS) for MySQL. For tables with an inconsistent data dictionary issue, you get the following error in upgrade-prechecks.log:

{
"id": "schemaInconsistencyCheck",
"title": "Schema inconsistencies resulting from file removal or corruption",
"status": "OK",
"description": "Error: Following tables show signs that either table datadir directory or frm file was removed/corrupted. Please check server logs, examine datadir to detect the issue and fix it before upgrade",
"detectedProblems": [
{
"level": "Error",
"dbObject": "test.inconsistent_dd_table",
"description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table"
}

When you see this issue during an upgrade, if one of following options work for you, we recommend you to try them first:

• Perform a logical dump and then restore to a new cluster, then upgrade the new cluster to Aurora MySQL v3. This strategy assume that you don’t need the problematic tables anymore since they will not be moved to the new cluster. You can use mysqldump or mydumper and myloader for multiple parallel threads.
• If there are binlog replica clusters, double check the same issues do not exist there then promote them to standalone clusters when there is no replica lag.
• If you know the time when the DDL/DCL that causes the issue ran, perform point in time recovery (PiTR) to a time before the original DCL or DDL began. Migrate the delta to the restored cluster to minimize data loss.

If none of these options work for you, contact AWS Support. Note that fixing data dictionary inconsistencies are done as a best effort basis—there are cases where the dictionary is in a state that is not recoverable.

Cluster has tables that contain orphaned FULLTEXT indexes

Creating a FULLTEXT index and then dropping the index may leave behind some metadata that causes the upgrade precheck to fail and the upgrade will be rolled back. The orphaned index is called dangling FULLTEXT index. Information about the problematic tables that contain dangling FULLTEXT indexes are printed out in the upgrade-prechecks.log file:

{
"id": "getDanglingFulltextIndex",
"title": "Tables with dangling FULLTEXT index reference",
"status": "OK",
"description": "Error: The following tables contain dangling FULLTEXT index which is not supported. It is recommended to rebuild the table before upgrade.",
"detectedProblems": [
{
"level": "Error",
"dbObject": "sandbox.dangling_fulltext_index_table",
"description": "Table sandbox.dangling_fulltext_index_table contains dangling FULLTEXT index. Kindly recreate the table before upgrade."
}

To fix the dangling FULLTEXT issue, you run the OPTIMIZE TABLE command on the table on your Aurora MySQL v2 cluster. For example, OPTIMIZE TABLE sandbox.dangling_fulltext_index_table;

Cluster has objects with reserved keywords

MySQL 8.0 introduced reserved keywords that were not reserved previously. The upgrade prechecker evaluates the usage of reserved keywords in the names of database objects and in their definitions and body. If it detects reserved keywords being used in database objects, such as stored procedures, functions, events, and triggers, the upgrade fails and an error is printed out in the upgrade-prechecks.log file:

{
"id": "routinesSyntaxCheck",
"title": "MySQL 8.0 syntax check for routine-like objects",
"status": "OK",
"description": "The following objects did not pass a syntax check with the latest MySQL 8.0 grammar. A common reason is that they reference names that conflict with new reserved keywords. You must update these routine definitions and `quote` any such references before upgrading.",
"documentationLink": "https://dev.mysql.com/doc/refman/en/keywords.html",
"detectedProblems": [
{
"level": "Error",
"dbObject": "test.EXCEPT",
"description": "at line 12,8: unexpected token '.'"
}

To resolve the issue, you must update these objects definitions and `quote` any such references before upgrading. Alternatively, you can change the name to a different name, which may require application changes.

Cluster has tables with invalid characters in the column definition

When you attempt to upgrade your Amazon Aurora MySQL DB cluster, the upgrade could fail because of invalid characters in the column comment definition in your tables. In error log, the you might see the following error:

2023-09-19T03:11:27.361837Z 2 [ERROR] [MY-013140] [Server] Comment for table 'test.problematic_tables' contains an invalid utf8mb3 character string: '\x8E\xE8\x94'.

We recommend examining the engine error log to identify all problematic tables, then run the command SHOW CREATE TABLE <table_name> for these tables and check for warnings using SHOW WARNINGS to know the details. You must update the comments definition of the columns before retrying the upgrade.

For more information about other common issues that would cause upgrade to fail, refer to Changes in MySQL 8.0

Conclusion

In this post, we discussed the upgrade precheck process and common issues that will cause the upgrade and upgrade prechecks to fail and how to resolve those issues. In Part 2, we will discuss common causes of prolonged or unsuccessful upgrade from Amazon Aurora MySQL 2 to Amazon Aurora MySQL 3.

About the Authors

Huy Nguyen is a Senior Engineer in AWS Support. He specializes in Amazon RDS, Amazon Aurora. He provides guidance and technical assistance to customers, enabling them to build scalable, highly available, and secure solutions in the AWS Cloud.

Leevon Abuan is a Database Engineer in AWS Support. He focuses on Amazon RDS and Amazon Aurora, and has been helping customers on resolving their complex technical issues when running Databases in the Cloud.