Sinatra and MySQL idle connection timeouts

[Juan Lupión]

Hi,

We have this fast & small API server written in Sinatra and deployed
with Thin. We are using ActiveRecord for data persistence and have
found that after long periods of inactivity the database server drops
the connection and subsequent requests get a "MySQL server has gone
away" error.

Some options that come to mind are:

- running spurious API requests via cron & curl.
- restarting the server periodically with cron.
- handling the ActiveRecord exception and restablishing the
connection.
- setting ActiveRecord::Base.verification_timeout and running
ActiveRecord::Base.verify_active_connections! in a before do ... end
filter.

Are there any other options? What would be the preferred "Sinatra way"
of doing this?

Thanks

[Ryan Tomayko]

I'd go with the before filter. That seems like it would be the most
robust and also would require the least code. It's an interesting
problem. I'd love to hear what you come up with.

Thanks
--
Ryan http://tomayko.com/about

[Alfredo]

Hi there,

Finally, we set a before do .. end filter like this:

before do
ActiveRecord::Base.connection.verify!(3600)
end

so in every request is checked if the connection to the mysql adapter
is active, is not, then is reconnect.


Note that we are using Active Record 1.15.6, so the instance method to
use is .verify!(timeout).

Thanks.

[Craig Jolicoeur]

I believe the AR connection verify method has been deprecated for that
last few revisions now and doesn't actually do anything anymore.

- Craig

[winton]

Sorry to bring this old thread back, but
ActiveRecord::Base.connection.verify! is NOT deprecated, and probably
the right way to handle this situation.

http://github.com/rails/rails/blob/9b8cde41bc84466bf60fc4de6af54dbeb11cc0d6/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb

[candlerb]

The question has come up before, and I think the right solution is to
find out what Rails does to prevent this problem.

The answer is in actionpack/lib/action_controller/dispatcher.rb:

if defined?(ActiveRecord)
before_dispatch
{ ActiveRecord::Base.verify_active_connections! }

So for Sinatra, this should do: (untested)

before do
ActiveRecord::Base.verify_active_connections!
end

I think it is unhelpful of the Rails people to mark this method as
#:nodoc: - ditto for ActiveRecord::Base.reset_subclasses, which you
need if you are reloading your model classes.

Regards,

Brian.

[candlerb]

ActiveRecord documentation patch submitted:
http://rails.lighthouseapp.com/projects/8994/tickets/2192-remove-nodoc-for-activerecord-methods-needed-outside-of-rails