This topic lists all the system variables of OceanBase Database based on the functions of the system variables.
Global level
Security-related variables
Variable |
Description |
|---|---|
| validate_password_check_user_name | Specifies whether the username can be the same as the password. |
| validate_password_length | Specifies the minimum length of a password. |
| validate_password_mixed_case_count | Specifies the minimum number of uppercase and lowercase letters in a password. |
| validate_password_number_count | Specifies the minimum number of digits in a password. |
| validate_password_policy | Specifies the password check strategy. |
| validate_password_special_char_count | Specifies the minimum number of special characters in a password. |
| ob_tcp_invited_nodes | Specifies the IP address list of the tenant. The IP address list can contain the percent sign (%), hyphen (-), and IP address. Multiple IP addresses in the list 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 MySQL SQL security mode. The default value is OFF. |
Global system 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 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 enable packet checksum verification between the OBproxy and the OBServer node. The setting takes effect immediately after it is specified. |
Debug-related variables
Variable name |
Description |
|---|---|
| debug_sync | Specifies the synchronization point during Debug mode, and is compatible with MySQL. |
| ob_global_debug_sync | Specifies the synchronization point during Debug mode. It differs from debug_sync in that setting this variable affects all sessions. |
Case sensitivity-related variables
Variable name |
Description |
|---|---|
| lower_case_table_names | Specifies whether to make the system case-sensitive. |
Read and write/Query-related
Variable name |
Description |
|---|---|
| query_cache_size | Specifies the memory allocated for storing the results of queries, which is the unused memory. |
| query_cache_type | Specifies the query cache type. |
| ob_query_timeout | Specifies the query timeout period. |
| is_result_accurate | When a query contains the topk hint, is_result_accurate indicates whether the result is accurate. |
| net_buffer_length | Specifies 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 | Specifies the read consistency level. |
| ob_max_read_stale_time | Specifies the maximum latency threshold (in microseconds) for weak-consistency reads. |
| net_read_timeout | Specifies the number of seconds to wait for other data to be received before interrupting the read. |
| net_write_timeout | Specifies the number of seconds to wait 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 | Specifies the request queuing time. If the queuing time exceeds this value, queries will not be executed. |
| parallel_servers_target | Specifies the conditions for queuing large queries on each server. |
| cte_max_recursion_depth | Specifies the maximum recursion depth for common table expressions (CTEs). The server will terminate any operations exceeding this CTE value. |
| regexp_stack_limit | Specifies the maximum amount of available memory for the internal stack used in regular expression matching. |
| regexp_time_limit | Specifies the time limit for regular expression matching operations. |
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 enable the strict mode for table creation. |
| ob_default_replica_num | Specifies the default number of replicas for each 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. |
Caches
Variable |
Description |
|---|---|
| ob_enable_plan_cache | Specifies whether to enable the plan cache. |
| ob_plan_cache_percentage | Specifies the percentage of tenant memory that can be used by the plan cache. |
| ob_plan_cache_evict_high_percentage | Specifies the percentage of the memory limit that triggers the eviction of the plan cache. |
| ob_plan_cache_evict_low_percentage | Specifies the percentage of the memory limit at which plan cache eviction is stopped. |
| ob_bnl_join_cache_size | Specifies the amount of data to cache for each batch in a Batch Nest Loop Join operation. |
Functions
Variable |
Description |
|---|---|
| interactive_timeout | The number of seconds the server waits for activity on an interactive connection before closing the connection. |
| wait_timeout | The number of seconds the server waits for activity on a non-interactive connection before closing the connection. |
| group_concat_max_len | The maximum length of the result of the GROUP_CONCAT() function. |
Cluster
Variable |
Description |
|---|---|
| ob_capability_flag | The capabilities that an OBServer node provides for connection with a Proxy. This variable is used for capability negotiation with a Proxy to inform the Proxy of the features supported and not supported by the current OBServer node. |
| ob_compatibility_mode | The compatibility mode of the tenant. |
| ob_route_policy | The routing strategy for selecting data replicas on an OBServer node. |
| 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. However, the setting does not take effect and no error is reported. |
| resource_manager_plan | The resource management plan to be activated. Different plans use different CPU quotas to limit user activities in a resource group. |
| license | The type of the license. |
| ob_proxy_partition_hit | Specifies whether to send SQL statements to the OBServer node that hosts the primary data partition. |
| ob_org_cluster_id | The CLUSTER_ID of the OceanBase cluster. |
| server_uuid | The UUID of the OBServer node. |
| default_storage_engine | The default storage engine of the OBServer node. |
System path-related variables
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 when importing or exporting files. |
| ob_enable_hash_group_by | Specifies whether to enable Hash Group by. |
Memory-related variables
Variable |
Description |
|---|---|
| ob_sql_work_area_percentage | The maximum percentage of tenant memory that can be used for SQL execution. |
| ob_sql_audit_percentage | The maximum percentage of 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 maximum percentage of tenant memory that can be used for temporary tablespaces. |
System log-related variables
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 of the current session. If not specified, the system log level is used. |
| tracefile_identifier | The content to be added to the log for easy filtering of row iteration trace logs. |
Runtime Filter related
Variable |
Description |
|---|---|
| runtime_filter_type | Specifies the runtime filter type at the tenant level. |
| runtime_filter_wait_time_ms | Specifies the maximum wait time for a runtime filter. |
| runtime_filter_max_in_num | Specifies the number of NDV (number of distinct values) for a runtime filter. |
| runtime_bloom_filter_max_size | Specifies the maximum memory size of 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 are in the +08:00 offset format and the Asia/Shanghai region format. |
| timestamp | A TIMESTAMP is 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 ambiguity occurs in daylight saving time overlap zones. |
| nls_language | Specifies the default language for database internationalization, which is used for prompts, dates, month names, and the default values of NLS_SORT and NLS_DATE_LANGUAGE. |
| nls_territory | Specifies the current region for database internationalization, which 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 variables
Variable name |
Description |
|---|---|
| autocommit | Specifies whether to automatically commit a transaction. |
| 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 a transaction.
NoteThis variable is deprecated in V2.2.30. The ELR feature is now controlled by the tenant-level configuration item |
| ob_trx_idle_timeout | Specifies the idle timeout period for a transaction. A transaction times out when the time interval between two statements exceeds this value. |
| transaction_isolation | Specifies the transaction isolation level. |
| transaction_read_only | Specifies whether to allow only read-only transactions. |
| ob_trx_lock_timeout | Specifies the lock wait timeout period for a transaction. |
| 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 variables
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 return an error when data is lost during conversion between NCHAR and CHAR or NVARCHAR2 and VARCHAR2. |
| nls_numeric_characters | Specifies the characters used as the decimal separator and the thousand separator 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 the date type. |
| nls_timestamp_format | Specifies the format for converting the TIMESTAMP or LTZ subtypes to a string and for implicitly converting a string to the TIMESTAMP or LTZ subtypes. |
| nls_timestamp_tz_format | Specifies the format for converting the TIMESTAMP TZ type to a string and for implicitly converting a string to the TIMESTAMP TZ type. |
| explicit_defaults_for_timestamp | Specifies whether to enable non-standard behavior for the TIMESTAMP data type when handling default values and null values. |
| log_row_value_options | Specifies whether to use partial updates for LOBs. |
| ob_default_lob_inrow_threshold | Specifies the default in-row storage threshold for LOB columns 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 perform parameterization when processing SQL statements. |
| innodb_strict_mode | Specifies the SQL check mode for specific syntaxes. |
| max_connections | Specifies the maximum number of connections for the entire tenant. |
| sql_mode | Specifies the SQL mode, which has a significant impact on behaviors such as inserting data. |
| sql_quote_show_create | Specifies whether to enable identifier quoting for the 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 error occurs during SQL execution. |
| 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 can be established from a single user to an OBServer node. If set to 0, the connection number is not limited. |
| max_sp_recursion_depth | Specifies the maximum number of recursive calls to 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 entire tenant. |
| ob_enable_blk_nestedloop_join | Specifies whether to enable block nested loop join. |
| parallel_max_servers | Specifies the maximum number of threads in the parallel execution (PX) thread pool on each server. |
Optimizer-related
Variable name |
Description |
|---|---|
| optimizer_use_sql_plan_baselines | Specifies whether to use plan baselines. |
| optimizer_capture_sql_plan_baselines | Specifies whether to automatically capture new plans to plan baselines. |
| parallel_degree_policy | Specifies the parallelism selection strategy. |
| parallel_degree_limit | Specifies the upper limit of the degree of parallelism selected by the optimizer 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 for different OceanBase 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, which applies only to MySQL clients. |
| auto_increment_offset | Specifies the starting value for the AUTO_INCREMENT column. |
| last_insert_id | Returns the value of the auto-increment field for the last inserted row in the current session. |
| identity | identity is a synonym for the variable last_insert_id. You can use select @@identity to query it. |
| sql_auto_is_null | Controls 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 to which received statements should be converted. |
| character_set_database | The character set for the default database. |
| character_set_results | The character set to which the server should convert result sets or error messages before sending them back 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 collation for the connection. |
| collation_database | The collation for the default character set when a database is created. |
| collation_server | The collation for the server. |
| character_set_filesystem | The character set for the file system. |
| sql_warnings | Controls whether a single-line INSERT statement generates an information string when an alert occurs. |
| init_connect | The string that the server executes for each client connection. The string contains 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, and other data types in the database. |
| nls_nchar_characterset | The default character set for NCHAR, NVARCHAR2, NCLOB, and other data types in the database. |
Session-level
Session-related variables
Variable name |
Description |
|---|---|
| ob_last_schema_version | The schema version used 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 nodes where the primary partitions of the requested data are located. |
| 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 add to the log for easier filtering of row iteration trace logs. |
Time zones and dates
Variable name |
Description |
|---|---|
| timestamp | TIMESTAMP is a timestamp, in seconds. This variable affects the result of select now(6). |
| nls_language | Specifies 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 | Specifies whether to send the START TRANSACTION syntax to the OBServer through obproxy. |
| ob_trace_info | Specifies whether to pass trace information through. This information can be persisted to the redo log. |
Auto-increment columns
Variable name |
Description |
|---|---|
| last_insert_id | Returns the value of the auto-increment column inserted in the last operation in the current session. |