I've got an Ubuntu server 16.04.3 installed running Mariadb and recently updated to 10.2.9-MariaDB. Since the previous update, I've now got a sql_mode looking like sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
, presumably the default, but I need to make some changes. Namely dropping NO_ZERO_DATE
and potentially STRICT_TRANS_TABLES
to get an application working again.
The problem is that I've added a specific sql_mode =
into several of the .cnf (options) files (listed in screenshot), bounced/restarted the mysql server after every change, bounced the entire server, and nothing. No change to the query > SELECT @@GLOBAL.sql_mode global;
.
Google is not helping – what am i missing?
Update:
I finally figured out where the default setting is coming from.
the /etc/mysql/my.cnf
contains sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
.
And given:
TRADITIONAL | Makes MariaDB work like a traditional SQL server. Same as:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER
Which tells me that the cascade of .cnf's IS NOT BEING READ as expected.
Best Answer
Removing
TRADITIONAL
from updated sql-mode value solves and preventsTRADITIONAL
from expanding intoSTRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER