Cannot change Mariadb/Mysql sql_mode

Question

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.

I don't see any "attached .cnf files". What exactly did you edit? What changes did you make? And by 'bounced" do you mean "restarted"? — Michael Hampton, Nov 28 '17 at 22:27
@AlexanderTolkachev - I had first tried changing sql_mode in CLI +log out and back into mysql, and saw no change to the SELECT return — CNSKnight, Nov 29 '17 at 13:21

score 0 · Answer 1 · answered Nov 30 '17 at 14:37

0

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

answered Nov 30 '17 at 14:37

CNSKnight

161
8

score 0 · Answer 2 · answered Nov 15 '18 at 14:32

This is not answering the OP, but may answer someone else's google search when they come upon this page (as I just did):

I had trouble setting the sql-mode in my.cnf because there were spaces between the individual settings. In other words, this worked:

[mysqld] sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

This didn't:

[mysqld] sql-mode="STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" Note: the sql-mode line is all on one line - word-wrapping on Stack-Exchange is pushing it onto a second line here.

Unfortunately MySQL workbench generated config for me with spaces - but MySQL was not telling me what the problem with my.cnf was.

I am running MySQL 5.7 on Ubuntu 18.04

Cannot change Mariadb/Mysql sql_mode

2 Answers2