mysql: packets.go:36: unexpected EOF (Invalid Connection)

The way to solve is :- db.SetMaxIdleConns(0)

Also running into this, seems strange that the generally accepted solution everywhere is to “disable idle connections”.

General solution is DB.SetConnMaxLifetime(time.Second).

Was the root cause for this discovered? Are there plans to fix it so that idle connections work as intended?

Root cause is TCP and request-response protocol. HTTP keep-alive has same problem. Client should have shorter timeout than server. Otherwise, client may try to use dead connection. And if the request may not be idempotent, client can’t retry automatically.

For example, AWS’s ELB (load balancer, which is “client” to application) has 60sec keep-alive. And it recommends 120sec keep-alive timeout on application server side. https://aws.amazon.com/jp/premiumsupport/knowledge-center/apache-backend-elb/

We recently ran into a similar issue, frequently seeing packets.go:36 unexpected EOF and i/o timeout errors. We found these blog posts very helpful in balancing our connection pool and resolving the issue.

• https://www.alexedwards.net/blog/configuring-sqldb
• https://making.pusher.com/production-ready-connection-pooling-in-go

Even if server’s wait_timeout is long enough, router or OS may close long idle connection to reduce resource usage. Which cause this error too. So I recommend 1sec ~ 10sec timeout, unless you’re network expert and you know optimal timeout length.

In my understanding, set SetConnMaxLifetime(time.Second) will cause the connections pool become useless.

It’s up to your application. In application using hundreds queries/sec, even only 1 second reduce connection overhead less than 1/100. But 1sec may be too defensive for most cases. 1~3 minutes may be better in general.

@methane Why not DB.SetConnMaxIdleTime? I just noticed that SetConnMaxIdleTime is new in 1.15, so maybe SetConnMaxIdleTime is the better solution from 1.15?

SetConnMaxIdleTime solves only one problem (someone closes long idle TCP connection). SetConnMaxLifetime solves some other pitfalls too. For example:

• Changing system variable (e.g. SET GLOBAL) doesn’t applied to existing connections. If you set SetConnMaxLifetime(time.Minute), changed system variables are applied in 1 minute.
• Imagine you are using load balanced database and you want to stop one node. You may drop the node from load balancer (e.g. DNS, LVS, etc). But existing connections from applications are kept forever. You can not stop the node until you restart the whole applications.

You can imagine many scenarios that forever reused connections make trouble. Limiting max lifetime is the best practice to keep your whole system healthy.

That’s why I recommend SetConnMaxLifetime even for Go 1.15+. SetConnMaxLifetime is useful only when you really need to set different timeout for lifetime and idletime. But it’s very rare. Setting only SetConnMaxLifetime is enough for 99.99% users.

Another way to solve this is to set the tcp keep alive on the connection:

func init() {
	mysql.RegisterDial("tcp", func(addr string) (net.Conn, error) {
		conn, err := net.Dial("tcp", addr)
		if err != nil {
			return nil, err
		}

		type keepAliveSetter interface {
			SetKeepAlive(keepalive bool) error
			SetKeepAlivePeriod(d time.Duration) error
		}

		if setter, ok := conn.(keepAliveSetter); ok {
			if err := setter.SetKeepAlive(true); err != nil {
			} else if err := setter.SetKeepAlivePeriod(time.Minute); err != nil {
			}
		}

		return conn, nil
	})
}

You didn’t read my comments on various places? Use SetConnMaxLifetime().

Vivid Cortex says to trust the lib to manage all retry, reconnect, connection pooling, timeout logic.

He didn’t say default configuration. You must use DB.SetConnMaxLifetime to use “e all retry, reconnect, connection pooling, timeout logic.”

Should the user set SetMaxIdleConns(0), and/or SetConnMaxLifetime(-1)?

Have you read my comments before bloat posting?? I never recommended such settings. In this issue, I recommended 1sec~10sec. I feel it was too defensive. 10sec~1min will be recommended in general.