ZF-11743: New Method: Zend_Db_Adapter_Abstract::refreshConnection()

Description

All of the Zend_Db_Adapter classes extend Zend_Db_Adapter_Abstract.

When forking a process the db connection get's copied to the child processes. If a child exits before the parent, the current database connection is closed. Since the connection is shared between the child and the parent process, when the parent process tries to use the db resource, say to clean up after the child, it gets a "MySQL has gone away" error.

I am proposing that a new method is added to the Zend_Db_Adapter_Abstract class that does two simple things.

Close any existing connections

Open a new connection




    /**
     * Closes the connection and opens a new one.
     *
     * @return void
     */
    public function refreshConnection()
    {
        if ($this->isConnected()) {
            $this->closeConnection();
        }
        $this->_connect();
    }


This can be called from any parent process for the new child and in the parent process after the child is spawned.

Example of scenario where an error would occur:


$this->_dbConnInfo = new Zend_Config_Array(/* db connection information file */);

$db = Zend_Db::factory($this->_dbConnInfo);
Zend_Db_Table::setDefaultAdapter($db);

// Do some mysql set-up queries here

$pid = pcntl_fork();
if ($pid) {
    // we are the parent

    pcntl_wait($status); //Protect against Zombie children

    // Any mysql statement here will result in gone away error
    
} else {
    // we are the child
    // do some mysql processing
}

Example of error fixed:


$this->_dbConnInfo = new Zend_Config_Array(/* db connection information file */);

$db = Zend_Db::factory($this->_dbConnInfo);
Zend_Db_Table::setDefaultAdapter($db);

// Do some mysql setup queries here

$pid = pcntl_fork();

// This will completely fix this issue
$db->refreshConnection();

if ($pid) {
    // we are the parent

    pcntl_wait($status); //Protect against Zombie children

    // Any mysql statement here will result in gone away error
    
} else {
    // we are the child
    // do some mysql processing
}

I know that this could also be done by simply running the functionality inside the proposed method. Adding this method saves code duplication and is very handy to have.

Putting this method in the Zend_Db_Adapter_Abstract class also means that it can be used in any Zend_Db_Adapter class.

Comments

Here is the finished method for Zend_Db_Adapter_Abstract


    /**
     * Closes the connection and opens a new one - non-persistent only.
     *
     * This method is used to release an existing database connection and
     * create a new one. Does not work with a persistent connection.
     *
     * @link http://framework.zend.com/issues/browse/ZF-11743
     *
     * @return void
     */
    public function refreshConnection()
    {
        if (array_key_exists('persistent', $this->_config) && $this->_config['persistent'] == true) {

            throw new Zend_Db_Adapter_Exception('Persistent connections cannot be refreshed.');
        }

        if ($this->isConnected()) {
            $this->closeConnection();

        }

        $this->_connect();

        return $this;
    }

I have also created complimenting method for the Zend_Db_Table_Abstract class:



    /**
     * Resets the default adapter - disconects and then reconnects.
     *
     * @return void
     */
    public static function resetDefaultAdapter()
    {
        if (is_null(self::getDefaultAdapter())) {
            return;
        }

        $db = self::getDefaultAdapter();
        $db->refreshConnection();
    }

This is great for when you need to reset the database connection from anywhere in the app. I.e.



Zend_Db_Table::resetDefaultAdapter();

Am I correct by saying this is only really needed because _connect is protected, so it's difficult to accomplish this in user-land code? I've personally ran into the issue outlined here with pcntl_fork(), and in my case I simply extended the adapter to provide the refreshConnection() functionality. So the question is: do we need or want this in the abstract adapter as proposed, and are there any other use-cases for performing such a re-connect beyond when the process is forked and you have two processes sharing the same connection resource?

I take your point. I believe it is required. Especially in the Abstract class. Extending the adapter is a work around for this problem not a solution.Making the _connect() method public would not solve the issue as it returns if a connection exists. To accomplish what this new method does, you have to wite three lines of code; check for connection, disconnect and connect. This new method does all this for your and hence, reducing code duplication in your application.

Is this something which should be included in ZF 1.12?

I think that adapter should autorefresh connction. This error occur also if you set short timeout in mysql server and your script has long job between thow DB operation. Workaround described at: http://lornajane.net/posts/2011/…