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.
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.