mysql_retry_lost_connection

Has your Rails app ever thrown the 'Lost Connection to MySQL server' exception? I love that error. How about 'MySQL server has gone away?' That.s a good one too. If you're a fan of these exceptions then stop reading now because I'm about to make them go away.

Rails is set up to handle database connections in such a way that the likelihood of hitting these errors increases with the size of your code base. ActiveRecord maintains one database connection per model. If you use that database command often enough then it times out resulting in the errors listed above. You can set the timeout interval in your environment.rb with this:

ActiveRecord::Base.verification_timeout = 14400

If your app only has a handful of models and you hit actions that trigger activity on each model.s connection frequently enough then bumping up the interval in the above config might just be a good enough solution for you. But when you keep adding models to your app to keep up with feature requests then you.ll eventually have a user execute a code path on a process whose database connection has timed out. We have 123 models at Zvents and counting. We got to the point where Increasing the verification_timeout wasn't really helping - we still hit a handful of connections timeouts on models that didn't receive a lot of usage (backend stuff mostly).

The solution, quite simply, is to have the database adapter reconnect to the database when it detects a lost connection. It's not an original or groundbreaking idea, but I couldn.t any plugins so I threw together the mysql_retry_lost_connection plugin. It looks like this:

module ActiveRecord module ConnectionAdapters class MysqlAdapter def execute(sql, name = nil) #:nodoc: reconnect_lost_connections = true begin log(sql, name) { @connection.query(sql) } rescue ActiveRecord::StatementInvalid => exception RAILS_DEFAULT_LOGGER.info("ActiveRecord::StatementInvalid Error: #{exception.message}\n#{exception.backtrace.join("\n")}") if reconnect_lost_connections and exception.message =~ /(Lost connection to MySQL server during query|MySQL server has gone away)/ reconnect_lost_connections = false reconnect! retry elsif exception.message.split(":").first =~ /Packets out of order/ raise ActiveRecord::StatementInvalid, "'Packets out of order' error was received from the database. Please update your mysql bindings (gem install mysql) and read http://dev.mysql.com/doc/mysql/en/password-hashing.html for more information. If you're on Windows, use the Instant Rails installer to get the updated mysql bindings." else raise end end end end end end

We've been running it in production for a couple weeks with no side effects - and lost connections are just a happy memory now.

I'm not entirely happy with the code. Instead of clobbering the existing execute method it should probably use alias_method to wrap the existing method. That would make it more resilient to changes to the underlying code in future Rails releases. But it works for me and I'm hitting the sack. Free beer to the first person who rewrites it that manner or improves it any other way.