Error related to only_full_group_by when executing

Question

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 nonaggregated 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 did some googling on the issue, but 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?

Let me know if you need more information.

Answer 1

Use ANY_VALUE() to refer to the nonaggregated column.

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;

Answer 2

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:

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`

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.

Answer 3

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)

Answer 4

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

Answer 5

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

Answer 6

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