Mariadb - Disabling Strict Mode

Add this to /etc/my.cnf and then restart MariaDB:

Code: 
sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
Any tips on that ditto? Because I disabled strict mode just by adding:
Code: 
sql_mode=
to my.cnf and that works too. Any benefits on the lines you still have in there?
 
When you only add sql_mode=, then you disable all that was previous present in sql_mode=, not only strict mode. If you remove sql_mode= and restart mysld, then you can for example look in phpMyAdmin on the tab "Variables" and look up "sql mode" then you will see a comma separated list of modes. You do not need to disable all to disable strict mode, so what I have done is only remove the ones related to strict mode. Please see: https://mariadb.com/kb/en/library/sql-mode/#strict-mode

A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode.
Click to expand...

I do not remember what the defaults of sql_mode was before I changed it, as it is a long time ago, but I only removed the ones relateted to strict mode. You might reverse it by removing sql_mode from my.cnf, so that you can see what the default is, and then only remove STRICT_TRANS_TABLES and/or STRICT_ALL_TABLES by removing them from sql_mode= but keep the rest of them that is not related to strict mode.
 
Last edited:
I had sql_mode= with mysql 5.7 so it was already in there too. Just wondering if there were any benefits to it.
Just for curiousity reasons I had a look at the Mysql references, and it looks you don't need those entry's anymore.
It shows this on Mysql 5.7, so probably also for MariaDB:
ERROR_FOR_DIVISION_BY_ZERO is deprecated. ERROR_FOR_DIVISION_BY_ZERO is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default.
Because ERROR_FOR_DIVISION_BY_ZERO is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.
Click to expand...

Since I found, this I also had a look at the second setting shows the same:
NO_AUTO_CREATE_USER -> Deprecated and will be removed

NO_AUTO_CREATE_USER is the only one which will keep existing in Mysql.

So I'm curious as to why MariaDB is defaulting to settings which are deprecated in Mysql.
 
You must log in or register to reply here.
Back
Top