[mysql] Disable ONLY_FULL_GROUP_BY

I accidentally enabled ONLY_FULL_GROUP_BY mode like this:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

How do I disable it?

For Mac OS Mojave (10.14) Open terminal

$ sudo mkdir /usr/local/mysql-5.7.24-macos10.14-x86_64/etc
$ cd /usr/local/mysql-5.7.24-macos10.14-x86_64/etc
$ sudo nano my.cnf

Paste following:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Shortkeys to Save & Exit nano: Ctrl+x and y and Enter

Note: You might need to update mysql-5.7.24-macos10.14-x86_64 in these commands, just check the correct folder name you got within /usr/local/

Hope it will help someone!

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'

One important thing to note, if you have ANSI on sql_mode:

Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY.

See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi

On:

• Ubuntu 14.04
• mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

Do:

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

Copy and paste:

[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

To the bottom of the file

$ sudo service mysql restart

Thanks to @cwhisperer. I had the same issue with Doctrine in a Symfony app. I just added the option to my config.yml:

doctrine:
    dbal:
        driver:   pdo_mysql
        options:
            # PDO::MYSQL_ATTR_INIT_COMMAND
            1002: "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"

This worked fine for me.

Adding only one mode to sql_mode without removing existing ones:

SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));

Removing only a specific mode from sql_mode without removing others:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));

In your case, if you want to remove only ONLY_FULL_GROUP_BY mode, then use below command:

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

Reference: http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html

Im working with mysql and registered with root user, the solution that work for me is the following:

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

On my sql (version 5.7.11 running on Mac OS X) this work for me on mysql shell client:

SET
@@GLOBAL.sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

According to MySQL 5.6 Documentation, sql_mode is default is

blank string in MySQL 5.6.5 and back NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES in 5.6.6 +

mysql 5.6 reference

Be careful using

SET sql_mode = '' 

This actually clears all the modes currently enabled. If you don't want to mess with other settings, you'll want to do a

SELECT @@sql_mode 

first, to get a comma-separated list of the modes enabled, then SET it to this list without the ONLY_FULL_GROUP_BY option.

For MySql 8 you can try this one. (not tested on 5.7. Hope it also works there)

First open this file

sudo vi /etc/mysql/my.cnf and paste below code at the end of above file

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Then restart mysql by running this sudo service mysql restart

I'm using doctrine and I have added the driverOptions in my doctrine.local.php :

return array(
'doctrine' => array(
    'connection' => array(
        'orm_default' => array(
            'driverClass' => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
            'params' => array(
                'host' => 'localhost',
                'port' => '3306',
                'user' => 'myusr',
                'password' => 'mypwd',
                'dbname' => 'mydb',
                'driverOptions' => array(
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
                ),
            ),
        ),
    ),
));

In phpmyadmin the user needs SUPER activated in the privileges.

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.

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

The MySQL documentation also specifies the following methods:

• Set sql-mode="<modes>" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows).
• To set the SQL mode at server startup via the command line, use the --sql-mode="<modes>" option.

*Where <modes> is a list of different modes separated by commas.

To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.

I added the sql-mode="" option to /etc/my.cnf and it worked.

This SO solution discusses ways to find out which my.cnf file is being used by MySQL.

Don't forget to restart MySQL after making changes.

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

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!

    mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    mysql> exit;

The best is to try to use ONLY_FULL_GROUP_BY on new projects while remaining backward compatible on existing sites and databases. For this I opted for a modification of the SQL Mode when loading mysqli, respectively PDO in my classes.

For mysqli :

mysqli_query($this->link,"SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");

For PDO class :

PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"

Example with PDO init :

try {
    $dns     = 'mysql:host=localhost;dbname=' . $prod_db;
    $user    = _DBUSER_;
    $pass    = _DBPASS_;
    $options = array(
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
    );

    $db = new PDO($dns, $user, $pass, $options);
} 

catch(Exception $e) {
    mail("[email protected]","Database Error ",$dns.",".$user);
    echo "Unable ot connect to mySQL : ", $e->getMessage();
    die();
} 

Thus, the new classes created on the same server will work with new standard of databases.

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

What about "optimizer hints" from MySQL 8.x ?

for example:

SELECT /*+ SET_VAR(sql_mode='STRICT_TRANS_TABLES') */
... rest of query

more information: https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-set-var

Here is my solution changing the Mysql configuration through the phpmyadmin dashboard:

In order to fix "this is incompatible with sql_mode=only_full_group_by": Open phpmyadmin and goto Home Page and select 'Variables' submenu. Scroll down to find sql mode. Edit sql mode and remove 'ONLY_FULL_GROUP_BY' Save it.

As of MySQL 5.7.x, the default sql mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default).

ONLY_FULL_GROUP_BY: Non-deterministic grouping queries will be rejected

For more details check the documentation of sql_mode

You can follow either of the below methods to modify the sql_mode

Method 1:

Check default value of sql_mode:

SELECT @@sql_mode

Remove ONLY_FULL_GROUP_BY from console by executing below query:

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

Method 2:

Access phpmyadmin for editing your sql_mode

• Login on phpmyadmin and open localhost
• Top on Variables present on the top in menu items and search out for sql mode
• Click on edit button to remove ONLY_FULL_GROUP_BY and save

