[RESOLVED] Reconnecting to MySQL after timeout

rogair

I'm having a very odd situation where I can not reconnect to MySQL after the MySQL server times out the connection. I have scripts that connect to multiple DB servers and so at times their connections to some of the servers sit idle for long periods of time (current server timeout is 300 seconds). Scenario. Script connects to DB1 and DB2. queries DB1, does stuff, queries DB2 does stuff that takes a long time. queries DB1 and fails because the server has dropped the connection. Script should now reconnect and resend the query. I am able to reconnect, I get a new MySQL resource, but when I send a query using that resource it fails. Has anyone seen this and have a resolution?

if $db1 is the mysql resource then sample code is (within a function, and no it's not a scope thing I've tested for that as well, when I run this code I get a successfull connection because I get the "reconnected" text. but when the query runs I get a MySQL error of 'MySQL server has gone away' again. What am I missing?!?
if(mysql_error($db1)=='MySQL server has gone away')
{
// try to reconnect and re-run query
// disconnect to ensure link is gone (this has been tested with and without close)
mysql_close($db1);
unset($db1);

	if($db1=mysql_connect ($dbIP, $dbuser, $dbpass))
	{
		echo "reconnected\n";
		if($data=mysql_query($sql, $db1)
		{
			print_r($data);
			die();
			return $data;

		}
		else
		{
			return false;
		}
	}
}

Thanks

Sxooter

I'd say the KISS solution here is to connect to db1, run your query, disconnect, connect to db2, run your query, disconnect, and then reconnect to db1, and run the next query. The connection time isn't that long, and it's easier than trying to jump through a lot of hoops.

HOWEVER, I'm gonna guess that you're being bitten by a PHP peculiarity where if you do this:

$res1 = mysql_connect(connect args here);
$res2 = mysql_connect(same connect args here as above);

You'll get the same resource back for $res2 as you got for $res1 because the $params are the same.

So, when the connection times out, php is giving you back the same old connection resource. In which case, the fix is to use the fourth arg set true that says to make a new connection.

Hope that helps.

rogair

Fantastic! That was all it was. I can't believe I didn't notice that. In any case, thank you. Huge help.