Add support for read/write timeouts

[newhook]

The mysql driver code doesn't have any read/write deadlines. Thats bad as poor network conditions can lead to connections hanging forever. We had, for example, this one:

goroutine 8585754 [IO wait, 6382 minutes]:
net.(_pollDesc).Wait(0xc2097f2f40, 0x72, 0x0, 0x0)
/usr/local/go/src/net/fd_poll_runtime.go:84 +0x47
net.(_pollDesc).WaitRead(0xc2097f2f40, 0x0, 0x0)
/usr/local/go/src/net/fd_poll_runtime.go:89 +0x43
net.(_netFD).Read(0xc2097f2ee0, 0xc2109a2000, 0x1000, 0x1000, 0x0, 0x7f74e732fe70, 0xc20a474c80)
/usr/local/go/src/net/fd_unix.go:242 +0x40f
net.(_conn).Read(0xc208955d10, 0xc2109a2000, 0x1000, 0x1000, 0x0, 0x0, 0x0)
/usr/local/go/src/net/net.go:121 +0xdc
github.com/go-sql-driver/mysql.(_buffer).fill(0xc21074e480, 0x4, 0x0, 0x0)
/source/.gopack/vendor/src/github.com/go-sql-driver/mysql/buffer.go:55 +0x28e
github.com/go-sql-driver/mysql.(_buffer).readNext(0xc21074e480, 0x4, 0x0, 0x0, 0x0, 0x0, 0x0)
/source/.gopack/vendor/src/github.com/go-sql-driver/mysql/buffer.go:76 +0x68
github.com/go-sql-driver/mysql.(_mysqlConn).readPacket(0xc209ab0280, 0x0, 0x0, 0x0, 0x0, 0x0)
/source/.gopack/vendor/src/github.com/go-sql-driver/mysql/packets.go:28 +0x7b
github.com/go-sql-driver/mysql.(_mysqlConn).readInitPacket(0xc209ab0280, 0x0, 0x0, 0x0, 0x0, 0x0)
/source/.gopack/vendor/src/github.com/go-sql-driver/mysql/packets.go:146 +0x72
github.com/go-sql-driver/mysql.(_MySQLDriver).Open(0x15ee208, 0xc20802d6c6, 0x35, 0x0, 0x0, 0x0, 0x0)
/source/.gopack/vendor/src/github.com/go-sql-driver/mysql/driver.go:54 +0x3f5
database/sql.(_DB).conn(0xc20841d400, 0xc210aef100, 0x0, 0x0)
/usr/local/go/src/database/sql/sql.go:664 +0x506
database/sql.(_DB).query(0xc20841d400, 0x107aab0, 0x67, 0xc20c53d5f8, 0x1, 0x1, 0x7f74c71ac6b8, 0x0, 0x0)
/usr/local/go/src/database/sql/sql.go:933 +0x43
database/sql.(_DB).Query(0xc20841d400, 0x107aab0, 0x67, 0xc20c53d5f8, 0x1, 0x1, 0x40f1cc, 0x0, 0x0)
/usr/local/go/src/database/sql/sql.go:924 +0xa6
database/sql.(*DB).QueryRow(0xc20841d400, 0x107aab0, 0x67, 0xc20c53d5f8, 0x1, 0x1, 0x40f98f)
/usr/local/go/src/database/sql/sql.go:1002 +0x66

[arnehormann]

Does https://godoc.org/github.com/go-sql-driver/mysql#RegisterDial help?
If you import the driver and do not use an anonymous import (the one starting with _), you can register your own dialer with various timeouts, logging, on the fly filters and whatnot.

[julienschmidt]

If you import the driver and do not use an anonymous import (the one starting with _), you can register your own dialer with various timeouts, logging, on the fly filters and whatnot.

That is for the dialer only then. The driver indeed does not support proper read / write timeouts currently.

[newhook]

I don't believe so as the deadlines are absolute times which are set on each read/write.

[julienschmidt]

Related: #372

[arnehormann]

A little convoluted, but: net.Conn is an interface. A long one, but still an interface.
The dial function could return a wrapper which does all that stuff.
If it's a common problem, the driver could even provide the wrapper - or a "does everything wrapper" with configuration options to tweak all these settings. We could provide common profiles in the DSN to configure such a wrapper.
Doing so keeps the code clean for the common/fast case but makes everything configurable.
Would that suffice? We could probably also use something like it for #302 to make the fix optional per DSN and keep the legacy behavior otherwise...

[newhook]

Doing socket reads/writes without a timeout is not really safe ever, so the common case should be to set a timeout, even if it is a long one.

[ghost]

Dead must early, then we can handle it . if the connection broken ,just tell us it broken, then we can reconnect by init new connection.
Currently we got problems in running SQL with mysql-driver and golang.
It will freeze and hangs up the whole process.
Really bad experience.

I have made issue report #392

[ghost]

How about set IPConn.SetReadDeadline for the connections ? such as 30 seconds (can be configured)if no response, then close connections
https://golang.org/pkg/net/#IPConn.SetReadDeadline

[methane]

libmysqlclient (official client library written in C++) have read_timeout option and it's infinite by default.

I'm +1 to adding read_timeout option to mysql driver, but it should be off by default since queries may take several hours (or several days!).

[ghost]

I can see this issue open for long times. and i faced problem like this. so i should do something, to help my self out.
It was not easy to handle timeout in the currenty mysql go driver. but i helped myself out.

[methane]

FYI, Go 1.6 have new API: DB.SetConnMaxLifetime.
golang/go@0c516c1

Setting it short (e.g. 1minute) may help to avoid some network troubles.

[ghost]

We using Golang 1.3 and 1.4. so we can not waiting for 1.6 golang release

[newhook]

methane, I don't get your comment. The driver is broken, it should correctly set timeouts. Don't blame the end user here.

[methane]

@newhook Sorry, I meant discussion at #392, not this one.

[lmas]

@arnehormann RegisterDial() helps, but if you set a deadline for the new connection then the driver will complain with:

[MySQL] 2016/01/08 16:05:15 packets.go:118: write tcp x.x.x.x:43198->y.y.y.y:3306: i/o timeout
[MySQL] 2016/01/08 16:05:15 packets.go:118: write tcp x.x.x.x:43198->y.y.y.y:3306: i/o timeout

each time when the deadline runs out (yes, it will also output two lines each time, that are duplicated).

Super annoying when you've set a deadline for 30seconds and it starts to spam the logs. It's solved with mysql.SetLogger(log.New(NullWriterblaah..)) but that disables all logs from the driver, which is super ugly too...

👍 for proper read/write timeouts in the driver.