search

mysqljs / mysql

A pure node.js JavaScript Client implementing the MySQL protocol.
MIT License
18.29k stars 2.52k forks source link

Add "reset session" method to connections #780

Open dougwilson opened 10 years ago

dougwilson commented 10 years ago

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 commented 10 years ago

+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 commented 10 years ago

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 commented 8 years ago

@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 commented 8 years ago

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 commented 8 years ago

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 :)