如果第一个失败，我可以连接到第二个数据库主机吗？

Question

I have a question about a connection to a second host if the first fail down or connection limit reached.

My Host is OVH in France on a shared host (I can't modify apache/php/sql config) and the MAX USER_CONNECTIONS (sql) is 30.

I wanted to switch Host is the first one reach the limit and refuse connection.

Can I use ?:

$dbhost = 'localhost';
$dbname = 'dbname';
$dbuser = 'dbuser';
$dbpswd = 'dbpass';
try{
    $db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname,$dbuser,$dbpswd,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}catch(PDOexception $e){
    $dbhost2 = 'secondhost';
    $dbname2 = 'dbname';
    $dbuser2 = 'dbuser';
    $dbpswd2 = 'dbpass';
    try{
        $db = new PDO('mysql:host='.$dbhost2.';dbname='.$dbname2,$dbuser2,$dbpswd2,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    }catch(PDOexception $e){
        die("DB Error please contact the web admin.");
    }
}

Or if you have a better option..

NB: I do not have much experience in programming, I do not use OOP yet.

Answer 1

The idea is right, but this particular implementation is inflexible and unhelpful towards the site admin who is supposed to fix errors.

Imagine a case when a password has been changed for the main server. Your code will start connecting to the backup server 100% of time, thanks to the unconditional algorithm, and nobody would have an idea on that.

Given you want to overcome a particular error ("too many connections"), in your catch block you should test whether it is the error you are expecting to, and at least log the error message otherwise. So the site ad min will notice a problem and will have an idea what's going on.

die("DB Error please contact the web admin."); won't be of a much use for the site admin either, as he won't have an idea what happened with the backup server in turn.

So for the second one do not catch its exception right in place, but follow the common error handling routine for your site. The easiest one would be a global try catch around the whole code, which looks rather blunt but at least better than several dozens local try catches with its own error message each

So it should be like

$dboptions = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)

try{
    $db = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8mb4",$dbuser,$dbpswd,$dboptions);
}catch(PDOexception $e){
    if ($e->getMessage() != "whatever particular error message you get") {
        error_log($e);
    }
    $dbhost2 = 'secondhost';
    $dbname2 = 'dbname';
    $dbuser2 = 'dbuser';
    $dbpswd2 = 'dbpass';
    $db = new PDO("mysql:host=$dbhost2;dbname=$dbname;charset=utf8mb4",$dbuser,$dbpswd,$dboptions);
}