This topic describes how to terminate a specified client session and the server session on it on OceanBase Database Proxy (ODP).
Procedure
You can use the KILL PROXYSESSION statement in the sys tenant to terminate a specified session.
Log on as the root user to the sys tenant of the cluster by using ODP.
Here is an example:
obclient -h10.xx.xx.xx -uroot@sys#obdemo -P2883 -p****** -c -A oceanbaseFor more information about how to connect to OceanBase Database by using ODP, see Connect to a MySQL tenant of OceanBase Database by using OBClient and Connect to an Oracle tenant of OceanBase Database by using OBClient.
Terminate the specified client session.
Terminate the specified client session
KILL PROXYSESSION {cs_id | connection_id};The parameters are described as follows:
In the statement,
idcan be replaced withcs_idorconnection_id. These parameters are equivalentcs_idindicates the ID of a client session in ODP.connection_idindicates the ID of a client session in OceanBase Database. You can use theSELECT CONNECTION_ID();statement to queryconnection_id.The effect of
KILL PROXYSESSION (cs_id | connection_id)is the same as that ofKILL connection_id. For more information about theKILLstatement, see KILL (MySQL Mode) or KILL (Oracle Mode).
Here is an example:
Query
cs_idorconnection_idof a client session.Execute the following statement to query
cs_id:SHOW PROXYSESSION;In the following query result,
Idis equivalent tocs_id.+----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 12402504630519660553 | 56932 | test420 | sys | root | 100.xx.xx.xx:63882 | NULL | 0 | 1 | MCS_ACTIVE_READER | 76286 | 76286 | 0 | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ 1 row in setYou can also execute the following statement to query
connection_id:SELECT CONNECTION_ID ();The result is as follows:
+------------------+ | CONNECTION_ID () | +------------------+ | 3221562906 | +------------------+ 1 row in setTerminate the client session whose
cs_idis56392or whoseconnection_idis3221562906.KILL PROXYSESSION 56392;or
KILL PROXYSESSION 3221562906;After the statement execution, the following error message is returned:
ERROR 1317 (70100): Query execution was interruptedView the client session, the connection is expected to be disconnected.
SHOW PROXYSESSION;The following message is returned:
ERROR 2013 (HY000): Lost connection to MySQL server during queryView the client session again.
SHOW PROXYSESSION;The following message is returned:
ERROR 2006 (HY000): OceanBase server has gone away No connection. Trying to reconnect... Connection id: 57195 Current database: *** NONE *** +----------------------+-------+----------+--------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +----------------------+-------+----------+--------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 12402504630519660555 | 57195 | test420 | sys | root | 100.xx.xx.xx:24996 | NULL | 0 | 1 | MCS_ACTIVE_READER | 76286 | 76286 | 0 | +----------------------+-------+----------+--------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ 1 row in set
The preceding example shows that if you terminate the current session based on
cs_idorconnection_id, the current connection is disconnected after the statement is executed. If you execute theSHOW PROXYSESSIONstatement, OBClient re-establishes a session connection, starts the statement execution, and then returns the result.Terminate the server session on the specified client session
KILL PROXYSESSION {cs_id | connection_id} ss_idThe parameters are described as follows:
In the statement,
idcan be replaced withcs_idorconnection_id. These parameters are equivalent.cs_idindicates the ID of a client session in ODP.connection_idindicates the ID of a client session in OceanBase Database. You can use theSELECT CONNECTION_ID();statement to queryconnection_id.ss_idindicates theIDof a server session on ODP, and can be obtained by using theSHOW PROXYSESSION ATTRIBUTE idstatement. For more information, see Query the details of a session.
Here is an example:
Query
cs_idof a client session.SHOW PROXYSESSION;In the following query result,
Idis equivalent tocs_id.+----------------------+-------+----------+--------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +----------------------+-------+----------+--------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 12402504630519660556 | 64940 | test420 | sys | root | 100.xx.xx.xx:63882 | NULL | 0 | 1 | MCS_ACTIVE_READER | 76286 | 76286 | 0 | +----------------------+-------+----------+--------+------+----------------------+------+------------- +-------------------+-------------------+-------+-------+-----------+ 1 row in setQuery
ss_idbased oncs_idobtained in the previous step.SHOW PROXYSESSION ATTRIBUTE 64940;The query result is as follows:
+----------------------------------+----------------------+----------------+ | attribute_name | value | info | +----------------------------------+----------------------+----------------+ | proxy_sessid | -6044239443189891060 | cs common | | cs_id | 64940 | cs common | | cluster | test3233 | cs common | | tenant | sys | cs common | | user | root | cs common | | host_ip | 100.xx.xx.xx | cs common | | host_port | 63882 | cs common | | db | NULL | cs common | | total_trans_cnt | 0 | cs common | | svr_session_cnt | 1 | cs common | | active | true | cs common | | read_state | MCS_ACTIVE_READER | cs common | | tid | 76286 | cs common | | pid | 76286 | cs common | | idc_name | | cs common | | modified_time | 0 | cs stat | | reported_time | 0 | cs stat | | hot_sys_var_version | 0 | cs var version | | sys_var_version | 2 | cs var version | | user_var_version | 0 | cs var version | | last_insert_id_version | 0 | cs var version | | db_name_version | 0 | cs var version | | server_ip | xx.xx.xx.xx | last used ss | | server_port | 2881 | last used ss | | server_sessid | 3221579563 | last used ss | | ss_id | 16 | last used ss | | state | MSS_KA_CLIENT_SLAVE | last used ss | | transact_count | 2 | last used ss | | server_trans_stat | 0 | last used ss | | hot_sys_var_version | 0 | last used ss | | sys_var_version | 2 | last used ss | | user_var_version | 0 | last used ss | | last_insert_id_version | 0 | last used ss | | db_name_version | 0 | last used ss | | is_checksum_supported | 1 | last used ss | | is_safe_read_weak_supported | 0 | last used ss | | is_checksum_switch_supported | 1 | last used ss | | checksum_switch | 1 | last used ss | | enable_extra_ok_packet_for_stats | 1 | last used ss | +----------------------------------+----------------------+----------------+ 39 rows in setTerminate the server session on the client session.
KILL PROXYSESSION 64940 16;