Disable ONLY_FULL_GROUP_BY

Give this a try:

SET sql_mode = ''



---
Community Note: As pointed out in the answers below, this actually clears **all** the SQL modes currently enabled. That may not necessarily be what you want.

On MySQL 5.7 and Ubuntu 16.04, edit the file mysql.cnf.

$ sudo nano /etc/mysql/conf.d/mysql.cnf

Include the sql_mode like the following and save the file.

[mysql]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Observe that, in my case, I removed the mode STRICT_TRANS_TABLES and the ONLY_FULL_GROUP_BY.

Doing this, it will save the mode configuration permanently. Differently if you just update the @@sql_mode through MySQL, because it will reset on machine/service restart.

After that, to the modified configuration take in action, restart the mysql service:

$ sudo service mysql restart

Try to access the mysql:

$ mysql -u user_name -p

If you are able to login and access MySQL console, it is ok. Great!

**BUT**, if like me, you face the error **"unknown variable sql_mode"**, which indicates that **sql_mode** is an option for **mysqld**, you will have to go back, edit the file mysql.cnf again and change the `[mysql]` to `[mysqld]`. Restart the MySQL service and do a last test trying to login on MySQL console. Here it is!

You can disable it using the config file `my.cnf` :


$ mysql --verbose --help | grep my.cnf

So in macOS 10.12, it's at `usr/local/etc/my.cnf`. You can edit `sql_mode` here:

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

This is a permanent solution for MySql 5.7+ on Ubuntu 14+:

$ sudo bash -c "echo -e \"\nsql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\" >> /etc/mysql/mysql.conf.d/mysqld.cnf"
$ sudo service mysql restart
# Check if login attempt throws any errors
$ mysql -u[user] -p # replace [user] with your own user name

If you are able to login without errors - you should be all set now.

**Solution 1:**
Remove **ONLY_FULL_GROUP_BY** from mysql console


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

you can read more [here][1]


**Solution 2:** Remove **ONLY_FULL_GROUP_BY** from phpmyadmin

- Open phpmyadmin & select localhost
- Click on menu Variables & scroll down for sql mode
- Click on edit button to change the values & remove **ONLY_FULL_GROUP_BY** & click on save.
[![enter image description here][2]][2]


[1]:

[To see links please register here]

[2]:

**Update:**
-----------

[![enter image description here][1]][1]

To keep your current mysql settings and disable `ONLY_FULL_GROUP_BY` I suggest to visit your phpmyadmin or whatever client you are using and type:

`SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','') copy_me`

next copy result to your `my.ini` file.

**mint**: `sudo nano /etc/mysql/my.cnf`

**ubuntu 16 and up**: `sudo nano /etc/mysql/my.cnf`

**ubuntu 14-16**: `/etc/mysql/mysql.conf.d/mysqld.cnf`

Caution! `copy_me` result can contain a long text which might be trimmed by default. Make sure you copy whole text!
------------------------------------------------------------------------

___
**old answer:**

If you want to disable **permanently** error "*Expression #N 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*" do those steps:

1. `sudo nano /etc/mysql/my.cnf`
2. Add this to the end of the file

[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"

3. `sudo service mysql restart` to restart MySQL

This will disable `ONLY_FULL_GROUP_BY` for **ALL** users


[1]:

I have noticed that @Eyo Okon Eyo solution works as long as MySQL server is not restarted, then defaults settings are restored. Here is a permanent solution that worked for me:

To remove particular SQL mode (in this case **ONLY_FULL_GROUP_BY**), find the current SQL mode:

SELECT @@GLOBAL.sql_mode;

copy the result and remove from it what you don't need (**ONLY_FULL_GROUP_BY**)

e.g.:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
to

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

create and open this file:

/etc/mysql/conf.d/disable_strict_mode.cnf
and write and past into it your new SQL mode:

[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
restart MySQL:

sudo service mysql restart

Or you can use `ANY_VALUE()` to suppress ONLY_FULL_GROUP_BY value rejection, you can [read more about it here][1]


[1]:

[To see links please register here]

If you are using MySQL 8.0.11 so, you need to remove the ’NO_AUTO_CREATE_USER‘ from sql-mode.

Add following line in file `/etc/mysql/my.cnf` and [mysqld] header


[mysqld]

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

To whom is running a VPS/Server with **cPanel/WHM**, you can do the following to permanently disable ONLY_FULL_GROUP_BY

**You need root access** (either on a VPS or a dedicated server)

1. Enter WHM as root and run phpMyAdmin

2. Click on Variables, look for `sql_mode`, click on 'Edit' and **copy the entire line** inside that textbox

e.g. copy this:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. Connect to you server via SFTP - SSH (root) and download the file `/etc/my.cnf`

4. Open with a text editor `my.cnf` file on your local PC and paste into it (under `[mysqld]` section) the entire line you copied at step (2) but remove `ONLY_FULL_GROUP_BY,`

e.g. paste this:

# disabling 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

5. Save the `my.cnf` file and upload it back into `/etc/`

6. Enter WHM and go to "WHM > Restart Services > SQL Server (MySQL)" and restart the service

This is what I performed to fix on Mysql workbench:

Before I got the current value with the below command

SELECT @@sql_mode

later I removed the ONLY_FULL_GROUP_BY key from the list and I pasted the below command

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'