Rails and Database Session Variables

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.

Managing DDL Propagation in Master-Slave Environments

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.

Manual Implementation: Explicit Session Setting in Migrations

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 

Automating Control with ActiveRecord Monkey Patching

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.

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 

Privilege Requirements and Real-World Application

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