mysql: packets.go:66: unexpected EOF followed by packets.go:412: busy buffer followed by driver: bad connection followed by connection.go:319: invalid connection

Issue description

MySQL SELECT queries fail unexpectedly at random times in a long running CLI script.

Example code

I can’t really provide a sample code, but I can tell you that I only do simple SELECTqueries in my CLI program. I don’t do any transactions, nor any UPDATEs. All the MySQL operations in my program are read-only (just a lot of SELECTs only). My program is doing just ONE query at a time, it runs on a single core virtual machine ($5 DigitalOcean Droplet) with only one go routine running at a time. I do not run out of RAM, I checked that, the swap file is only barely used on this virtual machine.

Error log

[mysql] 2017/05/02 16:48:51 packets.go:66: unexpected EOF
[mysql] 2017/05/02 16:48:52 packets.go:412: busy buffer
driver: bad connection
[mysql] 2017/05/02 16:48:52 connection.go:319: invalid connection

Configuration

Driver version (or git SHA): git log shows: commit 147bd02c2c516cf9a8878cb75898ee8a9eea0228 Author: astaxie xiemengjun@gmail.com Date: Sat Apr 29 00:02:31 2017 +0800

But the exact same issue is happening with a version from December, 2016. With that version the log is:

[mysql] 2017/05/01 09:55:47 packets.go:66: unexpected EOF
[mysql] 2017/05/01 09:55:47 packets.go:412: busy buffer
driver: bad connection
[mysql] 2017/05/01 09:55:48 connection.go:312: invalid connection

So this problem is not new. I first noticed this issue this year. Sometime last fall I was using the exact same program on the exact same database, compiled by the then latest stable version of Go (probably Go 1.7), and at that time I never ran into this issue. So this must be something new, it must be related to some change(s) that occurred right around or before Go 1.8 got released.

Go version: go version go1.8.1 linux/amd64

Server version: mysql-5.5.55

Server OS: CentOS release 6.9 (but I compiled the program under Debian 8.7 (jessie), transferred the binary file over to the CentOS 6.9 droplet and ran it under CentOS 6.9 – thanks to static builds this should not be a problem. I do the same with a bunch of other Go programs and this is the only one which fails)

I noticed today that someone else also ran into this error pattern ( “packets.go:66: unexpected EOF followwed by packets.go:412: busy buffer followed by driver: bad connection” ) recently, please see his Edit#2 on this StackOverflow page: http://stackoverflow.com/questions/43696607/mysql-to-json-inconsistent-extraction

This is a showstopper bug for me. I might need to switch back to Go 1.7.x and recompile my program and see if that fixes this issue or not. Or do you have a better idea on how to debug this?

BTW, my program is doing millions of SELECT queries and only a few of them fail. On every run different queries fail, it seems to me totally random.

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 37 (13 by maintainers)

Most upvoted comments

I managed to solve this problem! It had nothing to do with Go version or the version of the Go MySQL driver.

It turns out that it was a combination of things. In my case, the problem was very much resembling this issue: https://github.com/go-sql-driver/mysql/issues/281 So I added these lines to my.cnf :

net_read_timeout         = 14400
net_write_timeout        = 14400

I also changed back the following timeout settings to their MySQL default values (previously I had much lower values for these) :

wait_timeout             = 28800
interactive_timeout      = 28800

Besides, what was suggested by @methane was also required:

db.SetConnMaxLifetime(time.Second * 20)

I set it to 20 seconds instead of the suggested 10, it works for me perfectly.

Since I made the above changes I stopped seeing the error anymore.

When I said, I used the same program on the same database last year and at that time I did not see this error, that was also true. However, my program is generating static pages from a dynamic web site (WordPress based) and since last year the WP template became more complex and we also changed the web site to SSL. All these changes made the processing of every single page more time consuming. Due to that, recently I started to encounter MySQL timeouts in that long running loop, just like it is described here: https://github.com/go-sql-driver/mysql/issues/281 In my case processing became slower hence I started to see timeouts at places where I’ve never seen timeouts before.

tssajo on May 8, 2017

Have you tried DB.SetConnMaxLifetime()?

db.SetConnMaxLifetime(time.Second * 10) is good for most configuration.

methane on May 2, 2017

In my case the timeout occurs in a long running loop, that loop runs for several days! The loop is similar to the one as described here: #281

Oh, do you mean you’re doing heavy job inside rows.Next() loop? You should fetch all rows and call rows.Close() ASAP, and do your job with fetched rows.

methane on May 11, 2017