MySQL SQL Mode Fun

by Szymon Lipiński
tags: database mysql

While looking for some information on the web, I found quite a nice piece of SQL, something like:

-- Session scope for the purpose of this article
SET sql_mode='STRICT_ALL_TABLES'; 

This sql_mode looked quite interesting. I’ve checked that in the MySQL documentation and well… it is a little bit terrifying.

The GIGO

Unfortunately too little programmers think about the data, which is stored in the database. Usually the database is treated like an empty bag where you can throw anything in. That has it’s name: GIGO = Garbage In Garbage Out (yea, similar names to FIFO or LIFO). Applications change, new versions are coming, database is used by many different applications, but still the most important thing is the data stored in database.

The sql_mode

I’m writing all that because I found one of the modes in MySQL like this:

ALLOW_INVALID_DATES

Don’t do full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

Cool, it just means that we can insert the 35th of May to database and that’s OK. By the way: The 35th of May is quite a nice book. Why is such a mode there? The answer is in the manual, right below:

This mode is implemented in MySQL 5.0.2. Before 5.0.2, this was the default MySQL date-handling mode. As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as ‘2004-04-31’ are converted to ‘0000-00-00’ and a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enable ALLOW_INVALID_DATES.

I’d like to wish you many nice migration days to all users of MySQL before 5.0.2.

ANSI_QUOTES

Treat "`` as an identifier quote character (like the `quote character) and not as a string quote character. You can still use`` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

Another nice one.

ERROR_FOR_DIVISION_BY_ZERO

Produce an error in strict mode (otherwise a warning) when a division by zero (or MOD(X,0)) occurs during an INSERT or UPDATE. If this mode is not enabled, MySQL instead returns NULL for divisions by zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL. (Implemented in MySQL 5.0.2)

Great, now INSERT INTO X(y) VALUES (1/0); inserts just null… nice. Sorry, I don’t buy that, 1/0 is just WRONG.

If something is wrong, just treat it as wrong, don’t hide that.

HIGH_NOT_PRECEDENCE

From MySQL 5.0.2 on, the precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). Before MySQL 5.0.2, the expression is parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode. (Added in MySQL 5.0.2)

mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 0
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 1

Yes, I was afraid to find that: MySQL has the great ability to change the behaviour just because of some stupid configuration settings. So in fact performing queries in MySQL doesn’t guarantee the same results on all machines. How then I should test that? Set all available settings right after connecting to the database? Great.

NO_BACKSLASH_ESCAPES

Disable the use of the backslash character (\\) as an escape character within strings. With this mode enabled, backslash becomes an ordinary character like any other. (Implemented in MySQL 5.0.1)

Another great option… just perform INSERT INTO X(y) VALUES ('somethi\\ng' ); And you cannot be sure what is inserted.

NO_UNSIGNED_SUBTRACTION

mysql> SET SQL_MODE = '';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+

mysql> SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

Here I will just leave the examples, they are great, another situation where you just cannot be sure what you will get.

POSTGRESQL

Due to the manual PostgreSQL is said to be:

Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

Why All That Is Bad?

The problem that I see is that all the modes can change the behaviour of the database. This way you can have a testing database and well tested application. Then you move the application to a production database and you probably won’t see any errors. However the inserted data can be quite different.

Of course you could always do all the tests on production, but that wouldn’t be too clever anyway. This way I just think that I wouldn’t go with mysql for more complicated applications, than just simple storage with a couple of not complicated tables.