一.问题背景
测试环境测试SQL没有问题，但是上生产环境之后，sql执行报错
二.现象
生产环境：点击‘一键还款确认’按钮，显示‘发起还款确认失败’。监控系统发送邮件抱错‘BadSqlGrammarException’，
Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'malm.t.buy_back_date'; this is incompatible with sql_mode=only_full_group_by ### The error may exist in class path resource [mybatis/financial/customized/BuybackDao.xml] ### The error may involve
三.分析
查询的SQL为：
select
sum(a.advance_amount) as advanceSumAmount,
COUNT(1) as countNum,
sum(a.expected_principal_amount) as expectedPrincipalAmount,
sum(a.expected_interest_amount) as expectedInterestAmount,
sum(a.expected_fine_amount) as expectedFineAmount,
sum(a.expected_service_amount) as expectedServiceAmount,
a.pay_date as payDate
from tb_loan_prepayment a
where a.capital_id = 2
AND a.STATUS = 5
AND a.pay_date BETWEEN '2017-10-27' AND '2017-10-27 23:59:59';
该sql在测试环境运行时没有报错的，并且能够正确地返回结果。那为什么到了生产环境就不能执行呢？原因在于两个环境使用的SQL_Mode不同。
sql模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。我们可以使用 select @@sql_mode; 来查看mysql使用的模式。
以下为测试环境的执行结果(STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION)
以下为生产环境的执行结果(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)其中，ONLY_FULL_GROUP_B
Y就是导致以上现象的问题所在。其限制为：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。
四.解决：
方法1）在应用中修改sql语法问题，添加GROUP BY条件。
方法2）将生产环境的sql模式同步到测试环境。同步以后保证两个环境无差异，可以有效防止其他同事或其他sql限制造成该问题
网友评论