When installing MySQL always set the sql-mode

As I’ve described before, MySQL has some appalling out of the box settings which will thwart your attempts at good data integrity! They’ve clearly seen the light though and at least give you an option to achieve good data integrity through foreign keys in InnoDB and proper handling of non-nullable fields and invalid values through the servers sql-mode setting.

To make matters worse it seems MySQL may have different out of the box settings per platform and per version. For example a friends 5.0.21 install on Windows XP had the sql-mode set with relatively strict settings whereas 5.0.18 on my Mac had an empty sql-mode. This only illustrates the necessity of always explicitly defining these settings with each install yourself so that you get consistent behavior.

If you’re configuring MySQL for a new greenfield project there are 2 things that you should absolutely always do in your my.cnf (or my.ini in Windows):

1. Use InnoDB for your tables so that foreign keys will work and you’ll get row level locking instead of MyISAM’s table level locking: default-storage-engine=InnoDB
2. Use a strict SQL mode that insures if you try and insert an invalid value or null into a non-nullabel field that you get an error: sql-mode=ansi,strict_trans_tables.

Setting the character set to utf8 and timezone to utc is also a smart thing as recommended here by David Wheeler.

Where it gets tricky is when you work on multiple projects on your laptop where some legacy code depends on MySQL’s old settings. I handle this by leaving the sql-mode as ansi/strict and then downgrading when I need to by executing the command set @@global.sql_mode=”; and then when I’m done going back with set @@global.sql_mode=’ansi,strict_trans_tables’.