this is incompatible with sql_mode=only_full_group_by

Question

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

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

score 2 · Answer 1 · answered Apr 24 '17 at 08:02

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.

score 1 · Answer 2 · answered Apr 24 '17 at 08:00

1

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.

answered Apr 24 '17 at 08:00

Simos Fasouliotis

1,383
2
16
35

Yes! Would you please guide me how to resolve this issues. – Rajkaran Sahu Jun 10 '17 at 12:27
as I said - just run the query i enclosed before once. – Simos Fasouliotis Jun 10 '17 at 13:12

score 1 · Answer 3 · answered Jul 26 '17 at 18:35

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.

score 1 · Answer 4 · answered May 22 '20 at 09:54

1

You only have to replace global statement by session:

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

answered May 22 '20 at 09:54

Argynos

21
2

score 0 · Answer 5 · edited Aug 05 '22 at 11:20

Thank you for this very clear explanation.

There seems to be an exception to this rule that all columns in a Select clause with a Group By must be in the Group By clause. This is where the Group By column is the key field.

You might wonder why this would ever be interesting, since by definition a key from one table will only every produce one unique record.

This could be useful in case of a join, where you want to count the number of joined records in the joined table but also show the detail from the first table, eg:

Table 1 - names: key Id, field fullname
Table 2 - purchases: key Id, field name, field purchase, linked to Table 1 by purchases.name=names.id

select * from names;

Id	Fullname
1	John Foobar
2	Jane Woobar

select * from purchases;

Id	Name	Purchase
1	1	car
2	1	book
3	1	holiday
4	2	hat

Select names.Id, fullname, count(purchase) from (purchases left join names on purchases.name = names.Id) group by (names.Id);

Id	fullname	count(purchase)
1	John Foobar	3
2	Jane Woobar	1

this is incompatible with sql_mode=only_full_group_by

5 Answers5