Error related to only full group by when executing a query in MySql

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    gt  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

User · Accepted Answer

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   gt  0  ORDER BY g group name

User · Answer

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

User · Answer

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

User · Answer

Go 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

User · Answer

This is what helped me to understand the entire issue     https   stackoverflow com a 20074634 1066234 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  gt   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

User · Answer

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- gt createQueryBuilder  smth  - gt addGroupBy  smth mycolumn      Works on Symfony3 -

User · Answer

If you don t want to make any changes in your current query then follow the below steps -   vagrant ssh into your box Type  sudo vim  etc mysql my cnf Scroll to the bottom of file and type A to enter insert mode 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  Type esc to exit input mode Type  wq to save and close vim  Type sudo service mysql restart to restart MySQL

User · Answer

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

User · Answer

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

User · Answer

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    gt             strict    gt  false    behave like 5 6            strict    gt  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

User · Answer

You can try to disable the only full group by setting by executing the following   mysql gt  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 gt  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

User · Answer

For localhost   wampserver 3 we can set sql-mode   user mode to remove this error   click on wamp icon - gt  MySql - gt  MySql Setting - gt  sql-mode - gt  user mode   then restart wamp or apache

User · Answer

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  quot 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 quot   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

User · Answer

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  gt  1   note the key for me being  count    AS cnt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  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 gt  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] Error related to only_full_group_by when executing a query in MySql

Examples related to mysql

Examples related to sql

Examples related to group-by

Examples related to mysql-error-1055