WL#6797: Method for clearing session state

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

In load-balanced and high-availability situations, it is necessary to be able to
clear the session state completely. This should behave like using
COM_CHANGE_USER to the same user as before, except that it should not require

User Documentation

http://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html (for the 
resetconnection command)
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html (changelog entry)
Problem Description:
Existing COM_CHANGE_USER command effectively does a re-login without
closing/opening the connection. Important side effect of this command includes
cleaup of the session context (temporary tables, session variables, etc.) in the
MySQL server. Thread pool library uses COM_CHANGE_USER to clean up this
session context. Thus cleanup of the session context requires additional
authentication to be done as part of COM_CHANGE_USER causing more network
roundtrips between server and client.

New implementation w.r.t this WL:
With this WL we introduce a new API called mysql_reset_connection which 
will initiate clean up of the session context from both client and server side.
This new API will decouple cleanup of session context from COM_CHANGE_USER.
Thus if a thread pool library wants to cleanup the session context, will invoke
this API without needing to call COM_CHANGE_USER.
Also a new mysql command line command (resetconnection) will be introduced.

** Server-side changes **

New server command
A new server command (COM_RESET_CONNECTION) will be implemented to do clean up
of the session context.

Backward/Cross Compatibility
Backward/Cross compatibility is guaranteed by COM_RESET_CONNECTION by default.

For instance :
1) Old client <-> New server 
   Will never send COM_RESET_CONNECTION command.
2) New client <-> old server
   In this case the server will return with as error.

Server side clean up
Sever side cleanup includes following things:
  delete cached transactions, release transaction locks.
  close cached tables, temporary tabels.
  release global read lock.
  clean stored procedure/function cache.
  clean error state.
All the above cleanup is done as part of existing THD::change_user()
function which will be renamed to THD::cleanup_connection().

** Client-side changes(libmysql) **

New libmysql API

   A new function will be introduced which will send COM_RESET_CONNECTION   
   packet to the server. This packet has a 4 byte header followed by 1 byte 
   command. This command will do the clean up of session context.

   int mysql_reset_connection(MYSQL *mysql);

     mysql [IN]           Pointer to the connection handle.

   Return Values
     0 - Success
     1 - Error

New mysql Command

   A new mysql command (resetconnection) will be introduced which will
   clear the current session context from both client and server side.

   static int com_resetconnection(String *str,char*);
     both the parameters will be unused.

   Return Values
     0 - Success
     1 - Error

Client side clean up
For client side cleanup we detach all open statements from current connection
by making a call to mysql_detach_stmt_list().

Adding a flag set to reset the individual components of the session state was 
considered, but ruled out because these would be handled better with the SQL level 
RESET command.