MySQL升级到8.0版本的一些经验( 四 ) _MySQL

对于group by聚合操作，order by 列必须在group by中出现

mysql> select name,age from test group by name,age order by name;+------+------+| name | age|+------+------+| aa|18 |+------+------+1 row in set (0.00 sec)mysql> select name,age from test group by name,age order by id;ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'test.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_bymysql>

解决方案1：研发侧调整对应的SQL语句
解决方案2：调整MySQL5.7/8.0的sql_mode参数，保证兼容MySQL5.5的语法

2）MyISAM存储引擎的表可能存在潜在问题
解决方案：经过排查，目前线上bbs的库表均为innodb存储引擎或者memory存储引擎

3）部分SQL会出现执行计划发生改变，可能需要略微调整
解决方案：目前暂时没有发现，后续有类似SQL，可以针对性处理

4）字符集验证
MySQL5.7默认字符集是utf8字符集，如果是gbk等字符集需要调整并验证
解决方案：DBA侧保证升级过后，不会出现乱码等字符集报错信息

3、MySQL 5.7升级到MySQL8.0的补充兼容性测试：
1）表中需要包含主键
在8.0版本中会强制要求表中包含主键

2）timestamp数据类型默认值
如果表结构中有timestamp类型字段，并且设置了默认值DEFAULT CURRENT_TIMESTAMP，建议将参数设置为off:

explicit_defaults_for_timestamp=OFF(8.0默认为on)

否则有可能会出现：Error:1048 - Column ‘createTime‘ cannot be null

3）执行计划变化
部分SQL会出现执行计划发生改变，可能需要略微调整

解决方案：跨版本升级中的SQL异常，可以通过提前交付只读实例来进行预先验证，并且抓取原库的慢日志在8.0数据库中进行回放验证

4）MySQL8.0 新增关键字（如rank），可能导致查询、写入失败

mysql> select rank from activity_public_log limit 1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from activity_public_log limit 1' at line 1

解决方案：改写成 `rank`或者调整字段名

mysql> select `rank` from activity_public_log limit 1;

查询方式：

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAMEfrom information_schema.COLUMNS where COLUMN_NAME="rank";

5）对于load权限的确认
部分业务具有导入数据的权限，在默认模板中参数secure_file_priv和local_infile是关闭的，需要和业务侧确认是否有该类需求，或者从定时任务中识别

6）存储过程权限检查
部分业务中存在存储过程时，对于存储过程的权限粒度 invoker和definer差异可能导致迁移后业务调用失败，需要在迁移中进行检查

（五）制定资源申请和回收流程
有了前面的流程支持，整个过程基本可以跑起来了，还有一个风险则是采用资源平替的方案，也就意味着今天数据库实例是业务A的主库，完成升级之后我们会让系统的同事重新格式化后交付给我们，很可能明天就变成业务B的从库了，所以资源是以资源池的形式在反复利用，对于如何申请资源和下线资源就是关键，我们制定的流程是需要至少3次审核才可以下线，而且下线的过程中还需要有一定的观察期窗口。

为此我们也指定了专人负责制度，即最终的下线操作只能由固定的一个人来操作，他需要对下线操作做最后的审核，并且负责。