Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL server has gone away fix
# 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
@raphaelcosta

This comment has been minimized.

Show comment
Hide comment
@raphaelcosta

raphaelcosta Jul 28, 2010

reconnect: true not solve that?

reconnect: true not solve that?

@shwoodard

This comment has been minimized.

Show comment
Hide comment
@shwoodard

shwoodard Feb 27, 2011

My testing indicates that it does not. I'm using mysql2

My testing indicates that it does not. I'm using mysql2

@tispratik

This comment has been minimized.

Show comment
Hide comment
@tispratik

tispratik Mar 1, 2011

Do you have any generic code which is DB independent? Like for oracle.

Do you have any generic code which is DB independent? Like for oracle.

@nikosd

This comment has been minimized.

Show comment
Hide comment
@nikosd

nikosd Jun 3, 2011

It's not working on a Rails 3.0.7 project. It says it can not find execute method to alias. Any ideas?

nikosd commented Jun 3, 2011

It's not working on a Rails 3.0.7 project. It says it can not find execute method to alias. Any ideas?

@etagwerker

This comment has been minimized.

Show comment
Hide comment
@etagwerker

etagwerker Jul 6, 2011

I'm getting a stack level too deep error with this patch and Rails 3.0.7, Ruby 1.9.2-p0.

I'll check the forks for an improved version.

I'm getting a stack level too deep error with this patch and Rails 3.0.7, Ruby 1.9.2-p0.

I'll check the forks for an improved version.

@twinge

This comment has been minimized.

Show comment
Hide comment
@twinge

twinge Jul 18, 2011

If you're using the mysql2 gem (the current default), you need to change line 13 to the following:

class Mysql2Adapter

twinge commented Jul 18, 2011

If you're using the mysql2 gem (the current default), you need to change line 13 to the following:

class Mysql2Adapter

@nikosd

This comment has been minimized.

Show comment
Hide comment
@nikosd

nikosd Jul 22, 2011

Hey there, isn't the "reconnect" option on the database config file supposed to do the exact same thing?

nikosd commented Jul 22, 2011

Hey there, isn't the "reconnect" option on the database config file supposed to do the exact same thing?

@ScotterC

This comment has been minimized.

Show comment
Hide comment
@ScotterC

ScotterC Jul 22, 2011

Supposedly reconnect true in the database.yml file will drop other environment configs such as utf8 when it reconnects. Did you ever figure out why the execute method is undefined? Having the same issue.

Supposedly reconnect true in the database.yml file will drop other environment configs such as utf8 when it reconnects. Did you ever figure out why the execute method is undefined? Having the same issue.

@zettabyte

This comment has been minimized.

Show comment
Hide comment
@zettabyte

zettabyte Aug 29, 2011

Is there any reason to use this in applications using ActiveRecord versions ~> 3.0 when you're taking care to call #verify_active_connections! before accessing the database?

ActiveRecord::Base.connection_handler.verify_active_connections!

I'm just wondering if these kinds of exceptions still crop up for people and reconnect code like this is necessary when the above technique is used to preemptively nuke stale connections from the connection pools?

Is there any reason to use this in applications using ActiveRecord versions ~> 3.0 when you're taking care to call #verify_active_connections! before accessing the database?

ActiveRecord::Base.connection_handler.verify_active_connections!

I'm just wondering if these kinds of exceptions still crop up for people and reconnect code like this is necessary when the above technique is used to preemptively nuke stale connections from the connection pools?

@aimerickdesdoit

This comment has been minimized.

Show comment
Hide comment
@aimerickdesdoit

aimerickdesdoit Sep 22, 2011

whith mysql2 gem, line 18 becomes : rescue Mysql2::Error => e

whith mysql2 gem, line 18 becomes : rescue Mysql2::Error => e

@mauricioszabo

This comment has been minimized.

Show comment
Hide comment
@mauricioszabo

mauricioszabo Oct 10, 2011

If someone is gettting a "stack level too deep", it's because Rails is requiring the initializer multiple times. I made a GIST with the correction (and supporting Mysql2 too)

If someone is gettting a "stack level too deep", it's because Rails is requiring the initializer multiple times. I made a GIST with the correction (and supporting Mysql2 too)

@jspooner

This comment has been minimized.

Show comment
Hide comment
@jspooner

jspooner Oct 19, 2011

@twinge +1for the Mysql2Adapter fix.

@twinge +1for the Mysql2Adapter fix.

@agibralter

This comment has been minimized.

Show comment
Hide comment
@agibralter

agibralter Jan 24, 2012

