Mysql gone away on model->save()

How to reconnect and try to save one more time?

When I need in long db connection, for some cron script I use this

$di->set('db', function() use ($config) {
    $db = new \Phalcon\Db\Adapter\Pdo\Mysql([
        "host" => $config->database->host,
        "username" => $config->database->username,
        "password" => $config->database->password,
        "dbname" => $config->database->name
    ]);
    $db->query('SET QUERY_CACHE_TYPE = OFF;');
    $result = $db->query("SHOW VARIABLES LIKE 'wait_timeout'");
    $result = $result->fetchArray();
    $db->timeout = (int) $result['Value'];
    $db->start = time();
    $eventsManager = new \Phalcon\Events\Manager();
    //Listen all the database events
    $eventsManager->attach('db', function($event, $db) {
        $sql = $db->getSQLStatement();
        if ($event->getType() == 'beforeQuery' && $sql != 'SELECT 1+2+3') {
            $activeTimeout = time() - $db->start;
            if ($activeTimeout > $db->timeout) {
            echo "Reconnect to db";
            $db->connect();
            $db->start = time();
            }
            try {
            $res = $db->query('SELECT 1+2+3');
            $resArray = $res->fetch();
            if ($resArray[0] != 6) {
                echo "Reconnect to db";
                $db->connect();
            }
            } catch (\PDOException $e) {
            echo "Reconnect to db";
            $db->connect();
            }

            return true;
        }
    });

    //Assign the eventsManager to the db adapter instance
    $db->setEventsManager($eventsManager);

    return $db;
});

You can use it like prototype



15.1k

what is SELECT 1+2+3 for? how about this:

    $di->set('db', function () use ($config) {
        $db = new \Phalcon\Db\Adapter\Pdo\Mysql([
            'host' => $config->database->host,
            'username' => $config->database->username,
            'password' => $config->database->password,
            'dbname' => $config->database->dbname
        ]);

        $db->timeout = $config->database->timeout;
        $db->start = time();

        $eventsManager = new \Phalcon\Events\Manager();
        //Listen all the database events
        $eventsManager->attach('db', function ($event, $db) {
            if ($event->getType() == 'beforeQuery') {
                $idle = time() - $db->start;
                if ($idle > $db->timeout) {
                    $db->connect();
                    $db->start = time();
                }
            }
            return true;
        });
    });


15.1k

got an exception after change:

[Thu, 13 Feb 14 22:59:10 +0800][CUSTOM] My::Handler: exception 'Phalcon\Mvc\Model\Exception' with message 'Invalid injected connection service' in /xxx/application/controllers/MyController.php:133 Stack trace:

0 [internal function]: Phalcon\Mvc\Model\Manager->getReadConnection(Object(MyModel))

1 [internal function]: Phalcon\Mvc\Model->getReadConnection()

2 [internal function]: Phalcon\Mvc\Model\MetaData\Strategy\Introspection->getMetaData(Object(MyModel), Object(Phalcon\DI\FactoryDefault))

3 [internal function]: Phalcon\Mvc\Model\MetaData->_initialize(------)

4 [internal function]: Phalcon\Mvc\Model\MetaData->readMetaData(Object(MyModel))

5 [internal function]: Phalcon\Mvc\Model\MetaData->hasAttribute(Object(MyModel), 'trans_id')

6 [internal function]: Phalcon\Mvc\Model\Query->_getQualified(Array)

7 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array, true)

8 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array)

9 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()

10 [internal function]: Phalcon\Mvc\Model\Query->parse()

11 [internal function]: Phalcon\Mvc\Model\Query->execute(Array, Array)

12 /xxx/application/controllers/MyController.php(133): Phalcon\Mvc\Model::findFirst(Array)

add

return $db;

??

I try to use your code ,but it's has a problem http://p4.qhimg.com/t019e9a9e7477154e1b.png

my code is : $di->set('db', function () { /** * @var $this \Phalcon\Di\FactoryDefault */ $config = $this->getConfig();

$class = 'Phalcon\Db\Adapter\Pdo\\' . $config->database->adapter;
$connection = new $class([
    'host'     => $config->database->host,
    'username' => $config->database->username,
    'password' => $config->database->password,
    'dbname'   => $config->database->dbname,
    'charset'  => $config->database->charset,
    'port'     => $config->database->port,
]);

$eventsManager = new Manager();
$profiler = $this->getProfiler();
//监听所有的db事件
$eventsManager->attach('db', function($event, $connection) use ($profiler) {
    //一条语句查询之前事件,profiler开始记录sql语句
    if ($event->getType() == 'beforeQuery') {
        $profiler->startProfile($connection->getSQLStatement());
        try {
            $res = $connection->query("SELECT 1+2+3");
            $resArray = $res->fetch();
            if ($resArray[0] != 6) {
                $connection->connect();
            }
        } catch (\PDOException $e) {
            $connection->connect();
        }
    }
    //一条语句查询结束,结束本次记录,记录结果会保存在profiler对象中
    if ($event->getType() == 'afterQuery') {
        $profiler->stopProfile();
    }
});

$connection->setEventsManager($eventsManager);

return $connection;

When I need in long db connection, for some cron script I use this

```php $di->set('db', function() use ($config) { $db = new \Phalcon\Db\Adapter\Pdo\Mysql([ "host" => $config->database->host, "username" => $config->database->username, "password" => $config->database->password, "dbname" => $config->database->name ]); $db->query('SET QUERYCACHETYPE = OFF;'); $result = $db->query("SHOW VARIABLES LIKE 'wait_timeout'"); $result = $result->fetchArray(); $db->timeout = (int) $result['Value']; $db->start = time(); $eventsManager = new \Phalcon\Events\Manager(); //Listen all the database events $eventsManager->attach('db', function($event, $db) { $sql = $db->getSQLStatement(); if ($event->getType() == 'beforeQuery' && $sql != 'SELECT 1+2+3') { $activeTimeout = time() - $db->start; if ($activeTimeout > $db->timeout) { echo "Reconnect to db"; $db->connect(); $db->start = time(); } try { $res = $db->query('SELECT 1+2+3'); $resArray = $res->fetch(); if ($resArray[0] != 6) { echo "Reconnect to db"; $db->connect(); } } catch (\PDOException $e) { echo "Reconnect to db"; $db->connect(); }

       return true;
 }

});

//Assign the eventsManager to the db adapter instance $db->setEventsManager($eventsManager);

return $db; }); ```

You can use it like prototype