database/sql: Is it possible to release expired connections as soon as possible #39449
Description
What version of Go are you using (go version)?
$ go version go version go1.14.4 darwin/amd64
Does this issue reproduce with the latest release?
yes
What operating system and processor architecture are you using (go env)?
go env Output
$ go env GO111MODULE="on" GOARCH="amd64" GOBIN="" GOCACHE="/Users/chan/Library/Caches/go-build" GOENV="/Users/chan/Library/Application Support/go/env" GOEXE="" GOFLAGS="" GOHOSTARCH="amd64" GOHOSTOS="darwin" GOINSECURE="" GOOS="darwin" GOPATH="/Users/chan/gopath" GOPROXY="https://goproxy.cn,https://goproxy.io,direct" GOROOT="/usr/local/go" GOSUMDB="sum.golang.org" GOTMPDIR="" GOTOOLDIR="/usr/local/go/pkg/tool/darwin_amd64" GCCGO="gccgo" AR="ar" CC="clang" CXX="clang++" CGO_ENABLED="1" GOMOD="/dev/null" CGO_CFLAGS="-g -O2" CGO_CPPFLAGS="" CGO_CXXFLAGS="-g -O2" CGO_FFLAGS="-g -O2" CGO_LDFLAGS="-g -O2" PKG_CONFIG="pkg-config" GOGCCFLAGS="-fPIC -m64 -pthread -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -fdebug-prefix-map=/var/folders/qw/ddvmdjjd35xcj6823_nz3z780000gn/T/go-build941747274=/tmp/go-build -gno-record-gcc-switches -fno-common"
What did you do?
At present, database/sql closes expired conn at a fixed time interval, so when ConnMaxLifeTime is the same as the database configuration or greater than half of the database side, the database may close the conn earlier than the client, so that when the client obtains the conn A driver.ErrBadConn error will be generated, and an error may be generated when the expired conn is closed
So can we set the time interval to half of the original？
package main
import (
"context"
"database/sql"
"fmt"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s", "root", "123456", "127.0.0.1:3306", "test_db")
dsn += "?collation=utf8mb4_bin&clientFoundRows=false&interpolateParams=true&loc=Asia%2FShanghai&maxAllowedPacket=0&multiStatements=false&parseTime=true&timeout=5000ms&time_zone=%27Asia%2FShanghai%27"
dsn += "&wait_timeout=15"
fmt.Println("start time:", time.Now().Format("2006/01/02 15:04:05"))
DB, err := sql.Open("mysql", dsn)
if err != nil {
fmt.Println(err)
return
}
defer DB.Close()
DB.SetMaxOpenConns(100)
DB.SetMaxIdleConns(100)
DB.SetConnMaxLifetime(10 * time.Second)
var name string
var value string
if err := DB.QueryRow("show session variables where variable_name='wait_timeout'").Scan(&name, &value); err != nil {
fmt.Println(err)
return
}
fmt.Println(name, value)
time.Sleep(4 * time.Second)
// Get the existing conn
conn, err := DB.Conn(context.Background())
if err != nil {
fmt.Println(err)
return
}
// Create a new conn
fmt.Println("conn2 created time:", time.Now().Format("2006/01/02 15:04:05"))
conn2, err := DB.Conn(context.Background())
if err != nil {
fmt.Println(err)
return
}
_ = conn.Close()
_ = conn2.Close()
time.Sleep(30 * time.Second)
}What did you expect to see?
no closing bad idle connection: EOF
What did you see instead?
start time: 2020/06/07 16:55:00
wait_timeout 15
conn2 created time: 2020/06/07 16:55:04
[mysql] 2020/06/07 16:55:20 packets.go:123: closing bad idle connection: EOF