Disabling MariaDB's Strict Mode (Or: how to handle SQL_MODE)

I found many solutions claiming this:

Add this to /etc/my.cnf and then restart MariaDB sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Why? They didn't say. But it also did not work as expected, because different distros organise their file structure differently.

It is important to put this option under the [mysqld] section of your configuration file. Any other section would just ignore it.

But what does this value mean? All of that is needed to disable strict mode?

Well, no not really. There are many other (default) options already set in the SQL_MODE configuration, STRICT_TRANS_TABLES has been one of them since MariaDB 10.2.4 If you just copy-and-paste some SQL_MODE value from the internet and override it in your settings, you might disable certain values you did not intend to. Especially in the future, as more new defaults are added.

So the way it's supposed to work is that you remove the STRICT_TRANS_TABLES bit from your current (default?) value.

So first you better check the current value of SQL_MODE by executing this command:

mysql -e "SELECT @@sql_mode;"

That'll give you something like this:

+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

Turns out, the advice was correct. But if a future version adds any new default values, just willy-nilly using the earlier sql_mode value would have overridden those, too.

If you want to see what the current default SQL_MODE value is for your MariaDB version, take a look at https://mariadb.com/kb/en/sql-mode/