Handling MySQL Read‑Only Errors During Master‑Slave Switch in Go: Extending the Driver and Using rejectReadOnly

During a MySQL master‑slave switch, applications often encounter a short period of write errors reported as read‑only . The cause is that the old instance is set to read‑only before the DNS change, leaving existing connections that still point to the old instance.

PHP programs typically use short connections, so the read‑only error disappears within a few seconds when DNS propagates. Golang programs, however, usually employ a DB connection pool, which can keep reusing the stale connections and thus prolong the error window.

A simple Go program can be used to simulate the DB switch process:

package db_connect

import (
    "bytes"
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "testing"
    "time"
    _ "github.com/go-sql-driver/mysql"
    "github.com/txn2/txeh"
)

const (
    dbDomain     = "test_db"
    masterDBHost = "192.168.200.10"
    slaveDBHost  = "192.168.200.11"
)

var (
    masterDB *sql.DB
    hosts    *txeh.Hosts
)

func TestSwitchDB(t *testing.T) {
    db, err := sql.Open("mysql", "test_user:123456@tcp("+dbDomain+":3306)/test")
    if err != nil {
        log.Fatalln(err)
    }
    defer db.Close()
    db.SetConnMaxLifetime(time.Second * 30)

    go func() {
        <-time.After(5 * time.Second) // start switch after 5 s
        if err := SwitchDB(); err != nil {
            log.Fatalln(err)
        }
    }()

    tic := time.NewTicker(time.Second)
    defer tic.Stop()
    for {
        select {
        case <-tic.C:
            r, err := db.Exec("insert into user(name) values ('abc')")
            if err != nil {
                log.Println("insert fail:", err)
                break
            }
            rows, err := r.RowsAffected()
            if err != nil {
                log.Println(err)
                break
            }
            log.Println("insert successfully, affect rows:", rows)
        }
    }
}

func SwitchDB() error {
    // set master read‑only
    _, err := masterDB.Exec("SET GLOBAL read_only = ON")
    if err != nil {
        return err
    }
    log.Println("[Switch DB] Set read-only=ON successfully")

    // simulate 3 s DNS propagation
    <-time.After(3 * time.Second)

    // modify /etc/hosts to point to the slave
    hosts.AddHost(slaveDBHost, dbDomain)
    if err := hosts.Save(); err != nil {
        return err
    }
    log.Println("[Switch DB] Modify Host successfully")
    return nil
}

The program works as follows:

Two DB instances are defined (192.168.200.10 as master, 192.168.200.11 as slave) and an /etc/hosts entry maps test_db to the master.

The main goroutine writes a row to test_db every second.

An asynchronous goroutine starts after 5 s, sets the master to read‑only, waits 3 s, then changes the host entry to point to the slave.

Running the program shows that the read‑only error continues until the connection’s maximum lifetime (30 s) expires, which is unacceptable in production. DBA teams usually have to manually kill the lingering connections.

The root cause is connection reuse. If a connection that has encountered a read‑only error is returned to the pool, it will be reused and keep failing. The solution is to discard such connections by returning driver.ErrBadConn from the driver.

In Go’s database/sql package, the putConn function checks the error before returning a connection to the pool:

// putConn adds a connection to the db's free pool.
// err is optionally the last error that occurred on this connection.
func (db *DB) putConn(dc *driverConn, err error, resetSession bool) {
    ...
    if err == driver.ErrBadConn {
        // Don't reuse bad connections.
        db.maybeOpenNewConnections()
        db.mu.Unlock()
        dc.Close()
        return
    }
    if putConnHook != nil {
        putConnHook(db, dc)
    }
    added := db.putConnDBLocked(dc, nil)
    db.mu.Unlock()
    ...
}

The definition of driver.ErrBadConn is simple:

/// ErrBadConn should be returned by a driver to signal to the sql
// package that a driver.Conn is in a bad state (such as the server
// having earlier closed the connection) and the sql package should
// retry on a new connection.
var ErrBadConn = errors.New("driver: bad connection")

The sql package also retries a limited number of times when it sees driver.ErrBadConn :

func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error) {
    var rows *Rows
    var err error
    for i := 0; i < maxBadConnRetries; i++ { // maxBadConnRetries = 2
        rows, err = db.query(ctx, query, args, cachedOrNewConn)
        if err != driver.ErrBadConn {
            break
        }
    }
    if err == driver.ErrBadConn {
        return db.query(ctx, query, args, alwaysNewConn)
    }
    return rows, err
}

To discard connections on a read‑only error, the MySQL driver can be extended. A wrapper mysqlConn overrides Exec to detect MySQL error 1290 (read‑only) and return driver.ErrBadConn :

type mysqlConn struct {
    driver.Conn
}

func (mc *mysqlConn) Exec(query string, args []driver.Value) (driver.Result, error) {
    ec, ok := mc.Conn.(driver.Execer)
    if !ok {
        return nil, errors.New("unexpected Error")
    }
    r, err := ec.Exec(query, args)
    if err != nil && strings.Contains(err.Error(), "Error 1290") { // 1290 = read‑only
        mc.Conn.Close()
        return nil, driver.ErrBadConn
    }
    return r, err
}

A new driver is registered as mysqlv2 :

func init() {
    sql.Register("mysqlv2", &MySQLDriver{&mysql.MySQLDriver{}})
}

type MySQLDriver struct { *mysql.MySQLDriver }

func (d MySQLDriver) OpenConnector(dsn string) (driver.Connector, error) {
    rawConnector, err := d.MySQLDriver.OpenConnector(dsn)
    if err != nil { return nil, err }
    return &connector{rawConnector}, nil
}

type connector struct { driver.Connector }

func (c *connector) Driver() driver.Driver { return &MySQLDriver{&mysql.MySQLDriver{}} }

func (c *connector) Connect(ctx context.Context) (driver.Conn, error) {
    dc, err := c.Connector.Connect(ctx)
    if err != nil { return nil, err }
    return &mysqlConn{dc}, nil
}

Using the new driver in the test reduces the error window to about 1 second after the DNS change:

db, err := sql.Open("mysqlv2", "test_user:123456@tcp("+dbDomain+":3306)/test")
// ... same test logic ...

In practice, the official MySQL driver already provides a simpler solution via the DSN parameter rejectReadOnly . When this flag is true, the driver closes the connection and returns driver.ErrBadConn upon receiving error numbers 1290 or 1792:

func (mc *mysqlConn) handleErrorPacket(data []byte) error {
    // ...
    errno := binary.LittleEndian.Uint16(data[1:3])
    if (errno == 1792 || errno == 1290) && mc.cfg.RejectreadOnly {
        mc.Close()
        return driver.ErrBadConn
    }
    // ...
}

Therefore, the production‑ready way is to add ?rejectReadOnly=true to the DSN:

db, err := sql.Open("mysql", "test_user:123456@tcp("+dbDomain+":3306)/test?rejectReadOnly=true")

The observed behavior matches that of the custom mysqlv2 driver: the write error disappears almost immediately after the switch.

Summary : driver.ErrBadConn is a core error in Go’s database connection pool. By returning this error when a read‑only condition is detected, the pool discards the bad connection, preventing prolonged write failures during master‑slave failover.