Modify system variables of a tenant

System variables in OceanBase Database can be configured to meet business requirements. This topic describes how to modify the system variables of a tenant.

The system variables of a tenant can be modified using the SET statement.

• Setting session-level system variables only affects the current session and does not affect other sessions.

• Setting global-level system variables does not affect the current session. You need to log in again and establish a new session for the changes to take effect.

  Notice

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

The following sections use ob_query_timeout to illustrate how to modify a system variable. ob_query_timeout specifies the timeout period for SQL queries, in microseconds. By increasing the value of ob_query_timeout, you can avoid SQL query failures due to query timeout during large-scale data queries.

Modify a session-level system variable

1. Log in to a user tenant of the cluster using the root user.

   For example, for the mq_t1 tenant:

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

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

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

3. Modify the value of the session-level system variable using the SET statement.

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

   Note

   In Oracle mode, you can also use the statement ALTER SESSION SET ob_query_timeout = 20000000; to modify the value of the session-level system variable .

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-level system variable

1. Log in to a user tenant of the cluster using the root user.

   For example, for the mq_t1 tenant:

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

2. Query the global-level system variable 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. Modify the value of the global-level system variable using the SET statement.

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

   Note

   In Oracle mode, you can also use the statement ALTER SYSTEM SET ob_query_timeout = 20000000; to modify the value of the global-level system variable .

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

For variables displayed as ON/OFF in the SHOW VARIABLES command, you can modify the variable value using any of the following methods:

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