Developing Stored Routines, Triggers or Events for MariaDB and MySQL is not easy, because the language is not flexible and has a lot of limitations. Plus, the server tries to make things easier by hiding out errors and allowing bad practice. In my opinion, when you have to write at least 3 lines, bad practices make things esponentially harder. When the server complains you did something wrong, the problem is not in the server: it is in your code! And it remains even if you tell the server to shut down. The only solution is finding the problem and modifying your code. So, I suggest you make the server stricter. To do this, you have to modify the
SQL_MODE can be used to change the SQL syntax, and make it more compatible with another DBMS. I don’t like to use syntaxes which are not the de-facto standard, but I think it is harmless.
But some flags should always be set, because they prevent the server to be too much flexible.
Numbers can not be divided by zero, because the result is undefined. When you try to divide a number by zero, languages usually raise an error. And so does MariaDB, if this flag is set. Otherwise, it returns
NULL is logically correct when you want to represent an undefined value, this may be good for you. But usually it isn’t – if you are dividing a number by zero by mistake, you want to know and correct the bug!
GRANT rights to a user which doesn’t exist, it is created automatically, unless this flag is set. It’s not a good thing for security that you can create a user by mistake.
This flag allowes you to insert a 0 value into an
AUTO_INCREMENT field. Since 0 is a legal value, this flag should be set. But I don’t care too much about it, because changing an
AUTO_INCREMENT field is a bad practice too.
When you create a table, it is important to choose the right Storage Engine. If this flag is not set, and you specify a Storage Engine which doen’t exist, the server silently uses the default.
If this flag is not set, MariaDB accepts a special date value: ‘0000-00-00’. If you want to use it, you don’t need to set this flag; but it’s quite a rare situation. Otherwise it’s only a source of problems, because invalid dates are silently converted to a zero-date. Which is almost never what you want.
When you specify a non-aggregate and non-grouped column in the
SELECT clause, the result is undefined. For this reason an error should be raised, but this only happens if this flag is set.
This flag is very important, because if it is not set, no error is raised when you try to assign an out-of-range value (or a string which is too long). It works not only for tables, but also for variables and functions. For example, if a
TINYINT SIGNED function tries to return 200, no error is raised (just a warning), and the return value is 127.
So, here’s the
SQL_MODE I decided to use when I create a Stored Routine for the STK proects:
SET @@session.SQL_MODE = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
Here you can find a complete list of all MySQL
Setting the InnoDB’s Strict Mode is also a good idea, but it’s out of the purpose of this post.