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 |
|---|---|
| autocommit | Specifies whether to automatically commit transactions. |
| tx_isolation | The isolation level of transactions. |
| ob_proxy_set_trx_executed | Specifies whether to send START TRANSACTION Syntax to OBServer through OceanBase Database Proxy (ODP). |
| ob_early_lock_release | Specifies whether to enable the early lock release (ELR) feature for transactions. |
| ob_trx_idle_timeout | The idle timeout period of a transaction in microseconds. A timeout occurs when the execution interval between two statements in a transaction exceeds the specified timeout period. |
| transaction_isolation | The isolation level of transactions. |
| transaction_read_only | Specifies whether to enable just read-only transactions. |
| ob_trx_lock_timeout | The lock wait timeout period in microseconds for transactions. |
Character set and string variables
Variable |
Description |
|---|---|
| character_set_client | The character set in which the statements are sent by the client. |
| character_set_connection | The character set to which the received statements are converted. |
| character_set_database | The character set of the default database. |
| character_set_results | The character set to which the server converts the result sets or error messages before sending them to the client. |
| character_set_server | The character set of the server. |
| character_set_system | The character set of the system. |
| collation_connection | The character set and collation for the connection. |
| collation_database | The default character set and collation of the database. |
| collation_server | The default character set and collation for the server. |
| character_set_filesystem | The character set of the file system. |
| sql_warnings | Specifies whether to generate an information string for single-row INSERT statements when warnings occur. |
| init_connect | The character string that the server executes for each connected client. The string consists of one or more SQL statements which are separated by semicolons (;). |
| nls_sort | The string value sorting rule. Note At present, this variable does not take effect. |
| nls_comp | The string value comparison rule. Note At present, this variable does not take effect. |
| nls_characterset | Allows you to view the default character set of the data types such as CHAR, VARCHAR2, and CLOB in the database. Note At present, this variable does not take effect. |
| nls_nchar_characterset | The default character set of the database, which is used for data types such as NCHAR, NVARCHAR2, and NCLOB. Note At present, this variable does not take effect. |
Maximum value variables
Variable |
Description |
|---|---|
| max_allowed_packet | The maximum size in bytes of a network packet. |
| max_user_connections | The maximum number of connections that a single user can set up with the OBServer node. When the value is set to 0, the number of connections is not limited. |
| max_sp_recursion_depth | The maximum recursion level of a specified stored procedure. |
| parallel_servers_target | The queuing conditions for large queries on each server. |
| group_concat_max_len | The maximum length in bytes of the result of the GROUP_CONCAT() function. |
| sql_select_limit | The maximum number of rows to be returned for a SELECT query. |
| max_connections | The maximum number of connections for a tenant. |
Time zone and date variables
Variable |
Description |
|---|---|
| time_zone | The session time zone of the current tenant. The value can be an offset, such as '+08:00', or a geographical location, such as Asia/Shanghai. |
| timestamp | The timestamp in seconds. This variable affects the result of SELECT NOW(6). |
| system_time_zone | The system time zone of the server. |
| error_on_overlap_time | Specifies whether to report an error in the case of an ambiguous overlap timestamp. |
| nls_language | The default language of prompt messages, date, month, and the default values of nls_sort and nls_date_language. |
| nls_territory | The current territory, which is used in combination with nls_language. |
| nls_date_language | The default date language. |
| nls_calendar | The calendar system used by the database. |
Version information variables
Variable |
Description |
|---|---|
| version_comment | The version information of the OBServer node. |
| version | The version number of OBServer. |
| ob_last_schema_version | The latest schema version used in the session. |
| ob_proxy_global_variables_version | The latest version information of global variables returned by the OBServer node to ODP. |
Log variables
Variable |
Description |
|---|---|
| binlog_row_image | Specifies whether to record full column logs. |
| ob_enable_show_trace | Specifies whether to enable trace logs. |
| ob_trace_info | Trace information to be transparently transferred, which can be stored in REDO logs. |
| ob_log_level | The level of session logs. If this variable is not specified, the system log level is used. |
| tracefile_identifier | The custom identifier that becomes part of the log so that a row iteration trace log can be identified with ease. |
Directory variables
Variable |
Description |
|---|---|
| datadir | The local disk directory for data storage. |
| plugin_dir | The directory for storing plug-in DLL files. |
| secure_file_priv | The directory that can be accessed for file import or export. |
Debugging variables
Variable |
Description |
|---|---|
| debug_sync | The synchronization point during debugging, which is compatible with MySQL. |
| ob_global_debug_sync | The synchronization point during debugging. Unlike debug_sync, this variable takes effect on all sessions. |
Query variables
Variable |
Description |
|---|---|
| query_cache_size | The size of memory in bytes allocated for caching query results, which also indicates memory not in use. Note At present, this variable does not take effect. |
| query_cache_type | The query cache type. Note At present, this variable does not take effect. |
| ob_query_timeout | The query timeout period in microseconds. |
| is_result_accurate | Indicates whether the query result is correct when a query carries a topk hint. |
| net_buffer_length | The SQL query size acceptable to the OBServer node. |
Timeout period variables
Variable |
Description |
|---|---|
| connect_timeout | The connection timeout period in seconds. |
| default_password_lifetime | The permitted password lifetime. |
| net_read_timeout | The number of seconds to wait for more data of the connection before aborting the read. |
| net_write_timeout | The number of seconds to wait for a block to be written to a connection before aborting the write. |
| ob_trx_timeout | The transaction timeout period in microseconds. |
| ob_pl_block_timeout | The timeout period in microseconds for PL execution. |
| interactive_timeout | The number of seconds that the server waits for an interactive idle connection to become active before closing it. |
| wait_timeout | The number of seconds that the server waits for a non-interactive idle connection to become active before closing it. |
Plan cache variables
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 occupied by the plan cache. |
| ob_plan_cache_evict_high_percentage | Specifies the percentage of the absolute value of the maximum memory usage to trigger plan cache eviction. |
| ob_plan_cache_evict_low_percentage | Specifies the percentage of the absolute value of the maximum memory usage to stop plan cache eviction. |
Recycle bin variables
Variable |
Description |
|---|---|
| recyclebin | Specifies whether to enable the recycle bin. |
Memory variables
Variable |
Description |
|---|---|
| ob_sql_work_area_percentage | The maximum percentage of tenant memory for SQL execution. |
| ob_sql_audit_percentage | The maximum percentage of the SQL audit memory to the total memory of the current tenant. |
| ob_reserved_meta_memory_percentage | The percentage of reserved memory of a tenant. The reserved memory mainly stores metadata-related structure information. |
| ob_temp_tablespace_size_percentage | The temporary tablespace size threshold in percentage for a tenant disk. |
FOREIGN KEY constraint variables
Variable |
Description |
|---|---|
| foreign_key_checks | Specifies whether to enable FOREIGN KEY constraint check for DML or DDL statements. |
Allowlist variables
Variable |
Description |
|---|---|
| ob_tcp_invited_nodes | The IP address allowlist of a tenant. The value can contain percent signs (%), underscores (_), and IP addresses. Multiple IP addresses are separated with commas (,). IP list matching, mask matching, and fuzzy matching are supported. |
Throttling variables
Variable |
Description |
|---|---|
| sql_throttle_current_priority | The throttling priority. Only requests of sessions whose sql_throttle_current_priority value is less than the sql_throttle_priority value are throttled. |
| sql_throttle_priority | The throttling priority. |
| sql_throttle_rt | The response time (RT) in microseconds. |
| sql_throttle_network | The request queuing time. If the request queuing time is greater than or equal to the specified value, the query is not executed. |
Audit variables
Variable |
Description |
|---|---|
| ob_enable_sql_audit | Specifies whether to enable SQL audit for the current tenant. |
Optimizer variables
Variable |
Description |
|---|---|
| optimizer_use_sql_plan_baselines | Specifies whether the optimizer uses SQL plan baselines. |
| optimizer_capture_sql_plan_baselines | Specifies whether new plans can be automatically captured to the plan baseline. |
| parallel_degree_policy | The strategy based on which the optimizer selects the degree of parallelism (DOP). |
| parallel_degree_limit | The maximum DOP that the optimizer can select when the Auto DOP strategy is used. |
| parallel_min_scan_time_threshold | The minimum evaluation period for the parallel scan of base tables. |
| optimizer_dynamic_sampling | The level of dynamic sampling. |
| optimizer_features_enable | The version of the OceanBase Database optimizer that you want to enable. |
Data format variables
Note
At present, variables described in the following table do not take effect.
Variable |
Description |
|---|---|
| nls_length_semantics | The length semantics of the CHAR and VARCHAR2 data types. |
| nls_nchar_conv_excp | Specifies whether to report an error when data is lost during the conversion between NCHAR/NVARCHAR2 and CHAR/VARCHAR2. |
| nls_numeric_characters | The decimal and thousands separators used in a string during the conversion between a number and a string. |
| nls_currency | The local currency symbol for the L number format element. |
| nls_iso_currency | The local currency symbol for the C number format element. |
| nls_dual_currency | The local currency symbol for the U number format element. |
| nls_date_format | The format of converting a date to a string and the format of implicitly converting a string to a date. |
| nls_timestamp_format | The format to use when you convert a timestamp or LTZ timestamp to a string, or when you implicitly convert a string to a timestamp or LTZ timestamp. |
| nls_timestamp_tz_format | The format to use when you convert a timestamp or TZ timestamp to a string, or when you implicitly convert a string to a timestamp or TZ timestamp. |
Feature enabling or disabling variables
Variable |
Description |
|---|---|
| ob_enable_transformation | Specifies whether to enable the transformation feature of the SQL optimizer. |
| ob_enable_index_direct_select | Specifies whether to enable direct queries of index tables. |
| ob_enable_aggregation_pushdown | Specifies whether to enable aggregation pushdown. |
| ob_enable_transmission_checksum | Specifies whether to perform checksum on data packets between OBProxy and OBServer nodes. The setting takes effect in real time. |
| ob_enable_truncate_flashback | Specifies whether to enable flashback for truncated tables. |
| ob_enable_jit | Specifies whether to enable the just-in-time (JIT) execution engine. |
Case sensitivity variables
Variable |
Description |
|---|---|
| lower_case_table_names | Specifies whether object names are case-sensitive for storage comparison. |
Read/Write mode variables
Variable |
Description |
|---|---|
| read_only | Specifies whether the tenant is in read-only mode. |
| ob_read_consistency | The read consistency level. |
| tx_read_only | Specifies whether the transaction is read-only. |
| ob_max_read_stale_time | The maximum latency threshold in microseconds for weak-consistency read queries. |
User password variables
Variable |
Description |
|---|---|
| validate_password_check_user_name | Specifies whether the user password can be the same as the username. |
| validate_password_length | The minimum length of the user password. |
| validate_password_mixed_case_count | The minimum number of uppercase and lowercase letters that a user password must contain. |
| validate_password_number_count | The minimum number of digits that a user password must contain. |
| validate_password_policy | The password check strategy. |
| validate_password_special_char_count | The minimum number of special characters that a user password must contain. |
Runtime filter variables
Variable |
Description |
|---|---|
| runtime_filter_type | The runtime filter type at the tenant level. |
| runtime_filter_wait_time_ms | The maximum waiting duration for runtime filters. |
| runtime_filter_max_in_num | The number of distinct values (NDV) in the filter condition specified by the runtime filter of the IN type. |
| runtime_bloom_filter_max_size | The maximum memory usage in bytes allowed for the runtime filter of the BLOOM_FILTER type. |
Other variables
Variable |
Description |
|---|---|
| block_encryption_mode | The encryption algorithm used in the AES_ENCRYPT() and AES_DECRYPT() functions. |
| cursor_sharing | Specifies whether to parameterize an SQL statement when processing it. |
| explicit_defaults_for_timestamp | Specifies whether to enable non-standard behaviors for processing default values and NULL values in TIMESTAMP columns. |
| innodb_strict_mode | The check mode for SQL statements with specific syntax. |
| max_connections | The maximum allowable FD value. |
| ob_bnl_join_cache_size | The volume of the cached data that triggers a Batch Nest Loop Join. |
| ob_capability_flag | The capabilities that the OBServer node can provide. It is used to inform ODP of the features supported and features not supported by the OBServer node during capability negotiation between the OBServer node and ODP. |
| ob_compatibility_mode | The compatibility mode of a tenant. |
| ob_route_policy | The routing strategy for internal retries between ODP or the Java client and the OBServer node. |
| performance_schema | Declares to the client whether performance queries are supported. |
| resource_manager_plan | Activates a resource management plan. The CPU quota for limiting the user activities in a resource group varies based on the resource management plan. |
| sql_mode | The SQL mode. Actions such as Insert vary greatly with the SQL mode. |
| sql_safe_updates | Specifies whether to enable the safe update mode of MySQL. The mode is disabled by default. |
| license | The license type. |
| ob_proxy_partition_hit | Specifies whether to enable ODP to send the data requested by SQL statements to the OBServer node where the primary partition of the data is located. |
| sql_quote_show_create | Specifies whether to quote identifiers for the SHOW CREATE TABLE and SHOW CREATE DATABASE statements. |
| ob_org_cluster_id | The CLUSTER_ID of an OceanBase cluster. |
| server_uuid | The UUID of the OBServer node. |
| default_storage_engine | The default storage engine of an OBServer node. |
| 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. |