Solving a 2006 MySQL error connection timeout in Magento1
In my recent task I was testing a web crawler script which uses Magento database information for the crawling requests. I have encountered the following error:
Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000]: General error: 2006 MySQL server has gone away’ in lib/Zend/Db/Statement/Pdo.php:228
The Problem
This error occured after around 45 minutes of script runtime.
The script was written in a way that it was possible that there is no database interaction for a longer period.
In consequence to that when the script reached a point where it was trying to save or fetch something from the database, the mysql connection ran into a timeout – unnoticed by the script.
Thus resulting in the above mentioned MySQL error.
mysql wait_timeout
The variable controlling this timeout from MySQL is the wait_timeout system variable.
Definition from MySQL Reference Manual :
“The number of seconds the server waits for activity on a non-interactive connection before closing it.”
As it turns out we have already had this situation in another project – thanks to the netz98 developers for the hint.
The Solution
The solution is to close the connection before using it – in case of long running and un-interrupted code part that does no database communication.
We have added the following code snippet to our ResourceModel:
/**
* Initialize the ReadAdapter (force a re-connect)
*
* @param bool $useNewConnection
* @return Varien_Db_Adapter_Pdo_Mysql
*/
protected function _initReadAdapter($useNewConnection = false)
{
/** @var Varien_Db_Adapter_Pdo_Mysql $adapter */
$adapter = $this->_getReadAdapter();
/**
* In some occasions we want to use a fresh connection to fetch table data
* when crawling takes longer (between queries) than the configured mysql wait_timeout
*
*/
if ($useNewConnection === true) {
$adapter->closeConnection();
}
return $adapter;
}
With this method the Connection within the Adapter can be closed. When it is closed the connection will be re-initialized automatically with the next Database Interaction that is triggered by our code. To do so we introduced the parameter $useNewConncetion which enforces this behaviour.
Each time we have reached a point in our script where it could be possible that the connection hit the wait_timeout we just call this method with useNewConnection set to true.
I hope this article is helpful for you, in case you face the same situation. Feel free to comment if you faced this issue too or if you have any additions.
Update: Keep alive implementation by Ivan Chepurnyi (@IvanChepurnyi)
/**
* Zend db adapter validation, in order to get rid of possible server gone away
*
* @param Zend_Db_Adapter_Abstract $adapter
* @return bool
*/
protected function _validateConnection(Zend_Db_Adapter_Abstract $adapter)
{
try {
// Execute simple non heavy query
return $adapter->fetchOne('SELECT 1') === '1';
} catch (Zend_Db_Statement_Exception $e) {
$adapter->closeConnection();
}
return $adapter->getConnection() !== null;
}
Magento Certified Developer Plus
Magento 2 Professional Developer
Magento 2 Solution Specialist
5 thoughts on “Solving a 2006 MySQL error connection timeout in Magento1”
It is also nice to execute “SELECT 1” as a ping query. As closing connection on initing adapter might lead to undesired consequences with open transactions outside of your control. The best way is to make ping and if ping fails close connection and re-open it again.
Hi Ivan, thanks for your answer. I guess for a general solution you are right. In my case i am sure there is no activity in the connection when I call the _initReadAdapter() with true parameter because the script is quite straight forward. Maybe you have some general ready-to-post code for Magento1? Then I would update the article.
Sure, I use this one in my long running processes:
/**
* Zend db adapter validation, in order to get rid of possible server gone away
*
* @param Zend_Db_Adapter_Abstract $adapter
* @return bool
*/
private function _validateConnection(Zend_Db_Adapter_Abstract $adapter)
{
try {
// Execute simple non heavy query
return $adapter->fetchOne('SELECT 1') === '1';
} catch (Zend_Db_Statement_Exception $e) {
$adapter->closeConnection();
}
return $adapter->getConnection() !== null;
}
Seems formatting is very bad in [code] on wordpress comments. Here is better version:
Thanks for sharing, Ivan!