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.

This entry was posted in MySQL, Ruby, Software Engineering. Bookmark the permalink.

One Response to Configuring MySQL sql-mode in Ruby on Rails

  1. cmyers says:

    In addition to the code above:
    1. Change MysqlAdapter to Mysql2Adapter if you are using the new one
    2. add “ActiveRecord::Base.connection.reconnect! ” to your environment.rb to ensure it takes effect right away.

Leave a Reply

Your email address will not be published. Required fields are marked *