This topic describes how to query the details of a client session on OceanBase Database Proxy (ODP).
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']]
The parameters are described as follows:
If
idis 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
idis specified, the details of the client session of the specified ID are returned. This query method is supported for ODP V1.1.0 and later. You can also query the value of a specified attribute in fuzzy match mode. This query method is supported for ODP V1.1.2 and later.In the statement,
idcan be replaced withcs_idorconnection_id. These parameters are equivalent.cs_idindicates the ID of a client session in ODP.CONNECTION_IDindicates the ID of a client session in OceanBase Database. For more information aboutCONNECTION_ID, see CONNECTION_ID.The
likekeyword allows for fuzzy match of field names, supporting%and_.
The following sample code provides examples on how to use the SHOW PROXYSESSION ATTRIBUTE statement to query the details of a specified client session:
obclient> SHOW PROXYSESSION;
+--------------------+------+-----------------+--------+------+-----------------+------+-------------+-------------------+-------------------+---------+---------+
| proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------------------+------+-----------------+--------+------+-----------------+------+-------------+-------------------+-------------------+---------+---------+
| 756006681247547396 | 2 | ob1.jianhua.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 parameters in the returned results.
| Parameter | Description |
|---|---|
| attribute_name | The attribute name. |
| value | The attribute value. |
| info | The basic information. |
The following table describes the general attributes.
| Parameter | Description |
|---|---|
| proxy_sessid | The ID of the ODP session. |
| 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 on ODP. |