摘要：當(dāng)使用訪問(wèn)數(shù)據(jù)庫(kù)且腳本需要長(zhǎng)時(shí)間執(zhí)行時(shí)，頻繁會(huì)遇見(jiàn)的錯(cuò)誤。分析問(wèn)題產(chǎn)生原因因?yàn)槟_本較長(zhǎng)時(shí)間未與數(shù)據(jù)庫(kù)進(jìn)行通信，導(dǎo)致數(shù)據(jù)庫(kù)連接超時(shí)與服務(wù)器斷開(kāi)連接導(dǎo)致，這時(shí)使用斷開(kāi)的數(shù)據(jù)庫(kù)連接操作數(shù)據(jù)庫(kù)，就會(huì)產(chǎn)生的錯(cuò)誤提示。

當(dāng)PHP 使用PDO訪問(wèn)數(shù)據(jù)庫(kù)且腳本需要長(zhǎng)時(shí)間執(zhí)行時(shí)，頻繁會(huì)遇見(jiàn)’ MySQL server has gone away’的錯(cuò)誤。分析問(wèn)題產(chǎn)生原因：因?yàn)槟_本較長(zhǎng)時(shí)間未與數(shù)據(jù)庫(kù)進(jìn)行通信，導(dǎo)致數(shù)據(jù)庫(kù)連接超時(shí)與服務(wù)器斷開(kāi)連接導(dǎo)致，這時(shí)使用斷開(kāi)的數(shù)據(jù)庫(kù)連接操作數(shù)據(jù)庫(kù)（CRUD），就會(huì)產(chǎn)生’ MySQLserver has gone away’的錯(cuò)誤提示。解決之道在于長(zhǎng)時(shí)間運(yùn)行的腳本需要與服務(wù)器保持心跳，一旦檢測(cè)到連接斷開(kāi)，則需要重新連接數(shù)據(jù)庫(kù)。

在考慮保持最小數(shù)據(jù)庫(kù)負(fù)載（檢測(cè)手段開(kāi)銷(xiāo)小，心跳頻率不能太過(guò)頻繁）和較高腳本健壯性（需要更高頻率的心跳），可以選擇不同的心跳測(cè)試策略和心跳頻率。以下的代碼給出了一個(gè)解決辦法，通過(guò)單件封裝了數(shù)據(jù)庫(kù)連接，當(dāng)每次獲取數(shù)據(jù)庫(kù)連接時(shí)會(huì)判斷是否需要檢測(cè)連接仍然有效，如果連接失效則重建連接，在腳本健壯性與服務(wù)器壓力之間保持一個(gè)最好的平衡點(diǎn)

/*

使用單件來(lái)維護(hù)數(shù)據(jù)庫(kù)連接，當(dāng)需要數(shù)據(jù)連接時(shí)需要從單件獲取，這樣在單件內(nèi)部可以檢測(cè)并恢復(fù)數(shù)據(jù)連接

*/

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

}