Thursday, June 14, 2007

"MySQL server has gone away" on textdrive

Debugging is always hard, because you have to understand what's going on. In this day and age of leaky abstractions, there's just always more and more to know. This is why I think concepts are important. If you know concepts, you can more readily figure out details.

So I had a quizzing error last weekend that I was scratching my head over for about two days. This was mainly because I was getting the errors from backgroundrb. It would just hang with no exceptions reported. As it turns out, there's a bug in Backgroundrb 0.2.1, the latest version.

Thanks to Mathais on the Backgroundrb mailing list, my problem was exactly as he describes. I monkey patched it and the backgroundrb server log started spewing errors out. It was about MySQL servers going away.

After reading about why MySQL goes away at all, I figured out that one needs to check MySQL's interactive_timeout setting. The database will drop the connection to it from the client (in this case, the web app), if there has been no activity for at least that amount of time. By default, it is set to four hours. On the server I put the app on, however, it is set to 10 minutes.

There were a couple solutions to this. One could be, as I posted before, to retry the connection. The other is a setting that I found in Rails.
ActiveRecord::Base.verification_timeout = 570
I put this in the environment.rb file under config to keep the connection to the database alive. I set the timeout to be under the interactive_timeout, so that Rails will keep telling the MySQL server that it's still around.

I don't know if this is exactly a good idea, since on a shared server, that means everyone will be holding on to connections they're not using. I'm not sure what the performance implications are for a long standing connection is, if there is any. But for now, it seems like it's working.

No comments:

Post a Comment