How to disable only_full_group_by in mysql 5.7?

If you have group by function in your sql request you may have encountered this painful error.

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

1 – Disable ONLY_FULL_GROUP_BY in mysql (temporary solution)

If you are in a hurry, this will do the job.

  • Go to your phpmyadmin interface generally (http://localhost/phpmyadmin).
  • Then go to variables menu in header section
  • Search sql mod in filters field
  • Edit and remove ONLY_FULL_GROUP_BY
  • Save

Or if you are a purist who only use cli, connect to mysql and run this command

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

Now the error must be gone, but like i notice below, this is a temporary solution. Why ? Because when you’ll restart your mysql server, the ONLY_FULL_GROUP_BY will return and you’ll be annoyed again.

2 – Disable ONLY_FULL_GROUP_BY in mysql conf file (better solution)

A better solution is to disable it in conf file.
For this go to mysql directory

cd /etc/mysql

Edit my.cnf file with root access

sudo nano my.cnf
OR
sudo gedit my.cnf

Below [MYSQLD] section copy paste this.

[MYSQLD]

#disable only_full_group_by
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart your mysql server

systemctl restart mysql

3 – Last solution

Fix your damn requests 😀

Leave a Reply