What is a good pattern for reconnecting external databases?

Question

How can I efficiently reconnect to an external database when I find out that the persistent connection is down? If ExtClient loses connection, it would return "Broken pipe" on err.

func ListenForWork(cmdChannel <-chan *WorkCmd) {
    for {
        cmd, ok := <- cmdChannel
        if !ok {
            break
        }
        for { // Retry request until it's OK (`Broken pipe error` might destroy it)
            _, err := ExtClient.Request(cmd.Key, cmd.Value)
            if err == nil {
                break
            }
        }
    }
}

How can I, from this or another method, reconnect in an efficient way? Any improvements in this code is welcome as well. ExtClient does not reconnect on its own and is a global variable.

Answer 1

If you are using mymysql then you can use the auto reconnection interface.

From the docs

import (
    "github.com/ziutek/mymysql/autorc"
    _ "github.com/ziutek/mymysql/thrsafe" // You may also use the native engine
)

// [...]

db := autorc.New("tcp", "", "127.0.0.1:3306", user, pass, dbname)

// Initilisation commands. They will be executed after each connect.
db.Register("set names utf8")

// There is no need to explicity connect to the MySQL server
rows, res, err := db.Query("SELECT * FROM R")
checkError(err)

// Now we are connected.

// It does not matter if connection will be interrupted during sleep, eg
// due to server reboot or network down.

That said, from reading the sql docs and sql driver docs and the associated code, it looks like that if the SQL driver returns ErrBadConn then the sql package will retry with a new connection. This was only added in July 2012 so perhaps isn't well supported by SQL drivers yet.