Mysql: Connections in pool have an infinite lifetime

[dwardin]

I have a process running all the time and when it idles for a while the connections in the pool are in a sleeping state. Eventually MySQL purges those connections based on the wait_timeout setting in my.cnf. Once this happens and I try to use a connection it will fail because the module assumes the connection is still live and tries to use it only to get a timeout or connection exception.

I would propose adding an option when creating a pool connection_lifetime which will work in a following way. When getting a connection from the pool, if the connection_lifetime is not exceeded we simply return the connection, if however it is exceeded we kill the selected free connection and create a new one in it's place. That should solve the issue.

[dougwilson]

Sounds good to me. Please send us a PR :)

[jbdutton]

I am working on a PR for this now. My first attempt at this is using Date.now() whenever a PoolConnection is created, and again when pool.getConnection is called to determine whether or not a connection is too old to be used. Like so:

// Inside getConnection:
if (this._isExpiredConnection(connection)) {
        pool._purgeConnection(connection);
        return this.getConnection(cb);
}

// Elsewhere:
Pool.prototype._isExpiredConnection = function (connection) {
  var age = Date.now() - connection.createdAt;
  return age > this.config.connectionLifetime;
};

If the connection is expired, it is purged and getConnection is called again until a usable connection is received or one has to be created. Since Date.now returns UTC it should be safe to use even if DST changes during the life of a connection.

To test this new feature though, we have to simulate the passage of time in order to have an expired connection. Not sure how @dougwilson or the maintainers as a whole would like to accomplish that.

A couple of ideas:

1. Find a module that mocks out Date.now() and use that. (ex github.com/boblauer/MockDate)
2. Cause the tests to sleep a small amount.
3. Do not use Date.now() directly, but use a proxy object this._clock.now() or something similar that defaults to Date.now in normal use but can be replaced in tests.

[jbdutton]

It looks like #505 might already provide a better implementation of this feature (polling the connection pool for expired connections).

[acefxlabs]

If i understand you clearly and to contribute, dont you think a lifetime of connection is more killing to you server, interupts approach is better

[schmod]

I don't think #505 is relevant here. That PR periodically polls the server to determine whether or not the sever has closed the connection.

Solving the problem described in this ticket would require the _client_ to actively close the connection after it sits idle for some period of time.

[dwardin]

Has this been resolved yet? If not I might give it a try next weekend or so and send in a PR.

[schmod]

AFAIK, it does not appear to have been resolved.

[ifsnow]

I don't think this is necessary now. That's because Pool checks whether or not the connection is available using connection.ping before returning the pool's connection. If the connection is terminated by the server, it is removed from the pool, added to the connection queue, and processed next.