Problem sql_mode=only_full_group_by

Connect with other users about what to run on your webhosting (and how to run it) here.
Post Reply
ros7790
New to forums
New to forums
Posts: 1
https://www.youtube.com/channel/UC40BgXanDqOYoVCYFDSTfHA
Joined: Thu Feb 21, 2019 4:18 pm

Problem sql_mode=only_full_group_by

Post by ros7790 »

Hi!
I have a problem on my website
after the update
from mysqll
my website presents the following error

Expression #9 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'server002_latino.a.id_attach' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
some solution to this problem
Thank you
User avatar
Edge100x
Founder
Founder
Posts: 12945
Joined: Thu Apr 18, 2002 11:04 pm
Location: Seattle
Contact:

Re: Problem sql_mode=only_full_group_by

Post by Edge100x »

This means that your software is a bit outdated and doesn't properly handle the default MySQL 5.7 mode of ONLY_FULL_GROUP_BY.

The best option is generally to update the software to a newer version with a fixed query.

If you wrote the software in-house, or you are unable to update it, the two easiest ways to fix this are:
  • Add an SQL command before the query that overrides the session variables and removes ONLY_FULL_GROUP_BY, such as this one:

    Code: Select all

    SET SESSION sql_mode = '';
    You only have to run that command once, before running the queries that give you errors. Usually it is best to add it to a file that is included with every script (in the ideal case, your application may have a library that it uses for MySQL, and you could add it to an initialization routine there).
  • Changing the query itself to add ANY_VALUE() around the columns that MySQL has told you that it has a problem with. For instance, this query:

    Code: Select all

    SELECT name, address, MAX(age) FROM t GROUP BY name;
    Might have to be changed to this:

    Code: Select all

    SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
    In your specific case, id_attach appears to be the column that you would need to add ANY_VALUE() around. But, there may also be others. Basically, you have to put it around any column that MySQL would have to pick its final value from (because it can't deterministically decide what to select among the members of the group) before returning the result.
Post Reply