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

[rebootcode]

Issue description

Getting Invalid Connection issues.

Error log

[mysql] 2017/09/26 22:38:16 packets.go:36: unexpected EOF
[HTTP Server] http: panic serving xxx.xxx.xxx.xxx:39727: runtime error: invalid memory address or nil pointer dereference
goroutine 25 [running]:
net/http.(*conn).serve.func1(0x1a83a2a0)
        /usr/local/go/src/net/http/server.go:1697 +0x9f
panic(0x84ed320, 0x8849160)
        /usr/local/go/src/runtime/panic.go:491 +0x1d0
database/sql.(*Stmt).Close(0x0, 0x0, 0x0)
        /usr/local/go/src/database/sql/sql.go:2335 +0x2d
main.postLogin.func1(0x88225e0, 0x1a840a00)
        /root/go/src/app/main.go:977 +0x1b8
github.com/kataras/iris/context.Next(0x88225e0, 0x1a840a00)
        /root/go/src/github.com/kataras/iris/context/context.go:851 +0xce
github.com/kataras/iris/context.(*context).Next(0x1a840a00)
        /root/go/src/github.com/kataras/iris/context/context.go:1063 +0x2b
main.main.func2(0x88225e0, 0x1a840a00)
        /root/go/src/app/main.go:57 +0x24f
github.com/kataras/iris/context.Do(0x88225e0, 0x1a840a00, 0x1a827d18, 0x2, 0x2)
        /root/go/src/github.com/kataras/iris/context/context.go:864 +0x61
github.com/kataras/iris/context.(*context).Do(0x1a840a00, 0x1a827d18, 0x2, 0x2)
        /root/go/src/github.com/kataras/iris/context/context.go:1006 +0x43
github.com/kataras/iris/core/router.(*routerHandler).HandleRequest(0x1a8317f0, 0x88225e0, 0x1a840a00)
        /root/go/src/github.com/kataras/iris/core/router/handler.go:216 +0x3f1
github.com/kataras/iris/core/router.(*Router).BuildRouter.func1(0x88198a0, 0x1a578090, 0x1a89a580)
        /root/go/src/github.com/kataras/iris/core/router/router.go:70 +0x6d
github.com/kataras/iris/core/router.(*Router).ServeHTTP(0x1a75bb60, 0x88198a0, 0x1a578090, 0x1a89a580)
        /root/go/src/github.com/kataras/iris/core/router/router.go:147 +0x37
net/http.serverHandler.ServeHTTP(0x1a59f980, 0x88198a0, 0x1a578090, 0x1a89a580)
        /usr/local/go/src/net/http/server.go:2619 +0x8e
net/http.(*conn).serve(0x1a83a2a0, 0x881a0a0, 0x1a8898c0)
        /usr/local/go/src/net/http/server.go:1801 +0x5d1
created by net/http.(*Server).Serve
        /usr/local/go/src/net/http/server.go:2720 +0x1f6
[mysql] 2017/09/26 22:38:16 connection.go:158: invalid connection

Go version: run go version in your console
=> go1.9 linux/386

Server version: E.g. MySQL 5.6, MariaDB 10.0.20
=> 5.5.56-MariaDB

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
=> Centos

[rebootcode]

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

[heisGarvit]

There is a typo in your solution maybe version difference...

https://golang.org/pkg/database/sql/#DB.SetMaxIdleConns

so I used db.SetMaxIdleConns(0) and program ran. Thanks for the solution!

[rebootcode]

@heisGarvit - fixed

[notr1ch]

Also running into this, seems strange that the generally accepted solution everywhere is to "disable idle connections". Was the root cause for this discovered? Are there plans to fix it so that idle connections work as intended?

[methane]

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/

[methane]

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.

[muxalko]

Hi,
Recently encountered the same issue, and found this topic useful.
Hope it helps,

[mikegleasonjr]

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
    })
}

[methane]

TCP Keep-Alive solves only very limited cause of this issue.
It's not recommended way. SetConnMaxLifetime() is recommended.

[mcandre]

Wait, so what is the right way for us to use database/sql in Go applications?

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

https://www.vividcortex.com/blog/2015/09/22/common-pitfalls-go/

But in practice a default SQL client configuration fails to reconnect to the database after several hours of inactivity. Should the user set SetMaxIdleConns(0), and/or SetConnMaxLifetime(-1)? Will these defaults eventually be merged into database/sql? Is there a reliable function or method to force the client to reconnect after idling?

[methane]

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.

[bguilder]

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

[Laisky]

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

@methane Why not DB.SetConnMaxIdleTime?

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

---

I just noticed that SetConnMaxIdleTime is new in 1.15, so maybe SetConnMaxIdleTime is the better solution from 1.15?

[methane]

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.