CODEWITHSUNDEEP
sqlruby-on-railsdatabase-connection
Vida Ha
Vida Ha

Reputation: 201

How to get Rails to automatically reestablish database connections after a database downtime

After a database downtime, Rails will first throw this error once:

ActiveRecord::StatementInvalid: NativeException: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

From then on, every database call with have the following error, even after the database is back up:

ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: This connection has been closed.

To get the server running again, I have to restart the rails server. This is not ideal for us, as our prod engineers would like to do maintenance on our databases without having to also bring back up all the services that depend on the database. So, I'm wondering - is there a way to automatically get Rails to try to reestablish the database connection or a recommended way to get this behavior?

Things I have tried:

I have already tried setting reconnect to true in my database options, and with that, I can kill individual database connections, and rails will reestablish the connections. However, it will not after a database outage. I found that from a command console I could get the connection back up by calling

ActiveRecord::Base::establish_connection

So maybe finding a clean place for rails to call the above command this would work? Any suggestions?

Upvotes: 20

Views: 9600

Answers (5)

Abdo
Abdo

Reputation: 14051

I had the same issue with Mysql2Adapter. I replicated the failure by doing a very long query: User.find_all_by_id((1..1000000).to_a) ; from now on, all ActiveRecord requests fail (User.first fails)

Here's how I solved it:

The issue is very simple: whenever we get the exception above, we want to reestablish the connection and try again. We solve the issue by aliasing the execute method, wrapping it with begin rescue, and reestablishing db connection in rescue.

For Mysql, the code is in Mysql2Adapter, and below is the fix:

Place this code in config/initializers/active_record.rb

module ActiveRecord
  module ConnectionAdapters
    class Mysql2Adapter < AbstractMysqlAdapter
      alias_method :old_execute, :execute

      def execute(sql, name=nil)
        begin
          old_execute(sql, name)
        rescue ActiveRecord::StatementInvalid
          # you can do some logging here

          ActiveRecord::Base.establish_connection

          old_execute(sql, name)
        end
      end
    end
  end
end

You need to do the same for the postgres adapter.

https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb

I think since select is the most used query, you can alias select and as soon as it fails, the connection will be reestablished.

You want to create an initializer (config/initializers/active_record.rb) and alias select_rows (it might be something else, just find the right method and patch it. It might be async_exec or execute, I haven't looked much into Postgres' adapter) in:

module ConnectionAdapters::PostgreSQLAdapter
      module DatabaseStatements
      end
end

Upvotes: 3

Dean Winchester
Dean Winchester

Reputation: 659

I'd recommend using external tools/scripts to monitor such kind of events. Activerecord's reconnect works when databases kills the connection after certain idle time, and it'll give up after certain times of failure. So it's not gonna help in your case.

I think you should write your own script to monitor the status of you database. If it comes back after some time, simply restart your rails app.

Besides, you'll need those monitoring stuff anyway, like your server's memory & cpu & disk usages, server load, database status, bunch of stuffs. Just one more slightly customised monitor rule.

Upvotes: 0

Yam Marcovic
Yam Marcovic

Reputation: 8141

This is a really ugly solution, but I think it should work.

  • Set reconnect to true, just like you previously did.

  • Edit the file activerecord-X.Y.Z/lib/active_record/connection_adapters/postgresql_adapter.rb and change the reconnect! method to say

    def reconnect!
  clear_cache!
  ActiveRecord::Base.establish_connection
end

More research is needed

  • Check if it actually works
  • Check if it doesn't call establish_connection several times simultaneously (in which case you'd need a lock)
  • Check if there's a better place to put this code in. Ruby lets you redefine any method in runtime, but you need the symbols loaded. In other words, you need the PostgreSQLAdapter class to exist. The closest I've come to having that symbol loaded is in config/environment.rb after initialize!, but it still wasn't deep enough in the stack to have that symbol loaded.

If you do find a place outside the ActiveRecord code that already has the symbol loaded, and you can edit its methods, then put the following code in it:

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::StatementPool
  def reconnect!
    clear_cache!
    ActiveRecord::Base.establish_connection
  end
end

What's more, is that it's a bit overkill to actually call establish_connection. It might be possible to call the important stuff inside that method, to avoid some overhead.

Let me know if this helped, and if you've made any progress.

Upvotes: 2

sockmonk
sockmonk

Reputation: 4255

I'm not sure how to do exactly what you're asking, but I have another 'process' suggestion: set up simple scripts so your prod engineers can easily stop and start all applications.

Develop a set of capistrano recipes (or other scripts) that your prod engineers can use to stop and start all applications. For a normal Rails app, all you should really need to do is put a maintenance page, so that nginx or apache serves that page instead of forwarding requests to the rails instances. Ideally then, rails workers stop getting requests, db goes down, db comes up, then the maintenance page gets taken down and the workers get requests again, never realizing the database went away for a while.

In the case of background workers, they may need to be actually stopped and started by the script unless their queue is empty and stays empty. Any scheduled rake tasks or other scheduled jobs will probably fail if they depend on the database and run while it's down, so you'll want to try to schedule them to run outside the window when you normally do db maintenance.

If your prod engineers don't like running scripts (!), you could probably set up a nice web interface to make it easy for them. This will probably prove useful for more than just dealing with database connection errors, as it will empower more people in your organization to take care of basic things like stopping and starting your apps.

Upvotes: 0

khustochka
khustochka

Reputation: 2386

Insert a rescue somewhere

rescue ActiveRecord::StatementInvalid: ActiveRecord::JDBCError
  ActiveRecord::Base::establish_connection
  retry

But where? I do not know

Also you can use rescue from in ApplicationController. But this will not retry the action that failed, so you should probably also render some error template

rescue_from ActiveRecord::StatementInvalid: ActiveRecord::JDBCError do
  ActiveRecord::Base::establish_connection
  render 'errors/error', :status => 500
end

Upvotes: 0

Related Questions