Tatva-Artha

On a project recently, I needed to frequently clear out some database table on staging server. Since I was going to need it occasionally for the foreseeable future, I wanted to add a capistrano task. Capistrano is good for this kind of stuffs, however what made this one interesting is the fact that the mysql command doesn’t take password on the command line, for obvious reasons. This was tricky until I remembered that deprec gem does offer ability to create databases on remote machine.

Crack opened the deprec gem and found interesting solution. Here is how one of the mysql task looks like inside deprec:

desc "Create a database" 
task :create_database, :roles => :db do
  cmd = "CREATE DATABASE IF NOT EXISTS #{db_name}"
  run "mysql -u #{mysql_admin_user} -p -e '#{cmd}'" do |channel, stream, data|
    if data =~ /^Enter password:/
       channel.send_data "#{mysql_admin_pass}\n"
     end
  end       
end

So, I ended up doing this:

task :delete_confs do
  cmd = "delete from <table>; select count(*) from table"
  run "mysql -h <host> -u <user> -p <database> -e \"#{cmd}\"" do |channel, stream, data|
    if data =~ /^Enter password:/
      channel.send_data "pword\n"
    end
    puts data # data gets updated after mysql command executes!
  end
end

This turned out all working great.

Another interesting thing I found is that the “data” variable inside the block is kept updated with latest output from the ssh command. I could not have guessed this initially since you would expect it to be statically holding “Enter password:” and that’s it for the length of block.

Capistrano uses Net::SSH underneath so I am guessing there could be more options for even lower level access. And, yes there may be bash shell solution to this entire problem. The goal here was to create a capistrano task which fits will with ruby/rails development in general.

Fun stuff!

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.