[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

[MySQL] 匯出 & 匯入

使用 mysqldump 會產生 SQL 語法，以下是從官網介紹直接撈出資料的方式，用於大量匯入會比較快。

$ mysqldump db1 table1 > table1.sql
...
INSERT INTO `table1` VALUES ('0000-00-00 00:00:00',1,NULL,'apple'),('0000-00-00 00:00:00',2,'tw','apple');
...

$ mysql -u user1 table1 < table1.sql


mysql> SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;

0000-00-00 00:00:00^I1^I\N^Iapple
0000-00-00 00:00:00^I2^Itw^Iapple

mysql> SELECT * INTO OUTFILE '/tmp/table1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM table1;

"0000-00-00 00:00:00",1,\N,"apple"
"0000-00-00 00:00:00",2,"tw","apple"


mysql> LOAD DATA INFILE 'table1.txt' REPLACE INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

參考資料：

• MySQL 5.5 Reference Manual 13.2.9.1 SELECT ... INTO Syntax
• MySQL 5.5 Reference Manual 13.2.6 LOAD DATA INFILE Syntax