Web.cacheModule and Expiring DB connections

The Story

A while ago I posted about neko.web.cacheModule, a way to make your site run dramatically faster on mod_neko or mod_tora. After spending some time making ufront compatible with this mode of running, I was excited to deploy my app to our schools with the increased performance.

I deployed, ran some tests, seemed happy, went home and slept well. And woke up the next morning to a bunch of 501 errors.

The Error

The error message was coming from the MySQL connection: “Failed to send packet”.

At first I just disabled caching, got everyone back online, and then went about trying to isolate the issue. It took a while before I could reproduce it and pinpoint it. I figured it was to do with my DB connection staying open between requests, thanks to the new module caching.

A google search showed only one Haxe related result – someone on IRC that mentioned when they sent too many SQL queries it sometimes bombed out with this error message. Perhaps leaving it open eventually overran some buffer and caused it to stop working? Turns out this was not the case, I used `ab` (Apache Benchmark tool) to query a page 100,000 times and still didn’t see the error.

Eventually I realised it was to do with the MySQL Server dropping the connection after a period of inactivity. The `wait_timeout` variable was set to 28800 by default: so after 8 hours of inactivity.  So long enough that I didn’t notice it the night before, but short enough that the timeout occured overnight while all the staff were at home or asleep… So the MySQL server dropped the connection, and my Haxe code did not know to reconnect it. Whoops.

The Solution

I looked at Nicolas’s hxwiki source code, which runs the current Haxe site for some inspiration on the proper way to approach this for `neko.Web.cacheModule`. His solution: use http://api.haxe.org/sys/db/Transaction.html#main. By the looks of it, this will wrap your entire request in an SQL transaction, and should an error be thrown, it will rollback the transaction. Beyond that, it will close the DB connection after each request. So we have a unique DB connection for each request, and close it as soon as the request is done.

My source code looks like this:

class Server
{
  static var ufApp:UfrontApplication;

  static function main() {
    #if (neko && !debug) neko.Web.cacheModule(main); #end
    
    // Wrap all my execution inside a transaction
    sys.db.Transaction.main( Mysql.connect(Config.db), function() {
      init();
      ufApp.execute();
    });
  }

  static function init() {
    // If cacheModule is working, this will only run once
    if (ufApp==null) {
      UFAdminController.addModule( "db", "Database", new DBAdminController() );
      ufApp = new UfrontApplication({
        dispatchConfig: Dispatch.make( new Routes() ),
        remotingContext: Api,
        urlRewrite: true,
        logFile: "log/ufront.log"
      });
    }
  }
}