老师，麻烦问您一个数据库的问题

Question

我在本地的数据库测试的统计昨天的销量详情没一点问题，可是当我测试远程服务器数据库时就报如下错误，貌似提示我SQL语法错误，可是我把SQL语句粘贴到远程数据库执行没一点问题，能添加成功，就是用junit测试时报错，本地数据库junit也正常插入数据，就是远程的数据库不行，麻烦老师看一下吧
org.springframework.jdbc.BadSqlGrammarException:

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘o2o.tb_user_product_map.shop_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The error may involve com.imooc.o2o.dao.ProductSellDailyDao.insertProductSellDaily-Inline

The error occurred while setting parameters

SQL: INSERT INTO tb_product_sell_daily(product_id,shop_id,create_time,total) ( SELECT product_id,shop_id,date_format(create_time,’%Y-%m-%d’),count(product_id) AS total FROM tb_user_product_map WHERE date_format(create_time,’%Y-%m-%d’) = date_sub(curdate(),interval 1 day) GROUP BY product_id)

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘o2o.tb_user_product_map.shop_id’ 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 #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘o2o.tb_user_product_map.shop_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
AT org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
AT org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
AT org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
AT org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
AT org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)ATcom.sun.proxy.$Proxy72.insert(UNKNOWN Source)
AT org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
AT org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57)
AT org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
AT com.sun.proxy.$Proxy73.insertProductSellDaily(UNKNOWN Source)
AT com.imooc.o2o.dao.ProductSellDailyDaoTest.testAInsertProductSellDaily(ProductSellDailyDaoTest.java:34)
AT sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
AT sun.reflect.NativeMethodAccessorImpl.invoke(UNKNOWN Source)
AT sun.reflect.DelegatingMethodAccessorImpl.invoke(UNKNOWN Source)
AT java.lang.reflect.Method.invoke(UNKNOWN Source)
AT org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
AT org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
AT org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
AT org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
AT org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
AT org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
AT org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
AT org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
AT org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
AT org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
AT org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
AT org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
AT org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
AT org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
AT org.junit.runners.ParentRunnerKaTeX parse error: Expected 'EOF', got '#' at position 1090: …on: Expression #̲2 of SELECT lis…Proxy109.execute(UNKNOWN Source)
AT org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
AT org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
AT org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
AT org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
AT org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
AT org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
AT org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
AT sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
AT sun.reflect.NativeMethodAccessorImpl.invoke(UNKNOWN Source)
AT sun.reflect.DelegatingMethodAccessorImpl.invoke(UNKNOWN Source)
AT java.lang.reflect.Method.invoke(UNKNOWN Source)
AT org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
… 35 more

Answer 1

翔仔 2019-02-13 13:05:30

同学好，请先保证你本地和远程的数据库版本一致，都是5.x的 8.x的话语法可能会有问题

• 提问者 qmla #1

  谢谢老师，我本地的是5.5，远程的是5.7，如果是因为sql语法的问题，那么我从报错信息里复制的sql语句到远程数据库去执行可以正常添加数据，可是为什么juint测试时就不行呢？同样的sql语句呢

• 翔仔 回复 提问者 qmla #2

  同学好，由于我看不到同学的环境，这个错误o2o.tb_user_product_map.shop_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  如果真如同学说的，那么这个错误可能是在junit调用 也就是经过底层spring jdbc转换后， with sql_mode=only_full_group_by
  而如果直接粘贴执行，是按照另外一种模式来执行，所以才不报错的，这个错误比较奇怪呢。同学试试看不用junit 直接在程序中执行试试

• 提问者 qmla 回复 翔仔 #3

  嗯嗯，好的，谢谢老师，可能是因为数据库版本的问题，因为本地5.5的用junit就没事，远程linux那个5.7的junit就报错了，而且直接程序运行也不行的