You can set system variables to customize the behavior of OceanBase AI Database to meet your business requirements.
Variable types
System variables in OceanBase AI Database are classified into global variables and session variables:
Global variables: Modifications made at the global level are shared among different users within the same tenant. Changes to global variables do not become invalid when a session exits. However, after a global variable is modified, the change does not take effect for sessions that are already open. You must establish a new session for the change to take effect.
Session variables: Modifications made at the session level affect only the current session. When a client connects to the database, the database automatically generates session variables based on the global variables. Changes to session variables only apply to the current session.
Variables that start with an underscore (_), such as _primary_zone_entity_count, are hidden variables. They are intended for use by developers during troubleshooting or emergency maintenance.
System variable-related views
The following views describe system variables in a MySQL tenant:
INFORMATION_SCHEMA.GLOBAL_VARIABLES: This view contains information about global variables in the current tenant.INFORMATION_SCHEMA.SESSION_VARIABLES: This view contains information about session variables in the current tenant.DBA_OB_SYS_VARIABLES: This view contains modification records of global variables and default values of variables in the current tenant.
For more information about these views, see System views in the Reference Guide.
Set variables
Note
- A session-level variable takes effect only on the current session and not on other sessions. A global variable does not take effect on the current session and takes effect only on sessions established upon re-login.
- To set a global variable, you must have the
SUPERprivilege or theALTER SYSTEMprivilege.
The SQL syntax for setting session or global variables is as follows:
SET VARIABLE_NAME = 'VALUE'
SET GLOBAL VARIABLE_NAME = 'VALUE'
You can execute SQL statements to set session-level and global variables. Here are some examples:
obclient> SET ob_query_timeout = 20000000;
obclient> SET GLOBAL ob_query_timeout = 20000000;
The values of some global or session-level variables are of the INT type and are set to ON/OFF or True/False in the SHOW VARIABLE statement. You can set these variables in either of the following ways. Here are some examples:
SET foreign_key_checks = ON;
SET foreign_key_checks = 1;
SET GLOBAL foreign_key_checks = ON;
SET GLOBAL foreign_key_checks = 1;
For global variables and read-only variables, you can also set them when creating a tenant. The syntax is as follows:
CREATE TENANT [IF NOT EXISTS] tenant_name
[tenant_characteristic_list] [opt_set_sys_var];
tenant_characteristic_list:
tenant_characteristic [, tenant_characteristic...]
tenant_characteristic:
COMMENT 'string'
| {CHARACTER SET | CHARSET} [=] charsetname
| COLLATE [=] collationname
| PRIMARY_ZONE [=] zone
| RESOURCE_POOL_LIST [=](poolname [, poolname...])
| LOCALITY [=] 'locality description'
opt_set_sys_var:
{SET | SET VARIABLES | VARIABLES} system_var_name = expr [,system_var_name = expr] ...
When you create a tenant, set the value of the read-only variable ob_compatibility_mode to mysql and the value of the global variable ob_tcp_invited_nodes to %. Here is an example:
obclient> CREATE TENANT IF NOT EXISTS test_tenant
charset='utf8mb4', primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1')
SET ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%';
View variables
Use the
SHOW VARIABLESstatement to view variables.The SQL syntax for querying global and session-level variables is as follows:
SHOW VARIABLES [LIKE 'pattern' | WHERE expr] SHOW GLOBAL VARIABLES [LIKE 'pattern' | WHERE expr]You can execute SQL statements to query session-level and global variables. Here are some examples:
obclient> SHOW VARIABLES LIKE 'ob_query_timeout'; obclient> SHOW VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout'; obclient> SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout'; obclient> SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout'; obclient> SHOW GLOBAL VARIABLES WHERE VARIABLE_NAME LIKE 'ob_query_timeout'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ 1 row in set (0.00 sec)The column attributes in the returned result are shown in the following table:
Column nameDescriptionVariable_name The name of the variable. Value The value of the variable. You can execute the
SELECTstatement to query session-level and global variables from the related views of the current tenant. The SQL syntax is as follows:SELECT * FROM view_name WHERE VARIABLE_NAME = '[var name]'In MySQL-compatible mode, you can execute the
SELECTstatement to query session-level and global variables from the related views of the current tenant. Here are some examples:obclient> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout'; obclient> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'ob_query_timeout';You can use the following statement to query the modification history and default values of a specific global variable.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';
More information
For more information about variables, see Overview of parameters and system variables.