MySQL ignores any my.cnf configurations

I just did an fresh install of ubuntu 18.04 and MySQL from the Ubuntu repo (5.7.22-0ubuntu18.04.1 (Ubuntu)).

MySQL works so far, but I need to change some variables. Trying to change the sql-mode to "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".

So I put a new file "mysqld_custom.cnf" in /etc/mysql/mysql.conf.d/:

[mysqld]
skip-external-locking
bind_address            = 127.0.0.1
sql_mode                = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

query_cache_limit       = 1M
query_cache_size        = 16M

performance_schema      = 1

log_error = /var/log/mysql/error.log
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log          = 1
long_query_time         = 2
log_queries_not_using_indexes

expire_logs_days = 10
max_binlog_size   = 100M

general_log_file = /var/log/mysql/query.log
general_log = 1

Everything looked fine. /usr/sbin/mysqld --verbose --help gives me

sql-mode  STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

and it seems ok with mysqld --print-defaults:

# mysqld --print-defaults
mysqld would have been started with the following arguments:
--skip-external-locking --bind_address=127.0.0.1 --sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION --query_cache_limit=1M --query_cache_size=16M --performance_schema=1 --log_error=/var/log/mysql/error.log --slow_query_log_file=/var/log/mysql/mysql-slow.log --slow_query_log=1 --long_query_time=2 --log_queries_not_using_indexes --expire_logs_days=10 --max_binlog_size=100M --general_log_file=/var/log/mysql/query.log --general_log=1

But the running server says:

mysql> SHOW GLOBAL  VARIABLES LIKE "sql_mode";
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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.01 sec)

mysql> SHOW GLOBAL  VARIABLES LIKE "%general_log%";
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| general_log      | OFF                         |
| general_log_file | /var/lib/mysql/ubuntu18.log |
+------------------+-----------------------------+
2 rows in set (0.00 sec)

I tried to put the "mysqld_custom.cnf" in /etc/mysql/conf.d/ and in /etc/mysql/mysql.conf.d/.

Any ideas what I did wrong? Thanks in advance

I came across the same issue and spent like 2 days, but I think I finally have it. There are several configurations spread all across the system and to find the one or the ones, which are responsible for setting the sql_mode I used the following command:

sudo grep --include=\*.cnf -rnw '/' -e "sql[-_]mode"

(info on how the command works can be read here: https://stackoverflow.com/a/16957078/1806628)

This command is literally searching every file from the root to see which contain either sql_mode or sql-mode texts and for me it found additional configs with their default values at:

/usr/share/mysql/my-default.cnf:31:sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
/usr/my.cnf:28:sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

After I changed the values there and restarted the service the values finally changed.

The situation is tricky, because I used to check which file is being used as configuration by making a typo in /etc/mysql/my.cnf and it sure made mysql non-functional, so it lead me into believing that there is only one file and I'm editing the correct one. While in the real world there are other files too, which will override settings.

Hope this helps someone out there struggling with the same issue.