I used to get excited when I’d come up with some hack that would work-around some technical issue I was having with a web app. These days, it just depresses me that I have to do them.
One of my hosting providers has the wait_timeout setting on their MySQL server set to 5 minutes. What that means is that after five minutes, any connection that hasn’t seen any activity gets dumped. I guess it’s not uncommon for shared hosting providers to set this low, the trouble comes in when you’re using an ODBC connection to that database which attempts to re-use the connection on subsequent queries.
Users that are clicking between pages on this site will have no problems. Loading a new page executes a query which resets that 5 minute countdown. The problem arises if someone gets up to go pee, grab a cup of coffee, and then comes back. If it takes them 6 minutes, the next page they go to load will just chuck a “MySQL server has gone away” error at them.
I could handle this by automatically logging users out after 5 minutes of inactivity… but I think that’s a little harsh, don’t you? I usually like to give users an hour (give or take) before I’ll give them the automatic boot.
Back to the lame hack I implemented: after going back and forth with the non-English, lazy as fuck tech support for days, I just gave up. I played around with caching my queries a bit — which works great, but there are just some queries you can’t cache. I thought maybe about implementing some AJAX behind-the-scenes query that gets executed every 4 minutes to keep the connection alive — but what about those edge cases where the end-user has JavaScript turned off?
In the end, I decided to do something similar anyhow. I created a page on the server which runs a query. A really simple query, but just enough to keep that ODBC connection from dying after 5 minutes. That page is now getting called with a cron job from another host every 4 minutes.
I call this a dumb hack, because if the database server was just configured with a wait_timeout of an hour (did I forget to mention that the default setting is 8 hours?), I wouldn’t have to do this. The other part that pisses me off is that I’ve had to do all of the tech support legwork on this — I reported the initial error, didn’t know what was causing it at that point, requested to be moved to a new DB (which they did, but it took them a week and a half), still had the error, ran some tests to isolate the problem, did the research on wait_timeout, and then had to come up with a work-around after everything. If the tech support had been ever so slightly helpful in the beginning, and told me what was causing the error instead of playing dumb about the whole thing, I could have just cut to the chase.
This is cross-posted in my review category because the host in question is IX web hosting. They have the hands-down WORST technical support (outsourced to Russia as far as I can tell) of anybody I’ve ever had to deal with. If you’re willing to do tech support for yourself, feel free to host with them. Their reliability/uptime is pretty good.
