Mysql server has gone away?


#1

What on earth would cause the following error? I have multiple sites
using the same lighttpd and msyql and they are working just fine.

ActiveRecord::StatementInvalid (Mysql::Error: MySQL server has gone
away: SELECT * FROM contents WHERE (contents.key = ‘index’ ) LIMIT
1):
/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:78:in
log' /vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:185:inexecute’
/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:331:in
select' /vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:176:inselect_all’
/vendor/rails/activerecord/lib/active_record/base.rb:445:in
find_by_sql' /vendor/rails/activerecord/lib/active_record/base.rb:409:infind’
/vendor/rails/activerecord/lib/active_record/base.rb:407:in find' /vendor/rails/activerecord/lib/active_record/deprecated_finders.rb:22:infind_first’
/vendor/rails/activerecord/lib/active_record/base.rb:996:in send' /vendor/rails/activerecord/lib/active_record/base.rb:996:inmethod_missing’
/app/controllers/contents_controller.rb:12:in show' /vendor/rails/actionpack/lib/action_controller/base.rb:854:insend’
/vendor/rails/actionpack/lib/action_controller/base.rb:854:in
perform_action_without_filters' /vendor/rails/actionpack/lib/action_controller/filters.rb:332:inperform_action_without_benchmark’
/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:69:in
perform_action_without_rescue' /vendor/rails/actionpack/lib/action_controller/benchmarking.rb:69:inmeasure’
/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:69:in
perform_action_without_rescue' /vendor/rails/actionpack/lib/action_controller/rescue.rb:82:inperform_action’
/vendor/rails/actionpack/lib/action_controller/base.rb:369:in send' /vendor/rails/actionpack/lib/action_controller/base.rb:369:inprocess_without_session_management_support’
/vendor/rails/actionpack/lib/action_controller/session_management.rb:116:in
process' /vendor/rails/railties/lib/dispatcher.rb:38:indispatch’
/vendor/rails/railties/lib/fcgi_handler.rb:141:in process_request' /vendor/rails/railties/lib/fcgi_handler.rb:53:inprocess!’
/vendor/rails/railties/lib/fcgi_handler.rb:52:in each_cgi' /usr/local/lib/ruby/gems/1.8/gems/fcgi-0.8.6.1/./fcgi.rb:597:ineach’
/usr/local/lib/ruby/gems/1.8/gems/fcgi-0.8.6.1/./fcgi.rb:597:in
each_cgi' /vendor/rails/railties/lib/fcgi_handler.rb:52:inprocess!’
/vendor/rails/railties/lib/fcgi_handler.rb:22:in `process!’
/home/joevd/cisv/current/public/dispatch.fcgi:24


#2

For example, when you fork a child ruby interpreter. On exit, it closes
mysql
socket.

Kent.


#3

I ain’t forking anything, as far as I know.


#4

somebody posted their solution to this problem a few days ago.
wouldn’t hurt to give it a shot:

def something
begin
fork do
emails.each { |email|
email.send # calls SMTP
email.log # calls ActiveRecord subclass
}
end

  arInstance.do_something # do a bunch of AR stuff

rescue ActiveRecord::StatementInvalid => e
  if e.to_s =~ /away/
         ActiveRecord::Base.establish_connection and retry
  else
         raise e
  end
end

end

OR, perhaps

def monitor_connection
begin
yield

rescue ActiveRecord::StatementInvalid => e
  if e.to_s =~ /away/
         ActiveRecord::Base.establish_connection and retry
  else
         raise e
  end
end

end

monitor_connection do
fork do
emails.each { |email|
email.send # calls SMTP
email.log # calls ActiveRecord subclass
}
end

  arInstance.do_something # do a bunch of AR stuff

end

OR maybe

def something
fork do
emails.each { |email|
email.send # calls SMTP
monitor_connection { email.log }
}
end

  monitor_connection do
     arInstance.do_something
  end

end

Gerret A. wrote:

  email.send  # calls SMTP

}
end

which didnt help.

Any ideas from anybody are much appreciated – I’m stumped. How can I
do AR work in a forked process without borking my application?

cheers
Gerret

[snip]


#5

Hi Kent,

I saw your answer to Joe (below) and hope you can shed light on a
similar issue I’m having. I need to make AR calls in my main process
as well as in a forked process simultaneously. It looks something like
this:

def something
fork do
emails.each { |email|
email.send # calls SMTP
email.log # calls ActiveRecord subclass
}
end

arInstance.do_something # do a bunch of AR stuff
end

With the above code I get an ActiveRecord::StatementInvalid error
(sample stacktrace below). Not always, but regularly, and I can’t
narrow down the circumstances that make it happen.

You write that the DB socket is closed when the forked process exits.
So I’m guessing that is my problem.

But how to work around that?

I tried this:

fork do
at_exit {
ActiveRecord::Base.establish_connection
}
end

which didnt help.

Any ideas from anybody are much appreciated – I’m stumped. How can I
do AR work in a forked process without borking my application?

cheers
Gerret

Sample Stacktrace (edgerails):

ActiveRecord::StatementInvalid: Mysql::Error: : SELECT COUNT(*) FROM
logged_emails
/home/gerret/railswork/sosamerica/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:88:in
log' /home/gerret/railswork/sosamerica/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:181:inexecute’
/home/gerret/railswork/sosamerica/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:327:in
select' /home/gerret/railswork/sosamerica/vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:176:inselect_one’
/home/gerret/railswork/sosamerica/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:16:in
select_value' /home/gerret/railswork/sosamerica/vendor/rails/activerecord/lib/active_record/base.rb:518:incount_by_sql’
/home/gerret/railswork/sosamerica/vendor/rails/activerecord/lib/active_record/base.rb:511:in
count' test/unit/email_batch_sender_test.rb:60:intest_emails_get_logged’


#6

Lou, thanks much. I hadn’t caught the previous thread with Julik’s
workaround or the discussion on bug #428, only read them just now.

Your monitor_connection {…} method does fix my unit test. Great. The
special nature of the whole forking business still poses a problem
though, because I cannot know in advance when the forked process will
terminate and take down the DB connection. I would have to wrap any
code that calls AR in a monitor_connection block, and I really don’t
want to do that. Firing a dummy statement in a before_filter doesn’t
sound too good either.

Reading through the bug discussionI’m not sure that my issue is
exactly the same as that addressed by bug #428. I’m running on
edgerails and the ticket’s status is ‘fixed’. Yet without the
monitor_connection workaround, I keep getting those StatementInvalid
exceptions.

Has the fix in edgerails taken care of this problem for everybody
else? Is it possible that the bugfix in edgerails takes care of timed
out connections, but not connections disrupted by an exiting subshell?

cheers
Gerret


#7

I’m not sure what db you’re using but if it’s MySQL then it looks like
all db calls are bottlenecked down to just 3 methods:
def command
def read
def write
in the mysql.rb file. So they would be pretty easy to trap; much easier
than wrapping all of your db calls, as you have pointed out.


#8

Lou thats a good point and I am indeed using MySQL, so will give that
a shot and report back on whether it fixed the problem.

Would there be a reason why the adapter doesn’t already do this
(reconnect when connection lost)? I can see that its not great for
performance, and there’s no reason for the precaution when not
forking. But it doesn’t feel right that using fork+AR would require
your hacking your DB adapter. Maybe an ActiveRecord::base option
‘reconnect_on_lost_connect’ would be the way to go.

At any rate thanks for the suggestion and I’ll try it out later on.

cheers
Gerret


#9

I don’t think this is AR related problem. You will experience the same
problem
just by using mysql driver alone. Everytime you fork a child ruby
process, it
inherits the same mysql connection from the parent and on exit it closes
it.

When I need to fork a background process, I create a special daemon
using Drb
library that accepts requests and processes them for me. So that I can
avoid
forking at all.

Kent.


#10

If the database is properly tuned then connections are automatically
recycled after X number of seconds. In MySQL, it’s after wait_timeout
seconds. There really is no need to call an explicit ‘close’ on the
connection because MySQL does it automatically. I would hope that we
are given a config option in the future that precludes db connections
from explicitly being closed. That would solve this problem, and make
the code simpler.

As you point out, trapping for the lost connection is expensive, and
is probably why it is not being done.

I like the optional ‘reconnect_on_lost_connect’ idea.

I suspect that there’s not a lot of people doing "fork"s with AR, which
is probably why this AR problem exists.


#11

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You write that the DB socket is closed when the forked process exits.
So I’m guessing that is my problem.

Any ideas from anybody are much appreciated – I’m stumped. How can I
do AR work in a forked process without borking my application?

Open a new connection in the forked child.

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDmd9qAQHALep9HFYRAp0wAJ49xiLkudW6Rh23hN639SONCp8KLwCfVesF
krD7JqkztyWZXPqBuaxCP7M=
=nhjx
-----END PGP SIGNATURE-----


#12

Resurrecting an old but helpful thread just to note for future Googlers
that vanek’s below solution works in a pinch (even if it might not be
the most efficient)-- except “if e.to_s =~/away/” doesn’t catch all
possible exceptions that can raised by the db connection your forked
process closed upon exit. Every so often a forked process will exit in
the middle of an in-process db query in the parent, and you’ll get a
still-uncaught exception, “Lost connection to MySQL server during
query”. So you should at least do “if e.to_s =~ /has gone away/ or
e.to_s =~ /Lost connection/” to catch that one as well.

vanek wrote:

somebody posted their solution to this problem a few days ago.
wouldn’t hurt to give it a shot:

def something
begin
fork do
emails.each { |email|
email.send # calls SMTP
email.log # calls ActiveRecord subclass
}
end

  arInstance.do_something # do a bunch of AR stuff

rescue ActiveRecord::StatementInvalid => e
  if e.to_s =~ /away/
         ActiveRecord::Base.establish_connection and retry
  else
         raise e
  end
end

end


#13

“The child process can exit using Kernel.exit! to avoid running any
at_exit functions.”

This seems to have fixed the issue for me. I have no at_exit functions
I need executed.


#14

jeremy wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You write that the DB socket is closed when the forked process exits.
So I’m guessing that is my problem.

Any ideas from anybody are much appreciated – I’m stumped. How can I
do AR work in a forked process without borking my application?

Open a new connection in the forked child.

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDmd9qAQHALep9HFYRAp0wAJ49xiLkudW6Rh23hN639SONCp8KLwCfVesF
krD7JqkztyWZXPqBuaxCP7M=
=nhjx
-----END PGP SIGNATURE-----

Works for me

ActiveRecord::Base.connection.reconnect!

Was having a problem with simple daemon and this fixed it.