MySQL Strict Mode on OS X

If you are a web developer on OS X you probably are sporting your own installation of your database server.  If you are running MySQL you might be haunted by strict mode.  MySQL has modes as documented here that effect the behavior of various parts of the system.  In strict mode values do not get casted between types, which is where I ran into issues.  My favorite ORM was passing a boolean true into a tinyint(1) and causing the transaction to abort.  This is thanks to STRICT_TRANS_TABLES in my case.  The first thing I checked was my /etc/my.cnf to see if "sql_mode" was set in the [mysqld] section.  It was not.  So I tried setting it to something other than STRICT_TRANS_TABLES and restarted MySQL.  No luck, the setting was still on.  After a lot of poking around I found out that MySQL for OS X from Oracle ships with a /usr/local/mysql/my.cnf which is loaded on startup. In this file is a sole configuration directive for sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES.  Once I commented this out and restarted the server strict mode was off, my ORM worked and I was happy.

Do you want to get new posts from lemonbytes directly in your inbox? Subscribe today!