I have a simple Sinatra application hosted on Heroku and using Sequel to connect to a MySql database through the ClearDB addon.
The application works fine, except when it sits idle for more than a minute. In that case, the first request I make gives a "500 Internal Server Error", which heroku logs reveals to be:
sequel::DatabaseDisconnectError - Mysql::Error: MySQL server has gone away
If I refresh the page after this error, it works fine, and the error will not return until the application sits idle for another minute or so.
The application is running two dynos, so the problem is not being caused by the Heroku dyno idling you might see on a free account. I contacted ClearDB support, and they gave me this advice:
if you are using connection pooling, then you should set the idle timeout at just below 60 seconds and/or set a keep-alive as I mentioned below. If you are not using connection pooling, then you must make sure that the app actually closes connections after queries and doesn't rely on the network timeout to shut them down.
I understand that I could create a cron job to hit the server every 30s or so, but that seems an inelegant solution to the problem. The other suggestion about making sure the application closes connections I don't understand. I'm just using Sequel to make queries, I assumed that Sequel manages the connections for me under the hood. Do I need to configure it to ensure that it closes connections? How would I do that?
Your connection times out which is no big deal. Sequel can deal with that situation if you add the connection_validator extension to your DB:
DB.extension(:connection_validator)
As described in the documentation this extension
"detects an invalid connection, […] removes it from the pool and tries the next available connection, creating a new connection if no available connection is valid"