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

Question

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?

Answer 1

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

Answer 2

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.