why there is a busy buffer after unexpected EOF

[bronze1man]

Issue description

I use a one SELECT sql to pull 30GB data(40M number of rows) from one table and use those to do some data processes.But other part of the system is busy, so this select is blocked at 7.55GB data position for long time, then the mysql server kill this sql network connection.

why there is a busy buffer after unexpected EOF?
What can i do to fix this problem? Just fix the blocked part and run data processes again?

Error log

unexpected EOF
     <autogenerated>:2 xxx.(*nullLogger).Print
    xxx/src/github.com/go-sql-driver/mysql/packets.go:72 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*mysqlConn).readPacket
    xxx/src/github.com/go-sql-driver/mysql/packets.go:740 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*textRows).readRow
    xxx/src/github.com/go-sql-driver/mysql/rows.go:181 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*textRows).Next
    /usr/local/go/src/database/sql/sql.go:2149 database/sql.(*Rows).nextLocked
    /usr/local/go/src/database/sql/sql.go:2134 database/sql.(*Rows).Next.func1
    /usr/local/go/src/database/sql/sql.go:2545 database/sql.withLock
    /usr/local/go/src/database/sql/sql.go:2135 database/sql.(*Rows).Next

busy buffer
     <autogenerated>:2 xxx.(*nullLogger).Print
    xxx/src/github.com/go-sql-driver/mysql/packets.go:431 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*mysqlConn).writeCommandPacket
    xxx/src/github.com/go-sql-driver/mysql/connection.go:109 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*mysqlConn).Close
    xxx/src/github.com/go-sql-driver/mysql/packets.go:73 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*mysqlConn).readPacket
    xxx/src/github.com/go-sql-driver/mysql/packets.go:740 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*textRows).readRow
    xxx/src/github.com/go-sql-driver/mysql/rows.go:181 github.com/bronze1man/kmg/vendor/github.com/go-sql-driver/mysql.(*textRows).Next
    /usr/local/go/src/database/sql/sql.go:2149 database/sql.(*Rows).nextLocked
    /usr/local/go/src/database/sql/sql.go:2134 database/sql.(*Rows).Next.func1
    /usr/local/go/src/database/sql/sql.go:2545 database/sql.withLock
    /usr/local/go/src/database/sql/sql.go:2135 database/sql.(*Rows).Next

Configuration

Driver version (or git SHA):
Version 1.3 (2016-12-01)

Go version: run go version in your console
go version go1.8.1 linux/amd64

Server version: E.g. MySQL 5.6, MariaDB 10.0.20
gce mysql 5.7

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
mysql client os : ubuntu 1404
mysql server os: unknow (gce mysql 5.7)

[methane]

Because UnexpectedEOF error is happen while reading, buffer is used for reading.

In error handler, driver tries to send COM_QUIT command to close connection.
But since buffer is used for reading, driver can't use the buffer. It is 2nd error message.

So, 2nd error message is harmless in this case. Just ignore it.

[pengyuan1990]

@methane What can we do to fix this problem?

[methane]

What is "this problem"?

You can "Unexpected EOF" by properly configure your environment.
Busy buffer log is harmless, and it is disappeared when you solve your unexpected EOF.

To solve unexpected EOF, I always recommend to use DB.SetMaxConnLifetime(time.Second * 10)
https://golang.org/pkg/database/sql/#DB.SetConnMaxLifetime

If it doesn't fix unexpected EOF, something wrong in your environment or your SQL.
You need to investigate your problem by yourself. Here is not MySQL user forum
and I'm not free tech support for you.

If you need longer lifetime by some special reason, (for example, RTT for MySQL
server is longer than 300ms), you need network expert advice. (I'm not)

[mcandre]

Is that a general purpose solution?

To clarify, would a roughly 10 second connection max lifetime good for bulk application workflows with long intervals between queries? Is this configuration good for most deployment environments, including Kubernetes and local Docker?

What is the default connection max lifetime? Can we modify this driver to select a default value more appropriate for our users, so that our users don't have to tweak this value?

In the event that a connection does die due to the max connection lifetime expiring, can go-sql-driver/mysql please do a better job identifying this state, instead of burping up connection errors past database/sql, which typically asserts that retries and reconnects are performed automatically by the driver?

[methane]

To clarify, would a roughly 10 second connection max lifetime good for bulk application workflows with long intervals between queries? Is this configuration good for most deployment environments, including Kubernetes and local Docker?

Yes. Creating new connection to DB is cheap enough. Reconnecting hundreds per seconds will kill performance. But one reconnection every 10 seconds is not bad.

If you really dislike 10sec, or you're using massive connections, you can increase this timeout to 1min or 3min. I saw some people get trouble on 5min, on Docker environment. So I can't recommend more than 4min.

What is the default connection max lifetime? Can we modify this driver to select a default value more appropriate for our users, so that our users don't have to tweak this value?

It is infinite by default. And there are no way to change this default value from driver at the moment.

In the event that a connection does die due to the max connection lifetime expiring, can go-sql-driver/mysql please do a better job identifying this state, instead of burping up connection errors past database/sql, which typically asserts that retries and reconnects are performed automatically by the driver?

What you mean "max connection lifetime expiring" here?
If you configured SetMaxConnLifetime, expired connection is closed gracefully.

If you didn't configured SetMaxConnLifetime, there are no " lifetime expiring". TCP connections just killed by various reasons.
In some cases, #934 detects connection closed before sending query to safe retries.
But it covers not all cases. There are no 100% solution for safe retry. I explained it hundreds times.
You must use SetMaxConnLifetime.