After our Ruby on Rails application has run for a while, it starts throwing 500s with "MySQL server has gone away". Often this happens overnight. It's started doing this recently, with no obvious change in our server configuration.

11 Answers
11

This is probably caused by the persistent connections to MySQL going away (time out is likely if it's happening over night) and Ruby on Rails is failing to restore the connection, which it should be doing by default:

In the file vendor/rails/actionpack/lib/action_controller/dispatcher.rb is the code:

Yeah; I got past the problem by catching ActiveRecord::StatementInvalid once and manually calling ActiveRecord::Base.verify_active_connections! when I ran into this.
–
connyJan 25 '10 at 1:06

@Conny, where does ActiveRecord::StatementInvalid go? in application_controller.rb? Or elsewhere?
–
Christian FazziniDec 12 '11 at 18:21

1

@ChristianFazzini, this was inside a stand alone daemon which served an application-specific purpose: I never had this problem inside a plain old Rails web application. If I had, by now I would be using Rails 2.3 or newer and use the reconnect option as proposed by mixonic
–
connyDec 13 '11 at 4:01

As the other contributors to this thread have said, it is most likely that MySQL server has closed the connection to your Ruby on Rails application because of inactivity. The default timeout is 28800 seconds, or 8 hours.

Although the documentation doesn't indicate it, a value of 0 may disable the timeout completely, but you would need to experiment as this is just speculation.

There are however three other situations that I know of that can generate that error. The first is the MySQL server being restarted. This will obviously drop all the connections, but as the MySQL client is passive, and this won't be noticed till you do the next query.

The second condition is if someone kills your query from the MySQL command line, and this also drops the connection, because it could leave the client in an undefined state.

The last is if your MySQL server restarts itself due to a fatal internal error. That is, if you are doing a simple query against a table and instantly see 'MySQL has gone away', I'd take a close look at your server's logs to check for hardware error, or database corruption.

In rails apps I have worked on I have not seen Active Record reconnect during the current request, but it does reconnect on the next request, therefore this can be a temporary condition if you can copy one exception per mongrel.
–
Dave CheneySep 19 '08 at 13:51

You need to make sure that the number of connections you're making in your Ruby on Rails application is less than the maximum allowed number of connections. Note that extra connections can be coming from your cron jobs, delayed_job processes (each would have the same pool size in your database.yml), etc.

Monitor the SQL connections as you go through your application, run processes, etc. by doing the following in MySQL:

show status where variable_name = 'Threads_connected';

You might want to consider closing connections after a Thread finishes execution as database connections do not get closed automatically (I think this is less of an issue with Ruby on Rails 4 applications Reaper):

Thread.new do
begin
# Thread work here
ensure
begin
if (ActiveRecord::Base.connection && ActiveRecord::Base.connection.active?)
ActiveRecord::Base.connection.close
end
rescue
end
end
end

I had this problem in a Ruby on Rails 3 application, using the mysql2 gem. I copied out the offending query and tried running it in MySQL directly, and I got the same error, "MySQL server has gone away.".

The query in question was very, very large. A very large insert (+1 MB). The field I was trying to insert into was a TEXT column and their max size is 64 KB. Rather than throwing an errorm, the connection went away.

I increased the size of the field and got the same thing, so I'm still not sure what the exact issue was. The point is that it was in the database due to some strange query. Anyway!