go-mssqldb: too many read tcp xxx> xxx i/o timeout

when i use sql server to save data.but,when the app run some day.there is too many read tcp xxx> xxx i/o timeout.does i use it error?

my code:

func getSQLConn(databaseUri string) (*sql.DB, error) {
	conn, ok := connections[databaseUri]
	if ok {
		return conn,nil
	}
	sqlLock.Lock()
	defer sqlLock.Unlock()
	conn, err := sql.Open("mssql", fmt.Sprintf(connectUri, databaseUri))
	if err != nil {
		return nil, err
	}
	conn.SetMaxOpenConns(config.EveryDBConnectionSize)
	conn.SetMaxIdleConns(config.EveryDBConnectionSize)
	connections[databaseUri] = conn
	return conn, nil
}

func insert(conn *sql.DB, action *ContentCommitAction) error {
	stmt, err := conn.Prepare(fmt.Sprintf(insertSQL, config.SqlDataDb, action.taskName))
	if err != nil {
		fmt.Printf("prepare stmt error,    info:%s\n", err.Error())
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(action.content.Id, action.content.Url, action.content.Compress, action.content.Checksum, action.content.Data)
	if err != nil {
		if strings.Contains(err.Error(), "PRIMARY KEY") {
		} else {
			fmt.Printf("stmt exec error:%s    info:%s\n", action.taskName, err.Error())
			return err
		}
	}
	return nil
}

sqlConn, err := getSQLConn(databaseUri)
 if err != nil {
    fmt.Printf("task content commit service get sqlConnection error:,  info:%s\n", err)
    return
}
err = insert(sqlConn, action)

i nerver close the sql.DB

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 17

Most upvoted comments

I added ;Connection Timeout=0 to the end of the DSN I posted above and got the following result:

$ go run main.go
2018/03/16 13:49:00 call Exec Waitfor 28 seconds
2018/03/16 13:49:28 done waitfor 28 seconds - duration 28.566814s
2018/03/16 13:49:28 call Exec Waitfor 32 seconds
2018/03/16 13:50:00 done waitfor 32 seconds - duration 32.061654s

This is good news to have as a workaround, but it does not match how a default Microsoft SQL Server behaves. See:

I do understand that this Go-package doesn’t have a corresponding SqlCommand as .NET or ADO has, but I do not fancy re-using an already existing property on Connection timeouts to be re-purposed into something else. The option of having separate timeouts per ‘Command’ is much more appealing as we then can choose a short timeout on things supposed to go quickly, and explicit longer execution-time on the few commands that we actually want to allow to spend more time.

Please do not think of me as an angry user, I actually find this package just awesome. But my main focus is the consistent work and usage of databases with expected behaviors, and re-purposing the Connection timeout into a dual meaning like this is the ‘dark side’ i.m.o

Since this package lacks the SqlCommand my suggestion is this: Add a function on the Prepared statement to allow settings an explicit ‘Command Timeout’ That way the default 30 seconds is valid for all default ‘commands’ and if one need to enable long-running queries then just make them do ‘Prepare’ and set an explicit value.

ninlil on Mar 16, 2018

Try setting the connection timeout to zero 0 in the dsn with latest driver version.

kardianos on Mar 16, 2018

main.go

package main

import (
	"database/sql"
	"log"
	"time"

	_ "github.com/denisenkom/go-mssqldb"
)

const DSN = "server=XXXX;database=YYYY;User ID=ZZZZ;Password=####

"

func main() {
	db, err := sql.Open("mssql", DSN)
	if err != nil {
		panic(err)
	}

	log.Println("call Exec Waitfor 28 seconds")
	t := time.Now()
	_, err = db.Exec("WAITFOR DELAY '00:00:28'")
	if err != nil {
		log.Println("Error after", time.Since(t))
		panic(err)
	}
	log.Println("done waitfor 28 seconds - duration", time.Since(t))

	log.Println("call Exec Waitfor 32 seconds")
	t = time.Now()
	_, err = db.Exec("WAITFOR DELAY '00:00:32'")
	if err != nil {
		log.Println("Error after", time.Since(t))
		panic(err)
	}
	log.Println("done waitfor 32 seconds - duration", time.Since(t))

	db.Close()
}

$ go version
 go version go1.9.2 linux/amd64

$ go get -u github.com/denisenkom/go-mssqldb

$ go run main.go
2018/03/16 09:20:25 call Exec Waitfor 28 seconds
2018/03/16 09:20:53 done waitfor 28 seconds - duration 28.370434s
2018/03/16 09:20:53 call Exec Waitfor 32 seconds
2018/03/16 09:21:23 Error after 30.047447s
panic: read tcp 10.101.102.56:61884->#.#.#.#:1433: i/o timeout

goroutine 1 [running]:
main.main()
        main.go:33 +0x604
exit status 2

Result: This is not a connection-problem, this is a command timeout problem, sadly since the command timeout can’t be set using the DSN.

ninlil on Mar 16, 2018