Concerns with pooled connections; please address

[samholmes]

http://stackoverflow.com/questions/18116830/how-to-properly-handle-connection-persistent-things-within-node-mysql-connection

In the above link, I describe my concerns with pooled connections. How do most people address these concerns, and are they valid concerns?

Thanks for any feedback.

[samholmes]

Another recent concern I have is with temporary tables. If I release a connection back into the pool without dropping the temporary table(s) I created with the connection, will the temporary tables be automatically dropped?

[dougwilson]

Hi @samholmes since the connection will persist between uses, the tables will not be automatically dropped when you return the connection to the pool. I am working to address this by providing an option to the pool to set a connection reset packet whenever a connection is returned to the pool. This will add very little overhead (changeUser can do this, but it's much more expensive) and will mean that whenever you get a connection from the pool, it's session will be "clean" and not have garbage from other parts of your application.

I will keep you posted in this issue.

[SystemParadox]

On a related note, the documentation shows an example of using the 'connection' event to do a SET SESSION. When the automatic reset is implemented, this (or another event) would also need to be fired every time a connection is reset (or allocated), to allow for reliable environment setup.

At the moment, I am just using the 'connection' event along with connection.destroy() rather than connection.release().

Thanks.

[dougwilson]

When the automatic reset is implemented, this (or another event) would also need to be fired every time a connection is reset (or allocated), to allow for reliable environment setup.

Yep, this will 100% be what happens. There should never be a case where that event does not get a chance to run if the session is reset.

[sidorares]

So "reset after releasing" is going to be optional? My concert is prepared statement lifecycle as they live as long as connection (or until changeUser). What about having default "reset on each release" for getConnection() + release() and not reseting with pool.query() / pool.execute() ?

[dougwilson]

Whatever the addition, it will be configurable, especially since this will likely go in to a minor release where this would be opt-in behavior. I have not yet worked out a good API.

[mafischer]

I'm a fan of this feature, still planning on implementing it?
pool.query() could do the reset by default whereas acquiring a connection might require an api call to reset.

connection.query()
connection.reset()
connection.release()