Rails - Jan 2009 - Recovering Mysql::Error - Must I redo establish_connection?

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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Fritz Anderson wrote:
> 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?
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> 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
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---