说明:
近期在进行MySQL 5.7.X升级到8.0.X时，遇到了几个新问题。

问题一:列注释含有无效字符
操作:启动数据库，执行升级时
/mysqldata/app/8.0.33/bin/mysqld --defaults-file=/mysqldata/3308/conf/my.cnf --user=mysql &
报错如下，升级自动中断:

[ERROR] [MY-013140] [Server] Comment for field 'cjc.t1.col1' contains an invalid utf8mb3 character string: '\xCF\xDE\xD6'.
[ERROR] [MY-013140] [Server] Comment for field 'cjc.t2.col2' contains an invalid utf8mb3 character string: '\xB1\xB8\xD6'.
[ERROR] [MY-013140] [Server] Comment for field 'cjc.t3.col2' contains an invalid utf8mb3 character string: '\xB1\xB8\xD6'.
[ERROR] [MY-010022] [Server] Failed to Populate DD tables.
[ERROR] [MY-010119] [Server] Aborting

问题分析:
1.用升级之前的冷备回退数据库到原5.7.X版本;
2.根据报错提示，检查cjc库t1,t2,t3表col1,col2注释信息；

show create table cjc.t1\G;
show create table cjc.t2\G;
show create table cjc.t3\G;

检查发现，确实存在乱码:

CREATE TABLE `t1` (
  `ID` varchar(32) NOT NULL COMMENT 'ID',
  `col1` int(10) DEFAULT NULL COMMENT '??ҵӲ?????ʱ?',
  `XXX` tinyint(5) DEFAULT NULL COMMENT 'AAA',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BBB'
1 row in set (0.00 sec)

解决方案:
测试库，经沟通，可以修改注释信息:

alter table cjc.t1 modify column col1 int(10) comment 'XXX';
alter table cjc.t2 modify column col2 varchar(200) comment 'XXX';
alter table cjc.t3 modify column col2 varchar(200) comment 'XXX';

修改后，停库，冷备，再次升级，升级成功，日志如下:

2024-07-30T19:00:18.626375+08:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-07-30T19:00:18.626455+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-30T19:00:21.642473+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-30T19:01:20.279318+08:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-07-30T19:01:24.960837+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80033' started.
2024-07-30T19:01:56.247123+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80033' completed.

思考1:
如何在升级之前检查是否存在此类问题:
执行SQL，检查注释信息:

SELECT table_schema,table_name,column_name,hex(column_comment),column_comment FROM information_schema.columns WHERE length(column_comment)<>char_length(column_comment) ORDER BY table_schema,table_name;
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_COMMENT,LENGTH(COLUMN_COMMENT) from information_schema.COLUMNS WHERE LENGTH(COLUMN_COMMENT)>0;

还有表的注释

select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT,length(TABLE_COMMENT) from information_schema.tables where LENGTH(TABLE_COMMENT)>0 AND TABLE_COMMENT !='VIEW';

思考2:
为什么5.7版本列注释可以有无效字符，升级到8.0就不行了？
猜想可能是因为MySQL 8.0取消.frm文件，而用字典表代替，在升级时，会从数据目录中读取.frm文件到数据字典表中，然后删除.frm文件，在读取.frm文件时，会对每个表的每个字段的注释进行校验，如有异常，就会直接中断升级进程。本次升级值得庆幸的一点是error.log有详细打印出所有有异常的库、表、列信息，可以让我们一次性解决此问题。

问题二:frm和ibd文件匹配
场景1:t1.frm和t1.ibd都缺失
场景2:缺少t1.frm，只有t1.ibd
场景3:缺少t1.ibd，只有t1.frm
经测试缺少frm文件时，通过mysqlcheck并不能检查出来问题，而在升级过程中，又会出现如下问题，导致升级失败:

2024-06-07T14:02:43.115694+08:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-06-07T14:02:43.115748+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-06-07T14:02:45.995070+08:00 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2024-06-07T14:02:45.995149+08:00 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2024-06-07T14:02:45.995164+08:00 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './cjc/t1.ibd' OS error: 71
2024-06-07T14:02:45.995178+08:00 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `cjc/t1` because it could not be opened.
2024-06-07T14:02:46.139010+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-06-07T14:02:47.089458+08:00 1 [ERROR] [MY-011006] [Server] Got error 197 from SE while migrating tablespaces.
2024-06-07T14:02:47.117745+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

缺少ibd文件通常能检查出来:

mysqlcheck -u root -p --all-databases --check-upgrade
cjc.t1
Warning  : InnoDB: Tablespace is missing for table cjc/t1.
Error    : Tablespace is missing for table `cjc`.`t1`.
error    : Corrupt

问题原因:

通过history查到曾经有人执行过mv或rm命令，将t1.frm和t1.ibd移动到了其他目录，测试环境就是这样，什么千奇百怪的问题都有可能遇到，见怪不怪了，不要问为什么执行这个操作，我猜他是感觉这么删表更快。

经常和其他人解释为什么不能通过操作系统命令删除或移动表文件？
根本原因是删除不干净，一张表的信息记录在多个内部表里，当通过rm命令把表结构文件删除，例如t1.frm删除或移动走以后，确实在information_schema.tables或show tables里查不到t1表了，但是INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES等还有这张表的信息，这就导致了元数据不一致的问题。

SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE
NAME NOT LIKE '%#%' AND NAME NOT LIKE '%@%' AND NAME NOT LIKE '%FTS_000000%' AND
NAME NOT IN (SELECT CONCAT(TABLE_SCHEMA,'/',TABLE_NAME) NAME FROM INFORMATION_SCHEMA.TABLES);
+--------+
| NAME   |
+--------+
| cjc/t1 |
+--------+
1 row in set (0.01 sec)

解决方案:
经沟通后，可以通过SQL语句drop table xxx;彻底删除此表，当然缺少t1.frm直接执行drop table会报错，可以随便拷贝另一张表frm文件并重命名为t1.frm，然后删除表时如果还报错，通常会在error.log里提示列数量不匹配，并打印出实际列数，最后新增一个列数相同的表，拷贝并重命名为t1.frm，就可以正常drop table xxx了。

问题三:row size is xxx which is greater than maximum allowed size
如果是本地升级，遇到此类问题不会影响升级，但是会在 error.log里有如下提示:

2024-06-06T10:02:00.024187+08:00 11 [Warning] [MY-011825] [InnoDB] Cannot add field `col1` in table `cjc`.`t1` because after adding it, the row size is 8357 which is greater than maximum allowed size (8126) for a record on index leaf page.
2024-06-06T10:02:00.473517+08:00 12 [Warning] [MY-011825] [InnoDB] Cannot add field `col2` in table `cjc`.`t1` because after adding it, the row size is 8361 which is greater than maximum allowed size (8126) for a record on index leaf page.

检查表结构、表数据均没问题:

show create table cjc.t1\G;
select * from cjc.t1;

使用相同的建表语句，在8.0中建表会报相同的错误，而导致建表失败。这也导致如果是通过mysqldump逻辑导出、导入方式进行迁移，将会因为建表失败而导入失败。解决方案是在迁移前提前将ROW_FORMAT改成DYNAMIC或COMPRESSED，或者SQL文件不大直接编译修改也可以。

那么如何在升级之前找出类似的问题，提前解决呢？