1.场景
2020-05-09 17:54:23.297 WARN 1 --- [ XNIO-3 task-10] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hzero_platform.hu2.unit_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in URL [jar:file:/app.jar!/BOOT-INF/classes!/mapper/HsCodeMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select hu.unit_id unitId, hu.unit_name companyName, hu2.unit_name departName, hp.position_name positionName from hzero_platform.hpfm_unit hu left join hzero_platform.hpfm_employee_assign hea on hea.unit_company_id = hu.unit_id left join hzero_platform.hpfm_unit hu2 on hu2.unit_id = hea.unit_id left join hzero_platform.hpfm_position hp on hp.position_id = hea.position_id left join hzero_platform.hpfm_employee_user heu on heu.employee_id = hea.employee_id where hu.unit_type_code = 'C' and heu.user_id = ? group by hu.unit_id
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hzero_platform.hu2.unit_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hzero_platform.hu2.unit_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
2.原因分析
MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY
SQL模式(默认情况下),MySQL将拒绝选择列表,HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列,也不在功能上依赖于它们。(5.7.5之前,MySQL没有检测到功能依赖关系,默认情况下不启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明,请参见“MySQL5.6参考手册”。)
3.解决方案
1.修改docker.cnf
添加下面这句
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION