How do I permanently disable ONLY_FULL_GROUP_BY
If in command line mysql I run:
mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
It returns: Query OK, 0 rows affected (0.00 sec)
ONLY_FULL_GROUP_BY is successfully disabled.
BUT - if I reboot the server, it's enabled again.
To find the config file that mysql is using
I run: /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
I get: Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The file /etc/my.cnf does not exist and ~/.my.cnf does not exist.
That leaves me with /etc/mysql/my.cnf
I run: sudo nano /etc/mysql/my.cnf to open it.
I see:
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
There is nothing in the included folders/files that sets sql_mode.
Also, note that the line in /etc/mysql/my.cnf that does set it is BELOW those includes.
If I reboot the server and run:
mysql> SELECT @@sql_mode;
I get:
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
How can I permanently disable ONLY_FULL_GROUP_BY?