Scopes of System Variables: Global and Session

MySQL Tutorials - Herong's Tutorial Examples

∟System, User-Defined and Stored Procedure Variables

∟Scopes of System Variables: Global and Session

This section describes global and session scopes of system variables. GLOBAL and SESSION keywords can be used to separate system variable scopes.

What Is Scope of a System Variable? Scopes of system variables are levels of controls that system variables can be applied to.

MySQL server supports two scopes for system variables:

Global - Applies globally to all user sessions.
Session (or Local) - Overrides the Global scope and applies locally to the current user session only.

Here are some basic guidelines on how to view and manage system variables in Global and Session scopes.

1. Some system variables have only 1 scope: Global Scope. These variables are called Global System Variables and applies to all user sessions. Examples of Global System Variables are:

version - Version identifier of the MySQL Server.
basedir - The path to the MySQL server installation base directory.
datadir - The path to the MySQL server data directory.
port - The port number on which the server listens for TCP/IP connections.
connect_timeout - The number of seconds that the mysqld server waits for a connect packet before responding with "Bad handshake".

2. Other system variables are Non-Global System Variables and have 2 scopes: Global Scope and Session Scope. Each Non-Global System Variable has 2 values: one for the Global Scope and one for the Session Scope. Examples of Global System Variables are:

autocommit - Flag to force committing changes immediately without using and "COMMIT" statements.
character_set_client - The character set for statements that arrive from the client.
character_set_database - The default character set used when creating new databases.
foreign_key_checks - Flag to force checking foreign key constraints.
wait_timeout - The number of seconds the server waits for activity on a noninteractive connection before closing it.

3. When a MySQL server is started, the global value (value in the Global scope) of a system variable is initialized by the server based on server command options and the system environment.

4. When a user session is started, the session value (value in the Global scope) of a system variable is determined as below:

The session value is the global value for a Global System Variable. In other words, there is only 1 value for a Global System Variable. All user sessions shares the same value.
The session value is copied from the global value for a Non-Global System Variable. In other words, there are 2 values for a Non-Global System Variable. User can change the session value independently.

5. "SHOW [...] VARIABLES" statement can be used to view both global value and session value with different syntaxes:

"SHOW VARIABLES ..." - Shows session values of matched System Variables.
"SHOW GLOBAL VARIABLES ..." - Shows global values of matched System Variables.
"SHOW SESSION VARIABLES ..." - Shows session values of matched System Variables.
"SHOW LOCAL VARIABLES ..." - Shows session values of matched System Variables.

6. "@@var" syntax represents only the session value of a system variable. For example:

mysql> set @@wait_timeout = 30000;

mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|          30000 |
+----------------+

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

7. "SET [...] var" statement can be used to set both global value and session value with different syntaxes:

"SET var = ..." - Sets the session value of the given System Variable.
"SET GLOBAL var = ..." - Sets the global value of the given System Variable, if you have the required privilege.
"SET SESSION var = ..." - Sets the session value of the given System Variable.
"SET LOCAL var = ..." - Sets the session value of the given System Variable.

Note that Global System Variables do not have any session values. So you can only set their global values. For example:

mysql> set @@slow_query_log = OFF;
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and
  should be set with SET GLOBAL

mysql> set slow_query_log = OFF;
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and
  should be set with SET GLOBAL

mysql> set global slow_query_log = OFF;
Query OK, 0 rows affected (0.02 sec)