0

I have an API in GO using echo and SQL Server as the DB.

I use db.Ping() at the start of every endpoint to check if the db is still connected or not, this keeps opening new connections to the db with new sockets which eventually leads to a too many files open error.

db.Stats() returns this {"MaxOpenConnections":0,"OpenConnections":413,"InUse":413,"Idle":0,"WaitCount":0,"WaitDuration":0,"MaxIdleClosed":2,"MaxIdleTimeClosed":62,"MaxLifetimeClosed":0}

The OpenConnections keep on increasing even though the number of active requests is much lower than that.

Here is an example snippet:

package main

import (
    "database/sql"

    "github.com/labstack/echo/v4"

    _ "github.com/denisenkom/go-mssqldb"
)

var db *sql.DB

func main () {
    db, err = sql.Open("sqlserver", "sqlserver://username:passwrod@[server]?database=[db]&connection+timeout=1000")
    if err != nil {
        log.Fatal(err)
    }
    db.SetConnMaxLifetime(10 * time.Minute)
    db.SetConnMaxIdleTime(15 * time.Second)
    e := echo.New()
    e.GET("/", func(c echo.Context) error {
        err := db.Ping()
        if err != nil {
            return echo.NewHTTPError(500, err)
        }
        var name string
        err = db.QueryRow("select top 1 name from Test_Table;").Scan(&name)
        if err != nil {
            return echo.NewHTTPError(500, err)
        }
        return c.HTML(http.StatusOK, name)
    })
    e.Logger.Fatal(e.Start(":4000"))
}
Arsh Malik
  • 36
  • 1
  • 2
  • 3
    Is that your whole app? If so then there is no problem with the code. Possibly a problem with the driver. But if that's not your whole app and there are other places where `db` is used then we have no way of knowing *exactly* what's wrong. A common problem though is not closing `*sql.Rows` or other db-related resources. – mkopriva Oct 13 '21 at 17:51
  • 1
    This can happen if you are not closing dl resources (mostly *sql.Rows as mentioned by @mkopriva). This can also happen if db server is taking too long to send response and client is waiting for those responses. If you want you can use db.SetMaxOpenConnections() to limit the number of socket to db server. – chanchal1987 Oct 13 '21 at 18:24
  • @mkopriva Should I call `defer rows.Close()` after `Query()` to close it or should I close it after I scan through all the required rows? – Arsh Malik Oct 13 '21 at 21:19
  • After you call Query and confirm that the error value is nil. – mkopriva Oct 13 '21 at 21:21

0 Answers0