Advertisement
Advertisement


Disable ONLY_FULL_GROUP_BY


Question

I accidentally enabled ONLY_FULL_GROUP_BY mode like this:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

How do I disable it?

2020/07/30
1
643
7/30/2020 4:38:11 PM

Accepted Answer

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

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
2018/02/01
1220
2/1/2018 8:35:08 AM


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.

2016/01/07

    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;
2016/03/07

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.

2017/04/29

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

2016/05/29

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.

2016/11/07