Apparent vs. actual data integrity

Jan 25, 2009 / By Sheeri Cabral

Tags: ,

I realized tonight exactly why MySQL’s default behavior of silent truncation bothers me.

It reminds me of people who use a ticketing system and close every ticket as soon as they are done working on the issue instead of actually asking the other party if they are satisfied, because closing more tickets make it look like they’re doing more work.

It reminds me of workers at fast food restaurants who hit the button to make the order disappear as if they have already served me my food, because then their throughput times are faster.

Similarly, with MySQL’s default behavior of silent truncation, it’s as if the database server is saying “the fewer database errors raised, the better.” As in the previous two examples, the metrics do not matter if the quality of service is poor — particularly when the quality of service is poor specifically *because* people are trying to meet the metrics instead of the actual goal — customer satisfaction.

While it’s true that the goal is fewer database errors, the idea is to have fewer errors because there are fewer problems — not because the problems are not being reported!!!

3 Responses to “Apparent vs. actual data integrity”

  • Really have to agree with you on this one. Silent truncation is poisonous for applications because once you start ignoring the errors it’s quite painful to go back and fix them properly. It’s the same problem as adding constraints to a schema that already has bad data.

  • As I understand it, the underlying reason for the silent truncation, and for most of the other ignore-error behaviour, is due to the support for non-transactional storage engines, especially MyISAM which was the first engine and still the default on non-Windows installs I believe.

    Since MyISAM cannot roll back a partial change, the issue is what to do when a problem is detected half-way through a multi-row update or insert. Truncating the value (or other error-ignoring action) allows the statement to complete, but leaves possibly wrong data in one row. Aborting would leave half an update in the database, which can also cause serious inconsistencies. Either way we loose.

    With InnoDB there is no such problems, as we can rollback just the offending statement on detecting an error. Personally I would install MySQL with InnoDB and strict-mode defauls.

    However even an engine like NDB (which is transactional) does not fully support this, as it cannot roll back the last statement (only the whole transaction), which leads to some of the same problems.

  • […] colleague Sheeri wrote a great blog post on MySQL’s default silent truncation behavior and how this can affect your database server’s data […]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>