Forum: Ruby on Rails Recovering Mysql::Error - Must I redo establish_connection?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
06fa952559609d00a12ad22d74335282?d=identicon&s=25 Fritz Anderson (fritza)
on 2009-01-21 17:29
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
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2009-01-22 02:45
(Received via mailing list)
Fritz Anderson 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
06fa952559609d00a12ad22d74335282?d=identicon&s=25 Fritz Anderson (fritza)
on 2009-01-22 16:19
Thank you for taking an interest...

Mark Reginald James wrote:
> Fritz Anderson 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
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2009-01-23 11:39
(Received via mailing list)
Fritz Anderson 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
06fa952559609d00a12ad22d74335282?d=identicon&s=25 Fritz Anderson (fritza)
on 2009-01-26 16:41
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
06fa952559609d00a12ad22d74335282?d=identicon&s=25 Fritz Anderson (fritza)
on 2009-01-26 18:53
Fritz Anderson 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 * FROM
`messages` WHERE (`messages`.`disposition` IS NULL)  ORDER BY senttime
(ActiveRecord::StatementInvalid)
==

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

    — F
F15fdc7cb2e911b3808837f2be244add?d=identicon&s=25 AD (Guest)
on 2009-01-27 05:04
(Received via mailing list)
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 Anderson
This topic is locked and can not be replied to.