Connection reset if server sits idle.

[mattatcha]

If my application doesn't periodically send queries to the DB my connection gets dropped.

Here is the error from Go.

"read tcp 127.0.0.1:1433: connection reset by peer"

[denisenkom]

Thanks Matt,

Driver currently doesn't have any keep alive, that is something that needs to be implemented.

Here is one idea: http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/overview.html

[denisenkom]

Here is relevant TDS specification: http://msdn.microsoft.com/en-us/library/dd341108.aspx

[mattatcha]

I'll take a look at that. Thanks!

[dimdin]

The database/sql package retries automatically the operation if the driver returns ErrBadConn.
The driver must detect when the SQL Server closes the connection (perhaps when we have io.EOF error) and return ErrBadConn; if we don't do that, the faulty connection remains in the pool.
see http://golang.org/src/pkg/database/sql/driver/driver.go#L46

[denisenkom]

io.EOF is only returned if SQL server called close on connection, in other cases, e.g. when connection was dropped by router, you will get ECONNRESET. Probably any error from transport layer should result in ErrBadConn.

[mattatcha]

Have you been able to make a fix for this yet? I want to make sure before I start working on it.

[denisenkom]

No, I didn't do anything yet, you can work on it if you want.

[dimdin]

@MattAitchison can you test with branch badconn?
i.e. go get -u github.com/denisenkom/go-mssqldb ; cd $GOPATH/src/github.com/denisenkom/go-mssqldb ; git checkout badconn ; go install .

[mattatcha]

That does seem like it's working better. After I disconnect and reconnect the network from my SQL sever the first query returns with EOF but the next query returns data.

[dimdin]

If the EOF is after a Scan or Next the driver cannot do anything about it, only the application code can recover such errors by retrying the transaction.
What the driver does, it replies with a bad connection error to force an Exec, Query or QueryRow to retry the same sql in a new connection.

[dimdin]

@MattAitchison a new parameter that enables keep alive is added, that might solve the original issue "Connection reset if server sits idle".
Also a revised version of the badconn branch is merged into the master branch. If the driver cannot connect to SQL Server, after a Begin transaction or after an Exec/Query without a transaction, the driver returns ErrBadConn and sql/database removes the faulty connection from the pool and retries the Begin transaction or the Exec/Query sql statement.