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’.
June 7th, 2006 at 7:59 pm
[...] In my previous post I wrote about setting MySQL to a stricter sql-mode to make it behave like most other databases, however, I recently ran into a case where I couldn’t set the global sql-mode without breaking some legacy applications. I imagine this is also often the case when you host on a shared server where a global configuration change would be out of the question. I realized I needed to set the sql-mode in my Ruby on Rails project so that no matter where it ran it would get consistent behavior (such as when inserting nulls into non-nullable fields). I did it by reopening the MysqlAdapter class and setting the sql-mode upon connect. To do this add the following to the end of config/environment.rb: [...]