I have a LAMP website (PHP/MySQL), and I rely heavily on my MySQL database.
Recently, the following error started occurring intermittently:
[30-Oct-2011 16:11:49] PHP Warning: mysqli_connect() [<a href='function.mysqli-connect'>function.mysqli-connect</a>]: (HY000/2013): Lost connection to MySQL server at 'sending authentication information', system error: 32 in /home/path/to/my/code.php on line 1029
This is line 1029, the specific line that occasionally gives the above error:
$db = mysqli_connect($cfg['db_location'], $cfg['db_reader_username'], $cfg['db_reader_password']);
where $cfg['db_location'] is "localhost", and the username and password are correct.
It appears as though something is causing my PHP script to intermittently lose connection to the MySQL server, which is puzzling to me because all of my MySQL connections are made to localhost. That is, the PHP script and the MySQL server are both running on the same box, and I connect to MySQL via localhost.
Additionally, this is puzzling because my PHP script is able to successfully connect to the MySQL server for perhaps 90% of pages.
As best I can tell, my server has plenty of available RAM and CPU:
top - 16:38:23 up 173 days, 19:19, 2 users, load average: 0.57, 0.78, 0.84
Tasks: 142 total, 4 running, 137 sleeping, 0 stopped, 1 zombie
Cpu(s): 18.7%us, 27.5%sy, 0.7%ni, 52.6%id, 0.2%wa, 0.1%hi, 0.2%si, 0.0%st
Mem: 2976260k total, 1861368k used, 1114892k free, 532628k buffers
Swap: 2048248k total, 116k used, 2048132k free, 1035804k cached
And plenty of available disk space. When I run df -h, all of my filesystems are at most 25% in use.
So I don't know what would be causing the sporadic errors that I'm seeing in my PHP error logs.
Any ideas what might be causing my PHP script to intermittently lose connection to my MySQL server on localhost?
I upgraded to MySQL 5.1, and this is now what get when I run mysql -V:
mysql Ver 14.14 Distrib 5.1.56, for pc-linux-gnu (i686) using readline 5.1
The same errors are still happening, though:
[30-Oct-2011 20:26:18] PHP Warning: mysqli_connect() [<a href='function.mysqli-connect'>function.mysqli-connect</a>]: (HY000/2013): Lost connection to MySQL server at 'sending authentication information', system error: 32 in /home/path/to/my/code.php on line 1029
As before, the errors continue to be intermittent.
After I upgraded to MySQL 5.1, I've been looking carefully at my PHP errors logs. I've noticed a few other errors similar to the first one. For example, here's one such error:
[30-Oct-2011 22:42:29] PHP Warning: mysqli_connect() [<a href='function.mysqli-connect'>function.mysqli-connect</a>]: (HY000/2013): Lost connection to MySQL server at 'reading authorization packet', system error: 104 in /home/path/to/my/code.php on line 1029
And here's another error I've seen in my logs:
PHP Warning: mysqli::mysqli() [<a href='mysqli.mysqli'>mysqli.mysqli</a>]: (HY000/2002): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/path/to/different/code.php on line 7
In all cases, the problem fundamentally seems to be a failure to connect to MySQL. Any ideas what might be causing this or how I could debug it?
Perhaps this bug report will help - It seems that a fix may be to set a higher timeout for the global connect_timeout. It was set to a higher value in later versions of MySQL 5.1 (10 seconds) as opposed to 5 seconds for earlier versions.
http://bugs.mysql.com/bug.php?id=28359