Importance of Configuring Go sql.DB Connections

Background

I've recently started to switch from PHP to Go and I missed something big (but now seems super obvious). My first go at technical blogging was a giant fail:

Go sql.DB Periodic Error: invalid connection

Aaron Ellington ・ Aug 26 '18 ・ 2 min read

#go #sql #beginners

I wanted to loop back around and show what I've learned. I'm also hoping that this time I'm not missing anything as large.

Issue

Coming from PHP, I'm used to scripts starting and stopping pretty frequently and the database connections that were opened would only stay open for a few seconds (max). But when I switched to Go, my scripts were running for days instead of seconds.

If more than about 15 minutes went by without a MySQL query being executed, the following attempt would fail with a invalid connection error. In my previous post, I found that calling a db.Ping() every 5 minutes "mostly" fixed my issue. But as many people kindly pointed out, this was not a good solution. I also still got errors from time to time, but not nearly as often.

Solution (New!)

Not quite sure how I missed it the first time, but there is a db.SetConnMaxLifetime function. This allowed the sql package to handle how long connections can last before it will preemptively close them and reopen new ones. The default is to reuse them forever. The documentation puts it best:

$ go doc sql.DB.SetConnMaxLifetime
func (db *DB) SetConnMaxLifetime(d time.Duration)
    SetConnMaxLifetime sets the maximum amount of time a connection may be
    reused.

    Expired connections may be closed lazily before reuse.

    If d <= 0, connections are reused forever.

I worked with our database team to see how long before the MySQL server starts to kill idle connections and used my newfound SetConnMaxLifetime function to match the server. Since then, I have received near 0 connection errors over the past few months.

package main

import (
    "database/sql"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, _ := sql.Open("mysql", "user:password@/dbname")
    // Error handling omitted for this example
    db.SetConnMaxLifetime(time.Minute * 5)

    // Start the web server
}

Final Thoughts

Although this now seems like a obvious concept, it did not when I first start with Go. Hopefully this can help someone else avoid the same mistake that I made.