Prevent General error: 2006 MySQL server has gone away in Zend Framework

As introduction

I started to get General error: 2006 MySQL server has gone away error in one of my project. I’ve read http://dev.mysql.com/doc/refman/5.0/en/gone-away.html but still was not sure why does it happen. Until I stared use php Gearman workers. That was daemon processes they work in memory all the time. I had 5 workers and all of them have such error. I realized that workers sleep between tasks and do not send any command to mysql and mysql close connection. Such problem can happen with cron script that works for a long time but has delays between queries(do some other tasks).

Solution

One of the things for which I like Zend Framework is really easy extensibility. We can just override default Adapter and add some functionality. So the idea is simple if we get 2006 error during query we will try to reconnect. If it didnt’t help we will throw original exception.

<?php

/**
 * Extended Zend_Db_Adapter_Pdo_Mysql - try to reconnect if mysq gone away
 * User: radzserg
 * Date: 4/19/12
 */
class App_Db_Adapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql
{

    const MAX_RECONNECT_COUNT = 1;

    public function query($sql, $bind = array())
    {
        try {
            return parent::query($sql, $bind);
        } catch (Zend_Db_Statement_Exception $e) {
            $message = $e->getMessage();
            if ($message == 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away') {
                $this->_reconnect();
                return parent::query($sql, $bind);
            } else {
                throw $e;
            }
        }
    }

    private function _reconnect()
    {
        $this->_connection = null;
        for ($i = 1; $i <= self::MAX_RECONNECT_COUNT; $i++) {
            sleep(1);
            try {
                $this->_connect();
            } catch (Zend_Db_Adapter_Exception $e) {
                if ($i == self::MAX_RECONNECT_COUNT) {
                    throw $e;
                }
            }
            if ($this->_connection) {
                return ;
            }
        }
    }
}

All we have to do now is just add appropriate instruction to config
resources.multidb.default.adapterNamespace = “App_Db_Adapter”

That’s it feedback is welcome.

6 thoughts on “Prevent General error: 2006 MySQL server has gone away in Zend Framework

  1. Pingback: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

  2. User

    Great article, thank you! Helped me a lot.
    But I have in config file:
    resources.db.params.adapterNamespace = ‘App_Db_Adapter’

    Reply
  3. admin Post author

    Oh… right. In my example the app uses multiple connections. If you use only one connection this
    resources.db.params.adapterNamespace = ‘App_Db_Adapter’
    also should work

    Reply
  4. Andron

    Thanks!
    Searched for this solution.
    Worked well for me.

    Just a side note: this class must be placed in the correct location and must be named correctly too.
    In my case path is: C:\Projects\Web\htdocs\project\library\App\Db\Adapter\Pdo\
    File name: Mysql.php

    And in config file: db.params.adapterNamespace = “App_Db_Adapter”

    Thanks again, Andron.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *