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.


Update:

enter image description here

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


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.


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

Tags:

Mysql

Recent Posts