SELECT list is not in GROUP BY clause and contains non aggregated column

Hit the following issue when doing some testing on a new dev machine (Mac) that was just set up with a fresh install of MySQL

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

What's happening is MySQL is trying to force you in to good practices, but sometime we need to refactor and just get on with developing now. To fix this we need to view the sql_mode variable by running the following the following statement.

select @@sql_mode;  

In the returned valued look for *ONLY_FULL_GROUP_BY *, this is the setting which is throwing the issue. When I ran the above statement this is what was returned.


The fix is simple, just run the following with the above values copied in but remove ONLY_FULL_GROUP_BY


If you want a more permanent resolution then update your my.cnf file

comments powered by Disqus