MySQL and SQL_MODE Strict and Package BlindnessPosted by Jason Hawkins / November 7, 2008
The many configurations of MySQL Servers can be tricky. A real problem can arise when a development database server is not identical to the production one. Obviously, one can’t cater for every possible deployment environment; especially when you use other ISPs servers.
MySQL is very customizable, like all good application software, it needs a lot of tweaking before it is truly robust and reliable in a production environment. Because the standard package install is running almost immediately (compared to compiling it), investigations into what settings need to be changed and modified never seem to happen. They call it cargo-culting in the programming world and something very similar happens in the server world; I am trying to think of my own original phrase for this.
For the purposes of this article I will call it “package blindness”.
The ease with which a full Linux distro (and windows server) can be installed, means that more people can run and install their own servers; without the hassle of learning how anything really works. On the whole this is fantastic, but this is also where is falls apart.
(Play Funky Music now….) ANALOGY TIME: Before you get behind the wheel of a car (hopefully) you have to learn how to drive and many government-type institutions require that you are licensed to do so. This does not mean that you can rebuild the engine blindfolded, it just means you know how to drive the car and adjust the various parts of the car to your personal requirements (Read this as development). You probably won’t hit the NASCAR circuit in your Toyota Echo and you are definitely not going to manage the Paris to Dakar race. You would need a specialised team to support you, a very special highly modified car and better than average driving skills (Read this as production).
Commercial servers are exactly the same. Just because you own a race car doesn’t mean you will be paid to crash it.
Typical Scenario – The install took about 30 minutes, the damn thing booted in 2 minutes, it seems to have Apache and MySQL and PHP running and installed. Holy crap! I’m a Sysadmin. Better get my ass to Jobseek and get me another $20K on my salary.
Cynical and bitter, I hear you say, not really. Alarm bells sound when I hear “Oh yeah, I run a Linux server at home” in an interview. I also have a roof at my house, but you won’t see me coming to installing one for you.
So, what are the real world implications of package blindness?
For my example, today I will use MySQL and a funky little feature called SQL_MODE. Out of the box MySQL comes with this particular setting with a value of, you guessed it………….nothing![Note: for the windows installer it appears that is actually set to STRICT_TRANS_TABLE.] The fact that it varies from platform to platform is worrying enough.
Enough palaver! What is SQL_MODE?
Ok, straight from the manual to you:
“(SQL_) Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform.”
The sort of cool behaviour you see with this setting blank is shortened strings, rounded numbers and converted invalid dates. I know all you data-mavens out there are collectively pulling your hair out because of data quality issues!
Run this command on your nearest MySQL server and have a look at the results:
Your result set will include one or more of the following modes:
- ”, ahhhh the linux standard – no one’s sure what this one means but everyone agrees its not good for data quality and integrity. It probably allows some pretty sloppy practise on the programming side regardless of which language you use
- ANSI – the most standard of SQL modes – the jacket that fits every one adequately but looks cheap, especially after a night on the town.
- STRICT_TRANS_TABLES – this is the one that usually catches you if you are lazy and try to jam empty strings into your DATE columns. Now, if you don’t get this value right in this mode the transaction is halted and rolled back where applicable.
- TRADITIONAL – give up no matter what the error is a pretty good summary of this one
What should I set it to?
As a general rule it should be set to traditional. If you have other special requirements you can set it in a case by case basis (per session). Remember you can set it just for your session or for the server so it is picked up on reboot.
Change it for the session
Set session SQL_MODE = ‘TRADITIONAL’ ;
Change it in the server config
Edit your my.cnf file according to your particular distribution.