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



14.6k

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;
        });
    });


14.6k

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;

??



42.5k

I like Artem's solution and I'll be looking to make it less hacky when I have the time.