将 Aurora MySQL 版本 3 升级到 8.4 版的预检查说明

预检查级别：错误

检查 MySQL Router 内部账户是否使用了已弃用或无效的身份验证方法

此预检查用于验证 MySQL Router 内部账户没有使用已弃用或无效的身份验证方法，这些方法可能已在 MySQL 8.4 中删除或更改。如果未指示 Router 使用现有账户，MySQL Router 账户将在引导时自动创建。

输出示例：

{
  "id": "deprecatedRouterAuthMethod",
  "title": "Check for deprecated or invalid authentication methods in use by MySQL Router internal accounts.",
  "status": "OK",
  "description": "Warning: The following accounts are MySQL Router accounts that use a deprecated authentication method.\nThose accounts are automatically created at bootstrap time when the Router is not instructed to use an existing account. Please upgrade MySQL Router to the latest version to ensure deprecated authentication methods are no longer used.\nSince version 8.0.19 it's also possible to instruct MySQL Router to use a dedicated account. That account can be created using the AdminAPI.",
  "documentationLink": "https://dev.mysql.com/doc/mysql-shell/en/configuring-router-user.html https://dev.mysql.com/doc/mysql-router/en/mysqlrouter.html#option_mysqlrouter_account",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "mysql_router_test@%",
        "description": " - router user with deprecated authentication method."
      }
  ]
}

预检查会返回错误，因为 mysql_router_test 账户使用的是已弃用的身份验证方法，例如 mysql_native_password 或 sha256_password。

要验证所使用的身份验证方法，请执行以下操作：

SELECT user, host, plugin FROM mysql.user WHERE user = 'mysql_router_test';

解决方法：

更新 MySQL Router 账户来使用 caching_sha2_password 身份验证：

ALTER USER 'mysql_router_test'@'%' IDENTIFIED WITH caching_sha2_password BY 'new_password';

或者，将 MySQL Router 升级到最新版本，确保不再使用已弃用的身份验证方法。从版本 8.0.19 起，您还可以指示 MySQL Router 使用 AdminAPI 创建的专用账户。

预检查级别：错误

检查是否有作为分区依据的键使用了带前缀键索引的列

此预检查确定是否有分区表在分区键中使用了带前缀键索引的列。键分区不支持列前缀上的索引，分区函数会忽略这些索引，并且从 MySQL 8.4.0 起不允许使用这些索引。

有关更多信息，请参阅 MySQL 文档中的 Restrictions and limitations on partitioning。

输出示例：

{
  "id": "partitionsWithPrefixKeys",
  "title": "Checks for partitions by key using columns with prefix key indexes",
  "status": "OK",
  "description": "Indexes on column prefixes are not supported for key partitioning, they are ignored by the partition function and so they are not allowed as of 8.4.0. This check identifies tables with partitions defined this way, they should be fixed before upgrading to 8.4.0.",
  "documentationLink": "https://dev.mysql.com/doc/refman/en/partitioning-limitations.html",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.test_partition_prefix",
        "description": "Error: the `test`.`test_partition_prefix` table uses partition by KEY using the following columns with prefix index: name."
      }
  ]
}

解决方法：

-- Check for prefix indexes (Sub_part column shows prefix length)
SHOW INDEX FROM test.test_partition_prefix;

-- Option 1: Change partition to use non-prefix columns only
ALTER TABLE test.test_partition_prefix PARTITION BY KEY (id) PARTITIONS 4;

-- Option 2: Remove prefix from index
ALTER TABLE test.test_partition_prefix
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (id, name);  -- Full column, no prefix

-- Option 3: Remove partitioning
ALTER TABLE test.test_partition_prefix REMOVE PARTITIONING;

预检查级别：错误

检查列定义中是否有错误

此预检查验证所有列定义是否都与 MySQL 8.4 要求兼容。它专门用于识别类型为 FLOAT 或 DOUBLE 且设置了 AUTO_INCREMENT 标志的列，MySQL 8.4 中不再支持这种格式。

输出示例：

{
  "id": "columnDefinition",
  "title": "Checks for errors in column definitions",
  "status": "OK",
  "description": "Identifies column definitions that may not be supported in future versions of MySQL",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.test_column_def.id",
        "description": "The column is of type FLOAT and has the AUTO_INCREMENT flag set, this is no longer supported."
      }
  ]
}

解决方法：

将列类型从 FLOAT 或 DOUBLE 更改为整数类型：

-- Check current definition
SHOW CREATE TABLE test.test_column_def\G

-- Change FLOAT AUTO_INCREMENT to BIGINT AUTO_INCREMENT
ALTER TABLE test.test_column_def MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;

-- Verify
SHOW CREATE TABLE test.test_column_def\G

预检查级别：错误

检查是否存在不支持的插件

这项特定于 Aurora 的预检查识别是否存在 Aurora MySQL 版本 8.4 中不支持的插件。Aurora 具有特定的插件兼容性要求，与 MySQL 社区版不同。

输出示例：

