阅读中文版本

MySQL Lost Connection / Read-Only — Failover or Restart

Diagnose "MySQL server has gone away" and read-only errors by distinguishing idle timeouts, restarts, and failover to a read-only replica.

mysql -e "SHOW VARIABLES LIKE 'wait_timeout'; SHOW GLOBAL STATUS LIKE 'Uptime'"

mysql -e "SELECT @@read_only, @@super_read_only"

Symptoms

When you hit “MySQL Lost Connection / Read-Only — Failover or Restart”, guessing wastes time. The guide below follows evidence: reproduce the symptom, run targeted diagnostics, identify the root cause, apply the fix, and verify it with concrete checks.

Observed symptoms include:

• “MySQL server has gone away” (error 2006) after a period of idle time
• Writes fail with “The MySQL server is running with the –read-only option”
• Connections drop in bursts under load

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Check timeouts and server uptime

mysql -e "SHOW VARIABLES LIKE 'wait_timeout'; SHOW GLOBAL STATUS LIKE 'Uptime'"

A short wait_timeout plus low uptime explains idle disconnects and recent restarts.

Step 2: Confirm read-only status

mysql -e "SELECT @@read_only, @@super_read_only"

read_only=1 means this server is a replica; writes must go to the primary.

Likely contributors, in order of frequency:

• The connection exceeded wait_timeout and was closed by the server
• The server restarted (crash, deploy, or maintenance) during the session
• A failover routed writes to a read-only replica

Root Cause

The client-held connection can be closed by the server (idle timeout, restart, or failover) independently of application logic. Read-only errors indicate the connection landed on a replica after failover, not a permissions problem.

The Fix

Enable connection pooling with validation and short max lifetime, use TCP keepalive or periodic pings, reconnect-and-retry on 2006, and route writes to the primary endpoint.

Watch out: Blindly retrying non-idempotent writes can duplicate data; retry only idempotent statements or use a transaction with proper isolation.

// Node mysql2 pool with validation and lifetime control
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  waitForConnections: true,
  connectionLimit: 10,
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000,
  maxIdle: 10,
  idleTimeout: 60000,
});

// retry only on connection-loss errors
async function write(sql, params, tries = 3) {
  for (let i = 1; i <= tries; i++) {
    try { return await pool.execute(sql, params); }
    catch (e) {
      if (e.code === 'PROTOCOL_CONNECTION_LOST' || e.code === 'ECONNRESET') continue;
      throw e;
    }
  }
  throw new Error('write failed after retries');
}

Verify and Prevent

Simulate a restart and confirm the pool reconnects and the app recovers; verify writes succeed against the primary endpoint.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.