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.
Parameters:
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 like section supports fuzzy match. Supported wildcard characters are the percent sign (%) and the underscore (_).
Sample code:
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 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. |
More information
For more information about system variables, see Overview of system variables.