This topic lists the system variables of OceanBase Database in detail based on their intended use.
Auto-increment column variables
Variable |
Description |
|---|---|
| auto_increment_increment | The auto-increment step size. It is used only for MySQL client logon. |
| auto_increment_offset | The start value of an auto-increment column. |
| last_insert_id | The last inserted auto-increment value in the current session. |
| identity | The identity variable is synonymous with the last_insert_id variable and can be queried by using select @@identity. |
| sql_auto_is_null | Specifies whether to obtain the auto-increment column value of the last inserted row. |
| auto_increment_cache_size | The number of cached auto-increment values. |
| div_precision_increment | The number of digits by which to increase the scale of the result of a division operation based on the scale of the dividend. This variable is for compatibility with MySQL. |
Transaction variables
Variable |
Description |
|---|---|
| validate_password_check_user_name | Specifies whether a user password can 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 supports the percentage sign (%), underscore (_), and IP addresses (separated by 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 encryption and decryption functions. |
| sql_safe_updates | Specifies whether to enable the MySQL SQL security mode. The default value is OFF. |
| pid_file | Specifies the path name of the file in which the server writes its process ID. |
| port | Specifies the port number on which the server listens for TCP/IP connections. |
| socket | Specifies the name of the Socket file for local client connections. |
Version information
Variable name |
Description |
|---|---|
| version_comment | Displays the version information 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. This setting takes effect immediately. |
Debug
Variable name |
Description |
|---|---|
| debug_sync | Sets the synchronization point during Debug mode, and is compatible with MySQL. |
| ob_global_debug_sync | Sets the synchronization point during Debug mode. This variable affects all sessions and is different from debug_sync. |
Case sensitivity
Variable name |
Description |
|---|---|
| lower_case_table_names | Specifies whether to make the system case-insensitive. |
Read and write/Query-related
Variable name |
Description |
|---|---|
| query_cache_size | The memory size allocated for storing historical query results, 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, is_result_accurate 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 read queries. |
| net_read_timeout | The number of seconds to wait for other data to be received on the connection before interrupting the read. |
| net_write_timeout | The number of seconds to wait for block writes on the connection 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. If the queuing time is greater than or equal to this value, queries are not executed. |
| parallel_servers_target | The maximum number of servers on which a large query is queued. |
| cte_max_recursion_depth | The maximum recursion depth of a common table expression (CTE). The server terminates any operations exceeding this CTE value. |
| regexp_stack_limit | The maximum available memory for the internal stack used in regular expression matching operations. |
| regexp_time_limit | 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 and DDL operations. |
Replicas
Variable |
Description |
|---|---|
| ob_create_table_strict_mode | Specifies 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 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 plan cache eviction. |
| ob_plan_cache_evict_low_percentage | Specifies the percentage of the memory limit at which plan cache eviction stops. |
| ob_bnl_join_cache_size | Specifies the number of data entries to cache for each batch in a Batch Nest Loop Join operation. |
Functions
Variable |
Description |
|---|---|
| interactive_timeout | The number of seconds that the server waits for activity on an interactive connection before closing it. |
| wait_timeout | The number of seconds that the server waits for activity on a non-interactive connection before closing it. |
| group_concat_max_len | The maximum length of the result of the GROUP_CONCAT() function. |
Cluster
Variable |
Description |
|---|---|
| ob_capability_flag | The capabilities that the OBServer node provides for connection with the Proxy. This variable is used for capability negotiation with the Proxy and indicates to the Proxy which features the OBServer node supports and which features it does not support. |
| ob_compatibility_mode | The compatibility mode of the tenant. |
| ob_route_policy | 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. However, setting this variable does not take effect and does not return an error. |
| resource_manager_plan | The resource management plan to be activated. Different plans use different CPU quotas to limit user activities in the resource group. |
| license | The license type. |
| ob_proxy_partition_hit | Specifies whether to send SQL statement requests to the OBServer node where the primary data partitions are located. |
| 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 variables related to directory paths
Variable |
Description |
|---|---|
| datadir | The local disk path for storing data. |
| plugin_dir | The path for storing plugin DLLs. |
| secure_file_priv | The path that allows access during file import or export. |
| 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 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 maximum percentage of tenant disk space for temporary tables. |
System log-related variables
Variable |
Description |
|---|---|
| binlog_row_image | Specifies whether to record full-column logs. |
| ob_enable_show_trace | Specifies whether to use Show Trace logs. |
| ob_log_level | The log level for the session. If not specified, the system log level is used. |
| tracefile_identifier | Specifies content to add to logs for easier filtering of row iteration trace logs. |
Runtime Filter related
Variable |
Description |
|---|---|
| runtime_filter_type | Specifies the type of runtime filter for the tenant. |
| runtime_filter_wait_time_ms | Specifies the maximum wait time for a runtime filter. |
| runtime_filter_max_in_num | Specifies the number of NDVs (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 or the Asia/Shanghai region format. |
| timestamp | The TIMESTAMP is a timestamp in seconds. The value of this variable affects the result of the select now(6) statement. |
| system_time_zone | Specifies the system time zone of the server. |
| error_on_overlap_time | Specifies whether to return an error when the time zone overlaps occur and the time is ambiguous. |
| 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. |
| nls_territory | Specifies the current region for database internationalization support, which is used in combination with nls_language. |
| nls_date_language | Specifies the default language for dates in database internationalization support. |
| 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 to OBServer through obproxy. |
| ob_early_lock_release | Specifies whether to enable the early lock release (ELR) feature for transactions.
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 transaction idle timeout period, which is the time interval between the execution of two statements in a transaction that exceeds the timeout 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 maximum time that a transaction waits for a lock to be released. |
| tx_read_only | Specifies whether the transaction is read-only. |
| 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 data types. |
| nls_nchar_conv_excp | Specifies whether to return an error when data is lost during conversion between NCHAR and NVARCHAR2 data types and CHAR and VARCHAR2 data types. |
| 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 L format element in number formatting. |
| nls_iso_currency | Specifies the local currency symbol for the C format element in number formatting. |
| nls_dual_currency | Specifies the local currency symbol for the U format element in number formatting. |
| nls_date_format | Specifies the format for converting date data to a string and for implicitly converting a string to a date. |
| nls_timestamp_format | Specifies the format for converting TIMESTAMP or TIMESTAMP_LTZ data to a string and for implicitly converting a string to TIMESTAMP or TIMESTAMP_LTZ. |
| nls_timestamp_tz_format | Specifies the format for converting TIMESTAMP_TZ data to a string and for implicitly converting a string to 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 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 significantly affects behaviors 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 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 a network packet. |
| max_user_connections | Specifies the maximum number of connections that a single user can establish to an OBServer node. Set this parameter to 0 to indicate unlimited connections. |
| 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 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 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 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 of different OceanBase versions. |
| ob_enable_transformation | Specifies whether to enable the rewrite feature of the SQL optimizer. |
| range_index_dive_limit | Specifies the maximum number of range indexes that the optimizer uses when estimating rows based on the storage layer. |
| partition_index_dive_limit | Specifies the maximum number of partitions that the optimizer uses when estimating rows based on the storage layer. |
Auto-increment columns
Variable |
Description |
|---|---|
| auto_increment_increment | Specifies the auto-increment step, which is valid only for MySQL clients. |
| auto_increment_offset | Specifies the starting value of 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 and last_insert_id are synonyms. You can run the select @@identity statement to query the value. |
| 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 to which the 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 the result set or error message before sending it 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 character set and collation for the connection. |
| collation_database | The default character set and collation for the created database. |
| collation_server | The default character set and collation for the server. |
| character_set_filesystem | The character set type for the file system. |
| sql_warnings | Controls whether to generate an information string for a single-line INSERT statement when an alert is triggered. |
| init_connect | The string that the server executes 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, 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 variables related to version information
Variable name |
Description |
|---|---|
| ob_last_schema_version | The schema version number of the latest schema used in the session. |
Read/write and query related
Variable name |
Description |
|---|---|
| is_result_accurate | When a query contains the topk hint, the is_result_accurate variable indicates whether the query result is accurate. |
Cluster related
Variable name |
Description |
|---|---|
| ob_capability_flag | The capabilities of the OBServer node, used for capability negotiation with the Proxy. It informs the Proxy of the features supported and not supported by the current OBServer node. |
| ob_proxy_partition_hit | Specifies whether to send SQL statements to the OBServer nodes hosting the primary partitions. |
| ob_org_cluster_id | The CLUSTER_ID of the OceanBase cluster. |
System log related
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 zone and date related
Variable name |
Description |
|---|---|
| timestamp | The TIMESTAMP variable represents the timestamp in seconds. It affects the result of the select now(6) statement. |
| nls_language | The default language for database internationalization support, used for prompts, dates, month names, and the default values of NLS_SORT and NLS_DATE_LANGUAGE. |
Transaction and transaction log related
Variable name |
Description |
|---|---|
| ob_proxy_set_trx_executed | Specifies whether to send the START TRANSACTION Syntax statement to the OBServer through obproxy. |
| ob_trace_info | Specifies the trace information to be passed through. This information can be persisted to the redo log. |
Auto-increment column related
Variable name |
Description |
|---|---|
| last_insert_id | Returns the value of the last auto-increment column in the current session. |