Purpose
You can use this statement to terminate a session. This statement terminates only one session at a time.
Required privileges
If you have the PROCESS privilege, you can view all sessions. If you have the SUPER privilege, you can terminate all sessions and statements one by one. Otherwise, you can view and terminate only the sessions and statements that you have initiated. An administrator can terminate all sessions and statements one by one.
By default, a normal user can terminate only sessions of the current user, and sessions in other tenants are invisible to the user. Whether a normal user can terminate sessions of other users in the same tenant depends on the privileges of the current tenant. In OceanBase Database, the sys tenant can terminate any session in the current tenant. The super administrator (root@proxysys) of OceanBase Database Proxy (ODP) can terminate sessions in all clusters and tenants.
Syntax
KILL [CONNECTION | QUERY ] proceselist_id;
Parameters
| Parameter | Description |
|---|---|
| KIll | Terminates the session with the specified proceselist_id value. |
| KILL CONNECTION | Terminates the client session with the specified ID. It is equivalent to the KILL statement without a modifier. |
| KILL QUERY | Terminates the SQL query with the specified proceselist_id value.
NoteYou cannot execute the
|
| proceselist_id | The client session ID of the current session, which is the unique identifier of a session in a client. You can query the client session ID by using the SHOW PROCESSLIST; or SHOW PROXYSESSION; statement. The value of the Id column in the output is the client session ID. |
Examples
Log in to OceanBase Database through ODP as the
root@proxysysuser.obclient -h10.10.10.1 -uroot@proxysys -P2883 -p -c -AThe IP address (
h) and port number (-P) in this example are only for reference. You must replace them with the actual ones.View the client session and query the client session ID.
SHOW PROCESSLIST;The output is as follows, where the value of
Idis the client session ID.+------------+----------+------+---------------------+-----------+-------------+-------------------+-------------------+--------+--------+ | Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | +------------+----------+------+---------------------+-----------+-------------+-------------------+-------------------+--------+--------+ | 1607745580 | proxysys | root | 10.10.10.1:12472 | NULL | 0 | 0 | MCS_ACTIVE_READER | 108100 | 108071 | | 1607729162 | sys | root | 10.10.10.1:12174 | oceanbase | 0 | 1 | MCS_ACTIVE_READER | 108071 | 108071 | +------------+----------+------+---------------------+-----------+-------------+-------------------+-------------------+--------+--------+Terminate the specified session.
kill connection 1607745580;The output is as follows:
ERROR 1317 (70100): Query execution was interruptedIf you execute the
SHOW PROCESSLIST;statement, the client re-establishes a session, sends the SQL statement to ODP for execution, and displays the execution result. Here is an example:ERROR 2006 (HY000): OceanBase server has gone away No connection. Trying to reconnect... Connection id: 1607745581 Current database: oceanbase +------------+----------+------+---------------------+-----------+-------------+-------------------+-------------------+--------+--------+ | Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | +------------+----------+------+---------------------+-----------+-------------+-------------------+-------------------+--------+--------+ | 1607745581 | proxysys | root | 10.10.10.1:3984 | NULL | 0 | 0 | MCS_ACTIVE_READER | 108100 | 108071 | | 1607729162 | sys | root | 10.10.10.1:12174 | oceanbase | 0 | 1 | MCS_ACTIVE_READER | 108071 | 108071 | +------------+----------+------+---------------------+-----------+-------------+-------------------+-------------------+--------+--------+