connect_timeout on mySQL

Note to myself and to any others that may be experiencing the problem:

If, out of the blue, your ColdFusion pages start giving you errors that the MySQL server has gone away, or that you’ve lost your connection to the database server after a set period of time, your hosting company has made a change to the “connect_timeout” setting on their database server. One of my clients’ hosts has now set this to 15 seconds, so consistently, if you leave a page idle for 15 seconds, you get an error.

The trouble is that MySQL (on a Windows server) uses ODBC to connect to the server. Once the initial query is made, it keeps the connection open in order to re-query the database. Normally, it’s not that big a deal, because as people click through a site, it’s re-querying the database and keeping that connection active, but most hosts have started setting the timeout absurdly low in the name of performance tuning.

You can try to argue with your hosting provider, but chances are that they’ll just shovel a load of BS at you. The most effective way I’ve found of dealing with the problem (and it’s really sad that you should have to do this) is that I’ve set up a page to do a really simple query against the database and I set up a cron job to call that script every “x” minutes (where x is your host’s connect_timeout minus 1). That keeps that database connection open and active.

You have to do this for every DSN you have setup. On one hand, I can sympathize that hosting companies want to keep the timeout low in order to flush old connections. On the other hand, it seriously screws up ODBC users. You just can’t have a web app timeout after 14 seconds… that’s too short a time for a user.

If you start to mysteriously receive database errors on your ColdFusion app (or any app that uses an ODBC connection to the database), check the connection timeout. Betcha it’s set really low.

Update: the simplest solution is that in the ColdFusion Administrator, there is an option to disable persistent connections. That fixes everything.