MySQL - How to turn off ONLY_FULL_GROUP_BY?
If you run into this error with MySQL:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.table.col' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
It’s likely because you have the
ONLY_FULL_GROUP_BY function enabled. To fix this, you have to disable it.
Run this command:
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Another way to turn it off is that to find and modify the config file
my.cnf. Usually it’s in
First, check the
sql_mode by running this query:
The result should be something similar to this:
sql_mode values, remove
my.cnf and put the rest together in a line under [mysqld] section:
Then restart the mysql server.
Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.