MySQL Troubleshooting

The SET Statement

MySQL supports two kinds of variables: SESSION and GLOBAL. SESSION variables are set for the current connection only and do not affect others. GLOBAL variables apply to all connections created after you set such a variable. But setting a GLOBAL variable does not affect the current connection,^[10] so you should either set both SESSION and GLOBAL variables or reconnect if you need to use a new value in the current connection.

You can set a session variable using the statement:

SET [SESSION] var_name=value

I put SESSION in square brackets because you can omit this keyword; the set command uses SESSION as the default.

To set a GLOBAL variable, use the statement:

SET GLOBAL var_name=value

When you test options, I suggest you try to use a SESSION variable whenever possible. After you are happy with the results, you can use a GLOBAL variable to change the running server’s configuration, and then change the configuration file so that this value will be applied after a restart.

Note

Using SET SESSION is also very helpful when you want to check the effect of an option on a particular query. In this case, you can set the variable before the query, test, and then return back to the default value using the statement SET [SESSION] variable_name=DEFAULT.

If an option is shared among threads, you can start by setting a GLOBAL variable, and then examine how the server behaves. After you are happy with the result, change the configuration file to include the new variable and its setting. ...