To remove a mode from the current SESSION.sql_mode, you can use one of the following: SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY') To add or remove a mode from sql_mode, you can use list_add and list_drop functions. Or you can use ANY_VALUE() to suppress ONLY_FULL_GROUP_BY value rejection, you can read more about it here Restart MySQL: sudo service mysql restart 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 STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONĬreate and open this file: /etc/mysql/conf.d/disable_strict_mode.cnfĪnd write and past into it your new SQL mode: To remove particular SQL mode (in this case ONLY_FULL_GROUP_BY), find the current SQL mode: SELECT the result and remove from it what you don't need ( ONLY_FULL_GROUP_BY)Į.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 Here is a permanent solution that worked for me: I have noticed that Okon Eyo solution works as long as MySQL server is not restarted, then defaults settings are restored. This will disable ONLY_FULL_GROUP_BY for ALL users Sudo service mysql restart to restart MySQL 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 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: Ubuntu 14-16: /etc/mysql//mysqld.cnf Caution! copy_me result can contain a long text which might be trimmed by default.

Ubuntu 16 and up: sudo nano /etc/mysql/my.cnf 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:

Navicat mac free