{
  "id": "auroraUnsupportedPluginsCheck",
  "title": "Check for unsupported plugins",
  "status": "OK",
  "description": "Checks for unsupported plugins installed in the database",
  "documentationLink": "https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "all",
        "description": "Plugin simple_parser loaded in the engine. To proceed with the upgrade, remove this plugin."
      }
  ]
}

解决方法：

卸载不支持的插件：

UNINSTALL PLUGIN plugin_name;

预检查级别：错误

检查是否存在不支持的组件

此预检查用于确保 Aurora MySQL 版本 8.4 当前未安装不支持的 MySQL 组件或者此类组件未处于活动状态。组件不同于插件，它们通过组件基础设施提供扩展功能。

输出示例：

{
  "id": "auroraUnsupportedComponentsCheck",
  "title": "Check for unsupported components",
  "status": "OK",
  "description": "Checks for unsupported components installed in the database",
  "documentationLink": "https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "all",
        "description": "Component file://component_log_sink_json loaded in the engine. To proceed with the upgrade, uninstall this component."
      }
  ]
}

解决方法：

卸载不支持的组件：

UNINSTALL COMPONENT 'component_name';

预检查级别：错误

检查 sys 架构的对象类型不匹配

此预检查用于验证 sys 架构中的所有对象是否具有正确的对象类型和定义。sys 架构是系统架构，提供用于数据库监控和诊断的视图及过程。如果架构被手动修改或损坏，则可能会出现不匹配的情况。

输出示例：

{
  "id": "auroraUpgradeCheckForSysSchemaObjectTypeMismatch",
  "title": "Check object type mismatch for sys schema.",
  "status": "OK",
  "description": "Database contains objects with type mismatch for sys schema.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "sys.host_summary",
        "description": "Your object sys.host_summary has a type mismatch. To fix the inconsistency we recommend to rename or remove the object before upgrading (use RENAME TABLE command)."
      }
  ]
}

解决方法：

-- Check the object type
SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.tables
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'host_summary';

-- Option 1: Rename the mismatched object
RENAME TABLE sys.host_summary TO sys.host_summary_backup;

-- Option 2: Drop the mismatched object
DROP TABLE sys.host_summary;

预检查级别：警告

检查系统变量中是否存在已弃用或无效的默认身份验证方法

此预检查用于确保 default_authentication_plugin 系统变量未设置为已弃用的身份验证方法。

有关版本 8.4 中身份验证更改的更多信息，请参阅从 Aurora MySQL 版本 3 升级到版本 8.4 时的安全注意事项。

输出示例：

{
  "id": "deprecatedDefaultAuth",
  "title": "Check for deprecated or invalid default authentication methods in system variables.",
  "status": "OK",
  "description": "The following variables have problems with their set authentication method:",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "default_authentication_plugin",
        "description": "mysql_native_password authentication method is deprecated and it should be considered to correct this before upgrading to 8.4.0 release."
      },
      {
        "level": "Warning",
        "dbObject": "authentication_policy",
        "description": "mysql_native_password authentication method is deprecated and it should be considered to correct this before upgrading to 8.4.0 release."
      }
  ]
}

解决方法：

更新身份验证系统变量以使用 caching_sha2_password：

在进行此更改之前，请确保您的应用程序客户端支持 caching_sha2_password 身份验证。一些较早的 MySQL 客户端库可能不支持此身份验证方法。

预检查级别：警告

检查是否存在未引用完整唯一索引的外键

此预检查确保所有外键约束都引用完整的唯一索引或主键索引。从 MySQL 8.4.0 起，禁止了引用部分索引的外键。

输出示例：

{
  "id": "foreignKeyReferences",
  "title": "Checks for foreign keys not referencing a full unique index",
  "status": "OK",
  "description": "Foreign keys to partial indexes may be forbidden as of 8.4.0, this check identifies such cases to warn the user.",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "sample.child_table_ibfk_1",
        "description": "invalid foreign key defined as 'child_table(parent_name)' references a non unique key at table 'parent_table'."
      }
  ]
}

解决方法：

选择最适合您应用程序的选项：

-- Option 1: Add unique index on parent table (if values are unique)
ALTER TABLE test.parent_table ADD UNIQUE INDEX idx_parent_name_unique (parent_name);

-- Option 2: Drop the foreign key if not needed
ALTER TABLE test.child_table DROP FOREIGN KEY child_table_ibfk_1;

预检查级别：警告

查看是否存在已弃用的 validate_password 插件

此预检查确定是否使用了已弃用的 validate_password 插件。在 MySQL 8.0+ 中，validate_password 功能重新实施为一个组件（component_validate_password）。Aurora MySQL 版本 8.4 要求迁移到基于组件的实施。

有关更多信息，请参阅 密码验证组件迁移。

输出示例：

{
  "id": "auroraValidatePasswordPluginCheck",
  "title": "Check for deprecated validate_password plugin",
  "status": "OK",
  "description": "The validate_password plugin is deprecated in Aurora MySQL 8.4",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "validate_password",
        "description": "The validate_password plugin is deprecated and will be removed in a future release. It is recommended to transition to the validate_password component."
      }
  ]
}

