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 the OBServer node by using 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 |
The default character set for 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 for data types such as NCHAR, NVARCHAR2, and NCLOB in the database.
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 simultaneous connections that a single user can set up with OBServer nodes. When the value is set to 0, the number of connections is not limited. |
| ob_max_parallel_degree |
The maximum amount of parallelism for a request.
Note
This variable is obsolete in the current version. |
| max_sp_recursion_depth |
The maximum recursion level of a specified stored procedure. |
| parallel_max_servers |
The maximum number of threads in the parallel execution (PX) thread pool on each server.
Note
This variable is obsolete in the current version. |
| 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. |
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. |
| ob_timestamp_service |
The timestamp service. |
| nls_language |
The default language of prompt messages, date, month, and the default values of NLS_SORT and NLS_DATE_LANGUAGE.
Note
At present, this variable does not take effect. |
| nls_territory |
The current territory, which is used in combination with nls_language.
Note
At present, this variable does not take effect. |
| nls_date_language |
The default date language.
Note
At present, this variable does not take effect. |
| nls_calendar |
The calendar system used by the database.
Note
At present, this variable does not take effect. |
Log variables
| Variable |
Description |
| binlog_row_image |
Specifies whether to record full column logs. |
| ob_enable_trace_log |
Specifies whether to enable trace logs. |
| ob_trace_info |
The 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. |
| query_cache_type |
The query cache type. |
| ob_query_timeout |
The query timeout period in microseconds. |
| ob_stmt_parallel_degree |
The degree of parallelism of queries, or the number of tasks that can run in parallel. |
| is_result_accurate |
Indicates whether the query result is correct when a query carries a topk hint. |
| net_buffer_length |
Specifies the SQL query size acceptable to the OBServer node. |
Replica variables
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
Recycle bin variables
| Variable |
Description |
| recyclebin |
Specifies whether to enable the recycle bin. |
Memory variables
Foreign key constraint variables
| Variable |
Description |
| foreign_key_checks |
Specifies whether to enable foreign key constraint check for DML 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
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
Case sensitivity variables
| Variable |
Description |
| lower_case_table_names |
Specifies whether object names are stored and compared in a case-sensitive manner. |
Read/write mode variables
User password variables
Other variables
| Variable |
Description |
| block_encryption_mode |
The encryption algorithm used in the AES_ENCRYPT() and AES_DECRYPT() functions. |
| explicit_defaults_for_timestamp |
Specifies whether to enable non-standard behaviors for processing default values and NULL values in TIMESTAMP columns. |
| ob_bnl_join_cache_size |
The volume of the cached data that triggers a Batch Nest Loop Join. |
| ob_capability_flag |
The capabilities that an OBServer node can provide. It is used to inform ODP of the features supported and unsupported by the OBServer node during capability negotiation between the OBServer node and ODP. |
| ob_compatibility_mode |
The compatibility mode of a tenant. |
| ob_create_table_strict_mode |
Specifies whether to enable Strict Mode for table creation. This variable takes effect immediately after modification. |
| ob_route_policy |
The routing strategy for retries between the OBServer node and ODP or Java clients. |
| undo_retention |
The time range in seconds of data versions to be retained by the system. This variable is used to control the collection of data of multiple versions in minor compactions. |
| 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. |
