[PHP] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

近日遇到稍微罕見的案例：MySQL server has gone away

PHP Warning:  PDO::exec(): MySQL server has gone away in /.../A.inc on line 104
PHP Warning:  PDO::exec(): Error reading result set's header in /.../A.inc on line 104
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /.../A.inc:104
Stack trace:
#0 /.../A.inc(104): PDO->exec('SET NAMES utf8')
...

由於這是在執行排程的時候遇到，查詢之後還是決定從 PHP 著手，避免影響 APIs 效能。

下面是處理方式；拿掉註解則可以測試狀況。

$pdo = $this->getPdo();
try {
   // $pdo->exec('SET wait_timeout=1');
   // sleep(3);
   $pdo->exec('SET NAMES utf8');
} catch (PDOException $e) {
   $errInfo = $pdo->errorInfo();
   if ($errInfo[0] === 'HY000' && $errInfo[1] === 2006) {
      // SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
      $this->resetPdo();
      $pdo = $this->getPdo();
      $pdo->exec('SET NAMES utf8');
      return $pdo;
   }
   throw $e;
}
return $pdo;

參考資料：

• StackOverflow: PDO: MySQL server has gone away
• MySQL 5.7 Reference Manual: B.5.2.9 MySQL server has gone away
• PHP Manual: PDOStatement::errorInfo