Driver Bad Connection Errors in Go and What Causes Them

The error appears in logs as one of these, depending on the driver:

driver: bad connection
invalid connection
write: broken pipe
unexpected EOF

It comes out of nowhere in a service that has been healthy for hours, usually after a quiet period, and usually from a path that is not retried. It looks like a bug in the Go program and almost never is. It means a connection in the pool was already dead when the program used it, because something between you and the server ended it. This article reproduces the error with three drivers on Go 1.25.6, shows which operations recover on their own and which do not, and gives the settings that keep it away. The pool mechanics are in how database/sql works, and the failure is related to connection leaks. It sits in the database cornerstone.

What it means

A pooled connection is a TCP connection that may sit idle for a long time. Between the Go program and the database there can be several things that end idle or long lived connections without telling the client: the database server itself (a restart, a failover, pg_terminate_backend, an idle timeout), a connection pooler such as PgBouncer, a cloud load balancer with an idle timeout of a few minutes, or a firewall or NAT that drops tracking entries. The client finds out only when it next writes to the socket, and gets an error then.

database/sql has a name for this: a driver may return driver.ErrBadConn to say "this connection is unusable, do not use it again". The package then discards it and, for operations it can safely repeat, retries on another connection, up to a few times. The message driver: bad connection is that sentinel escaping to the caller, which happens when the retry is not allowed or has been used up.

Reproducing it

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"log"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"
	_ "github.com/jackc/pgx/v5/stdlib"
	_ "github.com/lib/pq"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable&application_name=badconn661"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func killAll(admin *sql.DB) int {
	var n int
	must(admin.QueryRow("select count(*) from (select pg_terminate_backend(pid) from pg_stat_activity where application_name = 'badconn661') t").Scan(&n))
	time.Sleep(200 * time.Millisecond)
	return n
}

func short(err error) string {
	if err == nil {
		return "ok"
	}
	s := err.Error()
	if i := strings.Index(s, "\n"); i > 0 {
		s = s[:i]
	}
	return s
}

func main() {
	mysql.SetLogger(log.New(io.Discard, "", 0)) // the driver logs with a timestamp
	ctx := context.Background()
	admin, err := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	must(err)
	defer admin.Close()

	// 1. An idle pooled connection is killed by the server, then the next call uses it.
	db, err := sql.Open("pgx", dsn)
	must(err)
	defer db.Close()
	db.SetMaxOpenConns(1)
	var n int
	must(db.QueryRowContext(ctx, "select 1").Scan(&n))
	fmt.Println("1. sessions the server ended while the connection sat idle:", killAll(admin))
	err = db.QueryRowContext(ctx, "select 2").Scan(&n)
	fmt.Println("   the next query on the pool:", short(err), "| value:", n)

	// 2. The same, inside a transaction: nothing can be retried for you.
	tx, err := db.BeginTx(ctx, nil)
	must(err)
	_, err = tx.ExecContext(ctx, "create temp table t661 (id int)")
	must(err)
	killAll(admin)
	_, err = tx.ExecContext(ctx, "insert into t661 values (1)")
	fmt.Println("2. a statement inside a transaction whose session was ended:", short(err))
	fmt.Println("   Commit then returns:", short(tx.Commit()))

	// 3. A statement that the server ends midway: the result is unknown.
	killer := time.AfterFunc(300*time.Millisecond, func() { killAll(admin) })
	_, err = db.ExecContext(ctx, "select pg_sleep(2)")
	killer.Stop()
	fmt.Println("3. the session ended during pg_sleep(2):", short(err))

	// 4. After a failure the pool recovers: the dead connection is dropped and a new one opened.
	must(db.QueryRowContext(ctx, "select 3").Scan(&n))
	s := db.Stats()
	fmt.Printf("4. the next call works (value %d); pool open=%d inUse=%d idle=%d\n", n, s.OpenConnections, s.InUse, s.Idle)

	// 5. A connection that outlives the server's idle limit is a common cause; a lifetime shorter than that limit avoids it.
	lim, err := sql.Open("pgx", dsn)
	must(err)
	defer lim.Close()
	lim.SetMaxOpenConns(1)
	lim.SetConnMaxLifetime(300 * time.Millisecond)
	must(lim.QueryRowContext(ctx, "select pg_backend_pid()").Scan(&n))
	first := n
	time.Sleep(400 * time.Millisecond)
	must(lim.QueryRowContext(ctx, "select pg_backend_pid()").Scan(&n))
	fmt.Println("5. with ConnMaxLifetime 300ms, the backend after 400ms is a new session:", first != n)

	// 6. Other drivers report the same event with the message the article is named after.
	pq, err := sql.Open("postgres", dsn)
	must(err)
	defer pq.Close()
	pq.SetMaxOpenConns(1)
	must(pq.QueryRowContext(ctx, "select 1").Scan(&n))
	killAll(admin)
	err = pq.QueryRowContext(ctx, "select 2").Scan(&n)
	fmt.Println("6. lib/pq, idle connection killed, plain query:", short(err), "| value:", n)
	ptx, err := pq.BeginTx(ctx, nil)
	must(err)
	_, err = ptx.ExecContext(ctx, "select 1")
	must(err)
	killAll(admin)
	_, err = ptx.ExecContext(ctx, "select 2")
	fmt.Println("   lib/pq, inside a transaction:", short(err))
	_ = ptx.Rollback()

	my, err := sql.Open("mysql", "root:secret@tcp(127.0.0.1:33099)/lab")
	must(err)
	defer my.Close()
	my.SetMaxOpenConns(1)
	var id int
	must(my.QueryRowContext(ctx, "select connection_id()").Scan(&id))
	mysqlAdmin, err := sql.Open("mysql", "root:secret@tcp(127.0.0.1:33099)/lab")
	must(err)
	defer mysqlAdmin.Close()
	mtx, err := my.BeginTx(ctx, nil)
	must(err)
	must(mtx.QueryRowContext(ctx, "select connection_id()").Scan(&id))
	_, err = mysqlAdmin.ExecContext(ctx, fmt.Sprintf("kill %d", id))
	must(err)
	time.Sleep(200 * time.Millisecond)
	_, err = mtx.ExecContext(ctx, "select 1")
	fmt.Println("7. go-sql-driver/mysql, inside a transaction whose connection was killed:", short(err))
	_ = mtx.Rollback()
}

