This topic describes the system variables of OceanBase Database based on the functions of the system variables.
Global level
Security-related
Variable |
Description |
|---|---|
| validate_password_check_user_name | Specifies whether to allow a user password to be the same as the username. |
| validate_password_length | Specifies the minimum length of a user password. |
| validate_password_mixed_case_count | Specifies the minimum number of uppercase and lowercase letters in a user password. |
| validate_password_number_count | Specifies the minimum number of digits in a user password. |
| validate_password_policy | Specifies the password check strategy. |
| validate_password_special_char_count | Specifies the minimum number of special characters in a user password. |
| ob_tcp_invited_nodes | Specifies the IP address list of the tenant, which is used as the IP address whitelist. The IP address list can contain the following characters: the percent sign (%), the hyphen (-), and the IP address. Multiple IP addresses are separated with commas (,). The IP address list supports IP address list matching, mask matching, and fuzzy matching. |
| default_password_lifetime | Specifies the password expiration period. |
| block_encryption_mode | Specifies the encryption algorithm used in the aes_encrypt and aes_decrypt functions. |
| sql_safe_updates | Specifies whether to enable the SQL safe mode of MySQL. The default value is OFF. |
Variables related to version information
Variable name |
Description |
|---|---|
| version_comment | Displays the version of the OBServer node. |
| version | Displays the server version number. |
| ob_last_schema_version | Specifies the latest schema version used in the current session. |
| ob_proxy_global_variables_version | Specifies the latest version of global variables that the OBServer node returns to the OBProxy. |
| ob_enable_transmission_checksum | Specifies whether to perform data packet checksum verification between the OBProxy and the OBServer node. This setting takes effect immediately after it is applied. |
Debug-related variables
Variable name |
Description |
|---|---|
| debug_sync | Specifies the synchronization point for debugging and is compatible with MySQL. |
| ob_global_debug_sync | Specifies the synchronization point for debugging. Unlike debug_sync, setting this variable affects all sessions. |
Variables related to case sensitivity
Variable name |
Description |
|---|---|
| lower_case_table_names | Specifies whether to make the database case-insensitive. |
Read and write/Query-related
Variable |
Description |
|---|---|
| query_cache_size | The size of memory allocated for storing the results of queries, which is the unused memory. |
| query_cache_type | The type of the query cache. |
| ob_query_timeout | The timeout period for a query. |
| is_result_accurate | When a query contains the topk hint, the is_result_accurate variable indicates whether the result is accurate. |
| net_buffer_length | The maximum size of an SQL query that an OBServer node can accept. |
| read_only | Specifies whether the tenant is in read-only mode. |
| ob_read_consistency | The read consistency level. |
| ob_max_read_stale_time | The maximum latency threshold (in microseconds) for weak-consistency reads. |
| net_read_timeout | The number of seconds that an OBServer node waits for other data to be read before interrupting the read. |
| net_write_timeout | The number of seconds that an OBServer node waits for block writes before interrupting the write. |
| ob_enable_index_direct_select | Specifies whether to allow users to directly query index tables. |
| ob_enable_aggregation_pushdown | Specifies whether to allow aggregation operations to be pushed down. |
| ob_enable_jit | Specifies the JIT execution engine mode. |
| sql_throttle_network | The request queuing time threshold. If the request queuing time is greater than or equal to this value, queries are not executed. |
| parallel_servers_target | The conditions for queuing a large query on each server. |
| cte_max_recursion_depth | The maximum recursion depth of a common table expression (CTE). The server terminates any operation that exceeds this CTE value. |
| regexp_stack_limit | The maximum available memory for the internal stack of a regular expression matching operation. |
| regexp_time_limit | The time limit for a regular expression matching operation. |
Event Scheduler
Variable |
Description |
|---|---|
| event_scheduler | Specifies whether to enable the event scheduler. |
Foreign keys
Variable |
Description |
|---|---|
| foreign_key_checks | Specifies whether to check foreign key constraints during DML or DDL operations. |
Replicas
Variable |
Description |
|---|---|
| ob_create_table_strict_mode | Specifies whether to use strict mode when creating a table. |
| ob_default_replica_num | Specifies the default number of replicas for a table in a cluster. |
Recycle bin
Variable |
Description |
|---|---|
| recyclebin | Specifies whether to enable the recycle bin. |
| ob_enable_truncate_flashback | Specifies whether to enable flashback for table truncation. |
Caching
Variable |
Description |
|---|---|
| ob_enable_plan_cache | Specifies whether to enable plan cache. |
| ob_plan_cache_percentage | Specifies the percentage of memory that can be used by the plan cache in a tenant. |
| ob_plan_cache_evict_high_percentage | Specifies the percentage of memory that triggers the eviction of the plan cache. |
| ob_plan_cache_evict_low_percentage | Specifies the percentage of memory at which the plan cache eviction is stopped. |
| ob_bnl_join_cache_size | Specifies the amount of data cached in a batch for a Batch Nest Loop Join operation. |
Functions
Variable |
Description |
|---|---|
| interactive_timeout | Specifies the number of seconds the server waits for activity before closing an interactive connection. |
| wait_timeout | Specifies the number of seconds the server waits for activity before closing a non-interactive connection. |
| group_concat_max_len | Specifies the maximum length of the result of the GROUP_CONCAT() function. |
Cluster
Variable |
Description |
|---|---|
| ob_capability_flag | The capabilities of the OBServer node, which are used to negotiate capabilities with the Proxy. This variable informs the Proxy of the features supported and not supported by the current OBServer node. |
| ob_compatibility_mode | Specifies the compatibility mode of the tenant. |
| ob_route_policy | Specifies the routing strategy for selecting data replicas on OBServer nodes. |
| performance_schema | Specifies whether to support performance information queries. The default value is OFF.
NoteThis variable is only for compatibility with MySQL 8.0. The related feature is not supported. You can query and set this variable, but the setting does not take effect and no error is returned. |
| resource_manager_plan | Activates a resource management plan. Different plans use different CPU quotas to limit user activities in a resource group. |
| license | Specifies the license type. |
| ob_proxy_partition_hit | Specifies whether to send SQL statement requests to the OBServer node that hosts the primary partition of the data. |
| ob_org_cluster_id | Specifies the CLUSTER_ID of the OceanBase cluster. |
| server_uuid | Specifies the UUID of the OBServer node. |
| default_storage_engine | Specifies the default storage engine of the OBServer node. |
Directories
Variable |
Description |
|---|---|
| datadir | The local disk path where data is stored. |
| plugin_dir | The path where the plugin DLL is stored. |
| secure_file_priv | The path that allows access during import or export to files. |
| ob_enable_hash_group_by | Specifies whether to enable Hash Group by. |
Memory
Variable |
Description |
|---|---|
| ob_sql_work_area_percentage | The percentage of tenant memory allowed for SQL execution. |
| ob_sql_audit_percentage | The maximum percentage of current tenant memory that can be used for SQL audit. |
| ob_reserved_meta_memory_percentage | The percentage of tenant memory reserved for storing meta-related structures. |
| ob_temp_tablespace_size_percentage | The percentage of tenant disk space for temporary tables. |
System logs
Variable |
Description |
|---|---|
| binlog_row_image | Specifies whether to record full-column logs. |
| ob_enable_show_trace | Specifies whether to use trace logs. |
| ob_log_level | The log level for the session. If not specified, the system log level is used. |
| tracefile_identifier | Specifies the content to add to the log for easier filtering of row iteration trace logs. |
Runtime Filter related
Variable |
Description |
|---|---|
| runtime_filter_type | Specifies the type of runtime filter for the current tenant. |
| runtime_filter_wait_time_ms | Specifies the maximum wait time for a runtime filter. |
| runtime_filter_max_in_num | Specifies the number of distinct values (NDV) in the runtime filter. |
| runtime_bloom_filter_max_size | Specifies the maximum memory size for a runtime Bloom filter, in bytes. |
Time zones and dates related
Variable |
Description |
|---|---|
| time_zone | Specifies the time zone for the current tenant session. Valid values: '+08:00' (offset) and Asia/Shanghai (region). |
| timestamp | A timestamp in seconds. The value of this variable affects the result of select now(6). |
| system_time_zone | Specifies the system time zone of the server. |
| error_on_overlap_time | Specifies whether to return an error when a time zone overlap occurs. |
| nls_language | Specifies the default language for database internationalization. This variable affects the default values of prompts, dates, month names, NLS_SORT, and NLS_DATE_LANGUAGE. |
| nls_territory | Specifies the current region for database internationalization. This variable is used in combination with nls_language. |
| nls_date_language | Specifies the default language for dates in database internationalization. |
| nls_calendar | Specifies the calendar system used in the database. |
| ob_timestamp_service | Specifies the timestamp service to be used. |
Transaction and transaction log related
Variable name |
Description |
|---|---|
| autocommit | Specifies whether to automatically commit transactions. |
| tx_isolation | Specifies the transaction isolation level. |
| ob_proxy_set_trx_executed | Specifies whether to send the START TRANSACTION Syntax statement to the OBServer node through obproxy. |
| ob_early_lock_release | Specifies whether to enable the early lock release (ELR) feature for transactions.
NoteThis variable is deprecated starting from V2.2.30. The ELR feature is now controlled by the tenant-level configuration item |
| ob_trx_idle_timeout | Specifies the transaction idle timeout period. A transaction times out if the execution interval between two statements exceeds this period. |
| transaction_isolation | Specifies the transaction isolation level. |
| transaction_read_only | Specifies whether to allow only read-only transactions. |
| ob_trx_lock_timeout | Specifies the timeout period for waiting for a lock. |
| tx_read_only | Specifies whether the transaction is a read-only transaction. |
| ob_trx_timeout | Specifies the transaction timeout period. |
| ob_trace_info | Specifies whether to pass trace information, which can be persisted to the redo log. |
Audit related
Variable name |
Description |
|---|---|
| ob_enable_sql_audit | Specifies whether to enable the SQL audit feature for the current tenant. |
Data types
Variable name |
Description |
|---|---|
| nls_length_semantics | Specifies the length semantics for CHAR and VARCHAR2 types. |
| nls_nchar_conv_excp | Specifies whether to report an error when data is lost during conversion between NCHAR/NVARCHAR2 and CHAR/VARCHAR2. |
| nls_numeric_characters | Specifies the characters used as decimal and thousand separators in string representations of numbers. |
| nls_currency | Specifies the local currency symbol for the number format element L. |
| nls_iso_currency | Specifies the local currency symbol for the number format element C. |
| nls_dual_currency | Specifies the local currency symbol for the number format element U. |
| nls_date_format | Specifies the format for converting the date type to a string and for implicitly converting a string to a date. |
| nls_timestamp_format | Specifies the format for converting the TIMESTAMP or TIMESTAMP_LTZ type to a string and for implicitly converting a string to a TIMESTAMP or TIMESTAMP_LTZ. |
| nls_timestamp_tz_format | Specifies the format for converting the TIMESTAMP_TZ type to a string and for implicitly converting a string to a TIMESTAMP_TZ. |
| explicit_defaults_for_timestamp | Specifies whether to enable non-standard behavior for the TIMESTAMP data type when handling default values and null values. |
| ob_default_lob_inrow_threshold | Specifies the default in-row storage threshold for LOB fields when creating a table. |
SQL/PL related
Variable |
Description |
|---|---|
| connect_timeout | Specifies the connection timeout period. |
| ob_pl_block_timeout | Specifies the maximum timeout period for PL. |
| cursor_sharing | Specifies whether to parameterize SQL statements. |
| innodb_strict_mode | Specifies the SQL check mode for specific syntaxes. |
| max_connections | Specifies the maximum number of connections for the tenant. |
| sql_mode | Specifies the SQL mode, which has a significant impact on operations such as data insertion. |
| sql_quote_show_create | Specifies whether to enable identifier quoting for SHOW CREATE TABLE and SHOW CREATE DATABASE statements. |
| ob_enable_rich_error_msg | Specifies whether to display information such as svr_ip, time, and trace_id when an SQL execution error occurs. |
| sql_throttle_current_priority | Specifies the throttling priority. Only requests from sessions where sql_throttle_current_priority is less than sql_throttle_priority will be throttled. |
| sql_throttle_priority | Specifies the throttling priority. |
| sql_throttle_rt | Specifies the response time (RT) value. |
| max_allowed_packet | Specifies the maximum size of network packets. |
| max_user_connections | Specifies the maximum number of connections that a single user can establish with an OBServer node. Setting this to 0 indicates no limit. |
| max_sp_recursion_depth | Specifies the maximum level of recursion for any specified stored procedure. |
| sql_select_limit | Specifies the maximum number of rows that a single SELECT query can return. |
| max_connections | Specifies the maximum number of connections for the tenant. |
| ob_enable_blk_nestedloop_join | Specifies whether to allow block nested loop joins. |
| parallel_max_servers | Specifies the maximum number of threads in the parallel execution (PX) thread pool on each server. |
Optimizer-related variables
Variable |
Description |
|---|---|
| optimizer_use_sql_plan_baselines | Specifies whether the optimizer uses a plan baseline. |
| optimizer_capture_sql_plan_baselines | Specifies whether to automatically capture new plans to a plan baseline. |
| parallel_degree_policy | Specifies the degree-of-parallelism selection strategy. |
| parallel_degree_limit | Specifies the maximum degree of parallelism that the optimizer selects when the auto DOP strategy is used. |
| parallel_min_scan_time_threshold | Specifies the minimum estimated execution time for parallel processing of base table scans. |
| optimizer_dynamic_sampling | Specifies the level of dynamic sampling. |
| optimizer_features_enable | Specifies the optimizer features enabled in different OceanBase Database versions. |
| ob_enable_transformation | Specifies whether to enable the rewrite feature of the SQL optimizer. |
Auto-increment columns
Variable |
Description |
|---|---|
| auto_increment_increment | Specifies the auto-increment step size. This variable is applicable only to MySQL clients. |
| auto_increment_offset | Specifies the starting value for the AUTO_INCREMENT column. |
| last_insert_id | Returns the last auto-increment field value inserted in the current session. |
| identity | The identity variable is a synonym for the last_insert_id variable. You can execute the select @@identity statement to query it. |
| sql_auto_is_null | Specifies whether to retrieve the auto-increment column value of the last inserted row. |
| auto_increment_cache_size | Specifies the number of cached auto-increment values. |
| div_precision_increment | Specifies the increment of the precision of the result of a division operation based on the precision of the dividend. This is a MySQL-compatible feature. |
Character sets and strings
Variable |
Description |
|---|---|
| character_set_client | The character set for statements sent by the client. |
| character_set_connection | The character set for statements received and then converted. |
| character_set_database | The character set for the default database. |
| character_set_results | The character set for converting result sets or error messages sent to the client. |
| character_set_server | The character set for the server. |
| character_set_system | The character set used by the server. |
| collation_connection | The character set and collation for the connection. |
| collation_database | The default character set and collation for creating databases. |
| collation_server | The default character set and collations for the server. |
| character_set_filesystem | The character set for the file system. |
| sql_warnings | Specifies whether to generate an informational string for a single-line INSERT statement when an alert is triggered. |
| init_connect | The string executed by the server for each client connection, consisting of one or more SQL statements separated by semicolons. |
| nls_sort | The collation for the string value. |
| nls_comp | The comparison rule for the string value. |
| nls_characterset | The default character set for CHAR, VARCHAR2, CLOB data types in the database. |
| nls_nchar_characterset | The default character set for NCHAR, NVARCHAR2, NCLOB data types in the database. |
Session level
Session-related variables
Variable name |
Description |
|---|---|
| ob_last_schema_version | The schema version used in the latest operation in the session. |
Read/write and query-related variables
Variable name |
Description |
|---|---|
| is_result_accurate | When a query contains the topk hint, the is_result_accurate variable indicates whether the result is accurate. |
Cluster-related variables
Variable name |
Description |
|---|---|
| ob_capability_flag | The capabilities of the OBServer node, which are used for capability negotiation with the Proxy. This variable informs the Proxy of the features supported by the current OBServer node. |
| ob_proxy_partition_hit | Specifies whether to send SQL statements to the OBServer node hosting the primary partition of the data. |
| ob_org_cluster_id | The CLUSTER_ID of the OceanBase cluster. |
System log-related variables
Variable name |
Description |
|---|---|
| ob_enable_show_trace | Specifies whether to use trace logs. |
| tracefile_identifier | Specifies the content to be added to the log for easier filtering of row iteration trace logs. |
Time zones and dates
Variable name |
Description |
|---|---|
| timestamp | TIMESTAMP indicates the timestamp in seconds. This variable affects the result of the select now(6) statement. |
| nls_language | This variable indicates the default language for database internationalization support, which is used for prompts, dates, month names, and the default values of NLS_SORT and NLS_DATE_LANGUAGE. |
Transactions and transaction logs
Variable name |
Description |
|---|---|
| ob_proxy_set_trx_executed | This variable specifies whether to send the START TRANSACTION Syntax statement to the OBServer through obproxy. |
| ob_trace_info | This variable is used to pass trace information, which can be persisted to the REDO log. |
Auto-increment columns
Variable name |
Description |
|---|---|
| last_insert_id | This variable returns the value of the last auto-increment column in the current session. |