Why SQL_MODE is essential even when not perfect

In a recent rant on Why I think SQL_MODE is useless…, I wanted to counteract this statement with why we MUST all use SQL_MODE, even with the inherit flaws.

The fundamental principle of a database is to restore and retrieve data. When I can insert data into the database and then I select this data it is different, this is fundamentally wrong. This is a loss of essential data integrity, something a database should NEVER do.

SQL_MODE solves the problem of “silent truncation” in most instances, and produces an all important error. As pointed out, the SQL_MODE has several limitations, however the benefits do out way the risks. Quality control on source code can reduce the limitations, but no amount of coding can stop the CRUD that comes out of the database without some SQL_MODE settings.

I would ask two more important questions.

  1. How in the first place can such a critical feature of silent data truncation ever be permitted in MySQL? Who made that decision and why?
  2. When is the owner of MySQL codebase realize this is rather ridiculous and enforce essential minimual data integrity that can be obtain with options including STRICT_ALL_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE and NO_ENGINE_SUBSTITUTION.

References

4 Responses to “Why SQL_MODE is essential even when not perfect”

  1. Anders Karlsson says:

    Ronald!

    I agree that my post was a bit of a rant, but I though it was needed. Also, when going through writing the code to make all the necessary checks, I don’t understand why we can’t enforce it. In my case, I would really like one feature removed, which means less code and less bugs (he he) in MySQL: The ability to change SQL_MODE per session! That said, I ask the same questions as you do. Why? Why?

    Sometimes, when I am in that mood (SQL_MOOD?) I tend to think that the reason SQL_MODE is so little used is because it is pretty useless, as anyone can, and will, circumvent it. (I really wanted to use it on my website. Then I installed Joomla and realized that Joomla by default would work around it. And when I fixed that in the Joomla sourcecode, I realized that Joomla really did some odd things that it gets away with when using a relaxed SQL_MODE setting, but not with a more strict one. Like how NULL works.)

    /Karlsson

  2. Mark Callaghan says:

    When you tell users they don’t need transactions because your fast database engine (MyISAM) doesn’t support them and doesn’t support undo then there is only one thing to do during bulk insert and update operations when there is an exception halfway through — insert bogus data. So I blame MyISAM for starting the problem.