Rails and Database Session Variables

Posted in: Technical Track

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.

email

Interested in working with Administrator? Schedule a tech call.

No comments

Leave a Reply

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