Pre-General Availability Draft: 2017-05-29
expr| [GLOBAL | SESSION | PERSIST]
expr| [@@global. | @@session. | @@persist. | @@]
syntax for variable assignment enables you to assign values to
different types of variables that affect the operation of the
server or clients:
System variables. See Section 6.1.5, “Server System Variables”. System variables also can be set at server startup, as described in Section 6.1.6, “Using System Variables”. (To display system variable names and values, use the
SHOW VARIABLESstatement; see Section 22.214.171.124, “SHOW VARIABLES Syntax”.)
User-defined variables. See Section 10.4, “User-Defined Variables”.
Stored procedure and function parameters, and stored program local variables. See Section 14.6.4, “Variables in Stored Programs”.
statement that assigns variable values is not written to the
binary log, so in replication scenarios it affects only the host
on which you execute it. To affect all replication hosts,
execute the statement on each one.
A user variable is written as
@ and is
assigned an expression value as follows:
SET @name = 43; SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);
As demonstrated by those statements,
expr can range from simple (a literal
value) to more complex (the value returned by a scalar
applies to parameters and local variables in the context of the
stored object within which they are defined. The following
procedure uses the
counter local variable as
a loop counter:
CREATE PROCEDURE p() BEGIN DECLARE counter INT DEFAULT 0; WHILE counter < 10 DO -- ... do work ... SET counter = counter + 1; END WHILE; END;
Many system variables are dynamic and can be changed at runtime
by using the
statement. For a list, see
Section 126.96.36.199, “Dynamic System Variables”. To change a system
refer to it by name, optionally preceded by a modifier:
To indicate that a variable is a global variable, precede its name by the
GLOBALkeyword or the
SET GLOBAL max_connections = 1000; SET @@global.max_connections = 1000;
Another way to set a global variable is to precede its name by the
PERSISTkeyword or the
SET PERSIST max_connections = 1000; SET @@persist.max_connections = 1000;
SETsyntax enables you to make configuration changes at runtime that also persist across server restarts. Like
SET PERSISTchanges the runtime variable value, but also writes the variable setting to an option file named
mysqld-auto.cnfin the data directory (replacing any existing variable setting if there is one). At startup, the server processes this file after all other option files. The
SUPERprivilege is required to persist global variables.Note
Management of the
mysqld-auto.cnffile should be left to the server and not performed manually:
Removal of the file results in a loss of all persisted settings at the next server startup. (This is permissible if your intent is to reconfigure the server without these settings.) To remove all settings in the file without removing the file itself, use this statement:
Manual changes to the file may result in a parse error at server startup. In this case, the server reports an error and exits. If this issue occurs, start the server with the
persisted_globals_loadsystem variable disabled or with the
--no-defaultsoption. Alternatively, remove the
mysqld-auto.cnffile, but, as noted previously, removing this file results in a loss of all persisted settings.
A plugin variable can be persisted if the plugin is installed when
SET PERSISTis executed. Assignment of the persisted plugin variable takes effect for subsequent server restarts if the plugin is still installed. If the plugin is no longer installed, the plugin variable will not exist when the server reads the
mysqld-auto.cnffile. In this case, the server writes a warning to the error log and continues:
currently unknown variable '
var_name' was read from the persisted config file
To indicate that a variable is a session variable, precede its name by the
SESSIONkeyword or either the
SET SESSION sql_mode = 'TRADITIONAL'; SET @@session.sql_mode = 'TRADITIONAL'; SET @@sql_mode = 'TRADITIONAL';
Setting a session variable normally requires no special privilege, although there are exceptions that require the
SUPERprivilege (such as
sql_log_bin). A client can change its own session variables, but not those of any other client.
Session-only system variables cannot be persisted. They cannot be set at server startup, so there is no reason to list them in
@@local.are synonyms for
If no modifier is present,
SETchanges the session variable.
An error occurs under these circumstances:
SET GLOBAL sql_log_bin = ON;ERROR 1231 (42000): Variable 'sql_log_bin' can't be set to the value of 'ON'
@@persist.) when setting a variable that has only a global value:
SET max_connections = 1000;ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
@@SESSION.) when setting a variable that has only a global value:
SET SESSION max_connections = 1000;ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
The preceding modifiers apply only to system variables. An error occurs for attempts to apply them to user-defined variables, stored procedure or function parameters, or stored program local variables.
statement can contain multiple variable assignments, separated
by commas. This statement assigns values to a user-defined
variable and a system variable:
SET @x = 1, SESSION sql_mode = '';
If you set multiple system variables, the most recent
SESSION modifier in the statement is used for
following assignments that have no modifier specified.
Examples of multiple-variable assignment:
SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000; SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000; SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;
If any variable assignment in a
statement fails, the entire statement fails and no variables are
changed, nor is the
If you change a session system variable, the value remains in effect within your session until you change the variable to a different value or the session ends. The change has no effect on other sessions.
If you change a global system variable, the value is remembered
and used for new sessions until you change the variable to a
different value or the server exits. The change is visible to
any client that accesses the global variable. However, the
change affects the corresponding session variable only for
clients that connect after the change. The global variable
change does not affect the session variable for any current
client sessions (not even the session within which the
GLOBAL statement occurred).
To make a global system variable setting permanent so that it
applies across server restarts, modify it with
PERSIST to record it in the
mysqld-auto.cnf file. It is also possible
GLOBAL and manually modify a
my.cnf file, but that is more cumbersome,
and an error in a manually entered setting might not be
discovered until much later.
PERSIST is more convenient and avoids the possibility
of malformed settings.
The Performance Schema
persisted_variables table provides
an SQL interface to the
file, enabling its contents to be inspected at runtime using
SELECT statements. See
Section 188.8.131.52, “Performance Schema persisted_variables Table”.
The Performance Schema
variables_info table contains
information showing when and by which user each system variable
was most recently set. See
Section 184.108.40.206, “Performance Schema variables_info Table”.
To set a
GLOBAL value to the compiled-in
MySQL default value or a
SESSION variable to
the current corresponding
GLOBAL value, set
the variable to the value
example, the following two statements are identical in setting
the session value of
max_join_size to the current
SET @@session.max_join_size=DEFAULT; SET @@session.max_join_size=@@global.max_join_size;
Not all system variables can be set to
DEFAULT. In such cases, assigning
DEFAULT results in an error.
@@persist.), setting a
global variable to
DEFAULT or to its literal
default value assigns the variable its default value and adds a
setting for the variable to
mysqld-auto.cnf. To remove the variable
from the file, use
An error occurs for attempts to assign
DEFAULT to user-defined variables, stored
procedure or function parameters, or stored program local
To refer to the value of a system variable in expressions, use
one of the
@@persist., which is not permitted in
expressions). For example, you can retrieve values in a
SELECT statement like this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
For a reference to a system variable in an expression as
@@session.), MySQL returns the session value
if it exists and the global value otherwise. This differs from
SET @@, which always refers
to the session value.