sql_mode for Mariadb on Debian 9 (Stretch)

[carlarogers]

The information on how sql_mode is supposed to be set does not seem to conform to what my system does.

OBJECTIVE: Permanently set sql_mode sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

APPROACH: Put the following statement into one of the config files for Mariadb, that is read after any other config file with a declaration for sql_mode.

sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

DETERMINING WHICH CONFIG FILE TO USE:

1. Get a list of config files read, by first, finding the binary for mariadb....
   

    which mariadb

   Result is : /usr/bin/mariadb
   Run this command, using the result from above:
   

   /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

   Result is: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
2. Those files are linked to files. I traced them all and found no statements in any of them for setting sql_mode.
3. The file I picked /etc/my.cnf. In that file, I entered

4. [mysqld]
   sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

5. Restarted mysql.
6. Checked sql_mode using this at command line
   

   mysql -u admin -p`cat /root/.nothing/lostssh` -e "select @@sql_mode"

   Result:
   

   +------------------------------------------------------------------------------------------------------------------------------------------------------+| @@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 |
   +------------------------------------------------------------------------------------------------------------------------------------------------------+

7. 
   
8. 
   

I have found dozens of explanations online for how to do this. No luck.

Obviously, i am missing something, but I am stumped at the moment. If you can help, I would appreciate it a lot. Thank you.

 

[JasKinasis]

Perhaps it’s being overridden somewhere else?

what happens if you use something like:

sudo grep -RiHn sql_mode / 2> /dev/null

That will take a while to run, but it will check every file in your file system for instances of sql_mode. Error messages will be directed to the bit bucket.

Perhaps there is another config file you don’t know about that is somehow superceding the ones you know about!

For example, if mysqld is being started as a service by systemd, maybe the initialisation is happening elsewhere - where the service is defined/started?!