3

My SQL query is not working showing this error:

this is incompatible with sql_mode=only_full_group_by

I am running this query in other mysql pannel there have working fine. In new server is not working....

If i run this then error is not working and also group by is not working

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

New server version

Client API library version : mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $ PHP Version 7.0.15-0ubuntu0.16.04.4

Your Common Sense
  • 154,967
  • 38
  • 205
  • 325
Rajkaran Sahu
  • 51
  • 1
  • 1
  • 4
  • 1
    Correct. The default setting has been changed. Invalid GROUP BY clauses do now by default raise an error. – jarlh Apr 24 '17 at 07:57

4 Answers4

2

Can't be sure without seeing your query, but I assume you're using different sets of columns in your select and in your group by clauses.

select  a, b, sum(c)
from    table
group by a

If that's the case, you can write your query like on of these, depending on what you actually want

select  a, b, sum(c)
from    table
group by a, b

or

select  a, sum(c)
from    table
group by a

Note that this is how group by has to be used in almost any database. MySQL allowing for different sets is the anomaly.

Stefano Zanini
  • 5,738
  • 2
  • 11
  • 32
1

See Disable ONLY_FULL_GROUP_BY for more answers. The answers here didn't work for me, but editing my.cnf, as suggested there worked. I tested mysql-server 5.7.19-0ubuntu0.16.04.1 from Ubuntu 16.04.

But of course the proper solution is to fix your query... reverting the old bad behavior is just a workaround.

Peter
  • 2,772
  • 2
  • 16
  • 18
1

You only have to replace global statement by session:

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Argynos
  • 21
  • 1
0

You can try SET sql_mode = ''; before your query;

My guess is that your mysql version is 5.7 where it's not working, which has changed the setting.

Simos Fasouliotis
  • 1,356
  • 1
  • 15
  • 30