Bug #6801 Reset Thread statement?
Submitted: 24 Nov 2004 15:14 Modified: 12 Feb 2006 12:21
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:* OS:Any (*)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[24 Nov 2004 15:14] Olaf van der Spek
Description:
The Reset Thread statement would be used for persistent connections and provide an efficient alternative for closing and reopening the connection.
It'd rollback all open transaction, drop all temporary tables and unlock all tables.

This can be used in scripts that serve multiple requests (in an efficient way) but that do not want 'state' from previous requests to carry over to the new request.

How to repeat:
-

Suggested fix:
-
[24 Nov 2004 16:05] Sergei Golubchik
if you aren't limited to sql statements, protocol level "change used" command is what you can use. It is effectively "reset connection" and it's what Connector/J uses for connection pooling (afaik).

Two deficiencies - it's not an sql statement, but a protocol command, and it resets everything, while it could be convenient to reset only something (e.g. prepared statements could be preserved, for performance reasons).
[24 Nov 2004 16:56] Olaf van der Spek
Ah, mysql_change_user().
It also 'redoes' authentication. I don't use prepared statements yet, but if you have to resend them every time, that advantage is lost too.
[12 Feb 2006 12:21] Valeriy Kravchuk
Thank you for a reasonable feature request. A separate command at the protocol level, without deficiencies mentioned by Sergei, can be useful. As well as some statement to use it at "SQL level".
[29 Sep 2007 9:14] Mark Robson
That would be a very useful facility.

Mark
[12 Nov 2008 15:49] Serdar S. Kacar
Some of the mysql functions depends on connection/session environment (like changing binlog format). The following "SQL level" connection/session management statements would help in those cases. 

RESETSESSION TEMPORARY : Deletes all temporary tables of the calling session.

RESETSESSION PREPARED STATEMENTS : Clears all prepared statements.

RESETSESSION VARIABLES: Clears all session variables.

RESETSESSION ALL: something resembles the C API mysql_change_user().