I found that this fix caused me a whole lot of trouble with data integrity when I upgraded to Rails 3.1 from 2.3. I don't know what changed between the two versions, but basically, my wait_timeout was set to 10 seconds. In the case that a single transaction took longer than that 10 seconds, the connection timed out returning the "sever has gone away" exception. This monkey patch would then catch that, reconnect, and retry the last statement that caused the exception. However, the first connection, which started the transaction, would have all of its statements rolled back due to the disconnect. So this patch's retry would cause the application to just chug along on subsequent statements not even knowing that the beginning statements had been rolled back. This led to child rows pointing to parent rows that did not even exist in the db (since the parents had been rolled back). I tested this out with a short wait_timeout: 5 in database.yml.

class User < ActiveRecord::Base
  has_many :posts
end

class Post < ActiveRecord::Base
  belongs_to :user
end

# ...

User.transaction do
  u = User.create!
  sleep(10)
  p = Post.new
  p.user = u
  p.save!
end

I found that a post row would be created with user_id = 1 even though no user rows existed. Now, when I removed the monkey patch in this gist and instead used reconnect: true, mysql2 seems to correctly restart the transaction and everything works out well.

I think reconnect: true is much safer since the mysql client has pre-defined behavior on what to do when a timeout happens: http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html. Anyway, I hope this helps anyone else who may or may not have been bashing his or her head against the wall trying to figure out why their database integrity is wacky.

I found that this fix caused me a whole lot of trouble with data integrity when I upgraded to Rails 3.1 from 2.3. I don't know what changed between the two versions, but basically, my wait_timeout was set to 10 seconds. In the case that a single transaction took longer than that 10 seconds, the connection timed out returning the "sever has gone away" exception. This monkey patch would then catch that, reconnect, and retry the last statement that caused the exception. However, the first connection, which started the transaction, would have all of its statements rolled back due to the disconnect. So this patch's retry would cause the application to just chug along on subsequent statements not even knowing that the beginning statements had been rolled back. This led to child rows pointing to parent rows that did not even exist in the db (since the parents had been rolled back). I tested this out with a short wait_timeout: 5 in database.yml.

class User < ActiveRecord::Base
  has_many :posts
end

class Post < ActiveRecord::Base
  belongs_to :user
end

# ...

User.transaction do
  u = User.create!
  sleep(10)
  p = Post.new
  p.user = u
  p.save!
end

I found that a post row would be created with user_id = 1 even though no user rows existed. Now, when I removed the monkey patch in this gist and instead used reconnect: true, mysql2 seems to correctly restart the transaction and everything works out well.

I think reconnect: true is much safer since the mysql client has pre-defined behavior on what to do when a timeout happens: http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html. Anyway, I hope this helps anyone else who may or may not have been bashing his or her head against the wall trying to figure out why their database integrity is wacky.

@ryansch

This comment has been minimized.

Show comment
Hide comment
@ryansch

ryansch Feb 20, 2012

@agibralter: I did some reading on reconnect: true and it seems like it doesn't guarantee that the connection encoding will be set to utf8 on reconnection. Have you run into any latin1 character encodings?

ryansch commented Feb 20, 2012

@agibralter: I did some reading on reconnect: true and it seems like it doesn't guarantee that the connection encoding will be set to utf8 on reconnection. Have you run into any latin1 character encodings?

@agibralter

This comment has been minimized.

Show comment
Hide comment
@agibralter

agibralter Feb 21, 2012

@ryansch Interesting -- no I haven't noticed it yet. I guess I could set up an example to test it out though... I'm a bit pressed for time right now though so I may not get around to it for a bit.

@ryansch Interesting -- no I haven't noticed it yet. I guess I could set up an example to test it out though... I'm a bit pressed for time right now though so I may not get around to it for a bit.

@mcjansen

This comment has been minimized.

Show comment
Hide comment
@mcjansen

mcjansen Apr 12, 2012

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?

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?

@ryansch

This comment has been minimized.

Show comment
Hide comment
@ryansch

ryansch Apr 13, 2012

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.

ryansch commented Apr 13, 2012

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.

@mcjansen

This comment has been minimized.

Show comment
Hide comment
@mcjansen

mcjansen Apr 14, 2012

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

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

@dei79

This comment has been minimized.

Show comment
Hide comment
@dei79

dei79 Sep 1, 2012

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

dei79 commented Sep 1, 2012

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
@dei79

This comment has been minimized.

Show comment
Hide comment
@dei79

dei79 Sep 1, 2012

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

dei79 commented Sep 1, 2012

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

@trevorturk

This comment has been minimized.

Show comment
Hide comment
@trevorturk

trevorturk Oct 2, 2012

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.

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.

@trevorturk

This comment has been minimized.

Show comment
Hide comment
@trevorturk

trevorturk Oct 2, 2012

Oops -- that should have been:

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

Oops -- that should have been:

Resque.after_fork do
  ActiveRecord::Base.clear_active_connections!
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment