PHP Unable to Connection to MySQL

Over the past few weeks we've run into an issue on flora where people attempting to any database-driven content are greeted with a MySQL connection error. The connection problem is intermittent and seems to run its course after a few hours. During that time I've been asking people to click the reload button in their browser; this usually results in them getting to where they were headed. Luckily, we're not hosting any content for the general public or I wouldn't be able to rely on this solution.

The first time it happened I couldn't find anything in the logs that led me to a solution. Since the problem eventually went away, and because I had no way of really seeing what was going on at the time I let it slide. However, about a week later another bout of connections errors started occurring. We'll be moving some high-use, general-public content onto the server in the near future and so now a solution needs to be found.

So far I've been unable to trace the source of the connection errors. There's nothing indicating a problem with Apache or MySQL (such as MySQL crashing). We are logging PHP errors and here's the relevant error message:

PHP Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server during query in %DBC% on line 8

%DBC% would be the connection script and for this particular file line 8 is the mysql_connect() statement. Nothing too helpful here except to note that we're not even making the connection.

One of the users had provided a printout of the error received on the front end. I don't recall there being anything helpful, but I tossed the print0ut after the problem righted itself. A mistake since the printout included output from mysql_error(). Though I can't say for certain what kind of error was being reported I don't believe I would have tossed the printout if it was truly useful. At any rate, I modified the connection script so that mysql_error() is recorded in the PHP error log. This won't help currently, but if/when connection errors are encountered in the future I'll have a little more information to help with debugging.

After the initial occurrence I spent some time looking for information on the web, but nothing I found showed any promise. Most of the references I found regarded too many connections or too much data or similar issues. Those just doesn't seem to be the case here; I don't recall any of those being part of the error reported.

After the second occurrence I did some more searching and believe I am closer to a solution. If my recollection is correct about the nature of the error reported by mysql_error() then I suspect we may just be running into a connection timeout. The server has been running fairly sluggishly of late and it does seem possible that PHP may be unable to connect by the timeout limit.

MySQL is set to time out after 5 seconds. But in reviewing the logs and status information I don't believe this is the source of the problem. MySQL should be reporting any connection timeout on its end as aborted, but the number of aborted connection reported by MySQL (10) is significantly smaller than the number of connection errors reported by PHP (~200). With this in mind, the most likely timeout is occurring on the client. What this means is that MySQL is not responding at all by the timeout set by PHP, 60 seconds.

The solution would be to increase the timeout on the client, server, or both. However, I don't want to make this change prematurely. I'm thinking of waiting until the problem emerges again in order to see what kind of errors are thrown by mysql_error().

If I am unable to find a solution the next step might be to try posting a query on the MySQL Community and MySQL Performance Blog forums.

References:

• Communication Errors and Aborted Connections (MySQL Reference Manual)
• System Variables (MySQL Reference Manual, 5.2.3)
• The Error Log (MySQL Reference Manual, 5.11.1)
• MySQL Functions (PHP Manual, XCVIII)
  

Alternative sources
I do have some thoughts on other issues causing the connection problems. These, however, seem less likely. I am listing them here mainly as a reminder to myself in case the connection timeout doesn't pan out.

Perhaps PHP isn't freeing connections and is running out of usable connections. The server is currently set to 100 connections by default and we don't get nearly enough traffic to approach this limit. Plus, if PHP acts appropriately it should be disconnecting the connection once it has completed processing a page. However, it is conceivable that something is going on that is preventing this from happening. If PHP isn't disconnecting when finished with a page the connections could inch up towards the limit.

• Lost connection to MySQL server during query (linode.com)
  

Another possibility, maybe MySQL is running out of back_log connections. The back_log connections are those that have been connected to MySQL but have yet to be handed off to a child process. This is currently set at 50, which should be more than enough to handle our traffic load.

• problem: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) -- socket back_log? (MySQL Forums)
• System Variables (MySQL Reference Manual, 5.2.3)