> 文档中心 > sql_mode兼容性,MySQL 8.0 升级踩过的坑

sql_mode兼容性,MySQL 8.0 升级踩过的坑


MySQL 8.0新特性专栏目录

双重密码,MySQL 8.0创新特性
sql_mode兼容性,MySQL 8.0 升级踩过的坑


sql_mode兼容性,MySQL 8.0 升级踩过的坑

  • MySQL 8.0新特性专栏目录
  • 前言:
  • 1. MySQL升级8.0之后,业务访问数据库报错
  • 2. 问题原因剖析
    • 2.1 sql_mode的历史变迁
    • 2.2 NO_AUTO_CREATE_USER的前世今生
    • 2.3 检查并解决不兼容的sql_mode
  • 总结

前言:

MySQL 8.0从GA到现在已经过去4年了,被各大互联网公司广泛使用,稳定性得到了充分的验证。最近,我们也在将存量的旧版本数据库升级到8.0。虽然前期做了很多的检查和验证,不过升级过程中终究免不了踩一些坑。

sql_mode,在MySQL 5.7之前是宽松模式;在MySQL 5.7之后一直到最新的8.0.28版本,都还是严格模式。不过,在不同的数据库版本之间、不同的数据库实例之间,sql_mode总会有一些变化;升级或者迁移的过程中一不小心就会掉入坑中。


1. MySQL升级8.0之后,业务访问数据库报错

一套业务库从MySQL 5.7升级到8.0之后,业务请求报错,SQLSTATE[42000]: Syntax error or access violation。

# 业务侧报错信息SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' (SQL: select `id`, `name` from `t`)

这里的报错提示为,语法错误。开发人员一看就蒙圈了,这代码跑了这么多年,怎么突然会报个语法错误呢?并且报错信息显示'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER',开发人员确认地说肯定没有写过这样的SQL。
这里我们可以先简单定位一下,打开general_log,看看客户端会话到底执行了哪些语句。

...2022-03-24T20:02:40.077202+08:00     9221 Execute   set session 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'2022-03-24T20:02:40.077202+08:00     9221 Execute  select `id`, `name` from `t`...

根据日志可以明确看到,客户端首先设置了会话级的sql_mode,然后再执行相应的业务查询。拿到这个具体的SQL,开发人员很快就能确定这是php框架底层自带的语句。

很明显,这是一个标准的基于MySQL 5.7版本开发的底层框架。

2. 问题原因剖析

2.1 sql_mode的历史变迁

为什么一眼就能断定这是基于MySQL 5.7版本开发的框架呢?

因为,报错的这句sql_mode设置,与MySQL 5.7 版本的默认sql_mode设置一模一样。在MySQL 5.7中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'

而在MySQL 8.0.11版本中,sql_mode的默认配置作了修改,去掉了一个sql模式。刚好,去掉的模式就是前面报错信息中提示的NO_AUTO_CREATE_USER

2.2 NO_AUTO_CREATE_USER的前世今生

在MySQL 5.7 之前,DBA经常习惯使用 grant 语法来创建用户和授权。MySQL 5.7 仍然支持这种语法来创建用户,但是为了限制这种创建用户的行为,引入了NO_AUTO_CREATE_USER的sql模式。[NO_AUTO_CREATE_USER], 即在grant语句中禁止创建空密码的账户,使用grant语法创建用户必须带上 “identified by”关键字设置账户密码,否则就被认为是非法的创建语句。

而在MySQL 8.0.11版本之后,官方认为DBA们已经接受了默认使用create user语法来创建账户的行为,就直接把grant创建账户的语法给废弃了。grant语法创建账户都不允许了,那么NO_AUTO_CREATE_USER模式也就自然要退出历史舞台了,所以就在8.0.11 中同时废弃了这个模式,以后不再支持。

# 在MySQL 8.0.23中测试一下grant语法,直接报错。MySQL [(none)]> grant select on test.* to 'no_user'@'100.124.43.85' identified by 'no_user';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 'identified by 'no_user'' at line 1

NOTE: 这里问题就很明确了,业务底层框架在连接数据库后设置会话级sql_mode,包含了MySQL 8.0中已经废弃的模式NO_AUTO_CREATE_USER,MySQL直接返回报错。

2.3 检查并解决不兼容的sql_mode

实际上,在MySQL 8.0中废弃的sql_mode并不仅仅只有NO_AUTO_CREATE_USER,还有一些模式同样也是8.0不兼容的。我们在制定MySQL升级方案时就已经考虑到了sql_mode的变化,在升级前检查中加入了MySQL 8.0不支持的sql_mode的检查。如下图所示:
MySQL 8.0升级前检查
并且在升级过程过程中,配置MySQL 8.0的初始参数时,将5.7的运行参数与8.0的参数模板进行了比对,将5.7的部分参数设置合并到了MySQL 8.0的参数文件。

但是,以上种种方案只能保证MySQL升级前后的全局参数配置是OK的;客户端连接设置的会话级参数我们无法控制。这里就需要开发人员配合一起检查业务侧代码里有没有与MySQL 8.0不兼容的参数设置。

总结

那么,哪些参数/设置是MySQL 8.0与之前版本不兼容的呢?
我将在下一篇文章同大家分享这个问题。欢迎大家点赞关注,我会持续做一些相关记录和分享。