Terminate a server session|V4.2.1| docs|Distributed Database

Terminate a server session

Last Updated：2023-12-25 08:49:40 Updated

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 oceanbase
```
For 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, 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:

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

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
```
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
```

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:

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

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

Terminate the server session on the client session.
```
KILL PROXYSESSION 64940 16;
```