Session variables are classified into system variables and user variables. This topic describes how to query the session variables of a specified client session on 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 AI 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 AI Database by using ODP, see Connect to OceanBase AI 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, and the query results are the same.cs_idindicates the ID of a client session in ODP.connection_idindicates the ID of a client session in OceanBase AI Database.You can obtain the
connection_idvalue by using theSELECT CONNECTION_ID();statement.The
LIKEoption supports fuzzy matches. Percent signs (%) and underscores (_) are supported.
Here is an example:
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 | +----------------------+-------+----------+----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+ | 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;Alternatively, use the following syntax:
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%';Alternatively, use the following syntax:
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
The following table describes the fields in the returned result.
FieldDescriptionvariable_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 Overview of parameters and system variables.