14

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 exists or not and if it does not exit, then I need to connect the database to continue the query execution.

I am new to the MySQL PDO. How should I handle this situation?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Raja
  • 3,477
  • 12
  • 47
  • 89

2 Answers2

10

I tried to find a solution for the same problem and I found the following 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);
    }
}

The full story is in 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".

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Cristian Ciocău
  • 1,024
  • 2
  • 10
  • 14
  • 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,380
  • 1
  • 11
  • 15
  • 2
    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