idle_connection_ttl does not work as expected, ignored inside \Doctrine\DBAL\Connection::connect()

[ilnytskyi]

Bug Report

Q	A
Version	4.4.x
Previous Version if the bug is a regression	x.y.z

Summary

Doctrine\DBAL\Connection::connect() short-circuits on $this->_conn !== null and returns the cached driver connection without any liveness or idle-time check. When a gateway sits between the app and the database (haproxy, RDS Proxy, PgBouncer, Patroni, tcp_keepalive_time) and reaps the socket due to timeout server, every subsequent op routed through the wrapper Connection (which is all of them) returns that dead reference.

Symfony's symfony/doctrine-bridge works around this externally via Symfony\Bridge\Doctrine\Middleware\IdleConnection\Driver + Symfony\Bridge\Doctrine\Middleware\IdleConnection\Listener, but the listener only fires on kernel.request (no coverage inside a single in-flight handler) and the driver stamps "time since first connect" rather than "time since last use".

Filed here but redirected from symfony/doctrine-bridge to doctrine/dbal: symfony/symfony#64314.

Current behavior

In src/Connection.php at line 217:

protected function connect(): DriverConnection
{
    if ($this->_conn !== null) {
        return $this->_conn;   // <-- returns whatever's cached, no liveness check
    }
    // ... reconnect on null ...
}

A handler that does SELECT then a long external call then UPDATE keeps $this->_conn non-null the whole time, so the second call to connect() returns the cached reference. If the gateway already reaped the socket, the UPDATE throws. On PgSQL it's classified as Doctrine\DBAL\Exception\DriverException SQLSTATE[HY000] (see #6812), not Doctrine\DBAL\Exception\ConnectionLost, so even reactive catch (ConnectionLost) recovery doesn't help.

Doctrine\DBAL\Configuration has no idle-TTL setting today.

Expected behavior

Doctrine\DBAL\Connection::connect() should honor an optional idle TTL on its cached-return branch. When now - lastUsedAt >= ttl AND no transaction is active, close the cached connection so the existing reconnect path produces a fresh one. Transaction safety is trivial via the existing $this->transactionNestingLevel field. Default ttl = 0 (disabled) preserves current behavior.

Doctrine\DBAL\Connection::connect() becomes something like this:

protected function connect(): DriverConnection
{
    if ($this->_conn !== null) {
        $ttl = $this->_config->getIdleConnectionTtl();
        if (
            $ttl > 0
            && $this->transactionNestingLevel === 0
            && microtime(true) - $this->lastUsedAt >= $ttl
        ) {
            $this->close();
            // falls through to reconnect below
        } else {
            $this->lastUsedAt = microtime(true);
            return $this->_conn;
        }
    }

    try {
        $connection = $this->_conn = $this->driver->connect($this->params);
    } catch (Driver\Exception $e) {
        throw $this->convertException($e);
    }

    if ($this->autoCommit === false) {
        $this->beginTransaction();
    }

    $this->lastUsedAt = microtime(true);
    return $connection;
}

Why this location: connect() is the funnel every wire-touching method already calls (executeQuery, executeStatement, prepare, quote, lastInsertId, getServerVersion, getNativeConnection, all transaction primitives). One site, full coverage.

A Doctrine\DBAL\Driver\Middleware cannot substitute: Doctrine\DBAL\Driver\Connection has 10 separate methods and no funnel, and Doctrine\DBAL\Driver\Middleware\AbstractConnectionMiddleware::$wrappedConnection is private readonly so swap-inside requires reflection or full interface reimplementation.
As a trade-off described in #6351 Doctrine\DBAL\Connection::connect can be decorated but it would conflict with other wrappers like \Doctrine\DBAL\Connections\PrimaryReadReplicaConnection that also override connect()

How to reproduce

Standalone Docker Compose reproducer at
connection-reproducer.zip

bash ./play.sh

boots PostgreSQL behind haproxy with timeout server 30s, and two PHP containers running identical Symfony

Three scenarios:

Scenario	DATABASE_URL	Outcome
A	direct to postgres	[OK] SELECT 1 OK at t+35s
B	through haproxy, console command	[ERROR] Doctrine\DBAL\Exception\DriverException SQLSTATE[HY000]
C	through haproxy, messenger handler	same [ERROR], messenger retries

haproxy log shows cD (client timeout) at 30s on the failing run.

What is expected after a fix on this reproducer ?

• set haproxy timeout server 30s
• set doctrine.dbal.idle_connection_ttl to 25
• Doctrine\DBAL\Connection::connect() reconnects after TTL as expected. Scenario B and C passes

