autocommit

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”.

big_tables

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.

error_count

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.

foreign_key_checks

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.

identity

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.

insert_id

The value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.

last_insert_id

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.

rand_seed1

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.

rand_seed2

See the description for rand_seed1.

sql_auto_is_null

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.

sql_big_selects

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

If set to 1, 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.

sql_log_bin

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.

sql_log_off

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.

sql_log_update

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.

sql_notes

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.

sql_quote_show_create

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.

sql_safe_updates

If set to 1, MySQL aborts UPDATE or 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.

sql_warnings

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.

timestamp = {timestamp_value | DEFAULT}

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.

unique_checks

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.

warning_count

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.