MySQL server has gone away fix

connection_fix.rb

# If your workers are inactive for a long period of time, they'll lose
# their MySQL connection.
#
# This hack ensures we re-connect whenever a connection is
# lost. Because, really. why not?
#
# Stick this in RAILS_ROOT/config/initializers/connection_fix.rb (or somewhere similar)
#
# From:
#   http://coderrr.wordpress.com/2009/01/08/activerecord-threading-issues-and-resolutions/

module ActiveRecord::ConnectionAdapters
  class MysqlAdapter
    alias_method :execute_without_retry, :execute

    def execute(*args)
      execute_without_retry(*args)
    rescue ActiveRecord::StatementInvalid => e
      if e.message =~ /server has gone away/i
        warn "Server timed out, retrying"
        reconnect!
        retry
      else
        raise e
      end
    end
  end
end

We tried the connection fix on a site that is running on Rails2.3.14 with a lot of traffic and run into all kind of strange locking issues on the database. Once we removed the fix, it was all fine again. Can't really explain why this happend.

So.. I have been looking for alternative solutions. I have seen people who suggested to add ActiveRecord::Base.verify_active_connections! to your perform. I used an alternative approach by using the after_fork hook inside resque.rb in the config/initializers folder.

Resque.after_fork = Proc.new { 
  ActiveRecord::Base.verify_active_connections!  
}

This seems to work fine. I wonder why other people are not suggesting this simple approach. Am I missing something?

I ended up doing the following.

module BaseJob
  def self.included(base)
    base.extend ClassMethods
  end

  module ClassMethods
    def perform(*args)
      ActiveRecord::Base.verify_active_connections!
    end
  end
end

class FooJob
  include BaseJob

  @queue = :foo_queue

  def self.perform(arg)
    super
    foo_stuff
  end
end

@mcjansen - Your after_fork solution might be a cleaner way to get this done.

Turns out there is already a plugin that is using the after_fork approach:
https://github.com/wireframe/resque-ensure-connected

I had a bit trouble with the gist when migrating to mysql2 adapter. After checking the rails code I found out that the rails team implemented an AbstractMysqlAdapter so I changed the gist as follows. This should work with mysql and mysql2 adapter, right?

Original

class MysqlAdapter

New

class AbstractMysqlAdapter

Because the resque-ensure-connected gem did not solved my issue when the SQL server goes down a longer time I added this gem. I'm open for discussions: https://github.com/dei79/mysql_stay_connected

This should do the trick:

Resque.after_fork do
  clear_active_connections!
end

This would work both before & after forking the child, since it wouldn't close active connections, but it would check everything back in to the connection pool, and checking back out from the pool verifies the connections.

Oops -- that should have been:

Resque.after_fork do
  ActiveRecord::Base.clear_active_connections!
end

For anyone running into this while forking in Rails, try clearing the existing connections before forking and then establish a new connection for each fork, like this:

# Clear existing connections before forking to ensure they do not get inherited.
::ActiveRecord::Base.clear_all_connections! 

fork do
  # Establish a new connection for each fork.
  ::ActiveRecord::Base.establish_connection 
  
  # The rest of the code for each fork...
end

See this StackOverflow answer here: https://stackoverflow.com/a/8915353/293280