解决PHP脚本 MySQL has gone away错误

来自技术流笔记
跳转至: 导航搜索

当PHP 使用PDO访问数据库且脚本需要长时间执行时,频繁会遇见’ MySQL server has gone away’的错误。分析问题产生原因:因为脚本较长时间未与数据库进行通信,导致数据库连接超时与服务器断开连接导致,这时使用断开的数据库连接操作数据库(CRUD),就会产生’ MySQLserver has gone away’的错误提示。解决之道在于长时间运行的脚本需要与服务器保持心跳,一旦检测到连接断开,则需要重新连接数据库。

在考虑保持最小数据库负载(检测手段开销小,心跳频率不能太过频繁)和较高脚本健壮性(需要更高频率的心跳),可以选择不同的心跳测试策略和心跳频率。以下的代码给出了一个解决办法,通过单件封装了数据库连接,当每次获取数据库连接时会判断是否需要检测连接仍然有效,如果连接失效则重建连接,在脚本健壮性与服务器压力之间保持一个最好的平衡点

  1 <?php
  2 /*
  3 
  4 使用单件来维护数据库连接,当需要数据连接时需要从单件获取,这样在单件内部可以检测并恢复数据连接
  5 */
  6 
  7 class DBConnection
  8 {
  9 
 10 const RECHECK_FREQUENCY = 300;// 5 minutes
 11 private static $instance = null;
 12 private $dbh = NULL;
 13 private $lastCheckTime = 0;
 14 
 15 private function __construct()
 16 {
 17     $this->lastCheckTime  =time();
 18 }
 19 
 20 public static function instance()
 21 {
 22 
 23     if (NULL ==self ::$instance)
 24     {
 25         self:: $instance =newDBConnection();
 26     }
 27     return self::$instance;
 28 }
 29 
 30 public function dbh()
 31 {
 32    
 33     $this->ensureConnection2();
 34    
 35     return $this->dbh;
 36 }
 37 
 38 private function ensureConnection()
 39 {
 40     if (is_null($this->dbh))
 41         return $this->makeConnection();
 42    
 43     try
 44     {
 45         $status = $this->dbh->getAttribute(PDO::ATTR_SERVER_INFO);
 46         error_log('MySQL server checked been there');
 47     }
 48     catch(PDOException$e)
 49     {
 50         if((int)$e->errorInfo[1]== 2006 &&$e->errorInfo[2] == 'MySQLserver has gone away')
 51         {
 52             error_log("MySQLserver has gone away, try to reconnection...");
 53             return $this->makeConnection();
 54         }
 55        
 56         error_log('Get db server attribute failed: ' .$e->getMessage());
 57     }
 58    
 59     return $this->dbh;
 60 }
 61 
 62 private function ensureConnection2()
 63 {
 64     if (is_null($this->dbh))
 65         return $this->makeConnection();
 66    
 67     try
 68     {
 69         $now = time();
 70        
 71         if ($now -$this->lastCheckTime> self::RECHECK_FREQUENCY)
 72         {
 73             $this->lastCheckTime  =$now;
 74             $status = $this->dbh->query("select1");
 75             error_log('MySQL server checked been there');
 76         }
 77        
 78     }
 79     catch(PDOException$e)
 80     {
 81         if((int)$e->errorInfo[1]== 2006 &&$e->errorInfo[2] == 'MySQLserver has gone away')
 82         {
 83             error_log("MySQL server has gone away, try to reconnection...");
 84             return $this->makeConnection();
 85         }
 86        
 87         error_log('Get db server attribute failed: ' .$e->getMessage());
 88     }
 89    
 90     return $this->dbh;
 91 }
 92 
 93 private function makeConnection()
 94 {
 95     try
 96     {
 97         $options = array (
 98             PDO:: MYSQL_ATTR_INIT_COMMAND => "set names 'utf8'",
 99             PDO:: ATTR_PERSISTENT => false,
100             PDO:: ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
101         );
102         $this->dbh = new PDO(DB_DSN, DB_USER, DB_PASSWORD, $options);
103         return $this->dbh;
104     }
105     catch (PDOException$e)
106     {
107         error_log('Connection failed: ' .$e->getMessage());
108         exit();
109     }
110    
111     return null;
112 }
113 }