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
Triage: Triaged: D5 (Feature request)

[27 Dec 2007 18:36] Morgan Tocker
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.
[27 Dec 2007 18:36] Morgan Tocker
Reset connection patch

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

[27 Dec 2007 18:40] Morgan Tocker
Suggested improvement:
RESET CONNECTION [ALL|VARIABLES|TEMPORARY_TABLES|ROUTINES|PREPARED_STATEMENTS]
[9 Jan 2008 19:51] Jeremy Cole
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.
[2 Dec 2013 14:46] Morgan Tocker
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.)