解决方法：

-- Check current plugin status
SELECT plugin_name, plugin_status FROM information_schema.plugins
WHERE plugin_name = 'validate_password';

-- Uninstall the plugin
UNINSTALL PLUGIN validate_password;

-- Install the component replacement
INSTALL COMPONENT 'file://component_validate_password';

-- Verify
SELECT * FROM mysql.component WHERE component_urn LIKE '%validate_password%';

预检查级别：通知

检查将会被删除的用户权限

此预检查识别具有会在 MySQL 8.4 中被删除或修改的权限的用户账户。在升级过程中，系统将移除 SET_USER_ID 权限。如果未使用该权限，则无需执行任何操作。否则，请确保在升级之前停止使用这些权限，因为它们会丢失。

输出示例：

{
  "id": "invalidPrivileges",
  "title": "Checks for user privileges that will be removed",
  "status": "OK",
  "description": "Verifies for users containing grants to be removed as part of the upgrade process.",
  "detectedProblems": [
      {
        "level": "Notice",
        "dbObject": "'test_user'@'localhost'",
        "description": "The user 'test_user'@'localhost' has the following privileges that will be removed as part of the upgrade process: SET_USER_ID"
      }
  ]
}

解决方法：

这是一条信息性通知，无需用户进行任何操作。在升级过程中，系统将自动移除 SET_USER_ID 权限。

但是，如果您的应用程序依赖于 SET_USER_ID 权限，请在升级之前检查并更新您的应用程序。

预检查级别：错误、警告或通知

检查是否存在已弃用或无效的用户身份验证方法

此预检查确定是否有用户账户在使用 MySQL 8.4 中弃用或者将删除的身份验证。从 MySQL 8.4.0 起，默认情况下，mysql_native_password 身份验证插件已弃用并被禁用。该插件会在未来版本中删除。

根据功能的生命周期，严重性级别会发生变化：在弃用前为“通知”，在弃用后为“警告”，在删除后为“错误”。

输出示例：

{
  "id": "authMethodUsage",
  "title": "Check for deprecated or invalid user authentication methods.",
  "status": "OK",
  "description": "Some users are using authentication methods that may be deprecated or removed, please review the details below.",
  "detectedProblems": [
      {
        "level": "Warning",
        "dbObject": "testuser@localhost",
        "description": ""
      }
  ]
}

解决方法：

更新用户账户来使用 caching_sha2_password 身份验证：

ALTER USER 'username'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_password';

预检查级别：错误、警告或通知

检查是否使用了已弃用或已移除的插件

此预检查用于识别已在 MySQL 8.4 中弃用或移除的插件。它会检查所有生效的插件并报告其弃用状态。根据功能的生命周期，严重性级别会发生变化。

输出示例：

{
  "id": "pluginUsage",
  "title": "Check for deprecated or removed plugin usage.",
  "status": "OK",
  "description": "The following plugins are ACTIVE and they have been deprecated or removed.",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "keyring_file",
        "description": "The 'keyring_file' plugin is removed as of MySQL 8.4.0. It must not be used anymore, please use the 'component_keyring_file' component instead."
      }
  ]
}

解决方法：

卸载已弃用的插件并安装替代组件：

-- Check installed plugins
SELECT plugin_name, plugin_status FROM information_schema.plugins
WHERE plugin_name IN ('keyring_file', 'keyring_encrypted_file', 'keyring_oci', 'authentication_fido');

-- Uninstall and replace (example for keyring_file)
UNINSTALL PLUGIN keyring_file;
INSTALL COMPONENT 'file://component_keyring_file';

-- Verify
SELECT * FROM mysql.component WHERE component_urn LIKE '%keyring%';

下表列出了已弃用的插件及其替代品：

预检查级别：错误、警告或通知

check table x for upgrade 命令报告的问题

此预检查在所有用户表上运行 CHECK TABLE ... FOR UPGRADE 命令，以识别结构问题、已弃用的功能或与 MySQL 8.4 的不兼容之处。该命令对表结构和元数据执行全面的验证。

与其它预检查不同，此项预检查可以根据 CHECK TABLE 输出返回错误、警告或通知。如果此项预检查返回了任何表，请在启动升级之前，仔细查看这些表以及返回代码和消息。

输出示例：

{
  "id": "checkTableCommand",
  "title": "Issues reported by 'check table x for upgrade' command",
  "status": "OK",
  "description": "Issues reported by 'check table x for upgrade' command",
  "detectedProblems": [
      {
        "level": "Error",
        "dbObject": "test.orphaned_view",
        "description": "View 'test.orphaned_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"
      },
      {
        "level": "Error",
        "dbObject": "test.orphaned_view",
        "description": "Corrupt"
      }
  ]
}

解决方法：

请在升级之前，检查所报告的对象并修复或移除它们。常见问题包括：

有关更多信息，请参阅 MySQL 文档中的 CHECK TABLE statement。