509

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySQL it works fine. Since upgrading to 5.7 I get this error:

Expression #1 of SELECT list is not in GROUP BY clause and contains non-aggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Note the manual page for Mysql 5.7 on the topic of Server SQL Modes.

This is the query that is giving me trouble:

SELECT mod_users_groups.group_id AS 'value', 
       group_name AS 'text' 
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id 
WHERE  mod_users_groups.active = 1 
  AND mod_users_groups.department_id = 1 
  AND mod_users_groups.manage_work_orders = 1 
  AND group_name != 'root' 
  AND group_name != 'superuser' 
GROUP BY group_name 
HAVING COUNT(`user_id`) > 0 
ORDER BY group_name

I don't understand only_full_group_by enough to figure out what I need to do to fix the query. Can I just turn off the only_full_group_by option, or is there something else I need to do?

2
  • here I found solution stackoverflow.com/a/7588442/612987
    – Wasim A.
    Jan 13, 2018 at 16:42
  • I had luck prefixing my select query with "create temporary table temp " then getting the result from a second query "select * from temp" and then a final query to clean up "drop table if exists temp". If this merits any rep, maybe I'll get enough to make this an answer.
    – Skull Kid
    Jan 16, 2021 at 7:51

18 Answers 18

408

I would just add group_id to the GROUP BY.

When SELECTing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT(), SUM(), MAX() etc... I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the only_full_group_by setting by default for good reasons, so it's best to get used to it and make your queries comply with it.

So why my simple answer above? I've made a couple of assumptions:

1) the group_id is unique. Seems reasonable, it is an 'ID' after all.

2) the group_name is also unique. This may not be such a reasonable assumption. If this is not the case and you have some duplicate group_names and you then follow my advice to add group_id to the GROUP BY, you may find that you now get more results than before because the groups with the same name will now have separate rows in the results. To me, this would be better than having these duplicate groups hidden because the database has quietly selected a value arbitrarily!

It's also good practice to qualify all the columns with their table name or alias when there's more than one table involved...

SELECT 
  g.group_id AS 'value', 
  g.group_name AS 'text' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name
1
  • 2
    In MySQL 5.7 they set a property that requires all non-aggregate fields in a query be a GROUP BY. So a query like SELECT a, SUM(b) FROM table; means that field "a" Must be in a GROUP BY. So if you don't have a GROUP BY then you must add it to the query. It is all about if you have at least one aggregate field in the SELECT portion of your query.
    – bytor99999
    Feb 22, 2018 at 16:30
397

You can try to disable the only_full_group_by setting by executing the following:

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL 8 does not accept NO_AUTO_CREATE_USER so that needs to be removed.

6
  • 102
    This is a work around, you better fix your query rather than silencing the warning
    – azerafati
    Jul 12, 2016 at 8:01
  • 2
    Updating the /etc/mysql/my.cnf (as seen below) is a more sustainable solution as the default settings tends to come back after restart. And for those that say you should fix the query, then that is not quite easy sometimes when you need a quick debugging query and do things like SELECT COUNT(*), t.* FROM my_table t GROUP BY col and you have 20-50 columns, do you want to spend that much time adding each column to the group by?
    – Shadoweb
    Jun 8, 2018 at 10:07
  • 6
    This "solution" is quite dangerous. You may activate modes you had disabled previously by blindly changing them instead of only removing the one settings flag in question. This may lead to unexpected behaviour and in worst case lead to wrong results or stop your app(s) from working entirely. Thus I consider this answer as wrong.
    – Chris S.
    Sep 25, 2018 at 7:40
  • 3
    I made this persistent through refresh by figuring out my current sql_mode via SELECT @@sql_mode; and then adding the result from there to my.cnf: sql_mode=[list of modes from query, minus ONLY_FULL_GROUP_BY]
    – wbharding
    Mar 1, 2019 at 18:35
  • 3
    "Fixing your query" is difficult when all queries were done years ago by another person who is not in the company anymore ;)
    – golimar
    Oct 26, 2020 at 17:06
366

you can turn off the warning message as explained in the other answers or you can understand what's happening and fix it.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY which means when you are grouping rows and then selecting something out of that groups, you need to explicitly say which row should that selection be made from.

For example, given these records:

id colour value
1 blue 1
2 yellow 4
3 red 8
4 yellow 9
5 blue 2
6 red 5
7 blue 3

We can ask MySQL to group by the colour field, giving us these groups:

