MySQL Logo

One of the things I like about MySQL is that it allows you to change some of its global state as it’s running. This article will provide a quick overview of how to view the current variables and how to make changes.

Please note: All examples in this article were written using MySQL 5.5 and done in a development environment. Please do not try this on a production environment unless you are sure of the ramifications.

Global vs Session

MySQL has two levels of variables. The first is session which only affects your current connection to the server. If you’re just playing around you should use this because it should only hurt you if you do something wrong. The second is global which affects all the connections and any future connections until a reboot. We’re using this level in this article but if you want to set the session level of the variables change “global” to “session” in the examples below.

Seeing the Variables

Viewing all the variables can be done using the show global variables command:

mysql> show global variables;
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                     | Value                                                                                                                  |
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
| auto_increment_increment                          | 1                                                                                                                      |
| auto_increment_offset                             | 1                                                                                                                      |
**snip**
| version_comment                                   | (Ubuntu)                                                                                                               |
| version_compile_machine                           | x86_64                                                                                                                 |
| version_compile_os                                | debian-linux-gnu                                                                                                       |
| wait_timeout                                      | 28800                                                                                                                  |
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
317 rows in set (0.00 sec)

317 rows is a bit much to read through to find a variable you’re interested in. Thankfully because show global variables is basically a query you can add a where clause:

mysql> show global variables where Variable_name like '%slow%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| log_slow_queries    | OFF                          |
| slow_launch_time    | 2                            |
| slow_query_log      | OFF                          |
| slow_query_log_file | /var/lib/mysql/slowquery.log |
+---------------------+------------------------------+
4 rows in set (0.00 sec)

mysql> show global variables where Variable_name = 'SQL_MODE';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

You can also put the variable name in an SQL select query to perform calculations on it:

mysql> select @@global.SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

Updating a Variable

In order to update a variable we can use the set command. For this example, we’re going to set the SQL_MODE to the mode that’s the default in MySQL 8 (per https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html). You can do this to see how your code will handle the new modes.

Globally:

set global SQL_MODE = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

Now we can see that the value has changed:

mysql> select @@global.SQL_MODE;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.SQL_MODE                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)