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.

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

• 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 as the root 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 the session-level system variables 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. Modify the value of the session-level system variable using the SET statement.

   obclient [oceanbase]> SET ob_query_timeout = 20000000;
   

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

1. Log in to a user tenant of the cluster as 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 variables 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. Modify the value of the global-level system variable using the SET statement.

   obclient [oceanbase]> SET GLOBAL ob_query_timeout = 20000000;
   

   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. Query the modification result.

   obclient [(none)]> SHOW GLOBAL 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
   

   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 |
   +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+
   | 2024-05-08 14:20:28.885780 | 2024-05-09 15:11:11.654128 | ob_query_timeout | 20000000 |           |           | GLOBAL | SESSION | Query timeout in microsecond(us) | 10000000      | NO        |
   +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+
   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, visit the following links:

• Overview
• View system variables of a tenant