Stale DB connections never recover after idle (SSL SYSCALL EOF → 'no connection to the server') — needs detect+close+retry

[ineersa]

Symptom

After the MCP server has been running for a while (idle periods of an hour or more), queries against a Postgres connection start failing and never recover:

1. First failure after idle: SQLSTATE[HY000]: General error: 7 SSL SYSCALL error: EOF detected
2. Every query after that, on the same connection: SQLSTATE[HY000]: General error: 7 no connection to the server

The only way to recover is to restart the MCP server (e.g. /mcp → reconnect in Claude Code). Observed against Azure-hosted Postgres 17, but any idle-timeout/NAT/firewall in the path will produce the same thing.

Root cause

The server is a single long-lived stdio process (bin/database-mcp → DatabaseMcpCommand::execute() → blocking $server->run($transport)), and DBAL connections are created once at startup and cached for the process lifetime:

• DoctrineConfigLoader::loadConnection() (src/Service/DoctrineConfigLoader.php, ~line 296) calls DriverManager::getConnection() and stores the Connection in a private array.
• getConnection($name) (~line 59) returns that cached object with no freshness check.

doctrine/dbal is pinned to ^4.4 — and DBAL 4 removed ping() and all auto-reconnect logic (it existed in DBAL 3). So once the server side (Postgres idle_session_timeout, Azure gateway, NAT) drops the TCP session:

• the internal PDO handle inside the cached Connection is permanently dead,
• SafeQueryExecutor::execute() has no catch for connection-level errors — beginTransaction()/executeQuery() throw straight through,
• the error is wrapped as ToolUsageError(retryable: true) (QueryTool::mapThrowableToToolUsageError()), but that flag is purely advisory to the client — nothing server-side ever calls $conn->close(), so retrying hits the same dead handle forever.

A secondary wart: in SafeQueryExecutor::execute() the finally block calls $conn->rollBack() on the same dead connection, which throws again and can mask the original exception.

Proposed fix: detect broken connection → close() → retry once

DBAL 4's Connection::close() nulls the internal driver connection, so the next call transparently reconnects. That's the whole recovery mechanism — it just needs to be triggered:

public function execute(Connection $conn, string $sql): array
{
    $this->validateSql($sql);
    return $this->doExecute($conn, $sql, isRetry: false);
}

private function doExecute(Connection $conn, string $sql, bool $isRetry): array
{
    try {
        $conn->beginTransaction();
        $stmt = $conn->executeQuery($sql);
        return $stmt->fetchAllAssociative();
    } catch (\Throwable $e) {
        if (!$isRetry && $this->isBrokenConnection($e)) {
            $conn->close(); // resets the driver handle; next call reconnects
            return $this->doExecute($conn, $sql, isRetry: true);
        }
        throw $e;
    } finally {
        if ($conn->isTransactionActive()) {
            try { $conn->rollBack(); } catch (\Throwable) { /* dead conn: rollback is moot */ }
        }
    }
}

private function isBrokenConnection(\Throwable $e): bool
{
    $msg = $e->getMessage();
    return str_contains($msg, 'SSL SYSCALL error')
        || str_contains($msg, 'EOF detected')
        || str_contains($msg, 'no connection to the server')     // pgsql
        || str_contains($msg, 'server has gone away')            // mysql
        || str_contains($msg, 'Lost connection')                 // mysql
        || str_contains($msg, 'Communication link failure');     // sqlsrv 08S01
}

Notes:

• The same handling is needed on the schema path (DoctrineConfigLoader::getTableNames() / introspectTable()), which uses the same cached connections — a cleaner variant is a DBAL driver middleware that wraps query()/exec()/beginTransaction() with detect→close→retry, so every caller (including introspection) gets it for free, same as the existing ReadOnly middleware.
• Reconnecting re-runs the driver connect() path, so the ReadOnlyDriver's SET default_transaction_read_only = on is re-applied automatically — the read-only guarantee survives the reconnect.
• Belt-and-braces (optional): support keepalives=1&keepalives_idle=60 style DSN params for pdo_pgsql in the YAML so the socket is less likely to die in the first place. But the close-and-retry fix is the part that makes the server self-heal.

Happy to PR this if the middleware vs. executor-level approach preference is stated.