12

I am using MySQL PDO for handling database querying and all. But most of the time, the MySQL connection is gone away. So i am looking in the PDO that will check if the db connection is exists or not and if it is not exit, then i need to connect the database to continue the query execution.

I am new to the MySQL pdo and i do not know how to handling this situation. It would be better if anybody suggest on this.

linuxatico
  • 1,800
  • 30
  • 43
Raja
  • 3,317
  • 12
  • 44
  • 82

2 Answers2

10

I tried to find a solution for the same problem and I found the next answer:

class NPDO {
    private $pdo;
    private $params;

    public function __construct() {
        $this->params = func_get_args();
        $this->init();
    }

    public function __call($name, array $args) {
        return call_user_func_array(array($this->pdo, $name), $args);
    }

    // The ping() will try to reconnect once if connection lost.
    public function ping() {
        try {
            $this->pdo->query('SELECT 1');
        } catch (PDOException $e) {
            $this->init();            // Don't catch exception here, so that re-connect fail will throw exception
        }

        return true;
    }

    private function init() {
        $class = new ReflectionClass('PDO');
        $this->pdo = $class->newInstanceArgs($this->params);
    }
}

Full story here: https://terenceyim.wordpress.com/2009/01/09/adding-ping-function-to-pdo/


Somebody else was thinking to use PDO::ATTR_CONNECTION_STATUS, but he figured out that: "$db->getAttribute(PDO::ATTR_CONNECTION_STATUS) keeps replying “Localhost via UNIX socket” even after stopping mysqld".

Cristian Ciocău
  • 1,004
  • 2
  • 10
  • 13
  • 1
    Apparently 'DO 1;' is slightly shorter than SELECT 1 (in network transfer, as well as in response time)... i have not tried this myself yet.. but just wanted to share as an alternative... – RVN Nov 03 '15 at 14:51
  • I guess "DO 1" is the better method for this process, except if the function should verify the server really answers (but we are on a too high level api for that) – John Oct 17 '16 at 00:57
  • 1
    If you're using PHP 5.6+, it'd be better to unpack your arguments over using reflection, as explained here: http://php.net/manual/en/migration56.new-features.php#migration56.new-features.splat $this->pdo = new \PDO(...$this->params); – bradynpoulsen Feb 14 '18 at 20:07
-3

You can use like this:

# connect to the database
try {
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

}
catch(PDOException $e) {
    echo "Connection error " . $e->getMessage() . "\n";
    exit;
}
Lucas Henrique
  • 1,407
  • 1
  • 11
  • 15
  • 1
    The questions asks how to ping and reconnect not how to catch a connection error! – transilvlad Feb 25 '15 at 14:41
  • There is a reason why mysql_ping is depreciated. It is no longer the correct way to do it. This IS the correct way. Though he should have gone into more detail – steve Apr 27 '15 at 23:09
  • mysql_ping is to check connection while script execution, not just after make the connection, in this way it's fully apreciated. – José Carlos PHP Jul 13 '16 at 09:13