This topic describes several general operations performed on a client session, which is established between a client and OceanBase Database Proxy (ODP).
View client sessions
You can use the SHOW PROXYSESSION statement to view the client sessions of all tenants in ODP. For example:
obclient> show proxysession;
+---------------------+------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+--------+--------+-----------+
| proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl |
+---------------------+------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+--------+--------+-----------+
| 7230691421644980673 | 88 | ob1.cc | sys | root | 127.0.0.1:49999 | NULL | 0 | 1 | MCS_ACTIVE_READER | 120685 | 120685 | 0 |
+---------------------+------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+--------+--------+-----------+
1 row in set
The following table describes the fields in the returned result.
| Field | Description |
|---|---|
| proxy_sessid | The ID of the client session allocated by OceanBase Database. |
| Id | The ID of the client session allocated by ODP. It is equivalent to cs_id mentioned later. |
| Cluster | The name of the OceanBase cluster to which the client session belongs. |
| Tenant | The tenant account for connecting to the OceanBase cluster. |
| User | The username for connecting to the OceanBase cluster. |
| Host | The IP address and port number of the client. |
| db | The database in which the statement is executed. |
| trans_count | The number of transactions completed in the client session. |
| svr_session_count | The total number of sessions held between ODP and OceanBase Database. |
| state | The state of the client session. Valid values:
|
| tid | The thread ID. |
| pid | The process ID. |
| using_ssl | Indicates whether the client session uses the Secure Sockets Layer (SSL) protocol for transmission. |
View the details of a client session
You can use the SHOW PROXYSESSION ATTRIBUTE statement to query the details of a client session, including related server sessions of this client session. SQL syntax:
SHOW PROXYSESSION ATTRIBUTE [id [like 'xxx']]
Parameters:
If the
idparameter is not specified, the details of the current session are returned (supported as of ODP V1.1.0). Fuzzy search is supported for retrieving the value of a specified attribute name in the current session (supported as of ODP V1.1.2).If the
idparameter is specified, fuzzy search is supported for retrieving the value of a specified attribute name (supported as of ODP V1.1.0).idcan be replaced withcs_idorCONNECTION_ID, and the returned result will still be the same.cs_idindicates theIDof a client session in ODP.CONNECTION_IDindicates theIDof a client session in OceanBase Database. For more information aboutCONNECTION_ID, see CONNECTION_ID.The
likeclause supports fuzzy search. Supported wildcard characters are the percent sign (%) and the underscore (_).
Example:
obclient> SHOW PROXYSESSION;
+--------------------+------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+---------+---------+
| proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------------------+------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+---------+---------+
| 756006681247547396 | 2 | ob1.cc | 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.cc | 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 |
| idc_name | | 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 |
| 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
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 ID of the client session allocated by OceanBase Database. |
| cs_id | The ID of the client session allocated by ODP. It is equivalent to the Id parameter mentioned earlier. |
| cluster | The name of the OceanBase cluster to which the client session belongs. |
| tenant | The tenant account for connecting to the OceanBase cluster. |
| user | The username for connecting to the OceanBase cluster. |
| host_ip | The IP address of the client. |
| host_port | The port number of the client. |
| db | The database in which the statement is executed. |
| total_trans_cnt | The total number of transactions transmitted by ODP. |
| svr_session_cnt | The total number of sessions held between ODP and OceanBase Database. |
| active | Indicates whether the session is active. |
| read_state | The state of the client session. Valid values:
|
| 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 of the server session in ODP. |
View the variables of a client session
Session variables are classified into system variables and user variables. You can use the SHOW PROXYSESSION VARIABLES [all] id [like 'xx'] statement to view the variables of a client session.
If the
allparameter is omitted, the local session variables of the specified client session, including modified system and user variables, are returned.If the
allparameter is not omitted, all session variables of the specified client session, including all system and user variables, are returned.
Parameters:
idcan be replaced withcs_idorCONNECTION_ID, and the returned result will still be the same.cs_idindicates theIDof a client session in ODP.CONNECTION_IDindicates theIDof a client session in OceanBase Database. For more information aboutCONNECTION_ID, see CONNECTION_ID.The
likeclause supports fuzzy search. Supported wildcard characters are the percent sign (%) and the underscore (_).
Example:
Query session variables by
cs_id.obclient> SHOW PROXYSESSION VARIABLES 3; +-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+ | variable_name | value | info | modified_type | sys_variable_flag | +-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+ | ob_proxy_global_variables_version | 1461742173142100 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_proxy_user_privilege | 65534 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_capability_flag | 654159 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_enable_transmission_checksum | 1 | changed sys var | cold modified vars | && global_scope && session_scope | | _min_cluster_version | '4.1.0.1' | user var | cold modified vars | | +-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+ 5 rows in setQuery session variables by
CONNECTION_ID.obclient> SHOW PROXYSESSION VARIABLES 2147549231; +-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+ | variable_name | value | info | modified_type | sys_variable_flag | +-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+ | ob_proxy_global_variables_version | 1461742173142100 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_proxy_user_privilege | 65534 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_capability_flag | 654159 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_enable_transmission_checksum | 1 | changed sys var | cold modified vars | && global_scope && session_scope | | _min_cluster_version | '4.1.0.1' | user var | cold modified vars | | +-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+ 5 rows in setQuery session variables with the
allparameter specified.obclient> SHOW PROXYSESSION VARIABLES all 3; +-----------------------------------+-----------------------+---------+------------------------------+-----------------------------------------------+ | variable_name | value | info | modified_type | sys_variable_flag | +-----------------------------------+-----------------------+---------+------------------------------+-----------------------------------------------+ | ob_proxy_global_variables_version | 1461742173142100 | sys var | cold modified vars | && invisible && session_scope && readonly | | ob_proxy_user_privilege | 65534 | sys var | cold modified vars | && invisible && session_scope && readonly | | ob_capability_flag | 654159 | sys var | cold modified vars | && invisible && session_scope && readonly | | ob_enable_transmission_checksum | 1 | sys var | cold modified vars | && global_scope && session_scope | | auto_increment_increment | 1 | sys var | cold modified vars | && global_scope && session_scope | | auto_increment_offset | 1 | sys var | cold modified vars | && global_scope && session_scope | ······ # Subsequent outputs omitted
The following table describes the fields in the returned result.
| Field | Description |
|---|---|
| variable_name | The name of the variable. |
| value | The value of the variable. |
| info | The type of the variable (system variable or user variable). |
| modified_type | The modification type of the variable (identified based on modification frequency). |
| sys_variable_flag | The scope of the system variable. |
For more information about system variables, see System variables. For more information about how to set user variables, see SET.
Terminate a client session
You can use the KILL (cs_id | connection_id) command to terminate a client session. The following sections describe how to terminate a client session through the client session ID or connection ID.
Note
If you perform a KILL operation on a session by specifying its cs_id or CONNECTION_ID, the specified session is terminated, indicating that the operation is successful. When you execute the SHOW PROXYSESSION statement, the client will reestablish a session, send the SQL statement to ODP for execution, and display the execution result.
Terminate a session by specifying the client session ID
Execute the
SHOW PROXYSESSIONstatement to query the ID of the client session to be terminated (cs_id).obclient> show proxysession;As shown in the returned result, the value in the second column (
Id) indicates the client session ID.+---------------------+--------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+------+------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +---------------------+--------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+------+------+-----------+ | 7230691418559283266 | 68 | ob1.cc | sys | root | 127.0.0.1:50260 | NULL | 0 | 1 | MCS_ACTIVE_READER | 8728 | 8728 | 0 | +---------------------+--------+---------+--------+------+-----------------+------+-------------+-------------------+-------------------+------+------+-----------+ 1 rows in setRun the following command to terminate the session:
obclient> kill 68; ERROR 1317 (70100): Query execution was interruptedExecute the following statement to verify whether the session is terminated:
obclient> select 88;The returned result shows that the connection is lost.
ERROR 2013 (HY000): Lost connection to MySQL server during query
Terminate a session by specifying the connection ID
Execute the following statement to query the connection ID of the current session:
obclient> select CONNECTION_ID();Returned result:
+-----------------+ | CONNECTION_ID() | +-----------------+ | 3221766868 | +-----------------+ 1 row in setRun the following command to terminate the session:
obclient> kill 3221766868; ERROR 1317 (70100): Query execution was interruptedExecute the following statement to verify whether the session is terminated:
obclient> select 88;The returned result shows that the connection is lost.
ERROR 2013 (HY000): Lost connection to MySQL server during query