MySQL - How to turn off ONLY_FULL_GROUP_BY

MySQL - How to turn off ONLY_FULL_GROUP_BY?

Some cases we encounter group by 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. we will show you How to turn off ONLY_FULL_GROUP_BY

Run this command:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

The above command will be temporary solution. It returns when the Mysql server restarts.

Another way to turn it off is that to find and modify the config file my.cnf. Usually it’s in /etc/my.cnf or /etc/mysql/my.cnf.

First, check the sql_mode by running this query. To run this enter mysql and press enter to get mysql prompt

SELECT @@sql_mode;

The result should be something similar to this in normal installation scenarios.

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Copy the sql_mode values, remove ONLY_FULL_GROUP_BY, edit my.cnf and put the rest together in a line under [mysqld] section:

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then restart the mysql server. In whm /cpanel this can be done from menu option