Reconnect to mysql after timeout

PHP
1

I’ve got a couple of scripts that make a database connection, grab some thing from remote servers, then do the required database work. Sometimes the time required at the remote servers means my database connection has timed out, and a get a 2006 “MySQL server has gone away” error.

So I’m trying to detect a 2006 error, and attempt to re-connect and re-run the query (this seems a better solution than simply raising timeout limits), but without success.
I’m trying to mysql_ping(), which I understand should attempt to re-connect, also trying to re-do mysql_connect(), but the status still appears to be ‘gone away’.

Any suggestions? Code below:


$dbhandle = mysql_connect($host,$un,$pw);
if(!$dbhandle)exit("Failed connecting");
echo "Status : ".mysql_stat($dbhandle)."<BR>";
echo "Sleeping<BR>";
sleep(65);//sleeping to force a connection timeout
echo "Status : ".mysql_stat($dbhandle)."<BR>";
//try a query
$query = mysql_query("SELECT COUNT(*) FROM mydb.test_table",$dbhandle);
if($query)
{
	echo "Result is ".mysql_result($query,0);
	exit;
}
else
{
	echo "An error occurred: ".mysql_error()."<BR>";
	if(mysql_errno()==2006)//server gone away error
	{
		echo "Status : ".mysql_stat($dbhandle)."<BR>";
		//attempt to re-connect
		echo "try to ping: <BR>";
		$ping = mysql_ping($dbhandle);
		if($ping) echo "ping successful";
		else
		{
			echo "ping unsuccessful. try to re-connect<BR>";
			$dbhandle = mysql_connect($host,$un,$pw);
			if(!$dbhandle)exit("Failed connecting");
			else echo "re-connected";
			
		}
		echo "<BR>";
		echo "Status : ".mysql_stat($dbhandle)."<BR>";
	}

}

This outputs:
Status : Uptime: 7049735 Threads: 3 Questions: 2282716432 Slow queries: 9407204 Opens: 24786 Flush tables: 1 Open tables: 1679 Queries per second avg: 323.802
Sleeping
Status : MySQL server has gone away
An error occurred: MySQL server has gone away
Status : MySQL server has gone away
try to ping:
ping unsuccessful. try to re-connect
re-connected
Status : MySQL server has gone away

2

Do you know what version of MySQL is being ran on the server?

3

Sorry, yes, it’s 5.0.77

4

I think I’ve got it now.
It seems you need to first close the connection with mysql_close($dbhandle) before re-connecting.
It’s only taken me 8 hours to get to the bottom of it!

5

Or you can force mysql_connect() to create a new link using the 4th parameter $new_link.