Current workarounds for Symfony

Symfony has a few boundary-based mechanisms for recovering a stale Doctrine connection. None can fire while the main thread is blocked.

• Symfony\Bridge\Doctrine\Middleware\IdleConnection\Listener fires on kernel.request. Never re-fires mid-request, no help while the handler is blocked.
• Symfony\Bridge\Doctrine\Messenger\DoctrinePingConnectionMiddleware pings between messenger messages. Runs before __invoke(), never mid-handler.
• Symfony\Component\Console\Event\ConsoleAlarmEvent (SIGALRM, #53533). Queued by the kernel during C calls (curl_exec, libpq); dispatches only after, by which point the socket is already reaped (compounded on PgSQL by Convert driver exceptions when starting transactions #6812).
• Subclassing Doctrine\DBAL\Connection and overriding protected connect() Reaches in-flight, but leans on @internal Connection::getParams() / ::getDriver() / ::getConfiguration() per Doctrine\DBAL\Connection::connect and @internal #5791; every shop rolls its own.

All of them operate at boundaries between units of work never in-flight.

[morozov]

idle_connection_ttl does not work as expected

The DBAL doesn't support such a configuration parameter. Why is it expected to work?

Note that this technique itself is a bandaid. Any connection can get closed for any reason at any point in time. A resilient application should handle that correspondingly (e.g. retry the idempotent query that failed due to the closed connection). The only job of the DBAL is to automatically mark the closed connection as such and lazily reconnect upon the next query (which it already does).

[ilnytskyi]

@morozov Ok, maybe not dbal package directly. As i see it shipped via DoctrineBundle

https://github.com/doctrine/DoctrineBundle/blob/3.2.x/src/DependencyInjection/DoctrineExtension.php#L546

            if ($connection['idle_connection_ttl'] > 0) {
                $ttlByConnection[$name] = $connection['idle_connection_ttl'];
            }

then it used by this middleware as storage in $connectionExpiries

https://github.com/doctrine/DoctrineBundle/blob/3.2.x/src/Middleware/IdleConnectionMiddleware.php

Then by symfony packages, connection is closed in between consumer messages or on each request like here https://github.com/symfony/doctrine-bridge/blob/8.1/Middleware/IdleConnection/Listener.php

So the technique to reconnect before idle ttl seems to be a built-in way to handle this situation just by closing the connection.

I agree about that:

Note that this technique itself is a bandaid. Any connection can get closed for any reason at any point in time. A resilient application should handle that correspondingly

I see there is also a case when the connection is closed mid query if it exceeds idle tll. But i did not find a clean way to globally handle such cases. The simplest option is to wrap Doctrine\DBAL\Connection::connect() to make

$this->_conn = null;

So every subsequent call of $connection = $this->connect(); in https://github.com/doctrine/dbal/blob/4.4.x/src/Connection.php would reconnect automatically.

But it still requires wrapping the connection class as single point, or implementing a custom driver wrapper, but in case of the driver, such check must be performed inside every method.

Regarding this:

The only job of the DBAL is to automatically mark the closed connection as such and lazily reconnect upon the next query (which it already does).

Not sure how it happens, we need to call Doctrine\DBAL\Connection::close() to clean the property before TTL reached.

    public function close(): void
    {
        $this->_conn                   = null;
        $this->transactionNestingLevel = 0;
    }

But if $this->_conn object already contains lost connection any query will cause exception.

I also found a bundle for mysql: https://github.com/facile-it/doctrine-mysql-come-back
And that bundle also wraps Connection class: https://github.com/facile-it/doctrine-mysql-come-back/blob/3.x/src/Connection.php but provides a bit advanced retry logic https://github.com/facile-it/doctrine-mysql-come-back/blob/3.x/src/ConnectionTrait.php#L89

So then what's the right way to approach this problem?
Is it ok to wrap Doctrine\DBAL\Connection::connect()?

[morozov]

[ERROR] Doctrine\DBAL\Exception\DriverException SQLSTATE[HY000]

This must be the real gap. The connection is closed but the driver doesn't see it as closed, so the rest of the error handling doesn't work as expected.

Could you debug or add some logging around Doctrine\DBAL\Driver\PDO\Exception::new() (I assume you use pdo_pgsql) and see if the exception representing the connection reaped by HAProxy contains any details that the DBAL discards? If there's some some useful signal, we can handle it.

Also look at ExceptionConverter implementations. Some of them process SQLSTATE, some error codes or their combinations. SQLSTATE[HY000] is not the only potential source of signal.