Client session|V4.2.2| docs|Distributed Database

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: `MCS_INIT`: being initialized `MCS_ACTIVE_READER`: active `MCS_KEEP_ALIVE`: alive `MCS_HALF_CLOSE`: half-closed `MCS_CLOSED`: closed
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 id parameter 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 id parameter is specified, fuzzy search is supported for retrieving the value of a specified attribute name (supported as of ODP V1.1.0).
id can be replaced with cs_id or CONNECTION_ID, and the returned result will still be the same.
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 clause 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: `MCS_INIT`: being initialized `MCS_ACTIVE_READER`: active `MCS_KEEP_ALIVE`: alive `MCS_HALF_CLOSE`: half-closed `MCS_CLOSED`: closed
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 all parameter is omitted, the local session variables of the specified client session, including modified system and user variables, are returned.
If the all parameter is not omitted, all session variables of the specified client session, including all system and user variables, are returned.

Parameters:

id can be replaced with cs_id or CONNECTION_ID, and the returned result will still be the same.
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 clause 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 set

Query 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 set

Query session variables with the all parameter 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 PROXYSESSION statement 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 set

Run the following command to terminate the session:

obclient> kill 68;
ERROR 1317 (70100): Query execution was interrupted

Execute 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 set

Run the following command to terminate the session:

obclient> kill 3221766868;
ERROR 1317 (70100): Query execution was interrupted

Execute 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
```