Session variables are classified into system variables and user variables. This topic describes how to query the session variables of a client session on OceanBase Database Proxy (ODP).
View the session variables of a specified client session
You can use the SHOW PROXYSESSION VARIABLES [all] id [like 'xx'] statement to query the variables of a client session.
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, all system and user variables, are returned.
Take note of the following considerations:
In the statement,
idcan be replaced withcs_idorconnection_id. These parameters are equivalent.cs_idindicates the IDof a client session in ODP. `connection_id` indicates the IDof a client session in OceanBase Database. For more information aboutCONNECTION_ID, see CONNECTION_ID.The
likeoption supports fuzzy match. Supported wildcard characters are the percent sign (%) and the underscore (_).
Here are some examples:
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_enable_aggregation_pushdown | 1 | 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_show_trace | 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_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 fields in the returned results.
| 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 Overview.