背景
接上一篇文章《5.7 升级 8.0 的升级检查利器 util.checkForServerUpgrade 原理(1)》
10. ENUM/SET column definitions containing elements longer than 255 characters
A 检查的目的
ENUM/SET 列定义包含了长度超过 255 个字符的元素。
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, UPPER(DATA_TYPE), COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH from information_schema.columns where data_type in ('enum','set') and CHARACTER_MAXIMUM_LENGTH > 255 and table_schema not in ('information_schema');
C 异常类型和异常信息
Upgrade_issue::ERROR
The following columns are defined as either ENUM or SET and contain at least one element longer that 255 characters. They need to be altered so that all elements fit into the 255 characters limit.
翻译：
以下列定义为 ENUM 或 SET，并至少包含一个长度超过 255 个字符的元素。需要对它们进行更改，以使所有元素都适应 255 个字符的限制。
D 例子
mysql> CREATE TABLE check10_table (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> long_enum_column ENUM('a', 'b', 'c', 这个元素我用了26个英文字母复制粘贴了10次，共260个字符)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE check10_table2 (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> long_enum_column ENUM('a', 'b', 'c', 这个元素用了非常离谱的26000个字符)
-> );
Query OK, 0 rows affected (0.11 sec)
E 点评
MySQL8.0 不支持 ENUM/SET 列定义包含长度超过 255 个字符的元素。而 MySQL5.7 则在 innodb 引擎里完全无限制，我用了两个例子，一个是 260 个字符刚刚超过，一个是离谱的 26000 个字符，两个例子都能创建表成功。使用枚举类型的情况不多见，同时枚举类型的某元素名称超过 255 个字符的情况更不多见，如果检查出这种情况请整改。
11. Usage of partitioned tables in shared tablespaces
A 检查的目的
在共享表空间中使用分区表的情况。
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
# 5.7 升级 8.0 使用这个SQL
SELECT TABLE_SCHEMA, TABLE_NAME, concat('Partition ', PARTITION_NAME, ' is in shared tablespace ', TABLESPACE_NAME) as description FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL AND (TABLESPACE_NAME IS NOT NULL AND TABLESPACE_NAME!='innodb_file_per_table');
# 8.0 升级 8.0使用这个SQL
select SUBSTRING_INDEX(it.name, '/', 1), SUBSTRING_INDEX(SUBSTRING_INDEX(it.name, '/', -1), '#', 1),concat('Partition ', SUBSTRING_INDEX(SUBSTRING_INDEX(it.name,'/', -1), '#', -1), ' is in shared tablespce ', itb.name) from information_schema.INNODB_TABLES it, information_schema.INNODB_TABLESPACES itb where it.SPACE = itb.space and it.name like '%#P#%' and it.space_type != 'Single';
C 异常类型和异常信息
Upgrade_issue::ERROR
The following tables have partitions in shared tablespaces. Before upgrading to 8.0 they need to be moved to file-per-table tablespace. You can do this by running query like 'ALTER TABLE table_name REORGANIZE PARTITION X INTO (PARTITION X VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table)'.
翻译：
以下表具有存放在共享表空间中的分区。在升级到 8.0 之前，需要将它们移动到独立表空间。您可以通过运行类似以下的查询来实现这个操作：'ALTER TABLE table_name REORGANIZE PARTITION X INTO (PARTITION X VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table)'。
D 例子
# 创建一个共享表空间
mysql> CREATE TABLESPACE my_shared_tablespace ADD DATAFILE 'my_shared_tablespace.ibd' FILE_BLOCK_SIZE = 16384 ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
# 创建数据存放在共享表空间的分区表
mysql> CREATE TABLE sales (
-> id INT AUTO_INCREMENT,
-> sale_date DATE,
-> PRIMARY KEY (id, sale_date)
-> )
-> PARTITION BY RANGE (YEAR(sale_date))
-> (
-> PARTITION p0 VALUES LESS THAN (2000) TABLESPACE my_shared_tablespace,
-> PARTITION p1 VALUES LESS THAN (2010) TABLESPACE my_shared_tablespace,
-> PARTITION p2 VALUES LESS THAN (2020) TABLESPACE my_shared_tablespace
-> );
Query OK, 0 rows affected, 1 warning (0.08 sec)
E 点评
在 MySQL8.0 中，分区表不再支持使用共享表空间。因此，在升级到 MySQL8.0 之前，需要将分区表从共享表空间迁移到独立表空间（file-per-table）。
实际上很少见有人建表时指定共享表空间的，这不是 MySQL 的玩法，在 5.7 默认配置下，参数 innodb_file_per_table=ON
，这意味着你不故意指定表空间，都是独立表空间的。这还有一个额外条件就是分区表，一般人遇不到这个问题。
12. Circular directory references in tablespace data file paths
A 检查的目的
表空间数据文件路径中的循环目录引用的情况。
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
SELECT tablespace_name, concat('circular reference in datafile path: \'', file_name, '\'') FROM INFORMATION_SCHEMA.FILES where file_type='TABLESPACE' and (file_name rlike '[^\\.]/\\.\\./' or file_name rlike '[^\\.]\\\\\\.\\.\\\\');
C 异常类型和异常信息
Upgrade_issue::ERROR
Following tablespaces contain circular directory references (e.g. the reference '/../') in data file paths which as of MySQL8.0.17 are not permitted by the CREATE TABLESPACE ... ADD DATAFILE clause. An exception to the restriction exists on Linux, where a circular directory reference is permitted if the preceding directory is a symbolic link. To avoid upgrade issues, remove any circular directory references from tablespace data file paths before upgrading.
翻译：
以下表空间在数据文件路径中包含循环目录引用（例如引用 '/../'），在 MySQL8.0.17 及以后版本的 CREATE TABLESPACE ... ADD DATAFILE 子句中不允许这种情况。对此限制在 Linux 上存在一个例外，如果前面的目录是符号链接，则允许循环目录引用。为避免升级过程中的问题，在升级前请从表空间数据文件路径中移除任何循环目录引用。
D 例子
CREATE TABLESPACE check12_tablespace ADD DATAFILE '/database/../database/check12.ibd' FILE_BLOCK_SIZE = 16384 ENGINE=InnoDB;
E 点评
自建表空间的情况不多见，还要如我例子展示这种循环路径建空间，用法太奇葩，一般人踩不到这个坑。
13. Usage of removed functions
A 检查的目的
检查使用了已删除的函数的情况。
检查的对象是视图、函数和存储过程、有 GENERATION 表达式列的表、触发器、事件。
以下是 MySQL8.0 已删除的函数的列表，第一个元素是已删除的函数名，第二个元素是推荐使用的替代函数。
{"ENCODE", "AES_ENCRYPT and AES_DECRYPT"},
{"DECODE", "AES_ENCRYPT and AES_DECRYPT"},
{"ENCRYPT", "SHA2"},
{"DES_ENCRYPT", "AES_ENCRYPT and AES_DECRYPT"},
{"DES_DECRYPT", "AES_ENCRYPT and AES_DECRYPT"},
{"AREA", "ST_AREA"},
{"ASBINARY", "ST_ASBINARY"},
{"ASTEXT", "ST_ASTEXT"},
{"ASWKB", "ST_ASWKB"},
{"ASWKT", "ST_ASWKT"},
{"BUFFER", "ST_BUFFER"},
{"CENTROID", "ST_CENTROID"},
{"CONTAINS", "MBRCONTAINS"},
{"CROSSES", "ST_CROSSES"},
{"DIMENSION", "ST_DIMENSION"},
{"DISJOINT", "MBRDISJOINT"},
{"DISTANCE", "ST_DISTANCE"},
{"ENDPOINT", "ST_ENDPOINT"},
{"ENVELOPE", "ST_ENVELOPE"},
{"EQUALS", "MBREQUALS"},
{"EXTERIORRING", "ST_EXTERIORRING"},
{"GEOMCOLLFROMTEXT", "ST_GEOMCOLLFROMTEXT"},
{"GEOMCOLLFROMWKB", "ST_GEOMCOLLFROMWKB"},
{"GEOMETRYCOLLECTIONFROMTEXT", "ST_GEOMETRYCOLLECTIONFROMTEXT"},
{"GEOMETRYCOLLECTIONFROMWKB", "ST_GEOMETRYCOLLECTIONFROMWKB"},
{"GEOMETRYFROMTEXT", "ST_GEOMETRYFROMTEXT"},
{"GEOMETRYFROMWKB", "ST_GEOMETRYFROMWKB"},
{"GEOMETRYN", "ST_GEOMETRYN"},
{"GEOMETRYTYPE", "ST_GEOMETRYTYPE"},
{"GEOMFROMTEXT", "ST_GEOMFROMTEXT"},
{"GEOMFROMWKB", "ST_GEOMFROMWKB"},
{"GLENGTH", "ST_LENGTH"},
{"INTERIORRINGN", "ST_INTERIORRINGN"},
{"INTERSECTS", "MBRINTERSECTS"},
{"ISCLOSED", "ST_ISCLOSED"},
{"ISEMPTY", "ST_ISEMPTY"},
{"ISSIMPLE", "ST_ISSIMPLE"},
{"LINEFROMTEXT", "ST_LINEFROMTEXT"},
{"LINEFROMWKB", "ST_LINEFROMWKB"},
{"LINESTRINGFROMTEXT", "ST_LINESTRINGFROMTEXT"},
{"LINESTRINGFROMWKB", "ST_LINESTRINGFROMWKB"},
{"MBREQUAL", "MBREQUALS"},
{"MLINEFROMTEXT", "ST_MLINEFROMTEXT"},
{"MLINEFROMWKB", "ST_MLINEFROMWKB"},
{"MPOINTFROMTEXT", "ST_MPOINTFROMTEXT"},
{"MPOINTFROMWKB", "ST_MPOINTFROMWKB"},
{"MPOLYFROMTEXT", "ST_MPOLYFROMTEXT"},
{"MPOLYFROMWKB", "ST_MPOLYFROMWKB"},
{"MULTILINESTRINGFROMTEXT", "ST_MULTILINESTRINGFROMTEXT"},
{"MULTILINESTRINGFROMWKB", "ST_MULTILINESTRINGFROMWKB"},
{"MULTIPOINTFROMTEXT", "ST_MULTIPOINTFROMTEXT"},
{"MULTIPOINTFROMWKB", "ST_MULTIPOINTFROMWKB"},
{"MULTIPOLYGONFROMTEXT", "ST_MULTIPOLYGONFROMTEXT"},
{"MULTIPOLYGONFROMWKB", "ST_MULTIPOLYGONFROMWKB"},
{"NUMGEOMETRIES", "ST_NUMGEOMETRIES"},
{"NUMINTERIORRINGS", "ST_NUMINTERIORRINGS"},
{"NUMPOINTS", "ST_NUMPOINTS"},
{"OVERLAPS", "MBROVERLAPS"},
{"PASSWORD", nullptr},
{"POINTFROMTEXT", "ST_POINTFROMTEXT"},
{"POINTFROMWKB", "ST_POINTFROMWKB"},
{"POINTN", "ST_POINTN"},
{"POLYFROMTEXT", "ST_POLYFROMTEXT"},
{"POLYFROMWKB", "ST_POLYFROMWKB"},
{"POLYGONFROMTEXT", "ST_POLYGONFROMTEXT"},
{"POLYGONFROMWKB", "ST_POLYGONFROMWKB"},
{"SRID", "ST_SRID"},
{"STARTPOINT", "ST_STARTPOINT"},
{"TOUCHES", "ST_TOUCHES"},
{"WITHIN", "MBRWITHIN"},
{"X", "ST_X"},
{"Y", "ST_Y"}};
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则判断是否存在已删除函数，再根据情况返回 Upgrade_issue 对象。
select table_schema, table_name, '', 'VIEW', UPPER(view_definition) from information_schema.views where table_schema not in ('performance_schema','information_schema','sys','mysql');
select routine_schema, routine_name, '', routine_type, UPPER(routine_definition) from information_schema.routines where routine_schema not in ('performance_schema','information_schema','sys','mysql');
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME, 'COLUMN', UPPER(GENERATION_EXPRESSION) from information_schema.columns where extra regexp 'generated' and table_schema not in ('performance_schema','information_schema','sys','mysql');
select TRIGGER_SCHEMA, TRIGGER_NAME, '', 'TRIGGER', UPPER(ACTION_STATEMENT) from information_schema.triggers where TRIGGER_SCHEMA not in ('performance_schema','information_schema','sys','mysql');
select event_schema, event_name, '', 'EVENT', UPPER(EVENT_DEFINITION) from information_schema.events where event_schema not in ('performance_schema','information_schema','sys','mysql');
C 异常类型和异常信息
Upgrade_issue::ERROR
Following DB objects make use of functions that have been removed in version 8.0. Please make sure to update them to use supported alternatives before upgrade.
翻译：
以下数据库对象使用了在 8.0 版本中已被移除的函数。请确保在升级之前更新它们，以使用支持的替代方案。
D 例子
# 已删除的函数 ENCODE 的例子
mysql> CREATE VIEW encoded_messages AS
-> SELECT ENCODE('message', 'mysecretkey');
Query OK, 0 rows affected (0.12 sec)
# 已删除的函数 PASSWORD 的例子
mysql> DELIMITER $$
mysql> create procedure change_password ()
-> begin
-> set password = password('Fander@9999');
-> end $$
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> DELIMITER ;
E 点评
"A 检查的目的" 里我列出了所有 MySQL8.0 已删除函数的函数名和推荐使用的替代函数，但我发现其中一个函数比较特殊，就是 PASSWORD
函数，他的推荐使用的替代函数一栏为"空"。所以我"D 例子"测试了他，作为对比，ENCODE
显示了推荐的替代函数，PASSWORD
只提示了他是已删除的函数。
为什么 PASSWORD
被删除了，我刚好写过一篇文章《你还在使用 set password 修改密码吗？》 。
14. Usage of removed GROUP BY ASC/DESC syntax
A 检查的目的
检查使用已删除的 GROUP BY ASC/DESC 语法的情况。
检查的对象是视图、存储过程和函数、触发器、事件。
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
select table_schema, table_name, 'VIEW', UPPER(view_definition) from information_schema.views where table_schema not in ('performance_schema','information_schema','sys','mysql') and (UPPER(view_definition) like '%ASC%' or UPPER(view_definition) like '%DESC%');
select routine_schema, routine_name, routine_type, UPPER(routine_definition) from information_schema.routines where routine_schema not in ('performance_schema','information_schema','sys','mysql') and (UPPER(routine_definition) like '%ASC%' or UPPER(routine_definition) like '%DESC%');
select TRIGGER_SCHEMA, TRIGGER_NAME, 'TRIGGER', UPPER(ACTION_STATEMENT) from information_schema.triggers where TRIGGER_SCHEMA not in ('performance_schema','information_schema','sys','mysql') and (UPPER(ACTION_STATEMENT) like '%ASC%' or UPPER(ACTION_STATEMENT) like '%DESC%');
select event_schema, event_name, 'EVENT', UPPER(EVENT_DEFINITION) from information_schema.events where event_schema not in ('performance_schema','information_schema','sys','mysql');
C 异常类型和异常信息
Upgrade_issue::ERROR
The following DB objects use removed GROUP BY ASC/DESC syntax. They need to be altered so that ASC/DESC keyword is removed from GROUP BY clause and placed in appropriate ORDER BY clause.
翻译：
以下数据库对象使用了已移除的 GROUP BY ASC/DESC 语法。需要对它们进行修改，将 ASC/DESC 关键字从 GROUP BY 子句中移除，并放置在适当的 ORDER BY 子句中。
D 例子
mysql> create table t_group_by_desc (
-> a int,
-> b varchar(10)
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> create view v_group_by_desc AS
-> select a, b from t_group_by_desc
-> group by b DESC;
Query OK, 0 rows affected, 1 warning (0.11 sec)
E 点评
MySQL8.0 移除了对 GROUP BY ASC/DESC
语法的支持，这是一项值得称赞的改进。这种语法并不符合 SQL 标准，同时也没有得到像 Oracle 和 PostgreSQL 这样的数据库系统的支持。因此，若发现数据库对象使用了此语法，需要将其修改为 GROUP BY
后跟列名，并在需要的情况下使用 ORDER BY ASC/DESC
进行排序。
除了与 SQL 标准保持一致之外，MySQL 8.0 对该语法的修改还暗含了一个逻辑上的变化。在 MySQL5.7 中，GROUP BY
和 GROUP BY ASC
是等价的，都会对结果进行升序排序。然而，在 MySQL8.0 中，单独使用 GROUP BY
不再自动进行排序。这一变化对于那些仅需要进行分组操作而不关心排序的用户来说，意味着性能上的提升。如果需要对分组结果进行排序，只需在 GROUP BY
语句后添加 ORDER BY
子句即可。
15. Removed system variables for error logging to the system log configuration
A 检查的目的
将错误日志记录到系统日志配置的已删除系统变量
B 检查的方法
检查 configPath
参数传入的配置文件路径，我这里是"/database/mysql/etc/3307/my.cnf"
检查是否配置了以下结构体中第一个元素的参数的情况，第二个元素是推荐使用的替换参数。
{"log_syslog_facility", "syseventlog.facility"},
{"log_syslog_include_pid", "syseventlog.include_pid"},
{"log_syslog_tag", "syseventlog.tag"},
{"log_syslog", nullptr}};
C 异常类型和异常信息
Upgrade_issue::ERROR
System variables related to logging using OS facilities (the Event Log on Windows, and syslog on Unix and Unix-like systems) have been removed. Where appropriate, the removed system variables were replaced with new system variables managed by the log_sink_syseventlog error log component. Installations that used the old system variable names must update their configuration to use the new variable names.
翻译：
与使用操作系统设施（Windows 上的事件日志和 Unix 及类 Unix 系统上的 syslog）进行记录相关的系统变量已被移除。在适当的情况下，已删除的系统变量被 log_sink_syseventlog 错误日志组件管理的新系统变量替换。使用旧系统变量名的安装必须更新其配置以使用新的变量名。
D 例子
cat >> my.cnf << EOF
# check15
log_syslog=OFF
log_syslog_facility=daemon
log_syslog_include_pid=ON
log_syslog_tag=""
EOF
我的例子里使用的这 4 个参数，实际上都是默认值，配置和不配置 MySQL5.7 行为是没变化的。
但 MySQL8.0 是不允许你配置的，因为这个功能已经挪到去用组件去实现了，参数名都修改了，所以还用旧的参数名，启动数据库就得报错，未能识别的参数 xxx。
如图所示，我们能看出有三个参数名修改了。有一个参数被移除了，看官方文档，他大概是挪到下图这里了(有需求的读者请自行确认):
E 点评
无。
16. Removed system variables
A 检查的目的
检查除"15"外已删除的系统变量的情况
B 检查的方法
检查 configPath
参数传入的配置文件路径，我这里是"/database/mysql/etc/3307/my.cnf"
检查是否配置了以下结构体中第一个元素的参数的情况，第二个元素是推荐使用的替换参数。
{"date_format", nullptr},
{"datetime_format", nullptr},
{"group_replication_allow_local_disjoint_gtids_join", nullptr},
{"have_crypt", nullptr},
{"ignore_builtin_innodb", nullptr},
{"ignore_db_dirs", nullptr},
{"innodb_checksums", "innodb_checksum_algorithm"},
{"innodb_disable_resize_buffer_pool_debug", nullptr},
{"innodb_file_format", nullptr},
{"innodb_file_format_check", nullptr},
{"innodb_file_format_max", nullptr},
{"innodb_large_prefix", nullptr},
{"innodb_locks_unsafe_for_binlog", nullptr},
{"innodb_stats_sample_pages", "innodb_stats_transient_sample_pages"},
{"innodb_support_xa", nullptr},
{"innodb_undo_logs", "innodb_rollback_segments"},
{"log_warnings", "log_error_verbosity"},
{"log_builtin_as_identified_by_password", nullptr},
{"log_error_filter_rules", nullptr},
{"max_tmp_tables", nullptr},
{"multi_range_count", nullptr},
{"old_passwords", nullptr},
{"query_cache_limit", nullptr},
{"query_cache_min_res_unit", nullptr},
{"query_cache_size", nullptr},
{"query_cache_type", nullptr},
{"query_cache_wlock_invalidate", nullptr},
{"secure_auth", nullptr},
{"show_compatibility_56", nullptr},
{"sync_frm", nullptr},
{"time_format", nullptr},
{"tx_isolation", "transaction_isolation"},
{"tx_read_only", "transaction_read_only"}});
可见，有一些是参数被新参数替代了，绝大多数是参数直接被移除了。
C 异常类型和异常信息
Upgrade_issue::ERROR
Following system variables that were detected as being used will be removed. Please update your system to not rely on them before the upgrade.
翻译：
以下检测到正在使用的系统变量将被移除。请在升级之前更新您的系统，以免依赖它们。
D 例子
cat >> my.cnf << EOF
# check16
query_cache_size = 0;
query_cache_type = 0;
EOF
E 点评
查询缓存（query_cache），这个功能在 MySQL 里可以说弱爆了，在 99% 的场景下，设置查询缓存不仅不能加速查询，反而可能会有害！因此，MySQL 8.0 官方直接废弃了这个功能。
事实上，单论 query_cache 涉及的这两个参数而言，一般人不会去设置他，也就是用的官方默认值，只要不在配置文件中强制配置，就不会遇到兼容性问题。但这条对我有影响，因为 query_cache_size = 0
和 query_cache_type = 0
是我故意设置的，在生产环境确实有设置上去，而且这是我认为的我对 MySQL5.7 的优化！
MySQL5.7 默认设置下(不在配置文件设置 query_cache 参数)
mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)
这里可以看到默认值 query_cache_type = OFF
，query_cache_size = 1MB
。我们在执行了一条 SQL 后，查看 query_cache 的状态值，居然发现有变化，Qcache_not_cached 加 1，并且能看到 Qcache_free_memory 确实分配了 1MB 内存。
mysql> show global status like 'qca%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
也就是这里明显有两个问题:
query_cache_size 真的占用了我的 1MB 内存 —— 非处女座的可以忽略 status 值有改变，意味着虽然我们表面上禁用了 query_cache，使 SQL 都不会走查询缓存，但这里还是没完全关闭 query_cache 代码逻辑，否则如何解释 status 会更新？这里的性能损耗我没有做过压力测试，不清楚具体有多少性能影响。
那么我设置 query_cache_size = 0
和 query_cache_type = 0
(可以不设置，因为是默认值，作为参数组一起强制设置，方便运维)，结果是什么？
mysql> show global status like 'qca%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)
所以，我认为我对 query_cache 设置的做法是对的，但 8.0 直接舍弃了 query_cache 了，我需要去掉不兼容的参数设置了。
17. System variables with new default values
A 检查的目的
提醒具有新默认值的系统变量的情况。
B 检查的方法
检查 configPath
参数传入的配置文件路径，我这里是"/database/mysql/etc/3307/my.cnf"
以下结构体中第一个元素指"要检查的参数"（26 个），第二个元素表示"这个参数的默认值的变化"。如果要检查的参数，在你的配置文件未配置，则会 warnings 提醒你"这个参数的默认值的变化"。
{"character_set_server", "from latin1 to utf8mb4"},
{"collation_server", "from latin1_swedish_ci to utf8mb4_0900_ai_ci"},
{"explicit_defaults_for_timestamp", "from OFF to ON"},
{"optimizer_trace_max_mem_size", "from 16KB to 1MB"},
{"back_log", nullptr},
{"max_allowed_packet", "from 4194304 (4MB) to 67108864 (64MB)"},
{"max_error_count", "from 64 to 1024"},
{"event_scheduler", "from OFF to ON"},
{"table_open_cache", "from 2000 to 4000"},
{"log_error_verbosity", "from 3 (Notes) to 2 (Warning)"},
{"innodb_undo_tablespaces", "from 0 to 2"},
{"innodb_undo_log_truncate", "from OFF to ON"},
{"innodb_flush_method", "from NULL to fsync (Unix), unbuffered (Windows)"},
{"innodb_autoinc_lock_mode", "from 1 (consecutive) to 2 (interleaved)"},
{"innodb_flush_neighbors", "from 1 (enable) to 0 (disable)"},
{"innodb_max_dirty_pages_pct_lwm", "from_0 (%) to 10 (%)"},
{"innodb_max_dirty_pages_pct", "from 75 (%) 90 (%)"},
{"performance_schema_consumer_events_transactions_current", "from OFF to ON"},
{"performance_schema_consumer_events_transactions_history", "from OFF to ON"},
{"log_bin", "from OFF to ON"},
{"server_id", "from 0 to 1"},
{"log_slave_updates", "from OFF to ON"},
{"master_info_repository", "from FILE to TABLE"},
{"relay_log_info_repository", "from FILE to TABLE"},
{"transaction_write_set_extraction", "from OFF to XXHASH64"},
{"slave_rows_search_algorithms", "from 'INDEX_SCAN, TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'"}},
C 异常类型和异常信息
Upgrade_issue::WARNING
Following system variables that are not defined in your configuration file will have new default values. Please review if you rely on their current values and if so define them before performing upgrade.
翻译：
以下未在您的配置文件中定义的系统变量将具有新的默认值。如果您依赖它们当前的值，请在执行升级之前对它们进行审核并定义。
D 例子
E 点评
我用的正经的生产环境的配置文件做的检查，有警告的参数配置还真不少，大多数不需要管，少部分需要处理，以下表格是我的分析:
我没有加一列解释参数是做什么的，虽然我个人认为自己是 MySQL 参数方面的专家，但由于篇幅和排版的原因，我不能太长篇大论。
实际上，我习惯于在 my.cnf 强制设置一些默认值，代表虽然我是用了默认值，但他很重要，我担心其未来参数改变。所以我如果没有配置的参数，绝大多数可以定性为"我不在乎"的。我这次案例里的 my.cnf 有两个参数我需要整改，分别是 event_scheduler
和 explicit_defaults_for_timestamp
。event_scheduler
我不同意官方把他设置为默认开启的做法，而 explicit_defaults_for_timestamp 参数我同意官方新默认值，但为了升级的兼容性，我只能给他们都强制配置了旧的默认值了。
所有检查项中，这项目是最多 warnings 的，需要 DBA 去逐一判断和处理。
18. Zero Date, Datetime, and Timestamp values
A 检查的目的
使用了零日期、日期时间和时间戳值的情况。
B 检查的方法
下发到数据库以下 SQL，如果检查发现有结果，则返回 Upgrade_issue 对象。
# 检查global.sql_mode中，没有包含NO_ZERO_DATE和NO_ZERO_IN_DATE的情况
select 'global.sql_mode', 'does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE which allows insertion of zero dates' from (SELECT @@global.sql_mode like '%NO_ZERO_IN_DATE%' and @@global.sql_mode like '%NO_ZERO_DATE%' as zeroes_enabled) as q where q.zeroes_enabled = 0;
# 检查所有线程的session.sql_mode中，没有包含NO_ZERO_DATE和NO_ZERO_IN_DATE的情况
select 'session.sql_mode', concat(' of ', q.thread_count, ' session(s) does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE which allows insertion of zero dates') FROM (select count(thread_id) as thread_count from performance_schema.variables_by_thread WHERE variable_name = 'sql_mode' and (variable_value not like '%NO_ZERO_IN_DATE%' or variable_value not like '%NO_ZERO_DATE%')) as q where q.thread_count > 0;
# 检查所有非系统表，如果有timestamp、datetime和date类型的列，检查其是否具有以0000-00-00开头的默认值的情况
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, concat('column has zero default value: ', COLUMN_DEFAULT) from information_schema.columns where TABLE_SCHEMA not in ('performance_schema','information_schema','sys','mysql') and DATA_TYPE in ('timestamp', 'datetime', 'date') and COLUMN_DEFAULT like '0000-00-00%';
C 异常类型和异常信息
Upgrade_issue::WARNING
By default zero date/datetime/timestamp values are no longer allowed in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in SQL_MODE by default. These modes should be used with strict mode as they will be merged with strict mode in a future release. If you do not include these modes in your SQL_MODE setting, you are able to insert date/datetime/timestamp values that contain zeros. It is strongly advised to replace zero values with valid ones, as they may not work correctly in the future.
翻译：
从 MySQL5.7.8 开始，默认情况下不再允许使用零日期/时间/时间戳值，NO_ZERO_IN_DATE 和 NO_ZERO_DATE 默认包含在 SQL_MODE 中。这些模式应与严格模式一起使用，因为它们将在未来的版本中与严格模式合并。如果您的 SQL_MODE 设置中不包含这些模式，您可以插入包含零值的日期/时间/时间戳值。强烈建议用有效值替换零值，因为它们在未来可能无法正常工作。
D 例子
mysql> SET global sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET global sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_IN_DATE');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET session sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET session sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_IN_DATE');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE employees (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(255),
-> hire_date DATE DEFAULT '0000-00-00'
-> );
Query OK, 0 rows affected (1.02 sec)
E 点评
MySQL5.7 和 MySQL8.0 的行为不一样，MySQL5.7 可以把数据读取出来，而 MySQL8.0 取不出来，会报错。
以下是模拟实验：
MySQL5.7 下操作
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.39 |
+-----------+
1 row in set (0.00 sec)
# session级别临时去掉 NO_ZERO_DATE、NO_ZERO_IN_DATE 限制
SET session sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_DATE');
SET session sql_mode = sys.list_drop(@@session.sql_mode, 'NO_ZERO_IN_DATE');
# 建表
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`hire_date` date DEFAULT '0000-00-00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入一个全零值的日期、一个日期是零值的日期、一个正常的日期。
insert into employees values (1,'fander1','0000-00-00');
insert into employees values (2,'fander2','2023-04-00');
insert into employees values (3,'fander3','2023-04-13');
# 把 sql_mode 改回来
SET session sql_mode = sys.list_add(@@session.sql_mode, 'NO_ZERO_DATE');
SET session sql_mode = sys.list_add(@@session.sql_mode, 'NO_ZERO_IN_DATE');
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from employees;
+----+---------+------------+
| id | name | hire_date |
+----+---------+------------+
| 1 | fander1 | 0000-00-00 |
| 2 | fander2 | 2023-04-00 |
| 3 | fander2 | 2023-04-13 |
+----+---------+------------+
3 rows in set (0.00 sec)
# 以hire_date='0000-00-00'为条件，可以查到数据
mysql> select * from employees where hire_date='0000-00-00';
+----+---------+------------+
| id | name | hire_date |
+----+---------+------------+
| 1 | fander1 | 0000-00-00 |
+----+---------+------------+
1 row in set, 1 warning (0.00 sec)
# 以hire_date='2023-04-00'为条件，也可以查到数据
mysql> select * from employees where hire_date='2023-04-00';
+----+---------+------------+
| id | name | hire_date |
+----+---------+------------+
| 2 | fander2 | 2023-04-00 |
+----+---------+------------+
1 row in set, 1 warning (0.00 sec)
# 但有个警告
mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2023-04-00' for column 'hire_date' at row 1 |
+---------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
但这个实例升级到 MySQL8.0 后，这些数据查不到了。
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from employees;
+----+---------+------------+
| id | name | hire_date |
+----+---------+------------+
| 1 | fander1 | 0000-00-00 |
| 2 | fander2 | 2023-04-00 |
| 3 | fander2 | 2023-04-13 |
+----+---------+------------+
3 rows in set (0.00 sec)
# 以hire_date='0000-00-00'为条件，查数据报错，而不是warnings
mysql> select * from employees where hire_date='0000-00-00';
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
# 以hire_date='2023-04-00'为条件，查数据也是报错，而不是warnings
mysql> select * from employees where hire_date='2023-04-00';
ERROR 1525 (HY000): Incorrect DATE value: '2023-04-00'
所以，数据库里有零值日期的情况，并不会影响数据库升级，但会影响升级后查询的逻辑，有可能之前能查到的数据升级后查不到了，所以给出 warnings 让您核实情况。脚本没有直接查询表里是否有零值的情况，而是通过检查变量检查表结构和检查参数设置的方法，我猜是因为工具不希望去扫描用户表(而且还是模糊搜索)，以免造成生产影响。