Update:

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

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.

with MySQL version 5.7.28 check by using
SELECT @@sql_mode; and update with

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'

If you are using WAMP. Left click on the WAMP icon then goto MySQL -> MySQL settings -> sql-mode and then select sql-mode->user mode

This worked for me:

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

in case anyone using Drupal 8 face this issue with mysql 8, I fixed that by overriding the default configuration by adding this piece of code.

Source: https://www.drupal.org/project/drupal/issues/2856270

In case someone else facing same issue as me. On Ubuntu 16.04, the only persistent solution it worked for me:

Edit /lib/systemd/system/mysql.service and set it to:

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
# Normally, we'd simply use:
# ExecStart=/usr/sbin/mysqld
ExecStart=/usr/sbin/mysqld --sql-mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755`

Add or Remove modes to sql_mode

MySQL 5.7.9 or later

To add or remove a mode from sql_mode, you can use list_add and list_drop functions.

To remove a mode from the current SESSION.sql_mode, you can use one of the following:

SET SESSION sql_mode = sys.list_drop(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY');
SET sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');

To remove a mode from the GLOBAL.sql_mode that persists for the current runtime operation, until the service is restarted.

SET GLOBAL sql_mode = sys.list_drop(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY');

MySQL 5.7.8 or prior

Since the sql_mode value is a CSV string of modes, you would need to ensure that the string does not contain residual commas, which can be accomplished by using TRIM(BOTH ',' FROM ...).

To remove a mode from the sql_mode variable, you would want to use REPLACE() along with TRIM() to ensure any residual commas are removed.

SET SESSION sql_mode = TRIM(BOTH ',' FROM REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET GLOBAL sql_mode = TRIM(BOTH ',' FROM REPLACE(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY', ''));

To add a mode to the sql_mode variable, you would want to use CONCAT_WS(',', ...), to ensure a comma is appended with the current modes and TRIM() to ensure any residual commas are removed.

SET SESSION sql_mode = TRIM(BOTH ',' FROM CONCAT_WS(',', 'ONLY_FULL_GROUP_BY', @@SESSION.sql_mode));
SET GLOBAL sql_mode = TRIM(BOTH ',' FROM CONCAT_WS(',', 'ONLY_FULL_GROUP_BY', @@GLOBAL.sql_mode));

NOTE: Changing the GLOBAL variable does not propagate to the SESSION variable, until a new connection is established.

The GLOBAL variable will persist until the running service is restarted.

The SESSION variable will persist for the current connection, until the connection is closed and a new connection is established.

Revert to GLOBAL.sql_mode

Since SET sql_mode = 'ONLY_FULL_GROUP_BY'; was executed without the GLOBAL modifier, the change only affected the current SESSION state value, which also pertains to @@sql_mode. To remove it and revert to the global default on server restart value, you would want to use the value from @@GLOBAL.sql_mode. [sic]

The current SESSION value is only valid for the current connection. Reconnecting to the server will revert the value back to the GLOBAL value.

To revert the current session state value to the current global value, you can use one of the following:

SET SESSION sql_mode = @@GLOBAL.sql_mode;
SET @@sql_mode = @@GLOBAL.sql_mode;
SET sql_mode = @@GLOBAL.sql_mode;

Change SESSION.sql_mode value to ONLY_FULL_GROUP_BY

SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT @@sql_mode, @@GLOBAL.sql_mode;
+--------------------+----------------------------------------------+
| @@sql_mode         | @@GLOBAL.sql_mode                            |
+--------------------+----------------------------------------------+
| ONLY_FULL_GROUP_BY | NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION |
+--------------------+----------------------------------------------+

Revert the SESSION.sql_mode value to the GLOBAL.sql_mode value

SET sql_mode = @@GLOBAL.sql_mode;
SELECT @@sql_mode, @@GLOBAL.sql_mode;
+----------------------------------------------+----------------------------------------------+
| @@sql_mode                                   | @@GLOBAL.sql_mode                            |
+----------------------------------------------+----------------------------------------------+
| NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------+----------------------------------------------+

Server Restart Persistent sql_mode using the option file

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). [sic]

Please see your version of MySQL to determine the supported and default modes.

MySQL >= 5.7.5, <= 5.7.6 default

[mysqld]
    
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

Please see the Option File Syntax for more information.

The syntax for specifying options in an option file is similar to command-line syntax. However, in an option file, you omit the leading two dashes from the option name and you specify only one option per line. For example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines in an option file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.

The value optionally can be enclosed within single quotation marks or double quotation marks, which is useful if the value contains a # comment character.

Default sql_mode values

Since the MySQL documentation per-version values have been removed, I have added them here for your reference.

• MySQL 8.0 [sic]
• MySQL 5.7 [sic]
• MySQL 5.6 [sic]

MySQL >= 8.0.11 8.0.5 - 8.0.10 Skipped

ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION

MySQL >= 5.7.8, <= 8.0.4

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

MySQL 5.7.7

ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION

MySQL >= 5.7.5, <= 5.7.6

ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION

MySQL >= 5.6.6, <= 5.7.4

NO_ENGINE_SUBSTITUTION

MySQL <= 5.6.5

''

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"