Strict mysql mode and why it should be enabled

In MySQL, there is such a special mode, designed to enter into the database of incorrect data. For example, instead of inserting 20000000000 into the INT field 2147483647 . Or fill the database with non-existent dates. Or cropped lines. Well, or you never know why this mode can be useful to you.

This mode is called “normal mode”.


')
Although at first glance it seems crazy, this behavior has a historical reason.

The MyISAM engine, which was the standard MySQL engine until very recently, does not support transactions . If one of the “as if atomic” set of requests fell off, the base (in general) lost integrity. Compared to this danger, writing incorrect values ​​to the database turned out to be the smallest evil.

But after all, any competent web developers know that you need to use the InnoDB engine , which - in the context of storage for the site - is better than MyISAM from all sides. In particular, InnoDB supports transactions, and all somewhat adequate ORMs (or their live deputies encoding database requests) use these transactions to ensure that the changes are atomic.

So it turns out that if you use InnoDB and transactions, a sparing MySQL relationship only hurts you. Each missed check threatens to clog the database. Here is a good example - how do you store the URL? VARCHAR(255) ? And the address may be 2 kilobytes long . In the "normal mode" when the line is full, MySQL will only write a warning to a log that no one reads.

Strict regime

From the extensive list of MySQL modes, we are interested in STRICT_TRANS_TABLES mode. He forbids what I mentioned above, and throws errors on any incorrect data.

In terms of queries,

 SET sql_mode = 'STRICT_TRANS_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE';

Of course, after such a change, you need to get rid of the tests (if any), or at least monitor the errors (as they appear).

Conclusions regarding STRICT_TRANS_TABLES in relation to Ruby on Rails can be read on my blog .