Set variables

You can set system variables to ensure that the behaviors of OceanBase Database meet your business requirements.

Variable categories

The system variables of OceanBase Database can be categorized into global variables and session-level variables.

• A global variable is used to implement a global modification. Different users of the same database tenant share the settings of global variables. Modification to global variables remains effective after you exit the session. In addition, modification to a global variable does not take effect on the currently open session and takes effect only after a new session is established.

• A session-level variable is used to implement a session modification. When a client is connected to the database, the database copies global variables to automatically generate session-level variables. Modifications made to session-level variables apply to the current session only.

Variables whose names start with an underscore (_) are hidden variables. For example, _primary_zone_entity_count is a hidden variable. Hidden parameters are used by developers only for troubleshooting or emergency O&M.

Views related to system variables

Views related to system variables in a MySQL tenant include:

• INFORMATION_SCHEMA.GLOBAL_VARIABLES: records global variables in the current tenant.

• INFORMATION_SCHEMA.SESSION_VARIABLES: records session-level variables in the current tenant.

Views related to system variables in an Oracle tenant include:

• SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE: records global variables in the current tenant.

• SYS.TENANT_VIRTUAL_SESSION_VARIABLE: records session-level variables in the current tenant.

For more information about the views, see System views in System reference.

Set a variable

The SQL syntax for setting global and session-level 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 is an example:

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 set to ON/OFF or True/False in the SHOW VARIABLE command. You can set these variables in the following way. Here is an example:

SET foreign_key_checks = ON;
SET foreign_key_checks = 1;
SET GLOBAL foreign_key_checks = ON;
SET GLOBAL foreign_key_checks = 1;

You can also set global or read-only variables when you create a tenant by using the following syntax:

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
    | REPLICA_NUM [=] num
    | ZONE_LIST [=] (zone [, zone...])
    | PRIMARY_ZONE [=] zone  
    | DEFAULT TABLEGROUP [=] {NULL | tablegroup}
    | RESOURCE_POOL_LIST [=](poolname [, poolname...])
    | LOGONLY_REPLICA_NUM [=] num
    | 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 read-only variable ob_compatibility_mode to mysql or oracle and the value of global variable ob_tcp_invited_nodes to %. Here is an example:

obclient> CREATE TENANT IF NOT EXISTS test_tenant
charset='utf8mb4', replica_num=3, zone_list=('zone1','zone2','zone3'),
primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1')
SET ob_compatibility_mode='oracle', ob_tcp_invited_nodes='%';

Query a variable

• You can query variables by using the SHOW VARIABLES statement.

  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 is an example:

  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 following table describes the column attributes in the execution results.

  Column name	Description
  Variable_name	The name of the variable.
  Value	The value of the variable.

  Note

  If you log on to the sys tenant and then execute the ALTER SYSTEM CHANGE TENANT statement to switch to a user tenant, the session-level variables obtained through a query are still the session-level variables of the sys tenant, but the global variables returned for a query are global variables of the user tenant. For more information about the ALTER SYSTEM CHANGE TENANT statement, see SQL Syntax.

• You can execute the SELECT statement 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 Oracle mode, you can execute the SELECT statement to query session-level and global variables from the related views of the current tenant. Here is an example:

  obclient> SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
  obclient> SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
  

  In MySQL mode, you can execute the SELECT statement to query session-level and global variables from the related views of the current tenant. Here is an example:

  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';
  

More information

For more information about the variables, see System variables in System reference.