This topic describes several general operations performed on a server session, which is established between OceanBase Database Proxy (ODP) and an OBServer node.
View a server session
At present, it is not supported to view a server session through ODP. You can log on to the sys tenant of OceanBase Database as the root user and view all OBServer nodes in the cluster. Then, you can directly connect to the target OBServer node and view the server session.
This section provides only one method for you to view a server session. For more information about the operations and output, see View tenant sessions.
Log on to the sys tenant of OceanBase Database as the root user and view all OBServer nodes in the cluster.
obclient> select * from oceanbase.__all_server; +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+ | gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+ | 2023-02-28 15:45:53.230044 | 2023-02-28 15:46:25.577180 | 10.10.10.1 | 2882 | 3 | z3 | 2881 | 1 | ACTIVE | 0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) | 0 | 1677570376568330 | 0 | 1 | | 2023-02-28 15:45:53.197477 | 2023-02-28 15:46:25.534448 | 10.10.10.2 | 2882 | 2 | z2 | 2881 | 0 | ACTIVE | 0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) | 0 | 1677570376522994 | 0 | 1 | | 2023-02-28 15:45:53.113870 | 2023-02-28 15:46:25.098607 | 10.10.10.3 | 2882 | 1 | z1 | 2881 | 0 | ACTIVE | 0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) | 0 | 1677570378084150 | 0 | 1 | +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+ 3 rows in setSelect the target OBServer node and directly connect to it. The following sample code directly connects to the OBServer node with the IP address 10.10.10.1 and the SQL port 2881.
[admin@test001 ~]$ obclient -h10.10.10.1 -P2881 -uroot@sys -p -Doceanbase -ARun the
show processlist;command to view all sessions of the current OBServer node.obclient> show processlist;The output is as follows:
+------------+---------+------------------+-----------+---------+------+--------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------------+---------+------------------+-----------+---------+------+--------+------------------+ | 3221812197 | root | 10.10.10.1:48563 | NULL | Query | 0 | ACTIVE | show processlist | | 3222117829 | proxyro | 10.10.10.1:37876 | oceanbase | Sleep | 6 | SLEEP | NULL | | 3221709618 | root | 10.10.10.1:51390 | NULL | Sleep | 831 | SLEEP | NULL | +------------+---------+------------------+-----------+---------+------+--------+------------------+ 3 rows in set
Terminate a server session
You can terminate a specified server session through ODP, or terminate a server session by directly connecting to the target OBServer node.
Terminate a server session through ODP
In the sys tenant, you can use the KILL PROXYSESSION {cs_id | connection_id} ss_id statement to terminate a specified server session. The parameters are described as follows:
cs_idindicates the ID of a client session in ODP. You can use theSHOW PROXYSESSIONstatement to obtaincs_id. The value ofIdin the output is the value ofcs_id.connection_idindicates the ID of a client session in OceanBase Database. You can use theSELECT CONNECTION_ID();statement to obtainconnection_id.ss_idindicates the ID of a server session in ODP. You can use theSHOW PROXYSESSION ATTRIBUTEstatement to obtainss_id.
Perform the following steps:
Log on to OceanBase Database through ODP as the
root@sysuser.obclient -h10.10.10.1 -uroot@sys#obdemo -P2883 -p -c -AThe preceding command is only an example. You need to modify the parameters based on the actual situation. For more information, see Connect to an OceanBase tenant by using OBClient (MySQL mode).
View the client session and obtain
cs_id.SHOW PROXYSESSION;The output is as follows, where the value of
Idis the value ofcs_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 setObtain the value of
ss_idbycs_id.SHOW PROXYSESSION ATTRIBUTE 64940;The output 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 for the client session.
KILL PROXYSESSION 64940 16;
Terminate a server session by directly connecting to the target OBServer node
Log on to the sys tenant of OceanBase Database as the root user and view all OBServer nodes in the cluster.
obclient> select * from oceanbase.__all_server; +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+ | gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+ | 2023-02-28 15:45:53.230044 | 2023-02-28 15:46:25.577180 | 10.10.10.1 | 2882 | 3 | z3 | 2881 | 1 | ACTIVE | 0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) | 0 | 1677570376568330 | 0 | 1 | | 2023-02-28 15:45:53.197477 | 2023-02-28 15:46:25.534448 | 10.10.10.2 | 2882 | 2 | z2 | 2881 | 0 | ACTIVE | 0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) | 0 | 1677570376522994 | 0 | 1 | | 2023-02-28 15:45:53.113870 | 2023-02-28 15:46:25.098607 | 10.10.10.3 | 2882 | 1 | z1 | 2881 | 0 | ACTIVE | 0 | 4.1.0.0_1-703037f0b023c8ffa880258463b25b1735cf27b3(Feb 28 2023 13:21:21) | 0 | 1677570378084150 | 0 | 1 | +----------------------------+----------------------------+------------+----------+----+------+------------+-----------------+--------+-----------------------+--------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+ 3 rows in setDirectly connect to the target OBServer node. The following sample code directly connects to the OBServer node with the IP address 10.10.10.1 and the SQL port 2881.
[admin@test001 ~]$ obclient -h10.10.10.1 -P2881 -uroot@sys -p -Doceanbase -ARun the
show processlist;command to view all sessions of the current OBServer node.obclient> show processlist;The output is as follows:
+------------+---------+------------------+-----------+---------+------+--------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------------+---------+------------------+-----------+---------+------+--------+------------------+ | 3221812197 | root | 10.10.10.1:48563 | NULL | Query | 0 | ACTIVE | show processlist | | 3222117829 | proxyro | 10.10.10.1:37876 | oceanbase | Sleep | 6 | SLEEP | NULL | | 3221709618 | root | 10.10.10.1:51390 | NULL | Sleep | 831 | SLEEP | NULL | +------------+---------+------------------+-----------+---------+------+--------+------------------+ 3 rows in setRun the
kill <id>command to terminate the current session whoseIdis3221812197.obclient> kill 3221812197; ERROR 2013 (HY000): Lost connection to MySQL server during query