Mysql connection closed after about 6 minutes idle

Getting Help
1

here is what i did

  1. open database connection in init() func of another package, say “package model”, not “package main”
var conn *sql.DB

func init() {
	// connect to database
	dsn := os.Getenv("DB_USERNAME")+":" + os.Getenv("DB_PASSWORD") + "@tcp("+os.Getenv("DB_HOST")+")/"+os.Getenv("DB_DATABASE")
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal("fail to connect to mysql")
	}
	conn = db
}
  1. when i want to make a query to database, i will call a method named InitSlave() which does a conn.Ping() 
func (m *Model) InitSlave() {
	err := conn.Ping()
	log.Println("Ping connection successful...")
	if err != nil {
		log.Fatal(err)
	}
	m.Slave = conn
}

  1. run the app, listening on 8080 port, it’s ok to visit the website

  2. if i leave the app to run 6 to 7 minute idle, try to visit again, i got following message

operation timeout...

In other words, cannot load website page again…

If i declared conn as global variable, it will be always there in memory, i think, so whenever i visit my website it will make a query to database, then a conn.Ping() is called, a new connection will be built or reuse an old one, i cannot figure out why the page can’t be loaded after several minutes idle…

thanks for helping :slight_smile:

2

Really need to see more of your code to be able to adequately assess whats going on here, for instance we don’t get to see your serve functions so we have no idea how your model types are being instantiated and a number of other problems there could be connected.

Can you show more code?

3

here is the source code
i wander if i should call sql.Open() on every request, in func main() for example, not just in init() function of package model, not sure, thanks:slight_smile:

4

I guess your problem come from using copies of conn variable. Don’t do this, use the same conn variable in every query from your program.

5

conn is a *sql.DB pointer, and the doc says

DB is a database handle representing a pool of zero or more
underlying connections. It’s safe for concurrent use by multiple
goroutines.

so conn should always be a pointer pointing to the connection pool, and it’s safe use it cocurrently.
Copies of conn point to the same memory address representing the same connection pool, so it should be safe to use like this, right ?
thanks :slight_smile:

6

No, sql.Open() should be called only once in func init(), here is the doc

// The returned DB is safe for concurrent use by multiple goroutines
// and maintains its own pool of idle connections. Thus, the Open
// function should be called just once. It is rarely necessary to
// close a DB.

7

The problem that I see with this is that you are defining your connection pool in the model package under a generic model type, this object doesn’t actually exist until you create an instance of it and it will get garbage collected if it is created in function scope.

When I do this I create a wrapper that passes the connection pointer to my handlers as like a middleware from main package.

8

The wrapper solution seems good, i consider to use it, thanks :slight_smile:
And i found this issue , same problem.
I solved this by setting the max connection lifetime to 1 minute like following

	conn.SetConnMaxLifetime(time.Duration(1) * time.Minute)

so any connection will be removed after 1 minute without affecting connection pooling dramaticlly.

9

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.