I recently wrote a custom program to handle URL rewrites and used the RewriteMap directive to make mod_rewrite use my program. I wrote it in PHP and use MySQL as a back-end to store rules. I just wanted to share one of the problems I had: MySQL drops the connection to my program and therefore a default URL is returned every time. Here is the gist of my code:

#!/usr/bin/php
<?php
    // start the code...
    // connect to MySQL...

    // open standard input and get URLs
    $fd = fopen ("php://stdin", "r");
    while (!feof ($fd)) {
        $input_url = trim (fgets ($fd));    // get a line from STDIN
        // ... construct query and send it to mysql ...
        mysql_query (... your query here ...);
        // ... get result and send back rewritten URL
        print $rewritten_url . "n";
        flush ();
    }

    // ... end mysql connection, and program ...
?>

The above code worked well, except there was one problem: it would work for a while, and after a long period of inactivity, it’d stop working. Keep in mind that I was developing this under a test server, which was not live to the public. So inactivity here was due to the fact that I’d only work daytime and at night no one would be accessing the server, and when I use the site the next day, the URL re-writer would stop working (i.e. my program would return a default, not-found URL).

At first I thought there was a problem with mod_rewrite, but then I realized that when the server is inactive, MySQL drop the connection to my program so all the SQL queries fail the next day after that inactivity, forcing me to reload the httpd server. This happens because mod_rewrite opens a persistent connection to the program and every time it gets a URL matching your parameters, it send it to the STDIN of the program and gets a URL out. So at night when the server is inactive, there are no queries run against MySQL and therefore that MySQL connection is dropped (I have my MySQL server drop connections after 3600 seconds of inactivity).

To fix this problem, I added a couple extra lines of code inside the main while loop. I’m basically checking to see if my connection with MySQL is still alive, and if it isn’t, then re-connect.

while (!feof ($fd)) {
    $input_url = trim (fgets ($fd));    // get a line from STDIN
    if (!mysql_ping ($mysql_link)) {
        // ... reconnect to mysql server ...
        $mysql_link = mysql_connect (... parameters ...);
    }
    // ... now we can run our query knowing that we have a connection to mysql,
    // even after long periods of inactivity ...
}

Keep in mind that if you have a busy website, or if your MySQL server keeps connections open for a long time, then you don’t need to worry about reconnecting. But if you’re working on a development or test site, or just don’t get a lot of visitors, this is something you need to considering coding into your program. Lastly, don’t forget to reload your httpd server after modifying your program.