CODEWITHSUNDEEP
phpmysqlexception
Talon
Talon

Reputation: 3593

MySQL server has gone away - ErrorException

I started getting this error when trying to query the database on a connection that had timed out.

Fatal error: Uncaught exception 'ErrorException' with message 'mysql_query(): MySQL server has gone away'

So I did a bunch of research and 99% of the users on the forum say you can use the mysql_ping command to check for a connection, so I put this in place:

if(!mysql_ping($this->sDBLink))
    Connect(true);

Now I get the same error, just in reference to the mysql_ping function instead of the mysql_query function:

Fatal error: Uncaught exception 'ErrorException' with message 'mysql_ping(): MySQL server has gone away'

How do I reliably check that a connection still exists? mysql_ping throws an exception.

Upvotes: 3

Views: 2628

Answers (2)

Talon
Talon

Reputation: 3593

So I know this is ugly and makes me sick it, but it will work until we can get enough income to warrant an upgrade.

All I did was close the connection then recreate it as I know the connection will have timed out almost 95% of the time.

if($this->sDBLink){
    mysql_close($this->sDBLink);
}

if($bPersistant){
    $this->sDBLink = mysql_pconnect($this->sHostname, $this->sUsername, $this->sPassword);
} else {
    $this->sDBLink = mysql_connect($this->sHostname, $this->sUsername, $this->sPassword);
}

Upvotes: 1

O. Jones
O. Jones

Reputation: 108851

Why does a connection time out? Because it has been unused for a period of time longer than a particular threshold. This kind of thing is necessary for all kinds of network applications to make them resilient. What if a user abruptly switches off a client machine while it is connected to a MySQL server? The server needs to be able to drop the client connection after a while.

This kind of thing is inherent in network programming. It's a feature, not a bug.

What can you do about this?

You can switch to a more modern mysql connection management library that handles this stuff.

Especially if your client software gets used infrequently, you can reorganize your software to connect to MySQL, use the connection, and disconnect. That way you won't need a persistent connection. But that's impractical if your client server gets used a lot; there's a lot of overhead to establishing a connection.

You can change the timeout value. See here. how to change timeout for mysql persistent connections

You can use the connection regularly. mysql_ping uses the connection without actually doing any server work. So would a query that said something like SELECT 1. If you ping the connection every minute or so, then a two minute timeout won't cause your MySQL server to conclude that your client has gone away and disconnect it.

You can handle the ErrorException you're getting correctly, by trying to re-establish the connection instead of just blowing out your program with an error message. Use PHP code something like this:

try {
    some operation on the mysql connection.
}
catch (ErrorException $ex) {
    disconnect mysql
    connect mysql
}

Upvotes: 4

Related Questions