General error: 2006 MySQL server has gone away

Yii 1.1 General Discussion
1

Hi.

i’m using Yii 116 with the following application/workflow:

  • creating an DB conncetion

  • some php code

  • executing one SQL Statement with $res=$con->createCommand($sql)->queryAll(); and processing the results

  • some php code

  • executing next SQL Statement, wehere $sql changed followed by: $res=$con->createCommand($sql)->queryAll();

  • creating some forks in an loop (for multi threaded PHP Processing)

-> $npid = pcntl_fork();

inside every fork fork:

** some php code

** calling $this->getData($con) (to bring the connection-information inside the method getData

** some php code

** end of process-child

*end of mother-process

The method $this->getData($con) works like that:

*$sql="<my sql statement>";

  • $res=$con->createCommand($sql)->queryAll();

  • processing $res and finish with

  • return $result

That script works in 1 of 5 cases. In 4 runs, the script ends with the error "General error: 2006 MySQL server has gone away".

Without calling the method $this->getData($con), the scripts runs fine. It looks like there are an problem with using the DB connection inside the forked process!?

Is it possible, that one fork ends this DB connection, while the other fork isn’t finish and still needs this connection?

How to avoid this situation?

Thank you!

2

check this thread it’s about the same error - http://www.yiiframework.com/forum/index.php?/topic/14411-

3

Thank you for that link.

Finlay i changed localhost into 127.0.0.1 in order to come to the same result as some users before:

Changing wouldn’t be the right way, because the defaults values are much higher than the application-runtime.

The current value of wait_timeout is the default value with 8 hours, same as connect_timeout: default value with 5 seconds. The application needs less than 2 seconds until finished.

So what else could it be?

How to solve? Is persistent connection the right idea??

//Edit Maybe the error rises in context with "applications that fork child processes"

-> http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

.

If I have an connection created in that way: $con=Yii::app()->db;

How can I ad an 2nd connection for an forked child process? Is "$con2=Yii::app()->db;" sufficient or my i have to create an new DB definition at the main.php? (an DBx)

4

…in order to answer my own question…

The cause of my problem was the same DB connection inside and outside of an child process.

Solution:

After starting the child process: stop the DB connection and restart again.


$con->setActive(false);

$con->setActive(true);

$con=Yii::app()->db;

Maybe it’s not “best practice”, but it works for me…

…better than every other "solution", i found at the Internet.

5

try this:

add a new line to your db config (eg./protected/config/main.php)


'class' => 'application.components.DbConnection',

and save the following code into file /protected/components/DbConnection.php :




<?php

/**

 * Created by PhpStorm.

 * User: riverlet

 * Date: 14-7-3

 * Time: 3:28 PM

 */


class DbConnection extends CDbConnection {

    private $_lastActive = 0;

    /**

     * @var mixed a positive integer as the timeout of trying to reconnect. set to false or 0 to disable this feature.

     */

    public $autoReconnect = 2;


    public function setActive($value) {

        if($value && $this->autoReconnect) {

            $lifetime = time() - $this->_lastActive;

            if($lifetime > intval($this->autoReconnect)) {

                try {

                    if($this->getActive()) {

                        @$this->getPdoInstance()->query('SELECT 1');

                    }

                } catch(Exception $e) {

                    Yii::trace($e->getMessage()." Last connection's lifetime: {$lifetime}, trying to close and reconnect... ", 'components.DbConnection.setActive');

                    parent::setActive(false);

                }

            }

        }

        parent::setActive($value);

        $this->_lastActive = time();

    }


}
6

Anyone getting "MySQL server has gone away (error 2006)" with YII should try this.

For me, doing a large file upload and parsing thousands of files on GoDaddy (or other multi-tenant host) the connection was getting dropped. GoDaddy does not provide access to the globals in MySQL to increase timeouts, etc… which really isn’t a good solution anyway. The proper way of handling these issues and for handling transient errors on cloud hosted MySQL instances is to do automatic retries. This little script worked perfect, thanks a ton for sharing, I hope other’s get some use out of it too.

7

Anyone know how to properly simulate the Gone Away error? I tried setting a low wait_timeout and manually killing the connection made by PHP but these result in a different error (Error while sending QUERY packet) that does not trigger an exception, meaning this script won’t help in that situation if the $autoReconnect value is higher than when the timeout occurs.

EDIT:

Oh ok, so I can get that exception error to occur if I use the @ error suppressor on the pdo query statement, just like the sample code above. I think this is pretty helpful for long running scripts, instead of manually calling


Yii::app()->db->setActive(false);

everytime to force a new connection (which is what I was doing before)