只禁用完整组 by

I accidentally enabled ONLY_FULL_GROUP_BY mode like this:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

How do I disable it?

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.

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.

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.

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.

    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;

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

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

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

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

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!

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"

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.

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.

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