Recovering Mysql::Error - Must I redo establish_connection?


#1

Periodically my application (a faceless daemon, so there’s no
human-interface considerations) gets the following exception:

Mysql::Error: MySQL server has gone away: SELECT * FROM messages WHERE
(messages.disposition IS NULL) ORDER BY senttime
(ActiveRecord::StatementInvalid)

I’ve concluded this is an inevitable vicissitude of our MySQL server
setup. I’m now catching the exception. My question is what I should do
once I catch it.

I am sleeping for 5 minutes and retrying the find(). Is this enough?

Or should I re-run ActiveRecord::Base.establish_connection as well?

It’s a naive question, but I’m naive about databases.

— F

#2

Fritz A. wrote:

I am sleeping for 5 minutes and retrying the find(). Is this enough?

Or should I re-run ActiveRecord::Base.establish_connection as well?

Is this in response to a normal controller request?

You might like to call Message.verify_active_connections!
before your find.


Rails Wheels - Find Plugins, List & Sell Plugins -
http://railswheels.com


#3

Thank you for taking an interest…

Mark Reginald J. wrote:

Fritz A. wrote:

I am sleeping for 5 minutes and retrying the find(). Is this enough?

Or should I re-run ActiveRecord::Base.establish_connection as well?

Is this in response to a normal controller request?

The application is not full Rails; it has no ActiveController instances.

You might like to call Message.verify_active_connections!
before your find.

An interesting proposition. verify_active_connections! has no rdoc
available, and in fact abstract/connection_specification.rb marks it
:nodoc:. Doesn’t this suggest it isn’t for typical use?

I don’t exactly see what the method does when a connection is lost. Its
last construct is a Hash#each_value, which doesn’t suggest a useful
return value. Does it raise an exception? Which? And what am I to do
with it once I rescue it?

Also, while I’m on Rails 1.2.6, I see that the method is deprecated for
2.2.1 (and still undocumented as of 2.1).

An update on my situation – can anyone verify that this is a sensible
approach? I propose to use this function as a wrapper on my find()s:

==
def catching_mysql_error
retval = true
begin
yield
rescue Mysql::Error => e
# Catch the exception and try again in two minutes.
# This also requires checking to mail the periodic report, or the
condition will never be reported.
log_to_report_file “#{Time.now} - ecod-poll - #{e}. Waiting 2
minutes.\n#{e.backtrace}”
check_email_task
sleep 120

  # Disconnect and reconnect the DB connection.
  spec = ActiveRecord::Base.remove_connection
  ActiveRecord::Base.establish_connection spec

  retval = false

end
retval
end

Thanks for your help.

— F

#4

Fritz A. wrote:

The application is not full Rails; it has no ActiveController instances.

That’ll mean you’ll miss out on Rails automatic connection verification.

You might like to call Message.verify_active_connections!
before your find.

An interesting proposition. verify_active_connections! has no rdoc
available, and in fact abstract/connection_specification.rb marks it
:nodoc:. Doesn’t this suggest it isn’t for typical use?

True. But your situation is not typical.

I don’t exactly see what the method does when a connection is lost. Its
last construct is a Hash#each_value, which doesn’t suggest a useful
return value. Does it raise an exception? Which? And what am I to do
with it once I rescue it?

You just call it (ignoring any return value) before your find
to ensure the model’s DB connection is established.

Also, while I’m on Rails 1.2.6, I see that the method is deprecated for
2.2.1 (and still undocumented as of 2.1).

On later versions of AR you can instead do Message.connection.verify!


Rails Wheels - Find Plugins, List & Sell Plugins -
http://railswheels.com


#5

Fritz A. wrote:

I’m still working on recovering from a Mysql::Error (MySQL server has
gone away: SELECT…). I constructed a function to wrap around Active
Record finds, which would rescue Mysql::Error, log the event, and
attempt to reconnect after a pause.

The rescue does not trap the exception. Can anyone help me figure out
why? The code sample below shows what’s on the call stack.

Further question: The full exception message (less the stack trace) is

==
/usr/lib64/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in
log': Mysql::Error: MySQL server has gone away: SELECT * FROMmessagesWHERE (messages.disposition` IS NULL) ORDER BY senttime
(ActiveRecord::StatementInvalid)

Note the “ActiveRecord::StatementInvalid.” Is that the exception I
should have been rescuing?

— F

#6

I’m still working on recovering from a Mysql::Error (MySQL server has
gone away: SELECT…). I constructed a function to wrap around Active
Record finds, which would rescue Mysql::Error, log the event, and
attempt to reconnect after a pause.

The rescue does not trap the exception. Can anyone help me figure out
why? The code sample below shows what’s on the call stack.

This is a non-web application, using ActiveRecord stand-alone.

The next thing I’m going to try is “rescue Exception => e”. However,
this error is rare, so I’m asking for your help in parallel.

$ rails --version
Rails 2.2.2
$ ruby -v
ruby 1.8.5 (2006-08-25) [x86_64-linux]

— F

==
def catching_mysql_error
retval = true
begin
yield # The exception is raised inside this “yield.”
rescue Mysql::Error => e
# PROBLEM: This rescue is never reached on a Mysql::Error
# (MySQL server has gone away) raised in the yielded
# Messages.find_all_by_disposition in the main loop.

  puts "#{Time.now} - ecod-poll - #{e}. Waiting 2 

minutes.\n#{e.backtrace}"
sleep 120

  # Disconnect and reconnect the DB connection.
  # NOTE: A better way to do this has been suggested, but
  # these lines are never reached, so I don't think they're the
  # problem.
  spec = ActiveRecord::Base.remove_connection
  ActiveRecord::Base.establish_connection spec

  retval = false

end
retval
end

The yielded (and exception-raising) statement is in this loop:

loop do
newMessages = nil
success = catching_mysql_error do
newMessages = Message.find_all_by_disposition nil, :order =>
“senttime”
# Raises Mysql::Error
end
next if ! success

… further work on newMessages …

end


#7

I got this issue in JRuby / tomcat / rails before using JNDI.
Basically the issue was prior to rails 2.2 there was no connection
pool so if the timeout value in mysql hit a stale connection I got
this. So we implemented DBCP connection pooling in tomcat which
forced a test on the connection before using it.

Now with rails and connection pooling its probably worth looking to
see if the new code supports something like this.

I will say that the “gone away…” errors are gone but i still see a
lot of “broken pipe” errors which I am trying to resolve.

Adam

On Mon, Jan 26, 2009 at 12:53 PM, Fritz A.