5

After a period of inactivity, my go web service is getting a net.OpError with message read tcp x.x.x.x:52086->x.x.x.x:24414: read: connection reset by peer when executing the first postgres sql query. After the error, the subsequent requests will work fine.

The postgres database is hosted with compose.com which has haproxy in front of the postgres db. My go web app is using standard sql and sqlx.

I've tried running a ticker invoking db.Ping() every 15 minutes, but this hasn't fixed the issue.

Why is the go standard sql lib not handling these connection drops?

Nick
  • 61
  • 1
  • 4
  • What are you hoping for here? If the connection was reset, you need to reconnect. – Jonathan Hall May 14 '18 at 20:25
  • I'm using the go standard lib `sql.Open` stuff which is meant to handle connecting pooling, so I can't just simply reconnect. Also as I mentioned, after the error, the subsequent queries go through fine. I'll update the question for clarity. – Nick May 14 '18 at 20:31
  • Yes, the way to handle that with the Go standard library is to detect a connection failure, and retry the operation. – Jonathan Hall May 14 '18 at 20:41
  • 1
    Hmm, ok. Last paragraph on here appears to suggest the opposite. http://go-database-sql.org/errors.html – Nick May 14 '18 at 20:47
  • 2
    Wild guess: the connection to haproxy is still alive, but the connection to the backend times out and haproxy doesn't close the other end. I've seen similar issues with redis. What happens if you don't use haproxy? Regular pings won't help much because there is no guarantee that all connections in either pool see some traffic before they time out. – Peter May 14 '18 at 20:53
  • 2
    Yeah, I think I may have just come to a similar conclusion. I'm thinking of setting `db.SetConnMaxLifetime(time.Minute * 15)` which I think should flush out the dead connections. – Nick May 14 '18 at 21:02
  • Hi @Peter, looks like that did the trick. If you want to comment that as the answer, I'll give you the points. – Nick May 25 '18 at 08:58
  • Does this answer your question? [HAProxy closes long living TCP connections ignoring TCP keepalive](https://stackoverflow.com/questions/32634980/haproxy-closes-long-living-tcp-connections-ignoring-tcp-keepalive) – user5994461 May 06 '20 at 18:12

1 Answers1

1

Since no one wrote that explicity. The solution to this problem is setting db.SetConnMaxLifetime(time.Minute). I tried it and it works. Connection reset occurs often on AWS where there is inactivity limit set to 350 seconds, after that TCP RST is returned.

pixel
  • 24,905
  • 36
  • 149
  • 251