WL#6797: Method for clearing session state
Affects: Server-5.7 — Status: Complete
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 re-authentication. User Documentation ================== http://dev.mysql.com/doc/refman/5.7/en/mysql-reset-connection.html 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); Parameters ---------- 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*); Parameters ---------- 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.
Copyright (c) 2000, 2020, Oracle Corporation and/or its affiliates. All rights reserved.