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?

The [MySQL documentation](

[To see links please register here]

) 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](

[To see links please register here]

) discusses ways to find out which my.cnf file is being used by MySQL.

Don't forget to restart MySQL after making changes.

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.

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;

*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:

[To see links please register here]

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

[![Checkout this image][1]][1]


[1]:

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.

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.

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][1]


[1]:

[To see links please register here]