Session variables are classified into system variables and user variables. This topic describes how to view the session variables of a specified client session on OceanBase Database Proxy (ODP).
Procedure
You can use the SHOW PROXYSESSION VARIABLES statement in the sys tenant or a user tenant to query the session variables of a specified client session.
Connect to OceanBase Database by using ODP.
Here is an example:
obclient -h10.xx.xx.xx -uusername@obtenant#obdemo -P2883 -p****** -c -A oceanbaseFor more information about how to connect to OceanBase Database by using ODP, see Connect to a MySQL tenant of OceanBase Database by using OBClient and Connect to an Oracle tenant of OceanBase Database by using OBClient.
Query the session variables of the specified client session.
SHOW PROXYSESSION VARIABLES [ALL] id [LIKE 'xx'];The parameters are described as follows:
If the
ALLparameter is not used in the statement, the local session variables of the specified client session, including modified system variables and user variables, are returned.If the
ALLparameter is used in the statement, all session variables of the specified client session, including all system variables and user variables, are returned.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.You can obtain the
connection_idvalue by using theSELECT CONNECTION_ID();statement in MySQL mode and theSHOW FULL PROCESSLIST;statement in Oracle mode.The
LIKEoption supports fuzzy matches. Percent signs (%) and underscores (_) are supported.
Here is an example:
MySQL modeOracle modeQuery
cs_idorconnection_id.Execute the following statement to query
cs_id:SHOW PROXYSESSION;In the following query result,
Idis equivalent tocs_id.+----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 12402504630519660559 | 65149 | test420 | mysql001 | root | 100.xx.xx.xx:46069 | NULL | 0 | 1 | MCS_ACTIVE_READER | 76286 | 76286 | 0 | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ 1 row in setYou can also execute the following statement to query
connection_id:SELECT CONNECTION_ID();The result is as follows:
+-----------------+ | CONNECTION_ID() | +-----------------+ | 3221593149 | +-----------------+ 1 row in setQuery the local session variables of the specified client session.
SHOW PROXYSESSION VARIABLES 65149;or
SHOW PROXYSESSION VARIABLES 3221593149;The query result is as follows:
+-----------------------------------+--------------+-----------------+--------------------+-----------------------------------------------+ | variable_name | value | info | modified_type | sys_variable_flag | +-----------------------------------+--------------+-----------------+--------------------+-----------------------------------------------+ | ob_proxy_global_variables_version | 0 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_proxy_user_privilege | 133009965054 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_capability_flag | 916303 | 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 | | character_set_database | 45 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | collation_database | 45 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | _min_cluster_version | '4.2.0.0' | user var | cold modified vars | | +-----------------------------------+--------------+-----------------+--------------------+-----------------------------------------------+ 7 rows in setQuery all session variables of the specified client session.
SHOW PROXYSESSION VARIABLES ALL 65149 LIKE '%id%';or
SHOW PROXYSESSION VARIABLES ALL 3221593149 LIKE '%id%';The query result is as follows:
+--------------------------------------+----------------------------------------+---------+------------------------------+--------------------------------------------+ | variable_name | value | info | modified_type | sys_variable_flag | +--------------------------------------+----------------------------------------+---------+------------------------------+--------------------------------------------+ | last_insert_id | 0 | sys var | last insert id modified vars | && session_scope | | identity | 0 | sys var | cold modified vars | && session_scope | | server_id | 1 | sys var | cold modified vars | &&global_scope | | query_cache_wlock_invalidate | 0 | sys var | cold modified vars | && global_scope && session_scope | | server_uuid | 'd72b5d0d-2c50-11ee-b1de-2646ab385e11' | sys var | cold modified vars | && global_scope && readonly | | ob_org_cluster_id | 0 | sys var | cold modified vars | && session_scope | | ob_statement_trace_id | 'Y0-0' | sys var | cold modified vars | && invisible && session_scope && readonly | | ob_trx_idle_timeout | 86400000000 | sys var | cold modified vars | && global_scope && session_scope | | tracefile_identifier | '' | sys var | cold modified vars | && session_scope | | validate_password_check_user_name | 0 | sys var | cold modified vars | && global_scope | | validate_password_length | 0 | sys var | cold modified vars | && global_scope | | validate_password_mixed_case_count | 0 | sys var | cold modified vars | && global_scope | | validate_password_number_count | 0 | sys var | cold modified vars | && global_scope | | validate_password_policy | 0 | sys var | cold modified vars | && global_scope | | validate_password_special_char_count | 0 | sys var | cold modified vars | && global_scope | +--------------------------------------+----------------------------------------+---------+------------------------------+--------------------------------------------+ 15 rows in set
Query
cs_idorconnection_id.Execute the following statement to query
cs_id:SHOW PROXYSESSION;In the following query result,
Idis equivalent tocs_id.+----------------------+-------+----------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | +----------------------+-------+----------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 12402504630519660558 | 65141 | test420 | oracle001 | sys | 100.xx.xx.xx:59648 | SYS | 0 | 1 | MCS_ACTIVE_READER | 76286 | 76286 | 0 | +----------------------+-------+----------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ 1 row in setExecute the following statement to query
connection_id:SHOW FULL PROCESSLIST;In the following query result,
idis equivalent toconnection_id.+------------+------+-----------+---------------------+------+---------+------+--------+-----------------------+---------------+------+ | ID | USER | TENANT | HOST | DB | COMMAND | TIME | STATE | INFO | IP | PORT | +------------+------+-----------+---------------------+------+---------+------+--------+-----------------------+---------------+------+ | 3221583071 | SYS | oracle001 | 172.xx.xx.xx:45343 | SYS | Query | 0 | ACTIVE | SHOW FULL PROCESSLIST | 172.xx.xx.64 | 2881 | +------------+------+-----------+---------------------+------+---------+------+--------+-----------------------+---------------+------+ 1 row in setQuery the local session variables of the specified client session.
SHOW PROXYSESSION VARIABLES 65141;or
SHOW PROXYSESSION VARIABLES 3221583071;The query result is as follows:
+-----------------------------------+--------------+-----------------+--------------------+-----------------------------------------------+ | variable_name | value | info | modified_type | sys_variable_flag | +-----------------------------------+--------------+-----------------+--------------------+-----------------------------------------------+ | autocommit | 0 | changed sys var | hot modified vars | && global_scope && session_scope | | character_set_connection | 46 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | character_set_database | 46 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | character_set_server | 46 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | collation_connection | 46 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | collation_database | 46 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | collation_server | 46 | changed sys var | cold modified vars | && global_scope && session_scope && nullable | | sql_mode | 2151677954 | changed sys var | cold modified vars | && global_scope && session_scope | | group_concat_max_len | 32767 | changed sys var | cold modified vars | && global_scope && session_scope | | ob_proxy_global_variables_version | 0 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_proxy_user_privilege | 133009965054 | changed sys var | cold modified vars | && invisible && session_scope && readonly | | ob_capability_flag | 916303 | 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.2.0.0' | user var | cold modified vars | | +-----------------------------------+--------------+-----------------+--------------------+-----------------------------------------------+ 14 rows in setQuery all session variables of the specified client session.
SHOW PROXYSESSION VARIABLES ALL 65141 LIKE '%id%';or
SHOW PROXYSESSION VARIABLES ALL 3221583071 LIKE '%id%';The query result is as follows:
+--------------------------------------+----------------------------------------+---------+------------------------------+--------------------------------------------+ | variable_name | value | info | modified_type | sys_variable_flag | +--------------------------------------+----------------------------------------+---------+------------------------------+--------------------------------------------+ | last_insert_id | 0 | sys var | last insert id modified vars | && session_scope | | identity | 0 | sys var | cold modified vars | && session_scope | | server_id | 1 | sys var | cold modified vars | &&global_scope | | query_cache_wlock_invalidate | 0 | sys var | cold modified vars | && global_scope && session_scope | | server_uuid | 'd72b5d0d-2c50-11ee-b1de-2646ab385e11' | sys var | cold modified vars | && global_scope && readonly | | ob_org_cluster_id | 0 | sys var | cold modified vars | && session_scope | | ob_statement_trace_id | 'Y0-0' | sys var | cold modified vars | && invisible && session_scope && readonly | | ob_trx_idle_timeout | 86400000000 | sys var | cold modified vars | && global_scope && session_scope | | tracefile_identifier | '' | sys var | cold modified vars | && session_scope | | validate_password_check_user_name | 0 | sys var | cold modified vars | && global_scope | | validate_password_length | 0 | sys var | cold modified vars | && global_scope | | validate_password_mixed_case_count | 0 | sys var | cold modified vars | && global_scope | | validate_password_number_count | 0 | sys var | cold modified vars | && global_scope | | validate_password_policy | 0 | sys var | cold modified vars | && global_scope | | validate_password_special_char_count | 0 | sys var | cold modified vars | && global_scope | +--------------------------------------+----------------------------------------+---------+------------------------------+--------------------------------------------+ 15 rows in set
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. A variable can be a system variable or a user variable. modified_type The modification type of the variable identified based on the modification frequency. sys_variable_flag The scope of the system variable.
References
For more information about system variables, see Parameters and system variables.