Connection dies in loop (?)

[ivoviz]

I have a query that returns ~70.000 records and then in a simple loop I process each record. After some time (30-60 minutes) I get this error and the app dies:

[MySQL] 2014/09/28 04:22:01 packets.go:59: unexpected EOF
[MySQL] 2014/09/28 04:22:01 packets.go:336: Busy buffer

The query and the processing is very simple, something like this:

rows, err := db.Mysql.Query("select field1, field2, field3 from table1")
for i := 0; rows.Next(); i++ {
    var field1, field2, field3 int
    err = rows.Scan(&field1, &field2, &field3)
    // bunch of processing...
}

In the loop itself I don't do any database related stuff.

Could this be a bug in the driver, or have I missed something?

[arnehormann]

It sounds like #257 - a stale network connection undetected by the driver.
But that's probably because MySQL cuts your connection when the processing (simple or not) takes to long. Please check how long rows is open and how long it takes from querying the database until you close rows.

[ivoviz]

I just did another test, rows was open for 25 minutes when the error occurred. wait_timeout is only set on server side (3600 seconds). The query itself takes about 60-70 seconds, most of that is just writing to net.

As mentioned in the linked issue, I'm trying SetMaxIdleConns(0) and SetMaxOpenConns(1000) at the moment.

Edit: that was quite fast, just died after 6 minutes with those settings.

[ivoviz]

Some additional info from a new test:

18:34:32 > starting the app

18:45:53 > netstat -tnp | grep app
tcp   7067571      0 q.z.y.x:21995    x.y.z.q:3306      ESTABLISHED 23199/app
19:02:16 > netstat -tnp | grep app
tcp   6004495      0 q.z.y.x:21995    x.y.z.q:3306      CLOSE_WAIT  23199/app

Up until 19:02 the connection was in ESTABLISHED state, since then it's in CLOSE_WAIT.
The app is still running though, not crashed yet.

Edit:
[MySQL] 2014/09/28 19:27:00 packets.go:59: unexpected EOF
[MySQL] 2014/09/28 19:27:00 packets.go:336: Busy buffer
driver: bad connection

[arnehormann]

There's more than one timeout. I think one of the net_X_timeouts could be the cause in your case.
See http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_net_write_timeout

[ivoviz]

Yup, increasing net_read_timeout solved the problem, thanks!

[julienschmidt]

The driver should still handle this nicely instead of dying with an busy buffer error. Something is still wrong here...

[ivoviz]

I recently had to run this script again and even with net_read_timeout increased it failed once, then restarted and went smooth again. I can do some more testing any time, but I'm not really sure where to look exactly at this point.

[julienschmidt]

Looks like this is the same bug as #314