CODEWITHSUNDEEP
mysqlgotimeoutconnection-timeout
adavea
adavea

Reputation: 1622

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

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.

Upvotes: 4

Views: 8276

Answers (4)

Abser Ari
Abser Ari

Reputation: 21

MySQL DSN format to timeout works perfectly fine.

I just want to point out why the set context timeout of 2s does not take effect, and why the displayed timeout is the number 2 min.

The context does not take effect because in golang's sql/driver, the context used by dial to establish a database connection is not the same context as the context passed to the ping command in your code. The timeout setting of the context used by this dial needs to be set using the parameters in the dsn.

The reason for the 2min timeout is that the setting of the kernel parameter tcp_syn_retries used by Linux to establish a tcp connection is estimated to be 6. This will cause tcp to try to establish a connection for up to 127s when the network packet is lost before returning an error.

Upvotes: 0

Josh Hibschman
Josh Hibschman

Reputation: 3724

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

Upvotes: 0

Amjad Hussain Syed
Amjad Hussain Syed

Reputation: 1040

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

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

Upvotes: 0

hendry
hendry

Reputation: 10833

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

Upvotes: 1

Related Questions