Fedora Desktop 35 running MariaDB 10.5, completely fresh install from about a month ago.
Why is my change to /etc/my.cnf/mariadb-server.cnf sql_mode changing @@GLOBAL.sql_mode but not @@sql_mode?
I'm trying to remove STRICT_TRANS_TABLES from my sql_mode (I have a column that is DATETIME NOT NULL that I need to omit from an INSERT and have it assume a default value). My system has a simple MariaDB installation with a nearly empty /etc/my.cnf that includes all files in /etc/my.cnf.d where there is a mariadb-server.cnf with a [server] section where I can set sql-mode.
If omitted, it defaults as specified in https://mariadb.com/docs/reference/mdb/system-variables/sql_mode/ to STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION. Confirmed by querying via SquirrelSQL:
SELECT @@GLOBAL.sql_mode global, @@sql_mode;
global @@sql_mode
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
So by default both @@sql_mode and @@GLOBAL.sql_mode are equivalent.
Adding this to mariadb-server.cnf ([server] line was already there):
[server]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Restart server with systemctl restart mariadb, no errors in journal, and re-run the query from above:
global @@sql_mode
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Note that the global sql_mode no longer has STRICT_TRANS_TABLES, but the non-global one does! Why, and what can I do about it?
The insert still fails due to the strict rule being in effect.
