SQLAlchemy OperationalError: Fix Dead Links Fast

Your app was green all week, then Monday's first request dies with sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, 'MySQL server has gone away'). You didn't change the SQL. The database is up. Re-running the same code sometimes passes — which makes it worse, because now nobody trusts the error or the rerun.

The confusion is that OperationalError is the connection's obituary, not the query's. It fires when the link between your pool and the database breaks: the server closed an idle connection overnight, the network dropped, the pool handed out a dead link, or too many threads queued for too few slots. Your SQL never even reached the server in most of these cases.

This guide gives you the connection-first reflex. You'll learn to separate link failures from SQL errors by reading e.orig, why gone-away links need pool_recycle, how pool_pre_ping catches corpses before checkout, how to size pools against real limits, and where safe retries live. By the end, a dead connection will be a two-minute diagnosis instead of a morning-long mystery.

Connection Failures vs SQL Errors: Reading e.orig

SQLAlchemy wraps driver failures in layers, and OperationalError is the layer that says transport, not syntax. When psycopg2 or pymysql hits a dead link, it raises its own error; SQLAlchemy catches it and re-raises as OperationalError with your statement, params, and the original tucked into .orig. Programmers who read only the outer message see their SQL quoted back and conclude the query broke. The query is just context the wrapper carries for logging — the cause sits in orig.

The snippet builds this structure with a refused connection inside and shows the correct reading order: wrapper type first (which family?), statement second (what was attempted?), orig third (what actually failed?). A ConnectionRefusedError in orig ends the debate instantly — no SQL review needed, page whoever owns the host and port. Make repr(e.orig) the first line of every OperationalError log record and half your future incidents will route themselves.

Internalize the family split while you're here. OperationalError means the link failed. ProgrammingError means the SQL failed. IntegrityError means constraints failed. Each demands a different owner and a different fix, and the exception type — not the message prose — is the routing label. Teach your team the three names and watch misrouted pages drop.

from sqlalchemy.exc import OperationalError

orig = ConnectionRefusedError(111, "Connection refused")
try:
    raise OperationalError("SELECT 1", {}, orig)
except OperationalError as e:
    print("wrapper:", type(e).__name__)
    print("statement:", e.statement)
    print("orig:", repr(e.orig))
    assert isinstance(e.orig, ConnectionRefusedError)
    print("diagnosis: database unreachable (link fault, not SQL fault)")

Gone-Away Links: pool_recycle and pool_pre_ping

Database servers reap idle connections on their own schedule — MySQL's wait_timeout defaults to eight hours — and they never notify your pool. The pool learns a link is dead only when your code tries to use it, which is why gone-away errors strike the first unlucky checkout after a quiet night. Two engine options close this gap from opposite sides, and you want both.

pool_recycle caps connection age: any link older than the limit is discarded and replaced, so links rotate before the server's reaper reaches them. Set it comfortably under the lowest idle timeout in your chain — 3600 seconds is the common choice against an eight-hour server default. pool_pre_ping covers everything recycle can't predict: it emits a lightweight ping at checkout and silently swaps dead links for fresh ones before your code ever sees them.

The snippet proves both options land on a live engine and that checkouts still work normally. The cost question answers itself: one ping per checkout is noise against real query time, and recycling an hour-old link costs one reconnect. Enable both on every pooled engine from day one — they're not performance tuning, they're the defaults the pool should arguably ship with.

import os
import tempfile
from sqlalchemy import create_engine, text

fd, path = tempfile.mkstemp(suffix=".db")
os.close(fd)
eng = create_engine(
    "sqlite:///" + path,
    pool_pre_ping=True,
    pool_recycle=3600,
    pool_size=5,
    max_overflow=10,
)
print("pool:", type(eng.pool).__name__)
assert type(eng.pool).__name__ == "QueuePool"
print(eng.pool.status())
with eng.connect() as conn:
    print("checkout ok:", conn.execute(text("SELECT 1")).scalar())
os.remove(path)
print("recycle+preping engine healthy")

Pool Limits and Saturation Arithmetic

Pools saturate from the inside, and the symptoms point everywhere except the pool. Six threads share two slots: four wait, and if the wait exceeds pool_timeout they fail with timeouts the database never caused. Server dashboards stay green because most attempts die queueing inside your own process. Engineers blame the database, the network, the ORM — everything except the arithmetic of threads versus slots.

The snippet runs this exact scenario against a real engine: six threads, two slots, no overflow. Everything succeeds here because the work is fast and the timeout generous — but stretch the work or shrink the timeout and you'll watch queue timeouts appear while the database idles. That experiment is worth running once, because feeling the queue form teaches more than any doc page.

Size pools like capacity, not vibes. pool_size covers steady-state threads, max_overflow absorbs bursts, and pool_timeout decides how fast saturation fails loudly instead of hanging. Multiply by process count — four gunicorn workers with twenty slots each can demand eighty server links — and cap the product against the database's max_connections. Then monitor checkout waits and alert before saturation, because a calm database with queuing clients is always a pool problem.

import os
import tempfile
import threading
from sqlalchemy import create_engine, text