id colour value
2 yellow 4
4 yellow 9
id colour value
3 red 8
6 red 5
id colour value
1 blue 1
5 blue 2
7 blue 3

But if we ask for the value field of each group, we'll get an error because each group has many rows with value fields.

Or, in image form: Pictorial representation of the tables above

MySQL needs to know which row in the group you're looking for, which gives you three options

  • add the column you want to the group statement group by rect.color, rect.value which can be what you want in some cases but otherwise would return duplicate results with the same color which you may not want
  • use MySQL's aggregate functions to indicate which value you are looking for inside the groups like AVG(), MIN(), MAX(), or FIRST()
  • use ANY_VALUE() if you are sure that all the results inside the group are the same.
1
  • For example you're selecting from sales, salesitems and customers. sales and customers are 1x1 and sales to salesitems is 1xN; Now because you're selecting multiple items everything in sales and customers will be repeated values per salesitems, if you group by sales.id to (for example) sum the sales Items values, you'll have to ANY_VALUE all sales and customers fields.
    – Felype
    Jul 31, 2020 at 17:44
184

If you don't want to make any changes in your current query then follow the below steps -

  1. vagrant ssh into your box
  2. Type: sudo vim /etc/mysql/my.cnf
  3. Scroll to the bottom of file and type A to enter insert mode
  4. Copy and paste

    [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
    
  5. Type esc to exit input mode

  6. Type :wq to save and close vim.
  7. Type sudo service mysql restart to restart MySQL.
4
  • 1
    Just a heads up to all Vagrant users trying to use this with AdonisJS you need this to run the paginate function. Aug 26, 2016 at 4:37
  • 1
    This obviously is not only for Vagrant users, but any Unix system. Note that for me the location of the my.cnf file was /etc. Als note the new syntax since MySQL 5.7.8: sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Oct 29, 2018 at 13:12
  • [ERROR] [MY-000077] [Server] /usr/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'
    – be3
    May 29, 2021 at 10:23
  • 2
    Remove NO_AUTO_CREATE_USER if you are using MYSQL 8. Feb 18, 2022 at 19:20
108

Use ANY_VALUE() to refer to the nonaggregated column.

SELECT name,           address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works

From MySQL 5.7 docs:

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

...

This query might be invalid with ONLY_FULL_GROUP_BY enabled because the nonaggregated address column in the select list is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

...

If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name. To tell MySQL to accept the query, you can use the ANY_VALUE() function:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
1
  • 3
    As someone who had been running into this issue for a while now - I especially like this solution because it doesn't require you to change any files or settings in your MySQL setup. Interestingly enough, I don't see ANY_VALUE mentioned anywhere else in similar discussions - works perfectly for me with my GROUP_BY contains nonaggregated column error.
    – adstwlearn
    Nov 20, 2019 at 20:44
61

I will try to explain you what this error is about.
Starting from MySQL 5.7.5, option ONLY_FULL_GROUP_BY is enabled by default.
Thus, according to standart SQL92 and earlier:

does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns

(read more in docs)

So, for example:

SELECT * FROM `users` GROUP BY `name`;

You will get error message after executing query above.

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testsite.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Why?
Because MySQL dont exactly understand, what certain values from grouped records to retrieve, and this is the point.

I.E. lets say you have this records in your users table:
Yo

And you will execute invalid query showen above.
And you will get error shown above, because, there is 3 records with name John, and it is nice, but, all of them have different email field values.
So, MySQL simply don't understand which of them to return in resulting grouped record.

You can fix this issue, by simply changing your query like this:

SELECT `name` FROM `users` GROUP BY `name`

Also, you may want to add more fields to SELECT section, but you cant do that, if they are not aggregated, but there is crutch you could use (but highly not reccomended):

SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`

enter image description here

Now, you may ask, why using ANY_VALUE is highly not recommended?
Because MySQL don't exactly know what value of grouped records to retrieve, and by using this function, you asking it to fetch any of them (in this case, email of first record with name = John was fetched).
Exactly I cant come up with any ideas on why you would want this behaviour to exist.

Please, if you dont understand me, read more about how grouping in MySQL works, it is very simple.

And by the end, here is one more simple, yet valid query.
If you want to query total users count according to available ages, you may want to write down this query

SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;

Which is fully valid, according to MySQL rules.
And so on.

It is important to understand what exactly the problem is and only then write down the solution.

0
43

I am using Laravel 5.3, mysql 5.7.12, on laravel homestead (0.5.0, I believe)

Even after explicitly setting editing /etc/mysql/my.cnf to reflect:

[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 was still receiving the error.

I had to change config/database.php from true to false:

    'mysql' => [
        'strict' => false, //behave like 5.6
        //'strict' => true //behave like 5.7
    ], 

Further reading:

https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2

0
30

Go to mysql or phpmyadmin and select database, then simply execute this query and it will work.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
3
  • 1
    would be nice if you provide some explanations what it does
    – devnull Ψ
    Dec 12, 2019 at 12:13
  • @devnullΨ Its actually remove 'ONLY_FULL_GROUP_BY' from variables , you can find it in phpmyadmin > variable menu > sql_mode option you can directly remove here
    – Anil Gupta
    Dec 13, 2019 at 4:46
  • 1
    But the problem with the solution is every time I restart mysql I need to execute the query again. It means the solution is not permanent or persistent. Jan 25, 2020 at 10:07
20

If you are using wamp 3.0.6 or any upper version other than the stable 2.5 you might face this issue, firstly the issue is with sql . you have to name the fields accordingly. but there is another way by which you can solve it. click on green icon of wamp. mysql->mysql settings-> sql_mode->none. or from console you can change the default values.

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
1
  • fantastic, building on what he said, insert command show variables like '%sql_mode%'; in mysql command line, sql_mode setting will expose
    – Jade Han
    May 31, 2018 at 4:30
18

Addition of lines (mention below) in file : /etc/mysql/my.cnf

[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

Work fine for me. Server version: 5.7.18-0ubuntu0.16.04.1 - (Ubuntu)

2
10

For mac:

1.Copy the default my-default.cnf to /etc/my.cnf

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

2.Change sql_mode in my.cnf using your favorite editor and set it to this

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.Restart MySQL server.

mysql.server restart
2
  • Thanks, worked for me as well. But I use mysql default of Mac, so I don't have brew path. Just sudo cp my-default.cnf /etc/my.cnf Dec 12, 2016 at 14:17
  • 2
    @Mike Nguyen sudo cp /usr/local/mysql-5.7.17-macos10.12-x86_64/support-files/my-default.cnf /etc/my.cnf sudo vi /etc/my.cnf set sql_model sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Restart MySQL server.
    – Yong Gao
    Dec 30, 2016 at 4:11
7

This is what helped me to understand the entire issue:

  1. https://stackoverflow.com/a/20074634/1066234
  2. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

And in the following another example of a problematic query.

Problematic:

SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
                        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
                        AND cookieid = #

Solved by adding this to the end:

  GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress

Note: See the error message in PHP, it tells you where the problem lies.

Example:

MySQL query error 1140: In aggregated query without GROUP BY, expression #4 of SELECT list contains nonaggregated column 'db.gameplay.timestamp'; this is incompatible with sql_mode=only_full_group_by - Query: SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM gameplay WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND userid = 1

In this case, expression #4 was missing in the GROUP BY.

0
7

For localhost / wampserver 3 we can set sql-mode = user_mode to remove this error:

click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode

then restart wamp or apache

0
4

You can add a unique index to group_id; if you are sure that group_id is unique.

It can solve your case without modifying the query.

A late answer, but it has not been mentioned yet in the answers. Maybe it should complete the already comprehensive answers available. At least it did solve my case when I had to split a table with too many fields.

1
  • that will be good only if your data is actually unique by that group id
    – yoni
    Jul 22, 2020 at 14:45
3

If you have this error with Symfony using doctrine query builder, and if this error is caused by an orderBy :

Pay attention to select the column you want to groupBy, and use addGroupBy instead of groupBy :

$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');

Works on Symfony3 -

3

I had to edit the below file on my Ubuntu 18.04:

/etc/mysql/mysql.conf.d/mysqld.cnf

with

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

and

sudo service mysql restart

0
1

Apologies for not using your exact SQL

I used this query to overcome the Mysql warning.

SELECT count(*) AS cnt, `regions_id`
FROM regionables 
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1

note the key for me being

count(*) AS cnt
1

The consensus answer above is good but if you've got problems running queries within stored procedures after fixing your my.cnf file, then try loading your SPs again.

I suspect MySQL must have compiled the SPs with the default only_full_group_by set originally. Therefore, even when I changed my.cnf and restarted mysqld it had no effect on the SPs, and they kept failing with "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".

Reloading the SPs must have caused them to be recompiled now with only_full_group_by disabled. After that, they seem to work as expected.

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