"Mysql server has gone away" with Rails

Question

After our rails app 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.

 Mysql::Error: MySQL server has gone away: SELECT * FROM `widgets`

Restarting the mongrels (not the mysql server) fixes it.

Any hints?

Answer 1

The connection to the MySQL server is probably timing out.

You should be able to increase the timeout in MySQL, but for a proper fix, have your code check that the database connection is still alive, and re-connect if it's not.

Answer 2

Do you monitor the number of open mysql connections or threads? What is your mysql.ini settings for max_connections?

mysql> show status;

Look at Connections, Max_used_connections, Threads_connected, and Threads_created.

You may need to increase the limits in your mysql config, or perhaps rails is not closing the connection properly*.

Note: I've only used rails briefly..

Here is the mysql docs for server status.

http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html

Answer 3

This is probably caused by the persistent connections to mysql going away (time out is likely if its happening over night) and rails 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:

if defined?(ActiveRecord)
  before_dispatch { ActiveRecord::Base.verify_active_connections! }
  to_prepare(:activerecord_instantiate_observers) {ActiveRecord::Base.instantiate_observers }
end

the method verify_active_connections! performs several actions, one of which is to recreate any expired connections.

The most likely cause of this error is that this is because a monkey patch has redefined the dispatcher to not call verify_active_connections!, or verify_active_connections! has been changed etc.

Answer 4

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

set-variable = wait_timeout=86400

Adding this line to your /etc/my.cnf will raise the timeout to 24 hours http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_timeout

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, 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, 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.

Answer 5

Rails 2.3 has a reconnect option for your database connection:

production:
  # Your settings
  reconnect: true

See:

http://guides.rubyonrails.org/2_3_release_notes.html#reconnecting-mysql-connections

http://groups.google.com/group/rubyonrails-core/browse_thread/thread/49d2a7e9c96cb9f4?pli=1

Good luck!