sql db connection not check the server whether closed the connection ,so it caused the tcp4 sockets has many can't identify protocol or TCP localhost:11335->localhost:6379 (WAIT_CLOSED)

[busyfree]

Issue description

sql db connection not check the server whether closed the connection ,so it caused the tcp4 sockets has many can't identify protocol or localhost:54190->localhost:mysql (WAIT_CLOSED)

db open config

max_idle_conns = 5
max_open_conns = 30
max_life_time=600
timeout=600

Example code

package dbtest

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"sync"
	"time"
	"github.com/golang/glog"
	"gopkg.in/gorp.v2"
)

type DatabaseConfig struct {
	DBName               string `toml:"dbname"`
	Host                 string `toml:"host"`
	Port                 int    `toml:"port"`
	User                 string `toml:"user"`
	Password             string `toml:"password"`
	Sslmode              string `toml:"sslmode"`
	ShowLog              bool
	DataSaveDir          string
	DataFileSaveLoopSize int
	MaxIdleConns         int `toml:"max_idle_conns"`
	MaxOpenConns         int `toml:"max_open_conns"`
	MaxLifeTime          int `toml:"max_life_time"`
	Timeout              int `toml:"timeout"`
	RTimeout             int `toml:"rtimeout"`
	WTimeout             int `toml:"wtimeout"`
}

func (c DatabaseConfig) MySQLSource() string {
	params := make(map[string]string, 0)
	params["charset"] = "utf8mb4"
	cfg := mysql.Config{}
	cfg.User = c.User
	cfg.Passwd = c.Password
	cfg.DBName = c.DBName
	cfg.ParseTime = true
	cfg.Collation = "utf8mb4_unicode_ci"
	cfg.Params = params
	cfg.Loc, _ = time.LoadLocation("Asia/Chongqing")
	cfg.Timeout = time.Duration(c.Timeout) * time.Second
	cfg.MultiStatements = true
	cfg.ReadTimeout = time.Duration(c.RTimeout) * time.Second
	cfg.WriteTimeout = time.Duration(c.WTimeout) * time.Second
	return cfg.FormatDSN()
}


var (
	dbmap                      *gorp.DbMap
	Dbm                        *gorp.DbMap
	config                     DatabaseConfig
	opened                     bool
	openMutex                  sync.RWMutex
	DB                         *sql.DB
)

//Open open the database for passport with config
func Open(cfg DatabaseConfig) {
	if !opened {
		config = cfg
		db, err := sql.Open("mysql", config.MySQLSource())
		glog.Infof("open err %v ", err)
		if err != nil {
			panic(fmt.Errorf("sql.Open failed: %v", err))
		}
		if config.MaxLifeTime > 0 {
			db.SetConnMaxLifetime(time.Duration(config.MaxLifeTime) * time.Second)
		}
		db.SetMaxIdleConns(config.MaxIdleConns)
		db.SetMaxOpenConns(config.MaxOpenConns)
		db.Ping()
		DB = db
		// construct a gorp DbMap
		dbmap = &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "utf8mb4"}}
		Dbm = dbmap

		err = dbmap.CreateTablesIfNotExists()
		if err != nil {
			panic("create table failed " + err.Error())
		}
		openMutex.Lock()
		opened = true
		openMutex.Unlock()
		if config.ShowLog {
			dbmap.TraceOn("[gorp]", log.New(os.Stdout, schemaName+" ", log.Lmicroseconds))
		}
	}
}

//Close close the database for passport
func Close() {
	if dbmap != nil && opened {
		glog.Infof("close database %s for %s", config.DBName, schemaName)
		dbmap.Db.Close()
		openMutex.Lock()
		opened = false
		openMutex.Unlock()
	}
}

Error log

2017/11/16 11:32:13 http: Accept error: accept tcp 127.0.0.1:hide : accept4: too many open files; retrying in 1s
2017/11/16 11:32:14 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:15 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:16 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:17 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:18 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:19 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:20 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:21 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s
2017/11/16 11:32:22 http: Accept error: accept tcp 127.0.0.1:hide: accept4: too many open files; retrying in 1s

Configuration

Driver version (or git SHA):

*78d399c - (HEAD -> master, origin/master, origin/HEAD) Implement NamedValueChecker for mysqlConn (#690)

Go version: run go version in your console
go version go1.9 linux/amd64

Server version: E.g. MySQL 5.6, MariaDB 10.0.20

Server version: 5.6.15-log MySQL Community Server (GPL)

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
CentOS release 6.7 (Final)

[methane]

I recommend max_idle_conns == max_open_conns.
max_life_time is enough to close redundant connections.

[busyfree]

after set max_idle_conns == max_open_conns still have the same issue ,pls help me, have any idea?

[methane]

Lifetime should be less than half of wait_timeout.
In your case, it should be less than 300 sec.

But I recomend 1~10sec unless your db and app is very far (e.g. 100ms RTT). Shorter lifetime makes less trouble.

[busyfree]

@methane it seems not working ,but when i set max_idle_conns =0 seems working . now it's closed normal.

[busyfree]

now start closed abnormal again. anyone has any idea?

[methane]

No enough information. Your example code demonstrates how you use config file, not how to reproduce.

Error message is very normal fd shortage. No one other than you can know why it happens; your
application really needs many fds or leak.

You said "WAIT_CLOSED" and I think it means "CLOSE_WAIT", but your screenshot doesn't include
any CLOSE_WAIT connection.

Minimum example to reproduce is required.

[methane]

And here is issue tracker. not "support forum" or Q&A site.
Asking such question on issue tracker making hard to manage real issue.
OSS maintainers is not free tech support.