0

I'm looking at an Rails app that would require establishing a remote SSH session (SSH Tunnel?) from Heroku to a remote Mysql database as part of an background, ActiveRecord session. The goal would be to migrate data at different times via this channel into the app. Connecting to the remote mysql database over the web would not be an option.

Few questions:

  1. Does Heroku permit SSHing off their Dyno?
  2. What would be the downsides of doing so?
  3. Would I have to be concerned about SSH session persistence (job could take 1 hour)?
  4. Lastly, how can I configure Rails and Heroku to enable remote connections for a database.yml mysql endpoint?
user1322092
  • 4,020
  • 7
  • 35
  • 52

2 Answers2

0

Well, I'm now able to answer my own question after digging around for a few days. In short, yes. 

class LegacyAccount < ActiveRecord::Base

 self.table_name = "account"

 def self.test_connection

      # net-ssh-gateway gem
      gateway = Net::SSH::Gateway.new("ip_address","ssh_user_name",
          password: "password",
          port: "port if different from 22",
          verbose: :debug
          )

      port = gateway.open('127.0.0.1', 3306, 3307)

      establish_connection :legacy_production

      result = LegacyAccount.first
      puts "Record: #{result.to_yaml}"

      gateway.close(port)
      gateway.shutdown!

      result
  end

end

and in your database.yml:

legacy_production:
  adapter: "mysql2"
  host: "127.0.0.1"
  username: "root"
  password: "password"
  database: "legacydb"
  port: 3307
  secure_auth: false
user1322092
  • 4,020
  • 7
  • 35
  • 52
  • I think where this approach falls down is in the `gateway.open()` call -- if another process has already opened the port, you'll get an error, and AFAIK there's no way to detect that a-priori. I'm beginning to think the right approach is to simply trap and ignore EADDRINUSE exceptions in that case. See http://stackoverflow.com/questions/27536729/how-can-i-detect-if-a-netsshgateway-port-is-already-opened for more discussion. – fearless_fool Dec 17 '14 at 23:30
  • @fearless_fool, yes... this solution (test rather) was not meant to illustrate/support multiple processes (hence why I added background job). In a background job, only one process would open the gateway. Also, the test succeeded on Heroku, which was my original question. – user1322092 Dec 18 '14 at 01:40
  • 1
    My app also accesses the remote DB from background tasks, the difference being that I have multiple tasks in different processes. I've lightly tested the `rescue EADDRINUSE` approach and it appears to serve my needs well. Thank you for getting me 80% of the way there! – fearless_fool Dec 19 '14 at 15:37
0

I tried @user1322092's approach and got into trouble when multiple clients tried to access the db, since each one would attempt to open a connection (all but the first would get an error).

I created a monstrous solution involving spawning an SSH process on the dyno and using that for all communications. Gory details are in https://stackoverflow.com/a/27361295/558639. Aside from being really kludgy, it incurs a delay every time a process starts up, whether or not you're accessing the remote database.

update

So here's a better approach that appears to work well: just trap (and ignore) Errno::EADDRINUSE errors. Here's the code:

require 'net/ssh/gateway'

class Mole

  TUNNEL_HOST = <redacted>
  TUNNEL_USER = <redacted>
  AWS_HOST = <redacted>
  TUNNEL_PORT_NUMBER = 3307

  attr_reader :tunnel_gateway, :tunnel_port

  # Call this to open an SSH tunnel from the current machine.  If the
  # the tunnel was already opened (e.g. by another process) , you'll
  # get a warning message
  def open_tunnel
    @tunnel_gateway = Net::SSH::Gateway.new(TUNNEL_HOST, TUNNEL_USER)
    begin
      @tunnel_port = @tunnel_gateway.open(AWS_HOST, 3306, TUNNEL_PORT_NUMBER)
    rescue Errno::EADDRINUSE => e
      $stderr.puts("Warning: #{e.class}: #{e.message}")
    end
  end

  # You won't normally call this, because the tunnel is a system wide
  # resource; you don't know when other processes want to release it.
  def close_tunnel
    r = @tunnel_gateway.close(@tunnel_port) if @tunnel_gateway
    @tunnel_gateway.shutdown!
    r
  end

end
Community
  • 1
  • 1
fearless_fool
  • 33,645
  • 23
  • 135
  • 217
  • because I'm migrating data, the process is sequential; however, what I've discovered is my first SSH tunnel connection was successful, but the second would throw 'mysql connection has gone away'...grrrr.. so I had run `heroku ps:restart`, and I guess somehow that causes the next connection to succeed. Is this the issue you also had? – user1322092 Dec 09 '14 at 03:41
  • @user1322092: I was testing on my local machine only. I got a different error message (sorry, I don't recall the exact msg), but probably the same reason: trying to re-open an ssh tunnel that was already open. I suspect the root cause is that the tunnel is per-machine, and if you have individual processes trying to create tunnels, all but the first one will get the error. Perhaps there's a way to detect if a tunnel is already running... – fearless_fool Dec 09 '14 at 15:45