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

2022-05-17 16:43:46 浏览数 (1)

sql_mode兼容性，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。

代码语言：javascript复制
# 业务侧报错信息
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，看看客户端会话到底执行了哪些语句。

代码语言：javascript复制
...
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 中同时废弃了这个模式，以后不再支持。

代码语言：javascript复制
# 在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的初始参数时，将5.7的运行参数与8.0的参数模板进行了比对，将5.7的部分参数设置合并到了MySQL 8.0的参数文件。

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

总结

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

云推荐引擎 数据库 sql 云数据库SQLServer

0 人点赞