背景
接上一篇文章《5.7 升级 8.0 的升级检查利器 util.checkForServerUpgrade 原理(2)》
19. Schema inconsistencies resulting from file removal or corruption
A 检查的目的
由于文件移除或损坏导致的数据库结构不一致
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
select A.schema_name, A.table_name, 'present in INFORMATION_SCHEMA''s INNODB_SYS_TABLES table but missing from TABLES table' from (select distinct replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(NAME, '/',1), '@@@', ''), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as schema_name, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(substring_index(NAME, '/',-1),if(locate(BINARY '#P#', substring_index(NAME, '/',-1)), '#P#','#p#'),1), '@@@', ''), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as table_name from information_schema.innodb_sys_tables where NAME like '%/%') A left join information_schema.tables I on A.table_name = I.table_name and A.schema_name = I.table_schema where A.table_name not like 'FTS_0%' and (I.table_name IS NULL or I.table_schema IS NULL) and A.table_name not REGEXP '@[0-9]' and A.schema_name not REGEXP '@[0-9]';复制
C 异常类型和异常信息
Upgrade_issue::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.
翻译：
以下表格显示了表数据目录或 frm 文件被删除或损坏的迹象。请在升级之前检查服务器日志，检查数据目录以发现问题并修复它。
D 例子
# 创建一张 innodb 表
mysql> create table check19_table(a int);
Query OK, 0 rows affected (0.02 sec)
# 操作系统级别 rm 掉 frm 文件
rm -f check19_table.frm复制
E 点评
这种情况是检查病库，我的例子里 check19_table 缺少了 frm 文件，这张表在查询时会报错，说找不到这张表。
mysql> select * from check19_table;
ERROR 1146 (42S02): Table 'fander.check19_table' doesn't exist复制
这项检查非常好，因为这张表如果平时应用程序不查询，你其实无法发现他是损坏的存在。MySQL 的 errlog 不会有任何异常信息记录，xtrabackup 备份时也不会提示备份失败。
20. Tables recognized by InnoDB that belong to a different engine
A 检查的目的
检查 InnoDB 识别了属于其他引擎的表的情况。
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
select a.table_schema, a.table_name, concat('recognized by the InnoDB engine but belongs to ', a.engine) from information_schema.tables a join (select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(NAME, '/',1), '@@@', ''), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as table_schema, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substring_index(substring_index(NAME, '/',-1),'#',1), '@@@', ''), '@002d', '-'), '@003a', ':'), '@002e', '.'), '@0024', '$'), '@0021', '!'), '@003f', '?'), '@0025', '%'), '@0023', '#'), '@0026', '&'), '@002a', '*'), '@0040', '@') as table_name from information_schema.innodb_sys_tables where NAME like '%/%') b on a.table_schema = b.table_schema and a.table_name = b.table_name where a.engine != 'Innodb';复制
C 异常类型和异常信息
Upgrade_issue::ERROR
Following tables are recognized by InnoDB engine while the SQL layer believes they belong to a different engine. Such situation may happen when one removes InnoDB table files manually from the disk and creates e.g. a MyISAM table with the same name.
A possible way to solve this situation is to e.g. in case of MyISAM table:
Rename the MyISAM table to a temporary name (RENAME TABLE). Create some dummy InnoDB table (its definition does not need to match), then copy (copy, not move) and rename the dummy .frm and .ibd files to the orphan name using OS file commands. The orphan table can be then dropped (DROP TABLE), as well as the dummy table. Finally the MyISAM table can be renamed back to its original name.
翻译：
以下表格被 InnoDB 引擎识别，而 SQL 层认为它们属于另一个引擎。当人为地从磁盘上删除 InnoDB 表文件并创建例如 MyISAM 表时，可能会发生这种情况。
解决这种情况的一种可能方法是，例如在 MyISAM 表的情况下：
将 MyISAM 表重命名为临时名称（RENAME TABLE）。 创建一个虚拟的 InnoDB 表（其定义不需要匹配），然后使用操作系统文件命令将虚拟的.frm 和.ibd 文件复制（复制，而非移动）并重命名为孤立的名称。 接着可以删除孤立的表（DROP TABLE），以及虚拟的表。 最后，MyISAM 表可以重命名回原始名称。
D 例子
mysql> use fander
Database changed
# 创建一张innodb表
mysql> create table check20_table(a int)engine=innodb;
Query OK, 0 rows affected (0.17 sec)
# 操作系统层，直接删除了其frm文件
[mysql@192-168-199-131 fander]$ ll
总用量 112
-rw-r----- 1 mysql mysql 8554 4月 17 14:18 check20_table.frm
-rw-r----- 1 mysql mysql 98304 4月 17 14:18 check20_table.ibd
-rw-r----- 1 mysql mysql 67 4月 17 14:18 db.opt
[mysql@192-168-199-131 fander]$ rm -f check20_table.frm
# 创建一张同名的表，但引擎是myisam
mysql> create table check20_table(a int)engine=myisam;
Query OK, 0 rows affected (0.09 sec)
# 操作系统层，查看文件，现在是这样的结构
[mysql@192-168-199-131 fander]$ ll
总用量 116
-rw-r----- 1 mysql mysql 8554 4月 17 14:19 check20_table.frm
-rw-r----- 1 mysql mysql 98304 4月 17 14:18 check20_table.ibd
-rw-r----- 1 mysql mysql 0 4月 17 14:19 check20_table.MYD
-rw-r----- 1 mysql mysql 1024 4月 17 14:19 check20_table.MYI
-rw-r----- 1 mysql mysql 67 4月 17 14:18 db.opt复制
这时，检查 SQL 可以识别到这种情况
E 点评
我举例的案例是需要有人恶意去动了数据文件才会发生，生产上是否会发生这种案例不清楚，并且一旦发生这种情况有什么严重的危害，是否会造成数据丢失，没试出来。请勿在生产上测试这种场景！
21. Issues reported by 'check table x for upgrade' command
A 检查的目的
由'check table x for upgrade'命令报告的问题
B 检查的方法
下发到数据库以下 SQL，根据结果判断，返回不同的 Upgrade_issue 对象。
FLUSH LOCAL TABLES;
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'sys');
CHECK TABLE `fander`.`test` FOR UPGRADE
CHECK TABLE `mysql`.`columns_priv` FOR UPGRADE
CHECK TABLE `mysql`.`db` FOR UPGRADE
CHECK TABLE `mysql`.`engine_cost` FOR UPGRADE
CHECK TABLE `mysql`.`event` FOR UPGRADE
CHECK TABLE `mysql`.`func` FOR UPGRADE
CHECK TABLE `mysql`.`general_log` FOR UPGRADE
CHECK TABLE `mysql`.`gtid_executed` FOR UPGRADE
CHECK TABLE `mysql`.`help_category` FOR UPGRADE
CHECK TABLE `mysql`.`help_keyword` FOR UPGRADE
CHECK TABLE `mysql`.`help_relation` FOR UPGRADE
CHECK TABLE `mysql`.`help_topic` FOR UPGRADE
CHECK TABLE `mysql`.`innodb_index_stats` FOR UPGRADE
CHECK TABLE `mysql`.`innodb_table_stats` FOR UPGRADE
CHECK TABLE `mysql`.`ndb_binlog_index` FOR UPGRADE
CHECK TABLE `mysql`.`plugin` FOR UPGRADE
CHECK TABLE `mysql`.`proc` FOR UPGRADE
CHECK TABLE `mysql`.`procs_priv` FOR UPGRADE
CHECK TABLE `mysql`.`proxies_priv` FOR UPGRADE
CHECK TABLE `mysql`.`server_cost` FOR UPGRADE
CHECK TABLE `mysql`.`servers` FOR UPGRADE
CHECK TABLE `mysql`.`slave_master_info` FOR UPGRADE
CHECK TABLE `mysql`.`slave_relay_log_info` FOR UPGRADE
CHECK TABLE `mysql`.`slave_worker_info` FOR UPGRADE
CHECK TABLE `mysql`.`slow_log` FOR UPGRADE
CHECK TABLE `mysql`.`tables_priv` FOR UPGRADE
CHECK TABLE `mysql`.`time_zone` FOR UPGRADE
CHECK TABLE `mysql`.`time_zone_leap_second` FOR UPGRADE
CHECK TABLE `mysql`.`time_zone_name` FOR UPGRADE
CHECK TABLE `mysql`.`time_zone_transition` FOR UPGRADE
CHECK TABLE `mysql`.`time_zone_transition_type` FOR UPGRADE
CHECK TABLE `mysql`.`user` FOR UPGRADE复制
C 异常类型和异常信息
Upgrade_issue::WARNING, Upgrade_issue::ERROR 或 Upgrade_issue::NOTICE。
异常信息：取决于检查表结构时发现的具体问题。
代码里如果遇到之前提到的 "6. Partitioned tables using engines with non native partitioning" 这种场景，虽然 check table xxx for upgrade 时有 warnings 状态，但代码会特殊处理，只在第六检查项报出来，这里不会告出来，避免重复警告。
D 例子
略
E 点评
这个又是一个几乎不会检查出问题的选项，这项目主要是检查版本兼容性，详见这个链接：
https://dev.mysql.com/doc/refman/5.7/en/check-table.html
正常安装和使用下不会有问题，基本上是很老的版本，包括 4.0 到 5.6 版本升级上来会历史遗留坑，还有 5.7 的很低版本(RC 版本)才会碰到的坑。
22. New default authentication plugin considerations
A 检查的目的
新的默认身份验证插件注意事项
B 检查的方法
不检查，直接 warning
C 异常类型和异常信息
The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password'(and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file:
[mysqld]
default_authentication_plugin=mysql_native_password
However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security.If you are using replication please take time to understand how the authentication plugin changes may impact you.
翻译：
新的默认身份验证插件'caching_sha2_password'比以前使用的'mysql_native_password'提供了更安全的密码哈希（以及相应改进的客户端连接认证）。然而，它也有兼容性问题，可能会影响到现有的 MySQL 安装。如果您的 MySQL 安装必须为 8.0 之前的客户端提供服务，并且在升级后遇到兼容性问题，解决这些问题的最简单方法是重新配置服务器，恢复到之前的默认身份验证插件（mysql_native_password）。例如，在服务器选项文件中使用以下行：
[mysqld] default_authentication_plugin=mysql_native_password
然而，这个设置应该被视为临时的，而不是长期或永久的解决方案，因为它会导致使用此设置创建的新帐户放弃改进的身份验证安全性。如果您正在使用复制，请花时间了解身份验证插件更改如何影响您。
D 例子
略
E 点评
这个检查项并未进行任何检查，而是强制发出警告——MySQL8.0 默认身份验证插件更换了，这个影响还挺大。
caching_sha2_password 身份验证插件有加"盐"，安全性更高。但也经常存在应用连接的兼容性问题，我懒得麻烦了，目前我会采取临时解决方案，通过以下配置文件，强制默认使用旧的身份验证插件。
[mysqld]
default_authentication_plugin=mysql_native_password复制
23. Columns which cannot have default values
A 检查的目的
不能有默认值的列
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'sys') AND COLUMN_DEFAULT IS NOT NULL AND LOWER(DATA_TYPE) IN ('point', 'linestring', 'polygon', 'geometry', 'multipoint', 'multilinestring', 'multipolygon', 'geometrycollection', 'geomcollection', 'json', 'tinyblob', 'blob', 'mediumblob', 'longblob', 'tinytext', 'text', 'mediumtext', 'longtext');复制
C 异常类型和异常信息
Upgrade_issue::ERROR
The following columns are defined as either BLOB, TEXT, GEOMETRY or JSON and have a default value set. These data types cannot have default values in MySQL versions prior to 8.0.13, while starting with 8.0.13, the default value must be specified as an expression. In order to fix this issue, please use the ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT statement.
翻译：
以下列被定义为 BLOB、TEXT、GEOMETRY 或 JSON，并设置了默认值。在 MySQL8.0.13 之前的版本中，这些数据类型不能有默认值，而从 8.0.13 开始，默认值必须指定为表达式。为了解决这个问题，请使用 ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT 语句。
D 例子
mysql> CREATE TABLE check23_table (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> content TEXT NOT NULL DEFAULT 'default_content'
-> );
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'content' can't have a default value复制
E 点评
实际上我造不出这种例子，5.7 版本就是默认阻止 BLOB、TEXT、GEOMETRY 或 JSON 设置默认值的。估计也是检查什么历史遗留坑。
24. Check for invalid table names and schema names used in 5.7
A 检查的目的
检查在 5.7 中使用的无效表名和库名
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
SELECT SCHEMA_NAME, 'Schema name' AS WARNING FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE '#mysql50#%';
SELECT TABLE_SCHEMA, TABLE_NAME, 'Table name' AS WARNING FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#mysql50#%';复制
C 异常类型和异常信息
Upgrade_issue::ERROR
The following tables and/or schemas have invalid names. In order to fix them use the mysqlcheck utility as follows:
$ mysqlcheck --check-upgrade --all-databases"
$ mysqlcheck --fix-db-names --fix-table-names --all-databases"
OR via mysql client, for eg:
ALTER DATABASE `#mysql50#lost+found` UPGRADE DATA DIRECTORY NAME;复制
翻译：
以下表和/或表具有无效的名称。为了修复它们，请使用 mysqlcheck 实用程序，操作如下：
mysqlcheck --fix-db-names --fix-table-names --all-databases"或者通过 mysql 客户端，例如：ALTER DATABASE #mysql50#lost+found
UPGRADE DATA DIRECTORY NAME;
D 例子
mysql> CREATE DATABASE `#mysql50#lost+found`;
Query OK, 1 row affected (0.06 sec)复制
E 点评
#mysql50#
开头的数据库名是有特殊含义的。如果您的 MySQL 版本早于 5.1.6 的数据库或表包含特殊字符，并且其底层目录名或文件名尚未更新为使用新编码，则服务器会显示其名称前缀为 #mysql50#
。如果有这些情况请升级前使用类似以下语句转换
mysql> ALTER DATABASE `#mysql50#lost+found` UPGRADE DATA DIRECTORY NAME;
Query OK, 0 rows affected, 1 warning (0.13 sec)复制
转换前此 database 在 Linux 中的映射名是
drwxr-x--- 2 mysql mysql 80 4月 17 17:22 lost+found复制
转换后此 database 在 Linux 中的映射名是
drwxr-x--- 2 mysql mysql 80 4月 17 17:33 lost@002bfound复制
5.7 是已经使用新编码来显示特殊字符的，例如中文名的库名。
mysql> create database 芬达helloworld;
Query OK, 1 row affected (0.08 sec)
# 此 database 在 Linux 中的映射名是
drwxr-x--- 2 mysql mysql 20 4月 17 17:27 @82ac@8fbehelloworld复制
所以说，一般人不会踩到这种历史遗留坑。
25. Check for orphaned routines in 5.7
A 检查的目的
检查 5.7 中的孤立函数和存储过程
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, 'is orphaned' AS WARNING FROM information_schema.routines WHERE NOT EXISTS (SELECT SCHEMA_NAME FROM information_schema.schemata WHERE ROUTINE_SCHEMA=SCHEMA_NAME);复制
这个 SQL 查询的目的是找出那些"孤立"的存储过程和函数。所谓"孤立"，是指它们所在的 schema（数据库）在 information_schema.schemata
表中找不到对应的记录。
解释一下各个部分：
information_schema.routines
表包含了当前 MySQL 实例中所有存储过程和函数的信息。information_schema.schemata
表包含了当前 MySQL 实例中所有 schema（数据库）的信息。NOT EXISTS (SELECT SCHEMA_NAME FROM information_schema.schemata WHERE ROUTINE_SCHEMA=SCHEMA_NAME)
子查询检查routines
表中的每一个记录的ROUTINE_SCHEMA
是否在schemata
表中存在。
如果某个存储过程或函数的 ROUTINE_SCHEMA
在 information_schema.schemata
表中找不到对应的记录，那么它就被认为是"孤立"的。查询结果将返回这些孤立的存储过程和函数的 schema（数据库）和名称，并在 WARNING
列中显示 "is orphaned"。
C 异常类型和异常信息
Upgrade_issue::ERROR
The following routines have been orphaned. Schemas that they are referencing no longer exists.
They have to be cleaned up or the upgrade will fail.
翻译：
以下存储过程已成为孤儿。它们引用的库已经不存在。在升级之前，必须清理这些存储过程，否则升级将失败。
D 例子
略
E 点评
第 25 检查项涉及的孤儿问题主要是指函数和存储过程等对象，它们对应的数据库对象已不存在，但它们仍然关联着这些数据库。这些孤儿函数和存储过程无法运行，但如果后续有人通过 create database
语句将这些关联对象重新创建，这些函数和存储过程又会突然开始工作。这是一个风险点，容易出现问题。孤儿问题不仅限于函数和存储过程，还可能出现在触发器、事件、视图共 5 类对象上。参见我之前撰写的一篇文章《技术分享 | MySQL 修改可信 IP 白名单的方法》。
这个问题的危害很大，而且在生产环境中，由于 DBA 的误操作可能会出现这种情况。建议大家阅读该文章，对孤儿问题进行排查和修复。需要注意的是，我这篇文章讨论的孤儿问题是由用户对象不存在导致的，与本检查项不同。
26. Check for deprecated usage of single dollar signs in object names
A 检查的目的
检查在对象名称中使用单美元符号的过时用法
B 检查的方法
SELECT SCHEMA_NAME, 'name starts with a $ sign.' FROM information_schema.schemata WHERE SCHEMA_NAME LIKE '$_%' AND SCHEMA_NAME NOT LIKE '$_%$';
SELECT TABLE_SCHEMA, TABLE_NAME, ' name starts with $ sign.' FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('sys', 'performance_schema', 'mysql', 'information_schema') AND TABLE_NAME LIKE '$_%' AND TABLE_NAME NOT LIKE '$_%$';
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ' name starts with $ sign.' FROM information_schema.columns WHERE TABLE_SCHEMA NOT IN ('sys', 'performance_schema', 'mysql', 'information_schema') AND COLUMN_NAME LIKE ('$_%') AND COLUMN_NAME NOT LIKE ('$%_$') ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, ' starts with $ sign.' FROM information_schema.statistics WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema') AND INDEX_NAME LIKE '$_%' AND INDEX_NAME NOT LIKE '$%_$' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ' name starts with $ sign.' FROM information_schema.routines WHERE ROUTINE_SCHEMA NOT IN ('sys', 'performance_schema', 'mysql', 'information_schema') AND ROUTINE_NAME LIKE '$_%' AND ROUTINE_NAME NOT LIKE '$%_$';
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ' body contains identifiers that start with $ sign.' FROM information_schema.routines WHERE ROUTINE_SCHEMA NOT IN ('sys', 'performance_schema', 'mysql', 'information_schema') AND ROUTINE_DEFINITION REGEXP '[[:blank:],.;\\)\\(]\\$[A-Za-z0-9\\_\\$]*[A-Za-z0-9\\_]([[:blank:],;\\)]|(\\.[^0-9]))';复制
C 异常类型和异常信息
Upgrade_issue::WARNING
The following objects have names with deprecated usage of dollar sign ($) at the begining of the identifier. To correct this warning, ensure, that names starting with dollar sign, also end with it, similary to quotes ($example$).
翻译：
以下对象的名称在标识符开头处使用了不推荐的美元符号（$）用法。为了纠正此警告，请确保以美元符号开头的名称也以美元符号结尾，类似于引号（$example$）。
D 例子
mysql> create database $hi;
Query OK, 1 row affected (0.07 sec)复制
E 点评
这个是 MySQL8.0.32 版本的新增的检查项，这看起来为 MySQL9.0 铺路，$ 会有新的玩法。见:
https://github.com/mysql/mysql-server/commit/950eb143129f4ef4ae6e89bf5353a5e8600c3dc2
MySQL9.0 还远着呢，发现了也可以先不改。