fd, path = tempfile.mkstemp(suffix=".db")
os.close(fd)
eng = create_engine("sqlite:///" + path, pool_size=2, max_overflow=0,
                    pool_timeout=10, pool_pre_ping=True)
results, errors = [], []


def worker(n):
    try:
        with eng.connect() as conn:
            v = conn.execute(text("SELECT " + str(n))).scalar()
            results.append(v)
    except Exception as e:
        errors.append(repr(e))


threads = [threading.Thread(target=worker, args=(i,)) for i in range(6)]
[t.start() for t in threads]
[t.join() for t in threads]
print("succeeded:", sorted(results))
print("pool errors:", len(errors))
assert sorted(results) == [0, 1, 2, 3, 4, 5]
os.remove(path)
print("small pool survived by queueing within timeout")

Safe Retries Start From a Rolled-Back Session

Retry logic around databases is where good intentions write double charges. The danger is the half-finished transaction: statement one committed, the link died, statement two never ran. Retrying statement two alone corrupts the unit; retrying both without rollback double-applies statement one. The only safe retry boundary is the whole unit of work starting from a rolled-back session.

That gives you a strict pattern: catch OperationalError outside the transaction, call session.rollback() to discard partial state, then re-run the entire unit — bounded, with backoff, and only for link-class failures read from e.orig. Connect-phase failures (refused, DNS) are always safe to retry since nothing ran. Mid-stream failures demand the rollback first, no exceptions. Cap attempts at three; a database that's still dead after three deserves a page, not a fourth attempt.

Also decide what never retries. Constraint violations and programming errors are deterministic — retrying them just fails identically while holding resources. Gate your retry helper on the orig errno (gone-away, refused, timeout) and let every other family propagate immediately. A retry helper that only fires on link faults is short, safe, and reviewable; one that catches everything is a corruption machine with logging.

A Bounded Retry Helper You Can Ship

The retry helper deserves to be concrete, not aspirational. The snippet runs a real unit of work — one transaction, one statement — through a bounded helper with three attempts. Against a healthy database it passes first try; the structure is what matters: the try wraps the whole unit, the except names OperationalError narrowly, and exhaustion re-raises instead of returning a comfortable lie.

Notice what the helper doesn't do. It doesn't catch broad Exception, so programming errors propagate instantly instead of retrying doomed SQL. It doesn't return None on exhaustion, so callers can't mistake failure for empty results. And the unit itself uses eng.begin() so commit and rollback are scoped — if the link dies mid-unit, the context manager rolls back and the next attempt starts clean.

Extend this skeleton with two production touches: sleep with backoff between attempts (a second, then two), and a link-fault gate that inspects e.orig before retrying. Gone-away and refused? Retry. Constraint violation that somehow arrived as OperationalError? Propagate. That gate is five lines and it's the difference between resilience and a polite infinite loop against a database that needs a human.

from sqlalchemy import create_engine, text
from sqlalchemy.exc import OperationalError

eng = create_engine("sqlite:///:memory:", pool_pre_ping=True)


def unit_of_work(n):
    with eng.begin() as conn:
        return conn.execute(text("SELECT " + str(n * 2))).scalar()


def run_with_retry(n, attempts=3):
    last = None
    for i in range(attempts):
        try:
            return unit_of_work(n)
        except OperationalError as e:
            last = e
            print(f"attempt {i + 1} link fault: {e.orig!r}; retrying")
    raise last


print("result:", run_with_retry(21))
assert run_with_retry(21) == 42
print("bounded retry helper ok")

Reproducing OperationalError on Purpose

Drills beat docs for this error family because the skill is reading, not writing. The snippet raises genuine OperationalErrors carrying three classic driver errnos — 2006 gone-away, 2013 lost-mid-query, 2003 can't-connect — and prints each orig errno the way your logs should. Run it, cover the answers, and quiz yourself: which errno means recycle your pool, which means check the host, which means investigate the network mid-path?

The last block raises the advertised error and lets it propagate, mirroring production where the exception must reach your handler, not die in a notebook. Confirm your handler prints the orig errno, rolls back, and retries or pages — then delete nothing, because this drill file belongs in the runbook next to the retry helper.

Onboard every backend hire with this file and the unwrap habit. Ten minutes of raising and reading beats a quarter of misrouted pages. The error will still fire at awkward hours — idle links die on their schedule, not yours — but the engineer holding the pager will name the errno before the coffee finishes brewing. Re-run the drill after every driver upgrade, since new versions sometimes reword messages while errnos stay put. Keep the drill output in the runbook so future you can compare.

from sqlalchemy.exc import OperationalError

samples = {
    2006: "MySQL server has gone away",
    2013: "Lost connection during query",
    2003: "Can't connect to MySQL server",
}
for code, text in samples.items():
    try:
        raise OperationalError("SELECT 1", {}, OSError(code, text))
    except OperationalError as e:
        print(f"errno {e.orig.errno}: {e.orig.strerror}")
        assert e.orig.errno == code
print("orig errno drill ok")
try:
    raise OperationalError("SELECT 1", {}, OSError(2006, "gone away"))
except OperationalError:
    print("caught advertised error: OperationalError")
    raise