php - 处理共享主机上的 MySql 'Too many connections' 错误

Question

我有一个网站，它使用 MySql 数据库来存储用于登录的用户信息以及我的数据。该网站托管在共享托管服务器上。我遇到的问题是我偶尔会收到 SQL 太多连接错误。我的最大连接数设置为默认 151。

我对所有服务器端脚本使用 php，并使用 mysqli pdo 连接。

这里有一些示例代码，展示了如何处理来自 php 脚本的 sql 连接。我删除了与该问题无关的所有内容，例如输入过滤和字符转义。

<?php 
require("common.php");
    //get POST data
    //My database query
    $query = " 
        SELECT 
            id, 
            username, 
            password, 
            salt,
            email
        FROM users 
        WHERE 
            username = :username 
    "; 
    //set params for prepared statements
    $query_params = array( 
        ':username' => $_POST['username']
    ); 
    try { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) { 
        $miscErr = "Something failed, please try again.";        
    }  
    $row = $stmt->fetch();

    //do my password hashing, and checking, and sign in user using data in $row 
}
?>

这是我的 common.php，其中抛出了错误。我不确定处理它的正确方法是什么，因为我希望代码在失败之前尝试几次。

<?php 
$username = "username"; 
$password = "**************"; 
$host = "localhost"; 
$dbname = "mydbname"; 


$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 
$miscErr = "";
try { 
    $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
} 
catch(PDOException $ex) { 
    $miscErr = "Something failed, please try again"; 
} 

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Answer 1

我知道这已经有一段时间了，但这是我想出的解决方案。由于我无法阻止这些错误，因此我只需在 common.php 文件中使用以下代码来处理它们。

$db = "";   // db object
$er = "";     // error object

/*setdb() is the function that actually gets and starts the db connection.
    It returns either the db object, or false. The loop will try up to 5 times 
    to connect with .1 second breaks in between. if that fails then it logs an
    error, and the page fails to load. This has not happened in over 5 months on
    a live site.*/ 

for ($i = 0; $i = 5; $i++) {     //  short loop
    if (setdb() !== false) {
        $db = setdb();          // if successful breaks
        break;
    } else {
        if ($i = 5) {         // after 5 trys, logs error.
            file_put_contents('sqlerror.er', $er . "\r\n", FILE_APPEND);
        }
    }
    usleep(100000);           // .1second sleep
}

function setdb(){
    $username = "my-username";
    $password = "***************"; 
    $host = "localhost";
    $dbname = "my_database";

    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
    $miscErr = "[1040] Too many connections";

    try {         // try to make connection
        $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options);
    }
    catch(PDOException $ex) {
        $er = $ex;
        $pos = strpos($ex, $miscErr);
        if ($pos !== false) {
            return false;           //return false on error
        }
        file_put_contents('sqlerror.er', $ex . "\r\n", FILE_APPEND);
    }
    return $db;         // return true
}

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 

session_start();