Making sql_mode permanent in MariaDB

Question

Every reboot, the STRICT_TRANS_TABLES gets added to sql_mode. I have to go to phpmyadmin and remove it under Variables. I have tried to change it using SQL commands but it doesn't seem to survive a server reboot.

There are existing answers to this question, but they appear to be dated. I am using MariaDB version 10.5.21-MariaDB-0+deb11u1 on Debian 11 and Apache 2.4.56. I also looked at the MariaDB sql_mode documentation but there is no mention of where to change sql_mode permanently.

Based on my searches I would expect it to be in /etc/mysql/my.cnf but it appears that MariaDB now does things differently. The my.cnf contains:

[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

so I examined each file in the above two directories, but no luck. I even tried grepping in case I missed it visually:

 sudo grep -rnw '/etc/mysql/conf.d/' -e 'sql_mode'
 sudo grep -rnw '/etc/mysql/mariadb.conf.d/' -e 'sql_mode'

What am I missing?

Answer 1

STRICT_TRANS_TABLES is part of the default sql_mode.

To make it permanent, you need to add it to a configuration file like.

/etc/mysql/mariadb.conf.d/general.cnf:

[mariadb]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY