"MySQL server has gone away" with Ruby on Rails

Question

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.

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

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

How can we fix this?

Answer 1

While forking in Rails.

For anyone running into this while forking in Rails, try clearing the existing connections before forking and then establish a new connection for each fork, like this:

# Clear existing connections before forking to ensure they do not get inherited.
::ActiveRecord::Base.clear_all_connections! 

fork do
  # Establish a new connection for each fork.
  ::ActiveRecord::Base.establish_connection 
  
  # The rest of the code for each fork...
end

See this StackOverflow answer here: https://stackoverflow.com/a/8915353/293280

Answer 2

I had this problem when sending really large statements to MySQL. MySQL limits the size of statements and will close the connection if you go over the limit.

set global max_allowed_packet = 1048576; # 2^20 bytes (1 MB) was enough in my case

Answer 3

Try ActiveRecord::Base.connection.verify! in Ruby on Rails 4. Verify pings the server and reconnects if it is not connected.

Answer 4

First, determine the max_connections in MySQL:

show variables like "max_connections";

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

Answer 5

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!

Answer 6

Ruby on Rails 2.3 has a reconnect option for your database connection:

production:
  # Your settings
  reconnect: true

See:

• Ruby on Rails 2.3 Release Notes, sub section 4.8 Reconnecting MySQL Connections.

• MySQL auto-reconnect revisited

Good luck!

Answer 7

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.

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

Answer 8

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 configuration, or perhaps rails is not closing the connection properly*.

Note: I've only used Ruby on Rails briefly...

The MySQL documentation for server status is in http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html.

Answer 9

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:

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 10

Something else to check is Unicorn config is correct. See before_fork and after_fork handling of ActiveRecord connection here: https://gist.github.com/nebiros/2776085#file-unicorn-rb

Answer 11

Using reconnect: true in the database.yml will cause the database connection to be re-established AFTER the ActiveRecord::StatementInvalid error is raised (As Dave Cheney mentioned).

Unfortunately adding a retry on the database operation seemed necessary to guard against the connection timeout:

begin
  do_some_active_record_operation
rescue ActiveRecord::StatementInvalid => e
  Rails.logger.debug("Got statement invalid #{e.message} ... trying again")
  # Second attempt, now that db connection is re-established
  do_some_active_record_operation
end

Answer 12

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.