解决PHP脚本 MySQL has gone away错误

解决PHP脚本 MySQL has gone away错误

原因分析

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

 

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

 

参考代码

<?php
/*
 *  使用单件来维护数据库连接，当需要数据连接时需要从单件获取，这样在单件内部可以检测并恢复数据连接
 */
 
class DBConnection
{
    const RECHECK_FREQUENCY = 300;// 5 minutes
    private static $instance = null;
    private $dbh = NULL;
    private $lastCheckTime = 0;
   
    private function __construct()
    {
        $this->lastCheckTime  =time();
    }
 
    public static function instance()
    {
 
        if (NULL ==self ::$instance)
        {
            self:: $instance =newDBConnection();
        }
        return self::$instance;
    }
 
    public function dbh()
    {
       
        $this->ensureConnection2();
       
        return $this->dbh;
    }
   
    private function ensureConnection()
    {
        if (is_null($this->dbh))
            return $this->makeConnection();
       
        try
        {
            $status = $this->dbh->getAttribute(PDO::ATTR_SERVER_INFO);
            error_log('MySQL server checked been there');
        }
        catch(PDOException$e)
        {
            if((int)$e->errorInfo[1]== 2006 &&$e->errorInfo[2] == 'MySQLserver has gone away')
            {
                error_log("MySQLserver has gone away, try to reconnection...");
                return $this->makeConnection();
            }
           
            error_log('Get db server attribute failed: ' .$e->getMessage());
        }
       
        return $this->dbh;
    }
   
    private function ensureConnection2()
    {
        if (is_null($this->dbh))
            return $this->makeConnection();
       
        try
        {
            $now = time();
           
            if ($now -$this->lastCheckTime> self::RECHECK_FREQUENCY)
            {
                $this->lastCheckTime  =$now;
                $status = $this->dbh->query("select1");
                error_log('MySQL server checked been there');
            }
           
        }
        catch(PDOException$e)
        {
            if((int)$e->errorInfo[1]== 2006 &&$e->errorInfo[2] == 'MySQLserver has gone away')
            {
                error_log("MySQL server has gone away, try to reconnection...");
                return $this->makeConnection();
            }
           
            error_log('Get db server attribute failed: ' .$e->getMessage());
        }
       
        return $this->dbh;
    }
   
    private function makeConnection()
    {
        try
        {
            $options = array (
                PDO:: MYSQL_ATTR_INIT_COMMAND => "set names 'utf8'",
                PDO:: ATTR_PERSISTENT => false,
                PDO:: ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            );
            $this->dbh = new PDO(DB_DSN, DB_USER, DB_PASSWORD, $options);
            return $this->dbh;
        }
        catch (PDOException$e)
        {
            error_log('Connection failed: ' .$e->getMessage());
            exit();
        }
       
        return null;
    }
}