Changing Variable Values with the Server Running

Until MySQL version 4.0.3, you always had to restart the server to change the variable values. Now, you can use the much more convenient SET statement to make a change without shutting down the server.

You can use the SET statement in two ways. The default is for the change you make to affect the SESSION only, meaning that when you connect next time (and for all other connections) the variable will still be at the setting specified in the configuration file. If you specify the GLOBAL keyword, all new connections will use the new value. When the server restarts, however, it will always use the values set in the configuration file, so you always need to make the changes there as well. To set a variable with the GLOBAL option, you need to have the SUPER permission.

The syntax is as follows:

SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION]–    sql_variable=expression...]

For example, the following:

mysql> SET SESSION max_sort_length=2048; 

is the same as this:

mysql> SET max_sort_length=2048; 

There is an alternate syntax too, used for compatibility with other database management systems (DBMSs), using the @@ syntax, as follows:

SET @@{global | local}.sql_variable=expression, [@@{global |–    local}.sql_variable=expression]

To repeat the previous example in this syntax, you use the following:

mysql> SET @@local.max_sort_length=2048; 

SESSION and LOCAL are synonyms.

If, after experimenting with the new variable, you decide to return to the old value, there's no need to trust your memory or to look it up in the configuration file. You can use the DEFAULT keyword to restore a GLOBAL value to the value in configuration file, or a SESSION value to the GLOBAL value. For example:

mysql> SET SESSION max_sort_length=DEFAULT; 

and

mysql> SET GLOBAL max_sort_length=DEFAULT; 

Table 13.4 shows variables that you set in a nonstandard way.

Table 13.4: Nonstandard Variables

Syntax	Description
AUTOCOMMIT= 0 | 1	When set (1), MySQL automatically COMMITs statements unless you wrap them in BEGIN and COMMIT statements. MySQL also automatically COMMITs all open transactions when you set AUTOCOMMIT.
BIG_TABLES = 0 | 1	When set (1), all temporary tables are stored on disk instead of in memory. This makes temporary tables slower, but it prevents the problem of running out of memory. The default is 0.
INSERT_ID = #	Sets the AUTO_INCREMENT value (so the next INSERT statement that uses an AUTO_INCREMENT field will use this value).
LAST_INSERT_ID = #	Sets the value returned from the next LAST_INSERT_ID() function.
LOW_PRIORITY_UPDATES = 0 | 1	When set (1), all update statements (INSERT, UPDATE, DELETE, LOCK TABLE WRITE) wait for there to be no pending reads (SELECT, LOCKTABLE READ) on the table they're accessing.
MAX_JOIN_SIZE = value | DEFAULT	By setting a maximum size in rows, you can prevent MySQL from running queries that may not make use indexes properly or may have the potential to slow the server down when run in bulk or at peak times. Setting this to anything but DEFAULT resets SQL_BIG_SELECTS. If SQL_BIG_SELECTS is set, then MAX_JOIN_SIZE is ignored. If the query is already cached, MySQL will ignore this limit and return theresults.
QUERY_CACHE_TYPE = OFF | ON | DEMAND	Sets the query cache setting for the thread.
QUERY_CACHE_TYPE = 0 | 1 | 2	Sets the query cache setting for the thread.
SQL_AUTO_IS_NULL = 0 | 1	If set (1, the default), then the last inserted row for an AUTO_INCREMENT can be found with WHERE auto_increment_column IS NULL. MS Access and other ODBC programs make use of this.
SQL_BIG_SELECTS = 0 | 1	If set (1, the default), MySQL allows large queries. If not set (0), MySQL will not allow queries where it will have to examine more than max_join_size rows. This is useful to avoid running accidental or malicious queries that could bring the server down.
SQL_BUFFER_RESULT = 0 | 1	If set (1), MySQL places query results into a temporary table (in some cases, speeding up performance by releasing table locks earlier).
SQL_LOG_OFF = 0 | 1	If set (1), MySQL will not log for the client (this is not the update log). The SUPER permission is required.
SQL_LOG_UPDATE = 0 | 1	If not set (0), MySQL will not use the update log for the client. Requires the SUPER permission.
SQL_QUOTE_SHOW_CREATE = 0 | 1	If set (1, the default), MySQL will quote table and column names.
SQL_SAFE_UPDATES = 0 | 1	If set (1), MySQL will not perform UPDATE or DELETE statements that don't use an index or a LIMIT clause, which helps prevent unpleasant accidents.
SQL_SELECT_LIMIT = value | DEFAULT	Sets the maximum number of records (default unlimited) that can be returned with a SELECT statement. LIMIT takes precedence over this.
TIMESTAMP = timestamp_value | DEFAULT	Sets the time for the client. You can use this to get the original timestamp when using the update log to restore rows. The timestamp_value is a Unix epoch timestamp.