Mysql restores ONLY_FULL_GROUP_BY after restart

Question

I followed the 2nd answer of this SO question to disable the global option ONLY_FULL_GROUP_BY.

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)


mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0,00 sec)


mysql> SELECT @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

I just discovered that after a mysql service restart, this option is restored.

This is my environment

Server: Localhost via UNIX socket
MySQL 5.7.17-0ubuntu0.16.04.1 
Ubuntu 16.04

I thinked it was in one of the config files, but I'm not able to find it. Can you point me where is it located or how to permanently disable it?

Answer 1

Found:

I must add a new line into /etc/mysql/mysql.conf.d/mysqld.cnf

At the end of the section [mysqld], I added

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then restarted

sudo systemctl restart mysql

My settings now is preserved after restart.

Answer 2

You can find (or create) config file in the paths listed here - Using Option Files

In config file in [mysqld] section write options you need, for example:

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY