Ruby Forum Ferret > MySQL has gone away

Hi,

in a production environment we are running into "MySQL server has gone
away" errors (see below) when we call rebuild_index in aaf. This happens
sporadically.

Any suggestions for what might be wrong or workarounds? I guess we try a
reconnect before we call rebuild_index?

Matthew



ActiveRecord::StatementInvalid (Mysql::Error: MySQL server has gone
away: SELECT count(*) AS count_all FROM bands ):
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/abstract_adapter.rb:128:in
`log'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/mysql_adapter.rb:243:in
`execute'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/mysql_adapter.rb:399:in
`select'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in
`select_one'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in
`select_value'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/calculations.rb:212:in
`execute_simple_calculation'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/calculations.rb:121:in
`calculate'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/calculations.rb:117:in
`catch'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/calculations.rb:117:in
`calculate'
    (druby://localhost:3009)
/usr/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/calculations.rb:45:in
`count'
    (druby://localhost:3009)
/var/www/web1/oms/current/script/../config/../vendor/plugins/acts_as_ferret/lib/ferret_extensions.rb:22:in
`index_model'
    (druby://localhost:3009)
/var/www/web1/oms/current/script/../config/../vendor/plugins/acts_as_ferret/lib/ferret_extensions.rb:9:in
`index_models'
    (druby://localhost:3009)
/var/www/web1/oms/current/script/../config/../vendor/plugins/acts_as_ferret/lib/ferret_extensions.rb:9:in
`each'
    (druby://localhost:3009)
/var/www/web1/oms/current/script/../config/../vendor/plugins/acts_as_ferret/lib/ferret_extensions.rb:9:in
`index_models'
    (druby://localhost:3009)
/var/www/web1/oms/current/script/../config/../vendor/plugins/acts_as_ferret/lib/ferret_server.rb:93:in
`rebuild_index'
    (druby://localhost:3009)
/var/www/web1/oms/current/script/../config/../vendor/plugins/acts_as_ferret/lib/ferret_server.rb:113:in
`with_class'
    (druby://localhost:3009)
/var/www/web1/oms/current/script/../config/../vendor/plugins/acts_as_ferret/lib/ferret_server.rb:88:in
`rebuild_index'
    /vendor/plugins/acts_as_ferret/lib/remote_index.rb:16:in `send'
    /vendor/plugins/acts_as_ferret/lib/remote_index.rb:16:in
`method_missing'
    /vendor/plugins/acts_as_ferret/lib/class_methods.rb:15:in
`rebuild_index'

I need to add that we are running the integrated aaf Ferret Drb Server, 
so I guess it is the Drb Server that is losing the connection? How can I 
get it to reconnect without throwing the error?

On 2007-09-03, at 3:52 PM, Matthew Langham wrote:

> I need to add that we are running the integrated aaf Ferret Drb  
> Server,
> so I guess it is the Drb Server that is losing the connection? How  
> can I
> get it to reconnect without throwing the error?

Hey ..

we've got the same error with our non-aaf backgroundrb solution ..

that's what we're doing:

begin
   index_object( record )
rescue ActiveRecord::RecordNotFound
   @logger.warn "Object gone"
rescue ActiveRecord::StatementInvalid
   @logger.warn "MySQL is gone.."
   clazz.connection.reconnect!
   enqueue_object( record )
end

So basically just catch the StatementInvalid Exception and
try again after you reconnected to the database..

I guess Jens can add something like that to AAF quite easily :)

Ben

Hi!

On Mon, Sep 03, 2007 at 03:42:07PM +0200, Matthew Langham wrote:
> Hi,
> 
> in a production environment we are running into "MySQL server has gone
> away" errors (see below) when we call rebuild_index in aaf. This happens
> sporadically.

Does it happen after a long time of inactivity on the DRb server's side?

If yes, a look at mysql's wait_timeout setting, and
ActiveRecord::Base.verification_timeout, wich should be less than the
former, might help.

Setting ActiveRecord::Base.allow_concurrency to true solved this
problem for me once, however I'm not sure why and it was not
ferret-related.

Cheers,
Jens

--
Jens Krämer
webit! Gesellschaft für neue Medien mbH
Schnorrstraße 76 | 01069 Dresden
Telefon +49 351 46766-0 | Telefax +49 351 46766-66
kraemer@webit.de | www.webit.de

Amtsgericht Dresden | HRB 15422
GF Sven Haubold, Hagen Malessa

>
> Setting ActiveRecord::Base.allow_concurrency to true solved this
> problem for me once, however I'm not sure why and it was not
> ferret-related.

We set the following .. but still - the sql connection is lost from time
to time..

ActiveRecord::Base.allow_concurrency = true
ActiveRecord::Base.verification_timeout = 10

Ben

Are you running Ubuntu?

If yes, try installing the libmysqlclient-dev package (latest one you
have), then run "sudo gem install mysql". You get my meaning. If
you're not running Ubuntu, get the mysql client development headers,
which are required by the mysql gem, then install the gem.

I had a similar issue to yours, then I installed the C based
extensions. Problem went away.

> I had a similar issue to yours, then I installed the C based
> extensions. Problem went away.

Julio,

i got the mysql bindings (version 2.7) installed. But there is a newer
version (2.7.3). I will update them and take a look. Thanks for the
suggestion :)

Ben

On 03.09.2007, at 16:27, Benjamin Krause wrote:

>>
>> Setting ActiveRecord::Base.allow_concurrency to true solved this
>> problem for me once, however I'm not sure why and it was not
>> ferret-related.
>
> ActiveRecord::Base.allow_concurrency = true
> ActiveRecord::Base.verification_timeout = 10

ActiveRecord just isn't reentrant. Period ;)

There are supposedly ways to make it work using allow_concurrency,
reconnect and whatever. But...

>  .. but still - the sql connection is lost from time to time..

Exactly.

I rebuild my index in a separate process and directory. When it's
done, I tell the index server via DRb that a new index is available
and it'll switch to the new directory. Works like a charm.

Cheers,
Andy

Andreas Korth wrote:

> 
> I rebuild my index in a separate process and directory. When it's
> done, I tell the index server via DRb that a new index is available
> and it'll switch to the new directory. Works like a charm.

Do you use backgroundrb for this?. I'm coming up against similar 
problems with long running processes which need an ActiveRecord 
connection. I briefly tried to use fork() to background it but that 
seemed to give me problems with acts-as-authenticated.

On Mon, Sep 03, 2007 at 04:11:34PM +0200, Benjamin Krause wrote:
> 
>    clazz.connection.reconnect!
>    enqueue_object( record )
> end
> 
> So basically just catch the StatementInvalid Exception and
> try again after you reconnected to the database..
> 
> I guess Jens can add something like that to AAF quite easily :)

Okay, I just committed this. According to my limited testing it works as
intended - close db connection with Model.connection.disconnect, and it
will reconnect again.

Please give it a try and report the outcome here :-)

