Terminate a server session

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.

1. Log in 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 oceanbase
   

   For more information about how to connect to OceanBase Database by using ODP, see Connect to a MySQL-compatible tenant of OceanBase Database by using OBClient and Connect to an Oracle-compatible tenant of OceanBase Database by using OBClient.

2. 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, id can be replaced with cs_id or connection_id. These parameters are equivalent

       cs_id indicates the ID of a client session in ODP. connection_id indicates the ID of a client session in OceanBase Database. You can use the SELECT CONNECTION_ID(); statement to query connection_id.

     • The effect of KILL PROXYSESSION (cs_id | connection_id) is the same as that of KILL connection_id. For more information about the KILL statement, see KILL (MySQL Mode) or KILL (Oracle Mode).

     Here is an example:

     1. Query cs_id or connection_id of a client session.

        Execute the following statement to query cs_id:

        SHOW PROXYSESSION;
        

        In the following query result, Id is equivalent to cs_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 set
        

        You can also execute the following statement to query connection_id:

        SELECT CONNECTION_ID ();
        

        The result is as follows:

        +------------------+
        | CONNECTION_ID () |
        +------------------+
        |       3221562906 |
        +------------------+
        1 row in set
        

     2. Terminate the client session whose cs_id is 56392 or whose connection_id is 3221562906.

        KILL PROXYSESSION 56392;
        

        or

        KILL PROXYSESSION 3221562906;
        

        After the statement execution, the following error message is returned:

        ERROR 1317 (70100): Query execution was interrupted
        

     3. View 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 query
        

     4. View 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_id or connection_id, the current connection is disconnected after the statement is executed. If you execute the SHOW PROXYSESSION statement, 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_id
     

     The parameters are described as follows:

     • In the statement, id can be replaced with cs_id or connection_id. These parameters are equivalent.

       cs_id indicates the ID of a client session in ODP. connection_id indicates the ID of a client session in OceanBase Database. You can use the SELECT CONNECTION_ID(); statement to query connection_id.

     • ss_id indicates the ID of a server session on ODP, and can be obtained by using the SHOW PROXYSESSION ATTRIBUTE id statement. For more information, see Query the details of a session.

     Here is an example:

     1. Query cs_id of a client session.

        SHOW PROXYSESSION;
        

        In the following query result, Id is equivalent to cs_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 set
        

     2. Query ss_id based on cs_id obtained 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 set
        

     3. Terminate the server session on the client session.

        KILL PROXYSESSION 64940 16;