MySQL
Raiting:
2

MySQL strict mode and why it should be enabled


There is a special mode in MySQL that is designed for inputting the invalid data in a database. For example, in order to input in INT-field 2147483647 instead of 20000000000 or to fill the database with non-existent dates and cut lines. Well, you never know what fro this mode might be useful.

This mode is called a "traditional mode"

Although, at first glance it seems crazy and such a behavior is a historical reason.

The engine of MyISAM was a former standard engine for MySQL recently; it does not support the database transactions. If one thing falls off from a set of queries, then the database loses its integrity. In comparison with such risk an entry into the database of incorrect values was lesser evil.

But all competent web developers know that the engine of InnoDB should be used, which is better than MyISAM in the context of storage for the website. In particular, InnoDB supports database transactions and all adequate ORM (or their substitutes that coordinate queries to the database) use these transactions to ensure atomicity of changes.

It turns out if you use InnoDB and the transactions the spare treatment of MySQL causes a damage. Each missed test threatens to stuff the database. That is a good example: how do you keep the field of URL? VARCHAR (255)? An address can be 2 kilobytes in length. If a row will be overfilled MySQL just writes a warning in the log in a "traditional mode", which nobody reads.

Strict mode

We are interested in STRICT_TRANS_TABLES mode from an extensive list of MySQL modes. It bars what is mentioned above, and throws the errors for any incorrect data.

In queries’ language

SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
Of course, we need to do the tests after such change (if any are there), or at least keep the track of errors (as they appear there).
Siera 30 october 2011, 13:53
Vote for this post
Bring it to the Main Page
 

Comments

Leave a Reply

B
I
U
S
Help
Avaible tags
  • <b>...</b>highlighting important text on the page in bold
  • <i>..</i>highlighting important text on the page in italic
  • <u>...</u>allocated with tag <u> text shownas underlined
  • <s>...</s>allocated with tag <s> text shown as strikethrough
  • <sup>...</sup>, <sub>...</sub>text in the tag <sup> appears as a superscript, <sub> - subscript
  • <blockquote>...</blockquote>For  highlight citation, use the tag <blockquote>
  • <code lang="lang">...</code>highlighting the program code (supported by bash, cpp, cs, css, xml, html, java, javascript, lisp, lua, php, perl, python, ruby, sql, scala, text)
  • <a href="http://...">...</a>link, specify the desired Internet address in the href attribute
  • <img src="http://..." alt="text" />specify the full path of image in the src attribute