net-ssh and ActiveRecord 3: bringing it all together

Question

I'm working on a small Ruby program that will connect to a remote MySQL Bugzilla database, perform a query of records, and email details of those records to a group on a daily basis.

So far, I've been able to SSH to the db server and execute a command using net-ssh. Here's an example:

require 'net/ssh'

Net::SSH.start("db.example.com", "sroach", :password => "secret") do |ssh|
result = ssh.exec!("ls -l")
puts result
end

That outputs just fine.

Using ActiveRecord 3.0.3, I wanted to test the establish_connection method so I established a connection to my local MySQL database and was able to execute commands using ActiveRecord. Example:

require 'active_record'

ActiveRecord::Base.establish_connection(
    :adapter  => "mysql2",
    :host     => "localhost",
    :database => "list_tool_development",
    :username => "my_username",
    :password => "secretpassword"
)

class MailingList < ActiveRecord::Base
end

MailingList.first  #=> Successfully retrieves first record from the table

So, where I'm having trouble is bringing it all together and applying it to my remote MySQL db. Here's my best try thus far:

require 'net/ssh'

Net::SSH.start("db.example.com", "sroach", :password => "secret") do |ssh|
ssh.forward.local(3307, "127.0.0.1", 3306)
ssh.loop { true }
end

But all that does is make my IRB session hang (which could be completely normal...don't know). Incase that hang was normal, I opened a new IRB session and tried to establish a connection to the remote database like so:

require 'active_record'

ActiveRecord::Base.establish_connection(
    :adapter => "mysql2",
    :host => "127.0.0.1",
    :port => 3307,
    :reconnect => false,
    :database => "bugs",
    :pool => 5,
    :username => "my_username",
    :password => "secret",
    :socket => "/tmp/mysql.sock"
)

class Bug < ActiveRecord::Base  #=> the table name in the "bugs" db is "bugs"
end                             #=> so I made the model singular

Bug.first  #=> the IRB session hangs at this point

So, I have no idea what's going wrong or how to degub it. Any and all suggestions would be helpful.

I'm on Mac OSX. The db that I'm trying to connect to is on FreeBSD 7.0 and is MySQL version Ver 14.12 Distrib 5.0.67.

Answer 1

Rather than try to tunnel the ActiveRecord connection inside SSH, have you tried connecting directly from ActiveRecord to the DB server? That is the normal way to connect across a network and is directly supported by ActiveRecord.

Replace the host ID with the server host IP or DNS entry, the port can probably be allowed to default to the MySQL driver's default of 3306, and the socket isn't needed since the DB is on the remote host.

If the DB host isn't on the same network as yours, and you're crossing firewalls, you might need to have that port opened to allow the connection. If it is on the same network it should work without needing ssh at all.