Tereta/Pdo

Introduction

This is a wrapper around PDO with lazy connection initialization and automatic reconnection on "connection lost" errors (MySQL gone away and connect-class errors — codes 1053/2002/2003/2005/2006/2013/4031; PostgreSQL connection/operator-intervention SQLSTATE — 08xxx, 25P03, 57P0x). It is designed for long-running workers and daemons whose TCP connection to the database may time out between queries. Transactions are not silently restarted on disconnect — the exception is propagated upwards so the calling code can decide what to do.

• A bare PDO decorator with no dependency on Doctrine/Laravel/any framework — for plain PDO.
• Covers both MySQL (1053/2002/2003/2005/2006/2013/4031) and PostgreSQL SQLSTATE codes (08000/08001/08003/08004/08006/08P01, 25P03, 57P01/57P02/57P03/57P05).
• Explicitly propagates the exception if the disconnect happens inside a transaction — the wrapper never silently retries a transactional statement.
• Requires PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION (the wrapper relies on PDOException to detect disconnects; ERRMODE_SILENT will not trigger reconnect).

Requirements

• PHP 8.2+
• ext-pdo

Installation

Install via Composer:

composer require tereta/pdo

Git repository

https://gitlab.com/tereta/library/pdo.git 🔗

Usage

Example of configuring a database connection:

use Tereta\Pdo\Connection as PdoConnection;  

new PdoConnection(
    'mysql:host=127.0.0.1;port=3306;dbname=developer;charset=utf8mb4',
    'developer',
    'developer'
);

The Tereta\Pdo\Connection object mirrors the PDO API (same method signatures). It does extend \PDO so that $conn instanceof \PDO is true and existing \PDO type-hints keep working, but it does not call parent::__construct() — the native PDO state inside the parent class is intentionally uninitialized; every operation is delegated to an internal lazy-built \PDO instance. Code that relies on native \PDO internals (e.g. via reflection) should type-hint against Tereta\Pdo\Interfaces\Pdo instead. For semantics of the individual methods see the PDO manual: https://www.php.net/manual/en/book.pdo.php

Example of a long-running worker that reads jobs and reconnects to the database transparently for the application code:

<?php

declare(strict_types=1);

use Tereta\Pdo\Connection;

require __DIR__ . '/vendor/autoload.php';

