database/sql: add lastUseTime or similar to driverConn, add SetConnMaxIdleLefttime to DB

[leaxoy]

I noticed some db like mysql support idle timeout, which means a conn will closed when timeout. In sql package, only has db.SetConnMaxLefttime and driverConn.createdAt available, so I suggest add lastUseTime to driverConn and SetConnMaxIdleLefttime to DB.

[kardianos]

I don't fully follow what you are suggesting. However, if a driver connection sets an idle timeout in the DSN or settings then the connection will close on idle. If that connection (still in the pool) attempts to be used, it will return driver.ErrBadConn and the pool will try to pull another one.

Actually, it may not be a bad idea to support a method to signal to the database connection pool that the driver is bad right away rather then retrying and risk getting all bad connections. I'm not sure how that would work of hand.

[leaxoy]

Thanks for your prompt, I find an another way to keep conns alive by Ping it by time ticker.

[leaxoy]

Sadly, I also had no idea how to achieve this feature now. But we can talk it in the future

[kardianos]

I think you are proposing a new database pool setting called "set max idle time" where if the connection is idle for ever the max time spent it is closed and removed from the pool.

Is this correct?

[leaxoy]

Yeah, thanks for your detailed explanation

[rsc]

@kardianos, does this proposal sound good to you? OK to accept if so.

[gopherbot]

Change https://golang.org/cl/145758 mentions this issue: database/sql: add SetConnMaxIdleTime

[methane]

@leaxoy Why SetMaxLifetime() is not enough?

When idle_timeout is 3600sec, SetMaxLifetime(time.Second * 1800) can avoid idle timeout.

I don't want to add MaxIdleTime, because it makes pool more complicated.

[odeke-em]

Thank you for sending CL 145758 @kardianos, but unfortunately we didn't get it in for Go1.14. @methane would you mind commenting on the tagged CL about how this new method won't work for you, or how you can use the previous available methods?

Thank you.