1
MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 

This bug is fixed using below query. by logging into MySQL console as root user

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

But the problem is that this bug reappears every time i restarts the system. How to fix this?

6
  • @Akina can you tell me an example for partial MySQL queries?
    – arunwebber
    Jul 4 '18 at 6:31
  • @Akina thank you also please make an answer for this question so that i can accept the same
    – arunwebber
    Jul 4 '18 at 7:29
  • Done. Comments are deleted and moved to the answer.
    – Akina
    Jul 4 '18 at 7:32
  • 4
    That's not a bug - it's the correct way to handle GROUP BY statements. Jul 4 '18 at 7:41
  • You upgraded MySQL recently?
    – Rick James
    Jul 4 '18 at 13:07
5

That's not a bug. That's 'move to standard'.

Do not fix this problem by change server's SQL Mode. Fix it by altering the queries which uses partial GROUP BY expressions.

Details can be read in MySQL Handling of GROUP BY.

0
3

You should add it to your config file, for example: /etc/my.cnf:

sql_mode='...'

Do note that this is not a bug, this is the way it is supposed to work. By disabling this check you risk getting indeterministic answers from your queries. A small example:

create table t (x int not null, y int not null, z int not null);
insert into t (x,y,z) values (1,1,2),(1,2,3);

What would be the result of:

select x,y,max(z) from t group by x

? MAX(z) in the group x is 3. What value should y have, 1 or 2? This would be logically valid if y were functionally dependent on x, but in this case, there is neither a constraint that guarantees it nor data that conforms to it.

1
  • This also an answer but the right answer is which I have accepted above. But, I am using this solution time being.
    – arunwebber
    Jul 5 '18 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.