cheers,
Jens

--
Jens Krämer
http://www.jkraemer.net/ - Blog
http://www.omdb.org/     - The new free film database

On 05.09.2007, at 18:40, Ed -- wrote:

>> I rebuild my index in a separate process and directory. When it's
>> done, I tell the index server via DRb that a new index is available
>> and it'll switch to the new directory. Works like a charm.
>
> Do you use backgroundrb for this?. I'm coming up against similar
> problems with long running processes which need an ActiveRecord
> connection. I briefly tried to use fork() to background it but that
> seemed to give me problems with acts-as-authenticated.

I use fork without problems. Here's a snippet from my controller
which triggers the rebuild:

     def exec_command(command)
       dbconfig = ActiveRecord::Base.remove_connection
       fork do
         begin
           ActiveRecord::Base.establish_connection(dbconfig)
           `cd #{RAILS_ROOT} && script/runner -e #{ENV['RAILS_ENV']}
'#{index_class}.#{command}'`
         ensure
           ActiveRecord::Base.remove_connection
         end
       end
       ActiveRecord::Base.establish_connection(dbconfig)
     end

As you can see, I'm actually running a shell script which starts a
whole new Rails process via script/runner. Note that
ActiveRecord::Base.allow_concurrency is NOT set to true.

I don't remember why this remove/establish connection was necessary,
but it's definitely needed.

Cheers,
Andy

I did a hack in the abstract connection code to enable the 
auto-reconnection property.

Ken Wei wrote:
> I did a hack in the abstract connection code to enable the 
> auto-reconnection property.
Hi Ken,

Where would this hack go? I am not sure I understand what you did there.

Bruno Miranda wrote:
> Ken Wei wrote:
>> I did a hack in the abstract connection code to enable the 
>> auto-reconnection property.
> Hi Ken,
> 
> Where would this hack go? I am not sure I understand what you did there.

module ActiveRecord
  class Base

    class << self
      alias :old_connection :connection
      def connection
        self.verify_active_connections!
        old_connection
      end
    end

  end
end