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.

Nick Craig-Wood · Accepted Answer · 2013-03-14 07:40:20Z

3

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.

answered Mar 14, 2013 at 7:40

Nick Craig-Wood

54.4k13 gold badges131 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gustav Over a year ago

I looking for a library independent solution, but maybe it's quite simple, the driver should handle reconnects.

Nick Craig-Wood Over a year ago

I would expect the driver to handle reconnects. That is how it works in other languages I have used like python. The driver has more domain specific knowledge so should be able to do it better.

Jeremy Wall · Accepted Answer · 2013-03-14 02:20:43Z

0

Assuming ExtClient has a Connect or Reconnect method.

And also assuming that the BrokenPipe err is exported as a variable that you can match agains.

Then this should work if err == BrokenPipeErr { ExtClient.Connect(args ...SomeType) }

Those are a lot of assumptions though so you should probably tell us a little bit more information like what database you are connecting to. Which client lib you are using. And other such information.

answered Mar 14, 2013 at 2:20

Jeremy Wall

25.6k5 gold badges59 silver badges75 bronze badges

1 Comment

Gustav Over a year ago

Yeah, the client library is quite stupid, I don't want to discuss that particular library. Should the driver handle reconnects then. Maybe it's that simple. The Request-method knows about the error before I do so I guess it could do something about it aswell.

Collectives™ on Stack Overflow

What is a good pattern for reconnecting external databases?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related