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.
This question is related to
mysql
sql
group-by
mysql-error-1055
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
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.
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 usingANY_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 theGROUP 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;
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';
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.
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 -
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)
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
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
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.
This is what helped me to understand the entire issue:
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.
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
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
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 wantAVG()
MIN()
MAX()
complete list ANY_VALUE()
if you are sure that all the results inside the group are the same. docGo to mysql or phpmyadmin and select database then simply execute this query and it will work. Its working fine for me.
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
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
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
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.
If you don't want to make any changes in your current query then follow the below steps -
sudo vim /etc/mysql/my.cnf
A
to enter insert modeCopy 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
Type esc
to exit input mode
:wq
to save and close vim.sudo service mysql restart
to restart MySQL.Source: Stackoverflow.com