Mysql – Cannot change Mariadb/Mysql sql_mode

mariadbMySQLubuntu-16.04

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.

/etc/mysql/**/*.cnf

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 prevents TRADITIONAL from expanding into STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER

Related Topic