How can I get golang mysql driver to timeout pings in 2 seconds?

Question

I am having some trouble figuring out how to make a database connection-attempt timeout properly in Go. I am using some of the examples at this excellent resource as a base. I believe I am setting up everything correctly, but my pings just refuse to time out after 2 seconds. I've extracted the code in question into a sample program as follows. Note that there is no database running at 172.1.2.3.

package main

import (
    "context"
    "database/sql"
    _ "github.com/go-sql-driver/mysql" //MySQL driver
    "log"
    "time"
)

func main() {
    log.Print("Trying to ping database!")

    //Prepare a "context" to execute queries in, this will allow us to use timeouts
    var bgCtx = context.Background()
    var ctx2SecondTimeout, cancelFunc2SecondTimeout = context.WithTimeout(bgCtx, time.Second*2)
    defer cancelFunc2SecondTimeout()

    //Open  database connection
    db, err := sql.Open("mysql", "root:@tcp(172.1.2.3)/testdb?parseTime=true")
    if err != nil {
        log.Printf("Unable to open db, %s", err.Error())
        return
    }
    log.Print("Successfully called open()")

    //Ping database connection with 2 second timeout
    err = db.PingContext(ctx2SecondTimeout)
    if err != nil {
        log.Printf("Can't ping database server in order to use storage, %s", err.Error())
        return
    }
    log.Print("Successfully pinged database!")
}

Running the program should take up to about 2 seconds, but instead it takes 2+ minutes:

$ go run lambdatry.go
2018/09/03 16:33:33 Trying to ping database!
2018/09/03 16:33:33 Successfully called open()
2018/09/03 16:35:43 Can't ping database server in order to use storage, dial tcp 172.1.2.3:3306: connect: connection timed out

If I change the IP of the "database" (I just picked a random IP so there's no database at this address) the database sometimes times out immediately and sometimes takes a really long time to timeout.

I am running go 1.10.1 on ubuntu 18.04.

Answer 1

Could it be this issue: https://github.com/golang/go/issues/27476 ?

My issue is slightly different, it times out one 1s but not 2s or 3s! https://media.dev.unee-t.com/2018-09-05/pingcontext.mp4

My source is here: https://media.dev.unee-t.com/2018-09-05/main.go

Answer 2

I use the following MySQL DSN format to timeout in 2s and it works perfectly fine.

user:password@tcp(localhost:80)/dbname?timeout=2s

Answer 3

It looks like this isn't the fault of PingContext. tldr skip to the bottom.

PingContext supports contexts with timeouts, but in debugging this, it seems the root of the cause is calling sql.Open(), or some other call to sql.Conn() followed by a call to PingContext after. The first two acquire the db.mu.lock, which PingContext ends up waiting on before it has the chance to use your timeout context: https://github.com/golang/go/blob/master/src/database/sql/sql.go#L1394

It becomes more clear when using conn.PingContext instead of db.PingContext, e.g. for a blocked port, this is how timeouts play out:

db, err := sql.Open(driver, connStr)
if err != nil {
    // This error will not happen
    log.Fatalln(err)
}

// Create a 1 second timeout
ctxBG := context.Background()
ctxConnTimeout, cancel := context.WithTimeout(ctxBG, 1*time.Second)
defer cancel()

conn, err := db.Conn(ctxConnTimeout) // Hangs here for 60 seconds, never uses my timeout
if err != nil {
        // This error fires
    log.Fatalln(err)
}

// Never got here
ctxPingTimeout, cancel := context.WithTimeout(ctxBG, 1*time.Second)
defer cancel()

err = conn.PingContext(ctxPingTimeout)
if err != nil {
    log.Fatalln(err)
}

So how to pass a timeout to sql.Open or sql.Conn? It seems this is the only way:

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

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

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

See https://stackoverflow.com/a/52895312/786389