Py学习  »  DATABASE

踩坑系列:MySql only_full_group_by配置,竟导致所有应用报错?

申城异乡人 • 3 年前 • 239 次点击  

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';
  • 1
  • 1

在这里插入图片描述
在这里插入图片描述

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

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

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

这也是为什么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"
  • 1
  • 1

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

如果重启后也不会生效,检查下sql_mode的位置是不是不对( 放在最后是不会生效的 ):

在这里插入图片描述


注:如果觉得本篇博客有任何错误或者更好的建议,欢迎留言,我会及时跟进并更正博客内容!

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/74301
 
239 次点击