MySQL has gone away

turnkeytraders · September 3, 2007, 3:42pm

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:inexecute’
(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:inselect_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:inexecute_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:incatch’
(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:incount’
(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:inindex_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:inindex_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:inwith_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:insend’
/vendor/plugins/acts_as_ferret/lib/remote_index.rb:16:in
method_missing' /vendor/plugins/acts_as_ferret/lib/class_methods.rb:15:inrebuild_index’

turnkeytraders · September 3, 2007, 3:52pm

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?

turnkeytraders · September 3, 2007, 4:12pm

On 2007-09-03, at 3:52 PM, Matthew L.ham 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

turnkeytraders · September 3, 2007, 4:28pm

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

turnkeytraders · September 4, 2007, 12:23am

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.

turnkeytraders · September 3, 2007, 4:22pm

Hi!

On Mon, Sep 03, 2007 at 03:42:07PM +0200, Matthew L.ham 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
[email protected] | www.webit.de

Amtsgericht Dresden | HRB 15422
GF Sven Haubold, Hagen Malessa

turnkeytraders · September 4, 2007, 9:34am

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

turnkeytraders · September 4, 2007, 1:35pm

On 03.09.2007, at 16:27, Benjamin K. 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

turnkeytraders · September 5, 2007, 11:10pm

On Mon, Sep 03, 2007 at 04:11:34PM +0200, Benjamin K. 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

turnkeytraders · September 6, 2007, 12:36pm

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

turnkeytraders · September 5, 2007, 6:40pm

Andreas K. 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.

turnkeytraders · August 27, 2008, 5:55am

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.

turnkeytraders · September 11, 2007, 9:12am

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

turnkeytraders · August 27, 2008, 6:00am

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