MySQL5.7.9版本sql_mode=only_full_group_by问题的解决办法
程序员文章站
2022-07-04 09:40:59
mysql 5.7.9版本sql_mode=only_full_group_by问题
21-jun-2018 18:45:58.056 info [http-nio-8080-exec-8] o...
mysql 5.7.9版本sql_mode=only_full_group_by问题
21-jun-2018 18:45:58.056 info [http-nio-8080-exec-8] org.springframework.beans.factory.xml.xmlbeandefinitionreader.loadbeandefinitions loading xml bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 21-jun-2018 18:45:58.078 info [http-nio-8080-exec-8] org.springframework.jdbc.support.sqlerrorcodesfactory. sqlerrorcodes loaded: [db2, derby, h2, hsql, informix, ms-sql, mysql, oracle, postgresql, sybase] org.springframework.jdbc.badsqlgrammarexception: ### error querying database. cause: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: in aggregated query without group by, expression #1 of select list contains nonaggregated column 'fmedia_insurance.product.unique_code'; this is incompatible with sql_mode=only_full_group_by ### the error may exist in file [/data/project/insurance-server/web-inf/classes/com/jufengad/insurance/server/mapping/insuranceratemapper.xml] ### the error may involve com.jufengad.insurance.server.dao.insuranceratemapper.querypricefactorbyproductcode-inline ### the error occurred while setting parameters ### sql: select get_age(product.unique_code) as age_data, get_sex(product.unique_code) as sex_data, max(rate.social_security) as social_data, get_pay_period(product.unique_code) as pay_period_data, get_safeguard_period(product.unique_code) as safeguard_period_data from insurance_rate rate left join insurance_product_main product on rate.product_id=product.id where rate.product_code=? ### cause: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: in aggregated query without group by, expression #1 of select list contains nonaggregated column 'fmedia_insurance.product.unique_code'; this is incompatible with sql_mode=only_full_group_by ; bad sql grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: in aggregated query without group by, expression #1 of select list contains nonaggregated column 'fmedia_insurance.product.unique_code'; this is incompatible with sql_mode=only_full_group_by at org.springframework.jdbc.support.sqlexceptionsubclasstranslator.dotranslate(sqlexceptionsubclasstranslator.java:95) at org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:73) at org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:81) at org.mybatis.spring.mybatisexceptiontranslator.translateexceptionifpossible(mybatisexceptiontranslator.java:73) at org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke(sqlsessiontemplate.java:371) at com.sun.proxy.$proxy43.selectone(unknown source)
解决方法 :
执行set global sql_mode = ''; 把sql_mode 改成非only_full_group_by模式。
验证是否生效 select @@global.sql_mode 或 select @@sql_mode
mysql> select @@global.sql_mode; +------------------------------------------------------------------------------- -----------------------------------------------+ | @@global.sql_mode | +------------------------------------------------------------------------------- -----------------------------------------------+ | only_full_group_by,strict_trans_tables,no_zero_in_date,error_for_division_by_z ero,no_auto_create_user,no_engine_substitution | +------------------------------------------------------------------------------- -----------------------------------------------+ 1 row in set (0.04 sec) mysql>
mysql> set sql_mode = 'strict_trans_tables,no_zero_in_date,error_for_division_by _zero,no_auto_create_user,no_engine_substitution'; query ok, 0 rows affected, 1 warning (0.04 sec) mysql>
推荐阅读
-
MySQL5.7 sql_mode=only_full_group_by的解决办法
-
MySql版本问题sql_mode=only_full_group_by的完美解决方案
-
jquery1.8版本使用ajax实现微信调用出现的问题分析及解决办法
-
关于HTML5 Placeholder新标签低版本浏览器下不兼容的问题分析及解决办法
-
解决MySQL 5.7.9版本sql_mode=only_full_group_by问题
-
MYSQL5.7版本sql_mode=only_full_group_by问题,重启有效的方法
-
MySQL5.7.9版本sql_mode=only_full_group_by问题的解决办法
-
关于部署版本遇到的---警告: 程序集绑定日志记录被关闭。问题解决办法
-
MySql版本问题sql_mode=only_full_group_by解决方案
-
Office/Visio卸载残留或版本冲突导致无法安装问题的解决办法