如何解决 Amazon RDS for MySQL 中的升级前检查失败问题？

简短描述

如果数据库实例的升级前检查失败，则 Amazon RDS for MySQL 版本升级将停止。当 Amazon RDS 检测到不兼容问题时，它会提供一个日志文件供您查看失败预检的详细信息。

**注意：**MySQL 数据库和 Amazon RDS 都内置了许多不同的预检。对低于 MySQL 5.7 的版本进行升级，不会运行预检。对于从 MySQL 5.6 升级到 MySQL 5.7，预检仅限于检测孤立表和足够的存储空间来重建表。

当您从 MySQL 5.7.x 升级到 MySQL 8.0.x 时，兼容 Amazon RDS 和 Amazon Aurora MySQL 的版本会自动运行预检。预检会检测可能影响升级的任何数据不兼容性。预检旨在尽可能减少版本升级期间的任何计划外停机时间。

如果您在其中一个版本升级期间遇到升级前检查失败，请查看 Amazon RDS 生成的 PrePatchCompatibility 日志文件。您可以在 Amazon RDS 控制台的日志和事件下查看 PrePatchCompatibility 日志文件。然后，查看列出的不兼容问题的详细信息并更正问题。在大多数情况下，日志条目包含指向有关如何纠正不兼容问题的 Amazon RDS for MySQL 文档的链接。

解决方法

**注意：**如果在运行 AWS 命令行界面（AWS CLI）命令时收到错误，请确保您使用的是最新的 AWS CLI 版本。

查看升级前检查失败消息，了解升级失败的原因。请参阅以下示例：

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

查看消息中是否有针对特定错误、警告和通知的消息。请参考以下列表，找出您遇到的错误、警告或通知，然后进行相应的故障排除：

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

使用 AWS CLI 运行以下类似命令来列出日志文件并下载数据：

$ 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

有关详细信息，请参阅 MySQL 文档升级到 MySQL 8.0？ 以下是您需要了解的内容。

**注意：**最佳做法是在执行主要版本升级之前测试数据库实例。