Modify system variables of a tenant

You can set system variables to ensure that the behaviors of OceanBase Database meet the service requirements. This topic describes how to modify system variables of a tenant.

You can use the SET statement to modify system variables of a tenant.

• A session-level variable takes effect only on the current session.

• A global variable does not take effect on the current session and takes effect only on sessions established upon re-login.

  Notice

  To set global variables for a MySQL tenant, you must have the SUPER or ALTER SYSTEM privilege. To set global variables for an Oracle tenant, you must have the ALTER SYSTEM privilege. Make sure that you have the required privileges before you set global variables. For more information about user privileges, see Overview.

The system variable ob_query_timeout is used as an example in the following sections. This system variable specifies the timeout period for SQL queries, in microseconds. When you run a big data query, you can increase the value of the system variable ob_query_timeout to avoid an execution failure caused by query timeout.

Modify a session-level system variable

1. Log in to a user tenant of a cluster as an administrator or regular user.

   For example, to log in to the mq_t1 tenant, run the following command:

   obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -A
   

2. Query a session-level system variable of the tenant.

   obclient [(none)]> SHOW VARIABLES LIKE 'ob_query_timeout';
   

   The query result is as follows:

   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 10000000 |
   +------------------+----------+
   1 row in set
   

3. Execute the SET statement to modify the value of a session-level system variable.

   MySQL mode

   Oracle mode

   In MySQL mode, you can modify the value of a session-level system variable in the following ways:

   obclient [oceanbase]> SET ob_query_timeout = 20000000;
   

   or

   obclient [oceanbase]> SET ob_query_timeout := 20000000;
   

   In Oracle mode, you can modify the value of a session-level system variable in the following ways:

   obclient [SYS]> SET ob_query_timeout = 20000000;
   

   or

   obclient [SYS]> ALTER SESSION SET ob_query_timeout = 20000000;
   

4. Query the modification result.

   obclient [(none)]> SHOW VARIABLES WHERE variable_name LIKE 'ob_query_timeout';
   

   The query result is as follows:

   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 20000000 |
   +------------------+----------+
   1 row in set
   

Modify a global system variable

1. Log in to a user tenant of the cluster as an administrator.

   Note

   In MySQL mode, the default administrator is the root user. In Oracle mode, the default administrator is the SYS user.

   For example, to log in to the mq_t1 tenant, run the following command:

   obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -A
   

2. Query a global variable of the tenant.

   obclient [(none)]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE  'ob_query_timeout';
   

   The query result is as follows:

   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 10000000 |
   +------------------+----------+
   1 row in set
   

3. Execute the SET statement to modify the value of a global variable.

   MySQL mode

   Oracle mode

   In MySQL mode, you can modify the value of a global system variable in the following ways:

   obclient [oceanbase]> SET GLOBAL ob_query_timeout = 20000000;
   

   or

   obclient [oceanbase]> SET GLOBAL ob_query_timeout := 20000000;
   

   In Oracle mode, you can modify the value of a global system variable in the following ways:

   obclient [SYS]> SET GLOBAL ob_query_timeout = 20000000;
   

   or

   obclient [SYS]> ALTER SYSTEM SET ob_query_timeout = 20000000;
   

4. Query the modification result.

   obclient [(none)]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';
   +------------------+----------+
   | Variable_name    | Value    |
   +------------------+----------+
   | ob_query_timeout | 20000000 |
   +------------------+----------+
   1 row in set
   

   After the modification, you can execute the following statement to query the DBA_OB_SYS_VARIABLES view for the modification status and default value of the ob_query_timeout variable:

   obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';
   

   The query result is as follows:

   +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+
   | CREATE_TIME                | MODIFY_TIME                | NAME             | VALUE    | MIN_VALUE | MAX_VALUE | SCOPE            | INFO                             | DEFAULT_VALUE | ISDEFAULT |
   +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+
   | 2023-12-08 14:00:20.583506 | 2023-12-12 11:51:46.237099 | ob_query_timeout | 20000000 |           |           | GLOBAL | SESSION | Query timeout in microsecond(us) | 10000000      | NO        |
   +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+
   1 row in set
   

Modify a Boolean variable

For a variable whose value is ON or OFF in the output of the SHOW VARIABLES statement, you can set its value in the following ways:

SET foreign_key_checks = ON;
SET foreign_key_checks = TRUE;
SET foreign_key_checks = 1;

SET GLOBAL foreign_key_checks = OFF;
SET GLOBAL foreign_key_checks = FALSE;
SET GLOBAL foreign_key_checks = 0;

References

For more information about system variables, see the following topics:

• Overview
• View system variables of a tenant