MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 5.1.4 Session System Variables

5.1.4. Session System Variables 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


    SHOW VARIABLES

, 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.5, “Using System Variables” . For example:

mysql> 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.2. Session System Variable Summary
          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
          or ROLLBACK
          to cancel it. By default, client connections begin with
          autocommit set to 1. If you
          change 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
          START
          TRANSACTION or
          BEGIN
          statement. See Section 12.3.1, “START TRANSACTION,
      COMMIT, and
      ROLLBACK Syntax”.
          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 tbl_name is
          full 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). As of MySQL
          4.0, you should normally never need to set this variable,
          because MySQL automatically converts in-memory tables to
          disk-based tables as necessary.
            This variable was formerly named
            sql_big_tables.
          The number of errors that resulted from the last statement
          that generated messages. This variable is read only. See
          Section 12.4.5.11, “SHOW ERRORS Syntax”.
          This variable was added in MySQL 4.1.0.
          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
          for reloading InnoDB tables in an order
          different from that required by their parent/child
          relationships. This variable was added in MySQL 3.23.52. See
          Section 13.2.5.4, “FOREIGN KEY Constraints”.
          Setting foreign_key_checks to
          0 also affects data definition statements:
          DROP DATABASE drops a database
          even if it contains tables that have foreign keys that are
          referred to by tables outside the database, and
          DROP TABLE drops tables that
          have foreign keys that are referred to by other tables.
            Setting foreign_key_checks
            to 1 does not trigger a scan of the existing table data.
            Therefore, rows added to the table while
            foreign_key_checks = 0 will
            not be verified for consistency.
          This variable is a synonym for the
          last_insert_id variable. It
          exists for compatibility with other database systems. As of
          MySQL 3.23.25, you can read its value with SELECT
          @@identity. As of MySQL 4.0.3, you can also set its
          value with SET identity.
          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
          function.
          The rand_seed1 and
          rand_seed2 variables exist as
          session variables only, and can be set but not read. They are
          not shown in the output of SHOW
          VARIABLES. These two variables were added in MySQL
          4.0.5.
          The purpose of these variables is to support replication of
          the RAND() function. For
          statements that invoke RAND(),
          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_seed1 and
          rand_seed2 so that
          RAND() on the slave generates
          the same value as on the master.
          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 tbl_name WHERE auto_col IS NULL
          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.10.3, “Information Functions”. If no
          AUTO_INCREMENT value was successfully
          inserted, the SELECT statement
          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 17.1.7.1.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 WHERE statement
          has been issued. The default value for a new connection is 1,
          which allows all SELECT
          statements.
          If you set the max_join_size
          system variable to a value other than
          DEFAULT,
          sql_big_selects is set to 0.
          sql_buffer_result forces
          results from SELECT statements
          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. This variable was added in MySQL 3.23.13.
          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. This variable was added in
          MySQL 3.23.16.
          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.
          If set to 0, no logging is done to the
          update log for the client. The client must have the
          SUPER privilege to set this
          option. The default value is 1. This variable was added in
          MySQL 3.22.5.
          If set to 1 (the default), warnings of Note
          level are recorded. If set to 0, Note
          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.
          sql_notes was added in MySQL
          4.1.11.
          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 12.4.5.7, “SHOW CREATE TABLE Syntax”,
          and Section 12.4.5.6, “SHOW CREATE DATABASE Syntax”. This variable was
          added in MySQL 3.23.26.
          DELETE statements that do not
          use a key in the WHERE clause or a
          LIMIT clause. This makes it possible to
          catch UPDATE or
          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
          was added in MySQL 3.22.32.
          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. This variable
          was added in MySQL 3.22.11.
          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.
          If set to 1 (the default), uniqueness checks for secondary
          indexes in 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
          InnoDB. This variable was added in MySQL
          3.23.52.
          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 12.4.5.26, “SHOW WARNINGS Syntax”.
          This variable was added in MySQL 4.1.0.