Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: ... incompatible with Sql_mode=only_full_ Group_by

First, abnormal information

org.springframework.jdbc.BadSqlGrammarException: # # # Error querying database. Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Expression #2 of the SELECT list is not in the GROUP by clause and contains nonaggregated column ' Daotable.he_store_file.file_url ' which are no T functionally dependent on columns in GROUP by clause; Thisis incompatible with sql_mode=only_full_group_by### The error may exist in COM/zbq/springbootbase/mysql/Hestorefiledao.java (best guess) # # # The error may involve Com.zbq.springbootbase.mysql.HeStoreFileDAO.findFileInfoByFileTypeAndStoreIdList-inline### the error occurred whilesetting parameters### sql:select store_id, File_url from He_store_file WHERE (1=1) and (store_id in (8310596)) and (File_type =?) GROUP by store_id### Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Expression #2 of the SELECT list is not in the GROUP by clause and contains nonaggregated column ' Daotable.he_store_file.file_url ' which are no T functionally dependent on columns in GROUP by clause; Thisis incompatible with sql_mode=only_full_group_by; bad SQL Grammar []; nested exception is Com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Expression #2 of the SELECT list is not in the GROUP by clause and contains nonaggregated column ' Daotable.he_store_file.file_url ' which are no T functionally dependent on columns in GROUP by clause; Thisis incompatible with sql_mode=only_full_group_by at Org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate ( Sqlexceptionsubclasstranslator.java:~[spring-jdbc-5.0.7.release.jar:5.0.7). RELEASE] at Org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate ( Abstractfallbacksqlexceptiontranslator.java:~[spring-jdbc-5.0.7.release.jar:5.0.7). RELEASE] at Org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate ( Abstractfallbacksqlexceptiontranslator.java:Bayi) ~[spring-jdbc-5.0.7.release.jar:5.0.7. RELEASE] at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible ( Mybatisexceptiontranslator.java:~[mybatis-spring-1.3.2.jar:1.3.2)] at Org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke (Sqlsessiontemplate.java:446) ~[mybatis-spring-1.3.2.jar:1.3.2] at Com.sun.proxy. $Proxy 85.selectList (Unknown Source)~[Na:na] at Org.mybatis.spring.SqlSessionTemplate.selectList (Sqlsessiontemplate.java:) ~[mybatis-spring-1.3.2.jar:1.3.2] at Org.apache.ibatis.binding.MapperMethod.executeForMany (Mappermethod.java:139) ~[mybatis-3.4.6.jar:3.4.6] at Org.apache.ibatis.binding.MapperMethod.execute (Mappermethod.java:~[mybatis-3.4.6.jar:3.4.6)] at Org.apache.ibatis.binding.MapperProxy.invoke (Mapperproxy.java:(~[mybatis-3.4.6.jar:3.4.6)] at Com.sun.proxy. $Proxy 94.findFileInfoByFileTypeAndStoreIdList (Unknown Source)~[Na:na] at Sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method)~[na:1.8. 0_112] at Sun.reflect.NativeMethodAccessorImpl.invoke (Nativemethodaccessorimpl.java:) ~[na:1.8. 0_112] at Sun.reflect.DelegatingMethodAccessorImpl.invoke (Delegatingmethodaccessorimpl.java:~[na:1.8). 0_112] at Java.lang.reflect.Method.invoke (Method.java:498) ~[na:1.8.0_112]

Second, the reasons for analysis

Only_full_group_by means: For a group by aggregation operation, if the column in select does not appear in group BY, then this SQL is not legal because the column is not in the GROUP BY clause, that is, the column found must be in the group This will cause an error or the field to appear in the aggregate function.

Command line execution: SELECT @ @SESSION. Sql_mode; Found: 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 The first entry opens only_full_group_by by default.

Third, the solution
1. Temporary solution turn off 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 ';

2. Permanent Solution

Modify the My.ini configuration (if you do not have this file MySQL, you can change My-default.ini to My.ini)

= ' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_user,no_engine_ SUBSTITUTION ';

Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: ... incompatible with Sql_mode=only_full_ Group_by