Query the details of a session|V4.1.0| docs|Distributed Database

Query the details of a session

Last Updated：2023-08-01 06:02:28 Updated

This topic describes how to query the details of a client session on OceanBase Database Proxy (ODP).

View the internal status of a specified client session

You can use the SHOW PROXYSESSION ATTRIBUTE statement to query the details of a client session, including the related server session of this client session. SQL syntax:

SHOW PROXYSESSION ATTRIBUTE [id [like 'xxx']]

Parameters:

If id is not specified, the details of the current session are returned. This query method is supported for ODP V1.1.0 and later. You can also query the value of a specified attribute of the current session in fuzzy match mode. This query method is supported for ODP V1.1.2 and later.
If id is specified, you can query the value of a specified attribute of a specified session in fuzzy match mode. This query method is supported for ODP V1.1.0 and later.
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. For more information about CONNECTION_ID, see CONNECTION_ID.
The like section supports fuzzy match. Supported wildcard characters are the percent sign (%) and the underscore (_).

Sample code:

obclient> SHOW PROXYSESSION;
+--------------------+------+-----------------+--------+------+-----------------+------+-------------+-------------------+-------------------+---------+---------+
| proxy_sessid       | Id   | Cluster         | Tenant | User | Host            | db   | trans_count | svr_session_count | state             | tid     | pid     |
+--------------------+------+-----------------+--------+------+-----------------+------+-------------+-------------------+-------------------+---------+---------+
| 756006681247547396 |    2 | ob1.jihua.sjh | sys    | root | 127.0.0.1:22540 | NULL |           0 |                 1 | MCS_ACTIVE_READER | 2230520 | 2230520 |
+--------------------+------+-----------------+--------+------+-----------------+------+-------------+-------------------+-------------------+---------+---------+
1 row in set

obclient> SHOW PROXYSESSION ATTRIBUTE;
+------------------------+---------------------+----------------+
| attribute_name         | value               | info           |
+------------------------+---------------------+----------------+
| proxy_sessid           | 756006681247547396  | cs common      |
| cs_id                  | 2                   | cs common      |
| cluster                | ob1.jianhua.sjh     | cs common      |
| tenant                 | sys                 | cs common      |
| user                   | root                | cs common      |
| host_ip                | 127.0.0.1           | cs common      |
| host_port              | 22540               | 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                    | 2230520             | cs common      |
| pid                    | 2230520             | cs common      |
| modified_time          | 1469435190244371115 | cs stat        |
| reported_time          | 1469435190244371115 | cs stat        |
| hot_sys_var_version    | 0                   | cs var version |
| sys_var_version        | 0                   | 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            | 13203               | last used ss   |
| server_sessid          | 2147549201          | last used ss   |
| ss_id                  | 4                   | 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        | 0                   | 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   |
+------------------------+---------------------+----------------+
33 rows in set

obclient> SHOW PROXYSESSION ATTRIBUTE 2 like '%id%';
+------------------------+--------------------+----------------+
| attribute_name         | value              | info           |
+------------------------+--------------------+----------------+
| proxy_sessid           | 756006681247547396 | cs common      |
| cs_id                  | 2                  | cs common      |
| tid                    | 2230520            | cs common      |
| pid                    | 2230520            | cs common      |
| last_insert_id_version | 0                  | cs var version |
| server_sessid          | 2147549201         | last used ss   |
| ss_id                  | 4                  | last used ss   |
| last_insert_id_version | 0                  | last used ss   |
+------------------------+--------------------+----------------+
8 rows in set

obclient> SHOW PROXYSESSION ATTRIBUTE 2147549201 like '%id%';
+------------------------+--------------------+----------------+
| attribute_name         | value              | info           |
+------------------------+--------------------+----------------+
| proxy_sessid           | 756006681247547396 | cs common      |
| cs_id                  | 2                  | cs common      |
| tid                    | 2230520            | cs common      |
| pid                    | 2230520            | cs common      |
| last_insert_id_version | 0                  | cs var version |
| server_sessid          | 2147549201         | last used ss   |
| ss_id                  | 4                  | last used ss   |
| last_insert_id_version | 0                  | last used ss   |
+------------------------+--------------------+----------------+
8 rows in set

The following table describes the fields in the returned result.

Field	Description
attribute_name	The attribute name.
value	The attribute value.
info	The basic information.

The following table describes the general attributes.

Attribute	Description
proxy_sessid	The session ID of the ODP.
cluster	The name of the cluster.
socket_fd	The socket descriptor.
tenant	The tenant.
user	The user.
host_ip	The IP address of the user.
host_port	The port number of the user.
db	The database.
total_trans_cnt	The total number of transmitted transactions.
svr_session_cnt	The total number of sessions.
active	Indicates whether the session is active.
read_state	The status of the client session.
tid	The thread ID.
pid	The process ID.
modified_time	The time when the session was modified.
reported_time	The time when the session was reported.
hot_sys_var_version	The version of the system variable that is updated in real time.
sys_var_version	The system variable version.
user_var_version	The user variable version.
last_insert_id_version	The last inserted ID version.
db_name_version	The database name version.
server_ip	The IP address of the OBServer node.
server_port	The port number of the OBServer node.
server_sessid	The session ID of the OBServer node.
ss_id	The ID that identifies the server session by the ODP.