MYSQL sql_mode Keeps reseting after mysql restart

Question

Setup

mysql Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using EditLine wrapper

Distributor ID: Ubuntu Description: Ubuntu 18.04.5 LTS Release: 18.04 Codename: bionic

MYSQL Keeps reseting my sql_mode and so my website is not working well.

The MySQL message is the following

Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdevnew.f.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What I do till now is from terminal to connect inside mysql with the command

mysql -u root -p

and run the following commands

mysql> set global sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set session sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

And the error is gone, but for a couple of days.. lets say that we restart mysql server and the above settings are gone.

Also tried to change the following files with the sql_mode that i would like to load after mysql restart.

File list

/etc/mysql/my.cnf

/etc/mysql/mysql.cnf

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

(I've also tried with value in quotes)

Then restart mysql

sudo service mysql restart

But the setting is not affecting mysql restart and I keep getting the error mentioned above.

mysqld --verbose --help

prints the following

sql-mode                                                     NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Any ideas of what am I doing wrong?

Answer 1

You might have other my.cnf files being referenced by mysqld.

Also make sure those MySQL configuration files are owned by a mysql user and has read/write permission. Once restarted, verify using the code below whether your sql-mode configuration is loaded properly.

SELECT @@GLOBAL.sql_mode;

This thread, How to make sql-mode="NO_ENGINE_SUBSTITUTION" permanent in MySQL my.cnf has all the required steps to help you troubleshoot this issue.