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?

realtebo · Accepted Answer · 2023-05-17T07:48:07.217

19

Found:

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

Ensure you have a section name [mysqld]

Add after this section name the following

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

Please, please, please NOTE THIS: This sql_mode is specific for my specific situation and use case; when applying this answer to your case, ensure you understand the principle and not copy/paste litterally.

First: read actual sql_mode

Second: apply changes to sql_mode, and test it

Third: using mysqld.conf or, better, a custom .cnf file is possible, apply the modification to sql_mode as illustrated above

Then restarted

sudo systemctl restart mysql

My settings now is preserved after restart.

edited May 17 '23 at 07:48

answered Feb 10 '17 at 09:13

realtebo

23,922
37
112
189

1

I'm getting this error: Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details. – Alauddin Ahmed Dec 02 '19 at 12:54
1

@AlauddinAhmed Make sure you've added a `[mysqld]` section. – MarcGuay Jan 13 '20 at 17:58
@MarcGuay It doesn't work to me. I get the sane error of Alaudin Ahmed – Luiz Alves Sep 19 '20 at 16:24
@LuizAlves Perhaps you should ask your own question and include a copy of your mysqld.cnf file. – MarcGuay Sep 20 '20 at 18:27
I am also gettin the same error of @AlauddinAhmed – Kritarth Sharma Oct 13 '22 at 10:35

Devart · Answer 2 · 2017-02-10T09:18:17.300

4

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

edited Feb 10 '17 at 09:18

answered Feb 10 '17 at 09:11

Devart

119,203
23
166
186

Mysql restores ONLY_FULL_GROUP_BY after restart

2 Answers2