Putting My DBA Hat On... Again

It's not often I have to worry too much about the minutia of database administration... well, I try not to. But this question on StackOverflow got me intrigued, so I put on my trilby.

labratmatt was having a bit of a problem with inserting data into a MySQL table with field defined as DECIMAL(3,2). Can you guess what his problem was? That's right.... 9.99! How did you guess?

This has got to be one of the most popular MySQL-related Google searches. The initial problem is easy to solve... correct the presumptuous field definition.

However, the underlying problem is really why his data was being truncated, even inserted incorrectly. You may notice the same if you run MySQL (v5.0+) from a default setup on other field types: VARCHAR for example, where you set a maximum field length. When you INSERT data that is too long it simply gets truncated.

Not hugely worrying you may think, especially in development and testing phases. True. But this wasn't enough for me, so I went on a hunt.

I found this interesting article by Robin Schumacher on MySQL Data Integrity.

It seems that there is a configuration variable in MySQL (v5.0+) called 'sql_mode' that determines exactly how strict MySQL should be when writing data to tables. The problem is that, by default, it's unset, which means MySQL uses its standard mode... fudged SQL.

It has a vast array of options, so read through and choose wisely.

The default MySQL setup essentially turns all of your INSERT and UPDATE statements into INSERT/UPDATE IGNORE statements. It is an unexpected "gotcha" for many... any self-respecting software developer would want the INSERT query to fail and for the DBMS to tell you why it failed, not automatically munge the data for you.

To achieve this, the general option to use for 'sql_mode' is STRICT_ALL_TABLES... but even this has some gotchas (VARCHAR and TEXT expect only string values etc...) and may need to be combined with other options.

Of course, if you write your programs to send MySQL the correct datatypes, changing this option shouldn't cause any problems :)

The annoying thing is that I've only just found about this now after nearly 6 years of database development.