1

The sql query:

select , sum(field1) as sumfield1 from knowntable where match(producer) against('+xyz' in boolean mode) group by producer having sumfield1>0 order by sumfield1 desc limit 10;

throws the following exception when running with local tomcat:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The above query is working perfectly in the production system/tomcat. Both servers (local and prod) are accessing the same database.

I have already tried the following solutions:

1) SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

2) Add the following lines in the /etc/mysql/conf.d/disable_strict_mode.cnf file:
[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I have checked all the answers on this website with related exception but no solution is working for me

Please help

user6649792
  • 21
  • 1
  • 3
  • What is `select ,`? Can you post your exact query? But the error is self-explanatory. Any field in your `select` list not included in an aggregate function (i.e. `sum`) need to be included in the `group by` clause... – sgeddes Jun 22 '18 at 15:32
  • A simple way to solve non aggregated columns: https://stackoverflow.com/a/40578865/439171 – Italo Borssatto Mar 07 '20 at 18:32

0 Answers0