How do I permanently disable ONLY_FULL_GROUP_BY
Posted by: Bad Programmer
Date: March 29, 2022 08:16AM

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?

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.