MySQL Bugs: #33540: No way to reset connection from SQL interface

Bug #33540	No way to reset connection from SQL interface
Submitted:	27 Dec 2007 18:36
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.1.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
From within the SQL interface there is no way to reset:

- Stored routine cache
- Temporary tables
- User variables
- Prepared Statements

This means that connection pooling software either needs to keep track of resources it creates[1] or use the workaround, which is to do a change user back to the same user.

Not every API has a way of doing the change user, so I've written a patch to roughly map this functionality to the SQL Layer. 

[1] Impossible, see bug: #33274 (There is also no way of building a list of temporary tables).

How to repeat:
See description.

Suggested fix:
Implement a 'RESET CONNECTION' command (proof of concept patch to be uploaded).  Example:

mysql> use test;
Database changed
mysql> CREATE TEMPORARY TABLE a (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> RESET CONNECTION;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TEMPORARY TABLE a (a int);
Query OK, 0 rows affected (0.00 sec)

Known defects in this patch:
- Don't know how it will work with replication.  It needs to be replicated, since slaves need to be able to drop temporary tables or variables.

Reset connection patch

Attachment: reset_connection.patch (, text), 2.57 KiB.

Suggested improvement:
RESET CONNECTION [ALL|VARIABLES|TEMPORARY_TABLES|ROUTINES|PREPARED_STATEMENTS]

Just a quick comment which I also left on Morgan's blog post:

> This doesn't address any character set issues, and I'm not sure it can.
> the change user interface used now (since it is not SQL) can easily do
> this, though.

> For instance, try doing a SET NAMES ucs2; and then RESET CONNECTION; --
> it won't recognize the SQL command.

5.7.3 adds a reset connection option to the protocol:
http://dev.mysql.com/doc/refman/5.7/en/mysql-reset-connection.html

(No SQL interface yet.)

in the mysql command line client you can do this:

mysql> resetconnection
mysql>