Hacker News Reader

Comment by jijji | Hacker News Reader

I encountered this same issue a few years ago when using the Go mysql driver, and the simple fix was calling db.Ping() before doing a query, which checks the connection and allows the query to proceed.

View on HN

The general approach I've used in other languages is if the mysql client API is implemented correctly [1], when it encounters a closed connection, will return a CR_SERVER_GONE_ERROR error to the calling code. You catch when this type of error occurs, re-initialize the connection, and replay the last query/transaction.

[1] https://dev.mysql.com/doc/refman/8.0/en/gone-away.html

So you have to make a round trip to the database before making a query? Yikes.

I think you could also accomplish the same thing with a goroutine. Just a struct that stores a RW mutex and a sql.DB connection that has a .Start() function that runs the ping on a loop. On each start of the loop, lock the write mutex, run the ping, reconnect if it fails, and then release the mutex. Then just wrap each of the functions in sql.DB so that it gets a read lock before it executes the sql.DB function and defer the release.

It's still a performance penalty, but it moves the round trip to a 99% latency situation instead of an every query situation.

You don't need a round-trip, you can push a ping request into the pipe immediately followed by your actual query. You don't care about the actual result of the ping unless your query fails - "if the ping also failed, this was a dead connection so I can retry the query, but if the ping was OK and the query was not, the query failed"

Reply on news.ycombinator.com