At the moment i am writing some workers that interact with a beanstalk server. When data gets pushed into the beanstalk server. My workers will be triggered to process this data. So the workers are sitting idle for most of the time. And just wait for some data to process. No rocket science there. But during testing i kept running into MySQL issues. The script seemed to lose connection to the MySQL server when it was idle for longer then a minute. And would respond with a
Warning: mysql_query(): MySQL server has gone away in /path/to/some/mysql4/class.php on line xxx
So whats going on here? Well actually it’s quite simple. When my script initializes the database connection it doesn’t use it. It just sits there waiting for incoming data. Once received it will process it and try to store it in the database. But when the waiting period exceeds the time for PHP to keep the MySQL connection open it responds with the warning mentioned above. Now in previous versions of PHP this would not be a big issue. As PHP would just initiate a reconnect when the connection is lost. But from PHP 5.0.3 and up this functionality has been disabled by default. For MySQLI this is no problem at all.
mysqli.reconnect = Off to On
Unfortunately i am working with PHP’s core mysql_* functions (don’t get me started) and there doesn’t seem to be an easy way to resolve this. According to the MySQL documentation
Should do the trick. But passing MYSQL_OPT_RECONNECT in anyway to mysql_connect didn’t give me the result i was looking for. So what now? Porting the database code to make use of the newer and better PDO or MySQLi is no option. As it would consume way to much time. Fortunately the mysql_* core functions come with mysql_ping. I never had to use this before. But in this case it comes in quite handy.
From the PHP manual:
Checks whether or not the connection to the server is working. If it has gone down, an automatic reconnection is attempted. This function can be used by scripts that remain idle for a long while, to check whether or not the server has closed the connection and reconnect if necessary.
Note: Automatic reconnection is disabled by default in versions of MySQL >= 5.0.3.
Adding the mysql_ping function call was rather straight forward. And didn’t require all that much work to be done. I extended the database class to include a ping method. That would simply throw an exception when it failed to reconnect.
And after that i started poking around the userland implementation. The worker is running inside a while(true) loop. A first test with ->ping() being called inside this loop proved to resolve the issue at hand. But running the ping function that often is overkill. And who knows it might actually result in DoS of the database server. So i decided to ping the server every one minute or so.
And the keepConnectionAlive method looks something like this
I’m not a big fan of this solution. And would rather be implementing MySQLi functions. But this functions well. And will do for now.