$pdo = new Connection(
    dsn: 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4',
    username: 'app',
    password: 'secret',
    options: [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

while (true) {
    $statement = $pdo->prepare('SELECT id, payload FROM jobs WHERE status = :status LIMIT 10');
    $statement->execute([':status' => 'pending']);

    foreach ($statement->fetchAll() as $row) {
        $pdo->beginTransaction();
        try {
            $update = $pdo->prepare('UPDATE jobs SET status = :status WHERE id = :id');
            $update->execute([':status' => 'done', ':id' => $row['id']]);
            $pdo->commit();
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    sleep(5);
}

Reconnect Configuration

Reconnect timeouts and the retry window can be tuned via fluent setters. Values are in seconds, and 0 is a valid value (disables the pause and/or retry limit):

$pdo
    ->setReconnectTimeInitial(1)   // Pause before the first retry (default: 1 second)
    ->setReconnectTime(15)         // Pause between subsequent retries (default: 15 seconds)
    ->setReconnectMaxTime(60 * 30) // Total retry window in seconds (default: 30 minutes)
;

The number of retry attempts is computed as the integer (floored) division setReconnectMaxTime / setReconnectTime, and the retry budget is considered exhausted once the attempt counter reaches that number. As a consequence the value is effectively off by one: with setReconnectMaxTime equal to setReconnectTime the budget is one attempt and it is exhausted immediately, so the first disconnect raises ConnectionLost with zero actual retries; setReconnectMaxTime smaller than setReconnectTime behaves the same way. To get N real retries set setReconnectMaxTime to at least (N + 1) × setReconnectTime (e.g. the defaults — 1800s / 15s — give 119 retries).

• setReconnectTime(0) — testing only, do NOT use in production. It removes both the retry limit and the pause between attempts. Against a permanently unreachable database the reconnect loop turns into an unbounded busy-loop: 100% CPU, a flood of connection attempts, and EVENT_RECONNECT_EXHAUSTED is never raised — the wrapper keeps reconnecting forever until the database comes back. That "reconnect until the DB is up" behavior is intentional and convenient in tests, but in production always set a non-zero setReconnectTime() so attempts are paced and the retry budget can be exhausted.
• setReconnectTimeInitial(0) only removes the pause before the first retry; subsequent retries are still paced by setReconnectTime(). It is safe in production (it just makes the first reconnect attempt immediate) and is mainly handy to speed up the test suite.

Negative values passed to the setters raise InvalidArgumentException.

Known Limitations

Reconnect replays statements at-least-once — transparent retry is only safe for reads and idempotent writes. When the connection drops after the server has already applied a write but before the client received the acknowledgement (the classic "server has gone away" race), the wrapper cannot distinguish "never executed" from "executed, ack lost". It re-runs the statement, so a non-idempotent INSERT/UPDATE/DELETE issued through exec() or Statement::execute() outside a transaction can be applied twice. SELECT and idempotent statements are safe. For non-idempotent writes choose one of:

• wrap the write in a transaction — a disconnect inside a transaction raises ConnectionLost instead of silently retrying (see below), so the caller decides;
• make the statement idempotent (INSERT ... ON DUPLICATE KEY UPDATE, a WHERE guard, a dedup/idempotency key);
• disable transparent retry with $pdo->setReconnect(false) and handle reconnection explicitly at the application level.

// Unsafe: a gone-away after the row was inserted but before the ack
// will run this a second time -> duplicate row.
$pdo->prepare('INSERT INTO payments (order_id, amount) VALUES (?, ?)')
    ->execute([$orderId, $amount]);

// Safe: idempotent upsert keyed by order_id.
$pdo->prepare(
    'INSERT INTO payments (order_id, amount) VALUES (?, ?)
     ON DUPLICATE KEY UPDATE amount = VALUES(amount)'
)->execute([$orderId, $amount]);

Reconnect only triggers on prepare and execute. If the connection drops between execute() and fetch()/fetchAll()/iteration, the server-side cursor is gone and the wrapper cannot replay the fetch safely (a fresh execute may return different rows due to concurrent transactions). Instead of a transparent retry, the wrapper raises a typed Tereta\Pdo\Exceptions\ConnectionLost (which extends PDOException for backwards compatibility):

try {
    foreach ($statement as $row) {
        process($row);
    }
} catch (Tereta\Pdo\Exceptions\ConnectionLost $e) {
    // Connection died mid-fetch. Decide per business logic:
    // re-run the whole query, skip the batch, bubble up.
} catch (PDOException $e) {
    // Application-level SQL error — not a disconnect.
}

The same exception is raised when the connection drops inside a transaction — the server has already rolled the transaction back, transparent retry would be unsafe, so the wrapper hands you a typed signal instead.

Reconnect is wired only into write/query entry points. The reconnect loop is invoked from exec(), prepare(), query() and Statement::execute(). The metadata/utility methods quote(), lastInsertId(), errorCode() and errorInfo() go through the underlying PDO directly — if the connection is dead they will raise a raw PDOException (not ConnectionLost). This is intentional: lastInsertId() and the error accessors are bound to the dead session and have no meaningful value on a fresh connection, so a transparent retry would be misleading.

setAttribute() / getAttribute() are managed by the wrapper, not delegated blindly. setAttribute() records the value in an internal attribute map and returns true without touching the live connection; the recorded attributes (together with the constructor $options) are (re)applied every time a PDO is built, so they survive reconnects. getAttribute() is lazy and never opens a connection on its own: while connected it reads from the live PDO (so driver-only attributes such as PDO::ATTR_SERVER_VERSION work); before the first connect — or after a disconnect() — it returns the recorded value, or null for attributes that were never set and would otherwise come from the driver. Neither method raises PDOException for a dead connection.

The list of disconnect codes is hard-coded. If your driver returns a code that is not in the list (MySQL: 1053/2002/2003/2005/2006/2013/4031; PostgreSQL SQLSTATE: 08000/08001/08003/08004/08006/08P01/25P03/57P01/57P02/57P03/57P05), no reconnect will happen. Extending the list currently requires a fork or a custom strategy via Tereta\Pdo\Factories\Strategy\Disconnection::setDriver().

getPdo() and reconnect() are internal. getPdo() is protected (accessible to subclasses, e.g. for the test seam), reconnect() is private. The retry loop is reachable only through the standard methods exec(), prepare(), query() and Statement::execute(). There is intentionally no public escape hatch to the native PDO from outside — every operation that goes through the wrapper must go through the reconnect logic.

Observer

For integration with logging, metrics, tracing or alerts the library exposes a single observation point — setObserver(). It accepts one Closure invoked on every meaningful event in the reconnect cycle:

$pdo->setObserver(function (int $eventCode, mixed $dataObject): void {
    // route to whatever you need
});

Events (constants of Tereta\Pdo\Connection):

Constant	When	$dataObject
EVENT_RECONNECT	After a successful reconnect, before the next attempt of the query.	PDOException — the original error that triggered the reconnect.
EVENT_RECONNECT_EXHAUSTED	Retry budget exhausted, right before throw ConnectionLostException.	PDOException — the last error.
EVENT_RECONNECT_TRANSACTION	Disconnect happened inside a transaction, right before throw ConnectionLostException.	PDOException — the disconnect error.

The default observer is a no-op. Setting one is optional.

Example: bridge to a PSR-3 logger:

$pdo->setObserver(function (int $eventCode, mixed $e) use ($logger): void {
    [$level, $msg] = match ($eventCode) {
        Connection::EVENT_RECONNECT             => ['warning', 'PDO reconnect performed'],
        Connection::EVENT_RECONNECT_EXHAUSTED   => ['error',   'PDO reconnect attempts exhausted'],
        Connection::EVENT_RECONNECT_TRANSACTION => ['error',   'PDO disconnect inside transaction'],
    };
    $logger->log($level, $msg, [
        'sqlstate'  => $e->errorInfo[0] ?? null,
        'errorCode' => $e->errorInfo[1] ?? null,
        'message'   => $e->getMessage(),
    ]);
});

Example: metrics counter (Prometheus):

$pdo->setObserver(fn(int $code) => match ($code) {
    Connection::EVENT_RECONNECT             => $metrics->inc('pdo_reconnect_total'),
    Connection::EVENT_RECONNECT_EXHAUSTED   => $metrics->inc('pdo_reconnect_exhausted_total'),
    Connection::EVENT_RECONNECT_TRANSACTION => $metrics->inc('pdo_reconnect_in_tx_total'),
});