Dealing with MySQL Gone Away in Zend Framework
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
The worker is a Zend Framework application, run from the CLI, and it seemed like the Zend_Db_Adapter had no way of knowing when MySQL had let go of its end of the connection. I tried a few different things, including Zend_Db_Adapter::getConnection(), but without success – until I dug through the source code (with some help from a friend) and realised that ZF was not reconnecting at all if it thought it already had a connection. So instead, I expressly disconnected and reconnected the database handler. At bootstrap time, I place my database handle into the registry, so I simply added this at the start of the actual function that the gearman worker calls:
$db = Zend_Registry::get('db');
$db->getConnection();
At the end of my script, before it returns to the loop waiting for another gearman job, I just disconnect my database:
$db->closeConnection();
Now Zend_Db_Adapter knows that when I ask it to connect, it needs to go off and make a new connection, and everything works really well! I was seeing the errors because I’m still only testing the system so it can go days between getting any new jobs, and the timeout on MySQL is shorter than that.
Hi,
A common “hack” to check the db conneciton is “select 1;”. Just close the connection if you can’t get “1 row affected” ;-)
Hi,
I also have this problem, and I currently have not solved it. I thought it is a problem of gearmanmanager, thats my “daemon script” that starts many worker scripts and if one of them dies, it will be restarted.
gearmanmanager uses pcntl_fork to create child processes, and I think this is the root of all evil in this case. I have to check that again and see if it is so easy to reestablish a new connection the way to propose.
I am moving away from GearmanManager. It is just too unwieldy to use. I agree that pcntl_fork causes a lot of problems.
The way I solved it is to use bootstrap in the actual worker function. This is slow and resource intensive. I am working on a custom manager using Lorna’s method of handling the database connections.
I’m also thinking about kicking gearmanmanager, but I’m not sure which other script can give me the features I need: start X workers of type Y, restart them if they crash or if the code changes or every hour to work around possible memory leaks and so on.
Perhaps I will use another daemon script out there and then extend it, or write my own, using system() calls to start other processes and then try to monitor them somehow.
Did you trying using mysql’s ping() function? mysql_ping/mysqli_ping ?
I decided against the “try to talk to mysql and reconnect if it fails” approach because it seemed like Zend_Db_Adapter will just return the same (lost) connection if it thinks it already has a connection object. For this application, where a batch of things happens, then the script may wait a long time before the next batch job, I figured it made more sense to just disconnect between jobs than to leave the connection lying around, potentially for ages.
Hmm, after more thought I think closing and reopening makes the most sense.
As a job could leave the database connection in a non-determinate state (transactions still open, different sessions options etc) which would cause problems in other jobs.
Seems to me that ZF needs a fix in this case.
I am not using MySQL that much, but I’ve written an extended Zend_Db_Adapter for Oracle covering my needs.
I think you could have written an adapter which looks something like this :
[geshi lang=php]
class My_Db_Adapter_Oracle extends Zend_Db_Adapter_Mysqli
{
protected function _connect()
{
parent::_connect();
if (!mysqli_ping($this->_connection)) {
$this->closeConnection();
parent::_connect();
}
}
}
[/geshi]
It’s smarter and should only disconnect/reconnect when a problem occurs.
As the proposed solution is no much than implement a non persistent connection to MySQL (and I think could be achieved by a mysqli configuration).
nice, simple solution. I kind of wish ZF would’ve been smart enough to catch the ‘Server has gone away’ exception… anyway, thanks! this should work well for my simple little CLI apps.
Cheers,
Darryl
You’ll want to consider, on newer versions of ZF, using:
$DB = Zend_Db_Table::getDefaultAdapter();
$DB->getConnection();
// … do your work
$DB->closeConnection();
in conjunction with:
resources.db.isDefaultTableAdapter = true
in your app.ini
With your code, a smart approach would be to also do:
Zend_Registry::set(‘db’, $db);
after either get or close Connection, since if later you would again do Zend_Registry::get(‘db’), you would end up with the old object
With the solution of connect, then check connection and close if problem and then call connect again i think we can fall in a recursive loop if the error is persisting for 5-6 seconds (may be network choke) and then PHP will crash because of out of memory.
Works fine for me, thanks for the lines of code that saved my life today. ;-)
With Zend Framework 2 (ZF 2), this solution could be written as:
$adapter->getDriver()->getConnection()->disconnect();
$adapter->getDriver()->getConnection()->connect();
I wish long running processes that tend to cause this would not run long. Now I waited like ten minutes so see if this solutions would work, and it doesn’t. At least, not for me.
Just found it. Someone buffered a concrete TableGateway, including the adapter, so that’s why taking a fresh adapter was not solving the problem and even disconnect()/connect() did not help: the fresh connection simply wasn’t used.