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

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.

Answer 2

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

Answer 3

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.

Answer 4

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';

Answer 5

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.

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

• You can also add the column you want to the group statement group by rect.color, rect.value which can be what you want in some cases otherwise would return duplicate results with the same color which you may not want
• you could also use aggregate functions of mysql to indicate which row you are looking for inside the groups like AVG() MIN() MAX() complete list
• AND finally you can use ANY_VALUE() if you are sure that all the results inside the group are the same. doc

Answer 6

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.