1. sessions the server ended while the connection sat idle: 1
   the next query on the pool: ok | value: 2
2. a statement inside a transaction whose session was ended: FATAL: terminating connection due to administrator command (SQLSTATE 57P01)
   Commit then returns: conn closed
3. the session ended during pg_sleep(2): FATAL: terminating connection due to administrator command (SQLSTATE 57P01)
4. the next call works (value 3); pool open=1 inUse=0 idle=1
5. with ConnMaxLifetime 300ms, the backend after 400ms is a new session: true
6. lib/pq, idle connection killed, plain query: ok | value: 2
   lib/pq, inside a transaction: driver: bad connection
7. go-sql-driver/mysql, inside a transaction whose connection was killed: invalid connection

The program uses a second connection to end the application's sessions with pg_terminate_backend, or kill on MySQL, which has the same effect on the client as a restart or a dropped connection. It does that in four situations.

An idle connection, then a plain query. The pool held one idle connection. The server ended it, and the next QueryRowContext returned its value, 2, with no error. The pool noticed the dead connection, discarded it and retried the query on a new one. lib/pq behaved the same way: a plain query after an idle kill returned its value. This is the common case, and it is why the error is rare in simple programs: a single statement is safe to repeat, so the package repeats it.

A transaction. The program began a transaction and ran a statement, then ended the session and ran another. Now the outcome differed by driver:

• pgx returned FATAL: terminating connection due to administrator command (SQLSTATE 57P01), and Commit afterwards returned conn closed.
• lib/pq returned driver: bad connection.
• go-sql-driver/mysql returned invalid connection.

A transaction is state on one connection: its temporary tables, locks and uncommitted writes lived in the session that died. The package cannot run the statement elsewhere, because the rest of the transaction is gone, so the error goes to the caller. This is the situation where you see the message in production: the first statement of a request ran, a connection died, the second failed.

A statement in flight. A pg_sleep(2) was running when the session ended. It returned the same SQLSTATE 57P01. The client cannot tell whether the statement had any effect: for an insert, the row may or may not have been written. Do not blindly retry non idempotent writes after this error.

Recovery. The next call on the pool worked, with open=1 inUse=0 idle=1: the dead connection was dropped and a new one opened, so the pool heals by itself and the failure is limited to the operation that was using the connection.

Finding the cause

Because the cause is outside the program, find which part ended the connection:

1. A server restart or failover. Check the server log and the time; a deploy or a managed service maintenance window lines up with a burst of errors.
2. An idle timeout in a proxy or load balancer. The tell is errors after quiet periods, at a regular interval such as 350 seconds or 10 minutes, on the first request after a lull. Check the idle timeout of every hop: PgBouncer's server_idle_timeout and server_lifetime, the cloud load balancer's idle timeout (often 60 seconds to 10 minutes), and NAT table timeouts.
3. The database's own limits: idle_in_transaction_session_timeout, idle_session_timeout and tcp_keepalives_* on PostgreSQL, wait_timeout on MySQL (28800 seconds by default, often set much lower).
4. Out of memory kills of the server process, visible in the host's logs.
5. An administrator or a script terminating sessions, as the lab does. pg_stat_activity and the server log show who.

The fix

1. Set SetConnMaxLifetime shorter than the shortest timeout between the client and the server. The fifth block of the lab sets 300ms and shows the backend process after 400ms is a different session: the pool retired the old connection before it could go stale. In production use minutes, such as 5 minutes, below the proxy's idle timeout, with a little jitter. Set SetConnMaxIdleTime too, to close connections that have been idle for longer than they are useful.
2. Retry the whole transaction on a connection error, when the transaction is idempotent, as in unit of work: a function passed to a helper that begins a new transaction and runs it again, a limited number of times. Distinguish the error: for lib/pq compare with driver.ErrBadConn; for pgx check for a *pgconn.PgError with code class 08 or 57P01, or pgconn.SafeToRetry(err), which tells you whether the driver knows the statement was never sent.
3. Enable TCP keepalives so the operating system detects dead peers, and so that idle connections generate traffic that keeps stateful middleboxes from forgetting them. pgx and libpq take keepalives_idle; the MySQL driver has its own settings.
4. Do not hold connections idle in a transaction. An open transaction that waits on a slow call is the most exposed state. Keep transactions short.
5. Health check and drain during known events: when you fail over a database, restart the application pool or expect a short burst of errors, and have retries ready.
6. Do not set the pool's MaxIdleConns high with a long idle time against an aggressive timeout: an idle pool of 50 connections will all die together.

What not to do

Do not catch the error and retry a statement inside a transaction on the same *sql.Tx: it is dead. Do not retry writes of unknown outcome without a unique key to make them safe; see upserts and unique violations. And do not "fix" it by setting ConnMaxLifetime to a few seconds across the board: it works and costs a reconnect, with its TLS handshake and authentication, every few seconds per connection. Choose the longest value that stays under the shortest timeout.

The package behaviour is documented in the database/sql/driver package, which defines ErrBadConn, and PostgreSQL's connection settings in the manual's pages on client connection defaults and connection settings.