SQL mode controls the way the MySQL / MariaDB DBMS engine (Data Base Management System) understand syntax and validates data given to it for processing.
We can compare it to a game with different rule sets, on which every player must agree before playing.
The UNO analogy
Let’s make an analogy with the UNO card game. There is many variants, but we can sum them up like this:
| Variant | Effect |
|---|---|
| DOUBLE | Every player can lay down several cards at once if they are strictly identical |
| INTERCEPTION | If a player has the very same card than the one just layed down, he can play his, even when it’s not his turn |
| STRAIGHT_FLUSH | Every player can lay down multiple cards at once if they follow the numerical order and has the same colour |
Before starting to play, one defines uno_mode=DOUBLE,INTERCEPTION, which means the STRAIGHT_FLUSH rule does not apply. If this rule is activated later in the game, it may cause problems around the table, some players may have been disadvantaged in the previous rounds and may not want to play any more.
A card game to manage data?
SQL mode works like this, but instead of specifying how to play cards, it specifies what to do in certain situations:
- Is “2020-11-00” a valid date (
NO_ZERO_IN_DATE)? It changes data validation performed by the DBMS. - Is
SELECT name FROM users GROUP BY first_namea valid query (ONLY_FULL_GROUP_BY)? It changes the allowed syntax.
When MariaDB 10.2 and MySQL 5.7 were released, the rules have changed:
| DBMS | Default SQL_Mode |
|---|---|
| MariaDB 10.1 | NO_ENGINE_SUBSTITUTION, NO_AUTO_CREATE_USER |
| MariaDB 10.2 | STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION |
| MySQL 5.6 | NO_ENGINE_SUBSTITUTION |
| MySQL 5.7 | ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION |
We can note that newer versions kept old rules, but new ones were added. That’s why when upgrading from MariaDB 10.1 to MariaDB 10.2 for instance, one must specify to its database to play with old rules, and not to enforce STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO.
Concretely
Actually, if my website should store the result of a division, but the denominator is `0`, my database with the MariaDB 10.1 default SQL mode database will allow it and store the result as NULL. My database with the MariaDB 10.2 default SQL mode will also allow and store a NULL value it but will raise a warning. If strict mode is also enabled, MariaDB 10.2 will throw an error and store nothing. Thus, changing SQL mode can break websites and should be done carefully.
As we cannot check every line of code of every website of each of our customers, when an DBMS major upgrade changing the default SQL mode is planned, OVHcloud activates the legacy SQL mode ensuring your website will not be affected. Of course, you can switch to the new default SQL Mode in the “Configuration” tab of your CloudDB. But keep in mind this is for experienced users and OVHcloud advises to stick with the default sql_mode, unless your database was upgraded from a previous version with a different default sql_mode.
Official Documentation:
MariaDB: https://mariadb.com/kb/en/sql-mode/
MySQL: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
Member of the database team since 2017