Rails and Database Session Variables

1 min read

Nov 21, 2011 12:00:00 AM

Ruby's ActiveRecord provides a very robust means of manipulating database table structures especially in the context of automated deployments such as Capistrano. When there is a master-slave database configuration, it may not always be prudent to perform DDL statements on the master and let those propagate through to the slave, especially in high-volume sites where Rails deployments may involve multiple migrations since replication lag may occur and present some significant problems. In addition, when no downtime is specified, a DDL rollout may break the current application code, so a targeted deployment may be more prudent. In MySQL, the solution would be to set the session variable SQL_LOG_BIN = 0 which causes subsequent statements not to get written to the binary log and therefore won't get replicated to slaves. Unfortunately, the given connection options of ActiveRecord do not accommodate the setting of database options. However, one way to accomplish setting this variable would be to have developers explicitly set it in the migration files: class AddAccounts < ActiveRecord::Migration def self.up execute("set SESSION sql_log_bin=0") create_table :api_accounts do |t| t.column "user_id", :int t.column "name", :text t.column "account",:text t.column "created_at", :datetime t.column "updated_at", :datetime end end def self.down execute("set SESSION sql_log_bin=0") drop_table :api_accounts end end But since this would be a hassle for developers and is easily overlooked, we can leverage Capistrano's architecture to monkey patch the ActiveRecord::Migration class so that this variable is set whenever the migrations are invoked. So we constructed a file, config/initializers/active_record.rb: #opens the ActiveRecord::Migration class #use alias_method to add functionality to the ActiveRecord::Migration.migrate method class ActiveRecord::Migration class << self alias_method :migrate_conn, :migrate def migrate(direction) ActiveRecord::Base.connection_pool.with_connection do |conn| @connection = conn connection.execute("SET SQL_LOG_BIN=0") @connection = nil end migrate_conn(direction) end end end Note that the database user that invokes the migrations needs to have the SUPER privilege granted to it in order to be able to set this variable. We successfully developed and implemented this technique with the devops team atSlideShare last month to build rolling DDL scripting to multiple databases using Capistrano. It allowed them to have explicit control over which database was being updated, thereby giving them the means necessary to update one database while the other served the current application code.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Rails and Database Session Variables

Share this

Share this

More resources

MySQL Query best practices

How Do You Edit a dump/exp/script File?

Have Your Scheduler Jobs Changed Run Times Since DST ??

Ready to unlock value from your data?