This topic describes how to query the session variables of a client session on OceanBase Database Proxy (ODP).
Session variables are classified into system variables and user variables.
You can use the SHOW PROXYSESSION VARIABLES [[all] id [like 'xx']] statement to query the variables of a client session.
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 and user variables, are returned.If the
allparameter is used in the statement, all session variables of the specified client session, including all system and user variables, are returned.If
idis not specified, the session variables 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 session variables 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 theIDof a client session in ODP.connection_idindicates theIDof a client session in OceanBase Database. For more information aboutCONNECTION_ID, see CONNECTION_ID.The
likekeyword supports fuzzy match. Supported wildcard characters are the percent sign (%) and the underscore (_).
The following sample code provides examples on how to use the SHOW PROXYSESSION VARIABLES statement to query the variables of a specified client session:
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 |
+-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+
2 rows in set
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 |
+-----------------------------------+------------------+-----------------+--------------------+--------------------------------------------+
2 rows in set
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 |
| autocommit | 1 | sys var | hot 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 |
| binlog_row_image | 2 | sys var | cold modified vars | && global_scope && session_scope |
| character_set_client | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| character_set_connection | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| character_set_database | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| character_set_filesystem | 63 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| character_set_results | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| character_set_server | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| character_set_system | 45 | sys var | cold modified vars | && global_scope && session_scope && readonly |
| collation_connection | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| collation_database | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| collation_server | 45 | sys var | cold modified vars | && global_scope && session_scope && nullable |
| connect_timeout | 10 | sys var | cold modified vars | && global_scope |
| datadir | /usr/local/mysql/data/ | sys var | cold modified vars | && global_scope && readonly |
| debug_sync | | sys var | cold modified vars | && invisible && session_scope |
| div_precision_increment | 4 | sys var | cold modified vars | && global_scope && session_scope |
| explicit_defaults_for_timestamp | 1 | sys var | cold modified vars | && global_scope && session_scope |
| group_concat_max_len | 1024 | sys var | cold modified vars | && global_scope && session_scope |
| identity | 0 | sys var | cold modified vars | && session_scope |
| init_connect | | sys var | cold modified vars | && global_scope |
| interactive_timeout | 28800 | sys var | cold modified vars | && global_scope && session_scope |
| last_insert_id | 0 | sys var | last insert id modified vars | && session_scope |
| license | | sys var | cold modified vars | && global_scope && readonly |
| lower_case_table_names | 2 | sys var | cold modified vars | && global_scope && readonly |
| max_allowed_packet | 4194304 | sys var | cold modified vars | && global_scope && session_scope && readonly |
| max_user_connections | 0 | sys var | cold modified vars | && global_scope && session_scope && readonly |
| net_read_timeout | 30 | sys var | cold modified vars | && global_scope && session_scope |
| net_write_timeout | 60 | sys var | cold modified vars | && global_scope && session_scope |
| ob_bnl_join_cache_size | 10485760 | sys var | cold modified vars | && global_scope && session_scope |
| ob_default_replica_num | 1 | sys var | cold modified vars | && global_scope && session_scope |
| ob_enable_aggregation_pushdown | 1 | sys var | cold modified vars | && global_scope && session_scope |
| ob_enable_blk_nestedloop_join | 0 | sys var | cold modified vars | && global_scope && session_scope |
| ob_enable_hash_group_by | 0 | sys var | cold modified vars | && global_scope && session_scope |
| ob_enable_index_direct_select | 0 | sys var | cold modified vars | && global_scope && session_scope |
| ob_enable_plan_cache | 1 | sys var | cold modified vars | && global_scope && session_scope |
| ob_enable_trace_log | 0 | sys var | cold modified vars | && global_scope && session_scope |
| ob_enable_transformation | 1 | sys var | cold modified vars | && global_scope && session_scope |
| ob_global_debug_sync | | sys var | cold modified vars | && invisible && session_scope |
| ob_interm_result_mem_limit | 2147483648 | sys var | cold modified vars | && global_scope && session_scope |
| ob_last_schema_version | 0 | sys var | hot modified vars | && session_scope |
| ob_log_level | disabled | sys var | hot modified vars | && global_scope && session_scope |
| ob_max_parallel_degree | 32 | sys var | cold modified vars | && global_scope && session_scope |
| ob_org_cluster_id | 0 | sys var | cold modified vars | && session_scope |
| ob_proxy_partition_hit | 1 | sys var | cold modified vars | && invisible && session_scope && readonly |
| ob_proxy_set_trx_executed | 0 | sys var | cold modified vars | && invisible && session_scope && readonly |
| ob_query_timeout | 10000000 | sys var | hot modified vars | && global_scope && session_scope |
| ob_read_consistency | 3 | sys var | cold modified vars | && global_scope && session_scope |
| ob_trx_timeout | 100000000 | sys var | cold modified vars | && global_scope && session_scope |
| read_only | 0 | sys var | cold modified vars | && global_scope |
| sql_auto_is_null | 0 | sys var | cold modified vars | && global_scope && session_scope |
| sql_mode | 4194304 | sys var | cold modified vars | && global_scope && session_scope |
| sql_select_limit | 9223372036854775807 | sys var | cold modified vars | && global_scope && session_scope |
| sql_warnings | 0 | sys var | cold modified vars | && global_scope && session_scope |
| timestamp | 0 | sys var | cold modified vars | && session_scope |
| time_zone | +8:00 | sys var | cold modified vars | && global_scope && session_scope |
| tx_isolation | READ-COMMITTED | sys var | cold modified vars | && global_scope && session_scope |
| tx_read_only | 0 | sys var | cold modified vars | && global_scope && session_scope |
| version | 1.0.6 | sys var | cold modified vars | && global_scope && readonly |
| version_comment | OceanBase 1.0.6 (r57980-local-f70f8267c28da07e638b124c6909e18883fb98c0) (Built Apr 27 2016 14:32:24) | sys var | cold modified vars | && global_scope && readonly |
| wait_timeout | 28800 | sys var | cold modified vars | && global_scope && session_scope |
+-----------------------------------+------------------------------------------------------------------------------------------------------+---------+------------------------------+-----------------------------------------------+
64 rows in set
obclient> SHOW PROXYSESSION VARIABLES all 2147549231 like "%id%";
+-------------------+-------+---------+------------------------------+-------------------+
| variable_name | value | info | modified_type | sys_variable_flag |
+-------------------+-------+---------+------------------------------+-------------------+
| identity | 0 | sys var | cold modified vars | && session_scope |
| last_insert_id | 0 | sys var | last insert id modified vars | && session_scope |
| ob_org_cluster_id | 0 | sys var | cold modified vars | && session_scope |
+-------------------+-------+---------+------------------------------+-------------------+
3 rows in set
The following table describes the parameters in the returned results.
| Parameter | 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. |
For more information about variables, see Overview (MySQL mode) or Overview (Oracle mode).
For more information about how to configure user variables, see SET (MySQL mode) or SET (Oracle mode).
