How to restore the connection to MySQL via PDO in PHP script?

Started by allricjohnson1, Mar 22, 2023, 07:17 AM

Previous topic - Next topic

allricjohnson1Topic starter

A PHP script runs on a Windows Server in CLI mode (similar to a daemon) and receives data that it writes to a database.

The connection to the database is established using a self-written class called "DB," which makes use of PDO:

class DB {
    protected static $dbInstance;

    private function __construct() {
        return self;
    }

    private function __clone() {
        return self;
    }

    private function __wakeup() {
        return self;
    }

    public static function getDB () {
        if (is_null(self::$dbInstance)) {
            try {
                $cfg = Config::getConfig();
                self::$dbInstance = new PDO(
                    'mysql:host='.$cfg['dbHost'].';dbname='.$cfg['dbName'].''
                    , $cfg['dbUser']
                    , $cfg['dbPass']
                );
            }
            catch (PDOException $e) {
                return false;
            }
        }
        return self::$dbInstance;
    }
}

When data is continuously processed, the connection functions properly. However, after the script remains idle for more than 16 hours (possibly less), the data ceases to be recorded. Checking the connection for null did not yield any results. I can speculate that it is necessary to somehow verify the status of the connection.

Question: How can I check the connection to MySQL using PDO?
  •  

pavithrathiva

Here's a similar code snippet:

private $lastPing = 0;

public function ping() {
    if ((time() - $this->lastPing) < 10) {
        return;
    }

    try {
        self::$dbInstance->query('SELECT 1');
    }
    catch {
        self::$dbInstance = NULL;
        self::getDB();
    }

    $this->lastPing = time();
}

If I understand correctly, in your standalone application, you may not need to ping the server every time. Instead, you could set it up to ping no more than once every 10 seconds. This way, when there are consecutive requests, you won't check the connection status repeatedly, but rather catch exceptions if the connection is broken due to a server shutdown, for example.
  •  

sasablogger

To ensure that exceptions are generated, you can modify the PDO constructor by passing an array as the fourth parameter, with the option `PDO::ATTR_ERRMODE` set to `PDO::ERRMODE_EXCEPTION`.

If the connection to the database is interrupted, the MySQL client on the server will always return the error "MySQL server has gone away." You can catch this error and handle it appropriately.
  •  

fallfro

To ensure a more stable connection, you can modify the code as follows:

$this->pdo = new \PDO(
                 "mysql:host=" . $this->DB_HOST . ";dbname=" . $this->DB_NAME . ";",
                 $this->DB_USER,
                 $this->DB_PW,
                 array(\PDO::ATTR_PERSISTENT => true)
             );

By setting the option `\PDO::ATTR_PERSISTENT` to `true`, the connection will be made using a persistent connection. This means that the connection will be maintained across multiple requests, reducing the overhead of establishing a new connection each time.

Adding this modification can help prevent connection dropouts and improve the overall performance of your application.
  •  

apolice9

To check the connection to MySQL using PDO, you can use the `getAttribute` method of the PDO object. Here's an example of how you can modify your code to check the connection status:

```php
class DB {
    // ...

    public static function getDB () {
        if (is_null(self::$dbInstance) || !self::$dbInstance->getAttribute(PDO::ATTR_CONNECTION_STATUS)) {
            try {
                $cfg = Config::getConfig();
                self::$dbInstance = new PDO(
                    'mysql:host='.$cfg['dbHost'].';dbname='.$cfg['dbName'].''
                    , $cfg['dbUser']
                    , $cfg['dbPass']
                );
            }
            catch (PDOException $e) {
                return false;
            }
        }
        return self::$dbInstance;
    }
}
```

In this modified code, we use the `getAttribute` method with the `PDO::ATTR_CONNECTION_STATUS` attribute to check if the connection is still active. If the connection is not active, or if `self::$dbInstance` is `null`, a new PDO connection is established.

By checking the connection status with `getAttribute`, you can ensure that the connection is always valid before making any database operations.
  •