MySql踩坑：only_full_group_by配置，竟导致所有应用报错？

1. 踩坑经历

一个很平常的下午，大家都在埋头认真写bug呢，突然企业微信群里炸锅了，好多应用都出现大量的Error日志，而且都报同一个错误，就是下面这个：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘online_saas.t.receive_amount’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

从异常信息可以看出，报错的原因是因为Sql语句SELECT后面的列包含了group by后面没有的列并且没有使用聚合函数。

因为近几天一直未发布，所以就问运维是不是改了MySql服务器的配置，打开了sql_mode里的only_full_group_by，导致原本执行正常的Sql通不过检查而执行失败。

最后运维说有台MySql服务器之前曾用Sql语句临时关闭过 only_full_group_by ， 而刚刚因为负载过高自动重启了，导致sql_mode又使用了原有的默认值，而MySql 5.7以后sql_mode默认是开启only_full_group_by的，导致了该错误。

最后运维修改了这台MySql服务器的my.cnf文件，将sql_mode里的only_full_group_by关闭了，重启了MySql服务器和报错的应用，事情得以最终解决。

为啥要重启应用呢？是因为运维修改配置后，各个应用还是报错，所以重启了各个报错的应用。

2. 原因分析

假设你安装的是MySql 5.7以后的版本，比如5.7.21，默认情况下，sql_mode里的only_full_group_by是被打开的：

这个打开后，对Sql的语法检查就会很严格，就比如上面报错的Sql语句，就是因为使用GROUP BY不规范造成的。

正常情况下，我们使用GROUP BY语句都是下面这样的：

SELECT语句后的列，要么是GROUP BY语句后面出现的列，要么是使用了聚合函数。

但如果有些地方写的不规范，就会报错，比如下面这样：

因为age列既没有出现在GROUP BY语句后，也没有使用聚合函数。

但如果我们将sql_mode里的only_full_group_by关闭，上面报错的语句就不报错了：

SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

注意事项：如果仍然报错，请打开新会话执行查询语句。

还要值得注意的是，上面关闭only_full_group_by的方式是临时的，如果重启了MySql服务器，only_full_group_by又被打开了，如下所示：

[图片上传失败…(image-460083-1603875070296)]

[图片上传失败…(image-8d42bf-1603875070296)]

这也是为什么MySql服务器自动重启后，我们的应用开始报错的原因，因为运维之前确实改过，但是是临时改的，重启后又被覆盖了。

3. 推荐解决方案

如果没有历史技术债，这个开关打开也挺好的，可以让大家按规范写Sql，如果不规范，就能及时发现。

但如果有历史技术债，就需要关闭only_full_group_by了，而且要永久性的关闭，避免MySql服务器重启后配置又被覆盖的情况。

可以通过在==/etc/my.cnf==文件添加以下内容，来永久关闭only_full_group_by：

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

修改完毕后，记得重启MySql。

如果重启后也不会生效，检查下sql_mode的位置是不是不对（放在最后是不会生效的）：

作者：申城异乡人

给大家分享下我的复习的面试资料

这些面试全部出自大厂面试真题和面试合集当中，小编已经为大家整理完毕（PDF版）

资料获取方式：戳这里前往我的腾讯文档免费下载

• 第一部分：Java基础-中级-高级

• 第二部分：开源框架（SSM：Spring+SpringMVC+MyBatis）

• 第三部分：性能调优（JVM+MySQL+Tomcat）

• 第四部分：分布式（限流：ZK+Nginx；缓存：Redis+MongoDB+Memcached；通讯：MQ+kafka）

• 第五部分：微服务（SpringBoot+SpringCloud+Dubbo）

• 第六部分：其他：并发编程+设计模式+数据结构与算法+网络

进阶学习笔记pdf

都已整理好，需免费下载点击这里即可

• Java架构进阶之架构筑基篇（Java基础+并发编程+JVM+MySQL+Tomcat+网络+数据结构与算法）

• Java架构进阶之开源框架篇（设计模式+Spring+SpringMVC+MyBatis）

• Java架构进阶之分布式架构篇 （限流（ZK/Nginx）+缓存（Redis/MongoDB/Memcached）+通讯（MQ/kafka））

• Java架构进阶之微服务架构篇（RPC+SpringBoot+SpringCloud+Dubbo+K8s）

• Java架构进阶之微服务架构篇（RPC+SpringBoot+SpringCloud+Dubbo+K8s）

[外链图片转存中…(img-tmsKc8Fz-1623580329137)]

[外链图片转存中…(img-BgS0sNq8-1623580329137)]