Configuring MySQL sql-mode in Ruby on Rails

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:

# Set MySQL to use a strict sql mode for all connections
class ActiveRecord::ConnectionAdapters::MysqlAdapter 
  alias :connect_no_sql_mode :connect
  def connect
    connect_no_sql_mode
    execute("SET sql_mode = 'ansi,strict_trans_tables'")
  end
end

What really appeals to me about this approach is that the application configures MySQL so that no matter where it runs ( on my servers, on my laptop, on another developers laptop, or on a shared server where you can’t change the global sql-mode), that MySQL will behave consistently without needing to configure each database installation. You could also use this approach to implement David Wheeler’s recommended character set and timezone settings.