在Go中为SQL连接设置TCP超时

When I connect to database (using standard go sql library) using VPN and VPN interface goes down, there's a 75 seconds timeout when I try to do SQL query, no matter if the interface goes up meanwhile. I'd like to decrease this timeout to some reasonable time, so my application won't be frozen for 75 seconds in such case.

db, err := sql.Open(driverName, dataSourceName)

Is it possible to set it somehow via db variable?

Starting with Go 1.8, the sql.DB abstraction now accepts context.Context, which can be used to time out connections faster.

func (c *Client) DoLookup(ctx context.Context, id int) (string, error) {
  var name string
  // create a child context with a timeout
  newCtx, cancel := context.WithTimeout(ctx, time.Second)
  // release resources used in `newCtx` if
  // the DB operation finishes faster than the timeout
  defer cancel()

  row := c.db.QueryRowContext(newCtx, "SELECT name FROM items WHERE id = ?", id)

  err := row.Scan(&name)
  if err != nil {
    return "", err
  }

  return name, nil
}

If your DoLookup function doesn't yet take a context.Context (and it really should!) you can create a parent one by calling context.TODO().

The database/sql package doesn't provide a general way to timeout a call to database/sql.Open. However, individual drivers provide this functionality via the DSN (dataSourceName) connection strings.

https://github.com/lib/pq

sql.Open("postgres", "user=user dbname=dbname connect_timeout=5")

https://github.com/go-sql-driver/mysql

sql.Open("mysql", "user:password@/dbname?timeout=5s")

https://github.com/denisenkom/go-mssqldb

sql.Open("sqlserver", "sqlserver://username:password@host/instance?dial+timeout=5")

etc ...

I used PingContext like this:

// Please remember that sql.Open only validate argument 
// without creating connection. Ping/PingContext do that.
db, err := sql.Open(config.DATABASE_DRIVER, config.DATABASE_URL)
if err != nil {
    // log.Fatal(err)
    log.Println(err) // continue for dev only. Should be Fatal in production mode
}
defer db.Close()
// Create database connection with context
ctx, cancel := context.WithTimeout(context.Background(), time.Second*15)
defer cancel()
err = db.PingContext(ctx)
if err != nil {
    // log.Fatal(err)
    log.Println(err) //should be Fatal in production mode
}

But the ping still took me 1 minute. Anyone has other idea?