Several system variables exist only as session variables. These
cannot be set at server startup but can be assigned values at
runtime using the
statement (except for those that are read only). Most of them are
not displayed by
but you can obtain their values using
SELECT. This section describes the
session system variables. For information about setting or
displaying their values, see
Section 5.1.6, “Using System Variables”. For example:
SELECT @@autocommit;+--------------+ | @@autocommit | +--------------+ | 1 | +--------------+
The lettercase of these variables does not matter.
The following table lists the system variables that have only session scope:
Table 5.3. Session System Variable Summary
|Name||Cmd-Line||Option file||System Var||Dynamic|
|- Variable: big_tables||Yes||Yes|
The autocommit mode. If set to 1, all changes to a table take
effect immediately. If set to 0, you must use
COMMIT to accept a transaction
to cancel it. By default, client connections begin with
autocommit set to 1. If you
autocommit mode from 0
to 1, MySQL performs an automatic
COMMIT of any open transaction.
Another way to begin a transaction is to use a
statement. See Section 12.3.1, “
If set to 1, all temporary tables are stored on disk rather
than in memory. This is a little slower, but the error
The table does not occur for
SELECT operations that require
a large temporary table. The default value for a new
connection is 0 (use in-memory temporary tables). Normally,
you should never need to set this variable, because in-memory
tables are automatically converted to disk-based tables as
This variable was formerly named
The number of errors that resulted from the last statement
that generated messages. This variable is read only. See
Section 18.104.22.168, “
SHOW ERRORS Syntax”.
If set to 1 (the default), foreign key constraints for
InnoDB tables are checked. If set to 0,
they are ignored. Disabling foreign key checking can be useful
InnoDB tables in an order
different from that required by their parent/child
Section 22.214.171.124, “
FOREIGN KEY Constraints”.
0 also affects data definition statements:
SCHEMA drops a schema even if it contains tables
that have foreign keys that are referred to by tables outside
the schema, and
drops tables that have foreign keys that are referred to by
This variable is a synonym for the
last_insert_id variable. It
exists for compatibility with other database systems. You can
read its value with
SELECT @@identity, and
set it using
The value to be returned from
LAST_INSERT_ID(). This is
stored in the binary log when you use
LAST_INSERT_ID() in a statement
that updates a table. Setting this variable does not update
the value returned by the
mysql_insert_id() C API
If set to 0 (the default), statement profiling is disabled. If
set to 1, statement profiling is enabled and the
SHOW PROFILES and
SHOW PROFILE statements provide
access to profiling information. See
Section 126.96.36.199, “
SHOW PROFILES Syntax”.
The number of statements for which to maintain profiling
enabled. The default value is 15. The maximum value is 100.
Setting the value to 0 effectively disables profiling. See
Section 188.8.131.52, “
SHOW PROFILES Syntax”.
The purpose of these variables is to support replication of
RAND() function. For
statements that invoke
the master passes two values to the slave, where they are used
to seed the random number generator. The slave uses these
values to set the session variables
rand_seed2 so that
RAND() on the slave generates
the same value as on the master.
See the description for
If this variable is set to 1 (the default), then after a
statement that successfully inserts an automatically generated
AUTO_INCREMENT value, you can find that
value by issuing a statement of the following form:
SELECT * FROM
If the statement returns a row, the value returned is the same
as if you invoked the
LAST_INSERT_ID() function. For
details, including the return value after a multiple-row
insert, see Section 11.11.3, “Information Functions”. If no
AUTO_INCREMENT value was successfully
returns no row.
The behavior of retrieving an
AUTO_INCREMENT value by using an
IS NULL comparison is used by
some ODBC programs, such as Access. See
Section 184.108.40.206.1, “Obtaining Auto-Increment Values”.
This behavior can be disabled by setting
sql_auto_is_null to 0.
If set to 0, MySQL aborts
SELECT statements that are
likely to take a very long time to execute (that is,
statements for which the optimizer estimates that the number
of examined rows exceeds the value of
max_join_size). This is
useful when an inadvisable
has been issued. The default value for a new connection is 1,
which allows all
If set to 1,
to be put into temporary tables. This helps MySQL free the
table locks early and can be beneficial in cases where it
takes a long time to send results to the client. The default
value is 0.
If set to 0, no logging is done to the binary log for the
client. The client must have the
SUPER privilege to set this
option. The default value is 1.
If set to 1, no logging is done to the general query log for
this client. The client must have the
SUPER privilege to set this
option. The default value is 0.
This variable is deprecated, and is mapped to
sql_log_bin. It is removed in
If set to 1 (the default), warnings of
level are recorded. If set to 0,
warnings are suppressed. mysqldump includes
output to set this variable to 0 so that reloading the dump
file does not produce warnings for events that do not affect
the integrity of the reload operation.
If set to 1 (the default), the server quotes identifiers for
SHOW CREATE TABLE and
SHOW CREATE DATABASE
statements. If set to 0, quoting is disabled. This option is
enabled by default so that replication works for identifiers
that require quoting. See Section 220.127.116.11, “
SHOW CREATE TABLE Syntax”,
and Section 18.104.22.168, “
SHOW CREATE DATABASE Syntax”.
If set to 1, MySQL aborts
DELETE statements that do not
use a key in the
WHERE clause or a
LIMIT clause. This makes it possible to
DELETE statements where keys
are not used properly and that would probably change or delete
a large number of rows. The default value is 0.
This variable controls whether single-row
INSERT statements produce an
information string if warnings occur. The default is 0. Set
the value to 1 to produce an information string.
Set the time for this client. This is used to get the original
timestamp if you use the binary log to restore rows.
timestamp_value should be a Unix
epoch timestamp, not a MySQL timestamp.
SET timestamp affects the value returned by
NOW() but not by
SYSDATE(). This means that
timestamp settings in the binary log have no effect on
server can be started with the
--sysdate-is-now option to
SYSDATE() to be an alias
NOW(), in which case
SET timestamp affects both functions.
If set to 1 (the default), uniqueness checks for secondary
InnoDB tables are performed. If
set to 0, storage engines are allowed to assume that duplicate
keys are not present in input data. If you know for certain
that your data does not contain uniqueness violations, you can
set this to 0 to speed up large table imports to
Note that setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still allowed to check for them and issue duplicate-key errors if it detects them.
The number of errors, warnings, and notes that resulted from
the last statement that generated messages. This variable is
read only. See Section 22.214.171.124, “
SHOW WARNINGS Syntax”.