Tenant variables can be classified into global variables and session-level variables. Session-level variables inherit from global variables. You can set session-level variables after a session is established. Session-level variables override global variables in a session.
Query variables
The following is the syntax for querying global or session-level variables:
SHOW [GLOBAL] VARIABLES [SHOW_VARIABLES_OPTS]
SHOW_VARIABLES_OPTS:
[LIKE 'pattern' | WHERE expr]
Example:
obclient> SHOW VARIABLES LIKE 'ob_query_timeout';
obclient> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';
The SYS tenant can query the internal table __all_virtual_sys_variable for global variables of all common tenants.
If you connect to the SYS tenant and then switch to a common tenant, the session-level variables obtained through a query are still the session-level variables of the SYS tenant, but the global variables obtained through a query are global variables of the common tenant.
Set variables
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.
The following is the syntax for setting a global or session-level variable:
SET [GLOBAL] VARIABLE_NAME = 'VALUE'
Example:
obclient> SET ob_query_timeout = 20000000;
obclient> SET GLOBAL ob_query_timeout = 20000000;
For a variable whose variable type is INT and that returns ON/OFF or True/False for the SHOW VARIABLE command, you can set its value by using any of the following methods:
SET @@foreign_key_checks = ON
SET @@foreign_key_checks = 1
SET @@foreign_key
The preceding three methods are equivalent in setting a session-level variable.
For more information about variables, see System variable reference (MySQL mode) and System variable reference (Oracle mode).