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-logon.

  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.

In the following examples, the ob_query_timeout 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 ob_query_timeout system variable to avoid an execution failure caused by query timeout.

Modify a session-level system variable

1. Log on to a user tenant of the cluster as the root user.

   To log on to the mq_t1 tenant, run the following command:

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

2. Query the session-level system variables of the tenant.

   obclient [(none)]> SHOW VARIABLES LIKE 'ob_query_timeout';
   +------------------+----------+
   | 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.

   obclient [oceanbase]> SET ob_query_timeout = 20000000;
   Query OK, 0 rows affected
   

   Note

   You can also execute the ALTER SESSION SET ob_query_timeout = 20000000; statement to modify the session-level variable in Oracle mode.

4. View the modification result.

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

Modify a global system variable

1. Log on to a user tenant of the cluster as the root user.

   To log on to the mq_t1 tenant, run the following command:

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

2. Query the global variables of the tenant.

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

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

   obclient [oceanbase]> SET GLOBAL ob_query_timeout = 20000000;
   Query OK, 0 rows affected
   

   Note

   You can also execute the ALTER SYSTEM SET ob_query_timeout = 20000000; statement to modify the global variable in Oracle mode.

4. View 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
   

Modify a Boolean variable

You can modify the value of a Boolean variable queried by using the SHOW VARIABLES statement 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, visit the following links:

• Overview
• View system variables of a tenant