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



17.0k

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


17.0k

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

$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