Add "reset session" method to connections

[dougwilson]

There should be an easy way to reset the session associated with a connection, like MySQL Connector has. Since MySQL 5.7.2 there is a new "reset connection" packet that will reset the connection state without making a new connection and authenticating (less overhead). Too bad 5.7.2 is way too new :)

MySQL Connector conveniently added a method to generically reset the session by using the new packet when available and falling back to the slower change user packet.

This feature is very useful, especially when interacting with transactions and a connection pool.

[sidorares]

+1 for this. Do you know if reset command supported by mariasql server? Note to me (or anyone implementing prepared statements) - this should clear statements cache. Shoud we have "reset" event to notify anyone relying on connection state? Also triggered by changeUser

[dougwilson]

Do you know if reset command supported by mariasql server?

I'm not sure. Seeing the version it was added to MySQL, it's possible it is not there. If you have access to a MariaDB server (maybe you do since you mentioned it?) you can run the tests from https://github.com/felixge/node-mysql/tree/feature/reset-connection-packet against it to determine this.

From reading https://mariadb.com/kb/en/mariadb-versus-mysql-compatibility/ it would seem that support for this packet would be in a MariaDB 5.7 release if it would be anywhere.

Shoud we have "reset" event to notify anyone relying on connection state?

Maybe. I'm still debating on how this should be handled, as we do want to support the cases where people run some initial queries to set up some session state and have them re-run when the session gets reset.

[ben-page]

@dougwilson, what happened with this feature? It would be really useful when pooling to ensure that session state is predictable. In my experience (in the SQL Server world), many connection pools perform a session reset automatically when the connection is released.

AFAIK, there are currently two alternatives and both have downsides. The first alternative is to change user. Which is basically disconnecting, reconnecting, and reauthenticating (so it's expensive). The second is to create a transaction and COMMIT RELEASE. Which is also very expensive and slow if you are only SELECTing data.

So, I'd really like to see this included. To facilitate that, I created a PR.

[ben-page]

Unfortunately, it seems that MariaDB does not support this command. I receive an ER_UNKNOWN_COM_ERROR from it. But reset works great in MySQL.

[dougwilson]

Hi @ben-page, the feature was implemented 2 years ago (branch https://github.com/mysqljs/mysql/tree/feature/reset-connection-packet) but never merged since the CI at the time didn't support testing it, and I'm not going to merge new things that cannot be tested on CI. Yes, I definitely dropped the ball on following up on the CI to see when it eventually got supported, sorry!

Do you know if one of our CIs (Travis CI or ApVeyor) supports testing this feature now?

The first alternative is to change user. Which is basically disconnecting, reconnecting, and reauthenticating (so it's expensive).

This is actually completely wrong; that is describing ending a connection and connecting again and logging back in. A change user is only the reauth part (that's what makes it slightly more efficient that disconnecting and reconnecting :)