sql_mode兼容性，MySQL8.0升级踩过的坑
MySQL 8.0新特性专栏⽬录
sql_mode兼容性，MySQL 8.0 升级踩过的坑
前⾔：
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:009221Execute 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:009221Execute 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的初始参数时，将5.7的运⾏参数与8.0的参数模板进⾏了⽐对，将5.7的部分参数设置合并到了MySQL 8.0的参数⽂件。
但是，以上种种⽅案只能保证MySQL升级前后的全局参数配置是OK的；客户端连接设置的会话级参数我们⽆法控制。这⾥就需要开发⼈员配合⼀起检查业务侧代码⾥有没有与MySQL 8.0不兼容的参数设置。
总结mysql下载哪个版本好2022
那么，哪些参数/设置是MySQL 8.0与之前版本不兼容的呢？
我将在下⼀篇⽂章同⼤家分享这个问题。欢迎⼤家点赞关注，我会持续做⼀些相关记录和分享。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。