Sign inTry Free

SHOW VARIABLES

On this page

Shows a list of variable bindings.

Syntax

SHOW [CLUSTER] [LOCAL|GLOBAL] VARIABLES [EXTENDED]
[LIKE 'pattern' | WHERE expression]

Arguments

  • pattern - Can contain the % wildcard which represents zero or more characters. Can also contain the _ wildcard which represents one character.

  • expression - A SQL WHERE clause.

  • See the Permission Matrix for the required permission.

Remarks

  • CLUSTER - Displays the list of variables that can sync to all nodes. _SYNC and CLUSTER are synonymous. Use either of the options in the command.

  • LOCAL- Displays the list of variable bindings in the current connection to the node. This includes the values of global variables that have been set. Values of session variables that can be set globally are not included, when these variables are set globally.

  • SESSION and LOCAL are synonymous. Use either of the options in the command.

  • GLOBAL - Displays the list of global variable bindings. Values of session variables that can be set globally are also included, when these variables are set globally.

  • If the _SYNC|CLUSTER option is specified, GLOBAL variables are shown by default.

  • If the _SYNC|CLUSTER option is not specified, LOCAL variables are shown by default.

  • EXTENDED - Displays additional information about the variables in the output.

Examples

Using SHOW VARIABLES with a Wildcard

SHOW VARIABLES LIKE 'auto%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_attach              | ON    |
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| auto_replicate           | OFF   |
| autocommit               | ON    |
+--------------------------+-------+

Behavior of SHOW LOCAL VARIABLES When a Global Variable is Set

When a global variable is set, its value becomes effective in the current connection to the node. For this reason, after setting the global variable connect_timeout to 20, this value is shown in the SHOW LOCAL VARIABLES output:

SET GLOBAL connect_timeout = 20;


SHOW LOCAL VARIABLES LIKE 'connect_timeout';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 20    |
+-----------------+-------+

Behavior of SHOW LOCAL VARIABLES When a Session Variable that can be Set Globally is Set

When a session variable that can also be set globally is set globally, its value is not effective in the current connection to the node. For this reason, after setting the variable enable_broadcast_left_join to OFF, the value is not shown in the SHOW LOCAL VARIABLES output:

SET GLOBAL enable_broadcast_left_join = OFF;


SHOW LOCAL VARIABLES LIKE 'enable_broadcast_left_join';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| enable_broadcast_left_join | ON    |
+----------------------------+-------+

Using the EXTENDED Clause

In this example, the value of Variable_type is Local because character_set_server is a session variable that can be set globally. The value of Workspace_type is not Local because character_set_server is a sync variable. The In_Allow_List column shows users with SYSTEM_VARIABLE_ADMIN privilege which variables they are allowed to set.

SHOW VARIABLES EXTENDED LIKE 'character_set_server';
+----------------------+-------+---------------+----------------------------+---------------+
| Variable_name        | Value | Variable_type | Workspace_type               | In_Allow_List |
+----------------------+-------+---------------+----------------------------+---------------+
| character_set_server | utf8  | Local         | Workspace-wide for all nodes | Yes           |
+----------------------+-------+---------------+----------------------------+---------------+

SHOW VARIABLES EXTENDED LIKE 'memsql_version';

+----------------+-------+---------------+--------------+---------------+
| Variable_name  | Value | Variable_type | Workspace_type | In_Allow_List |
+----------------+-------+---------------+--------------+---------------+
| memsql_version | 7.8.0 | Local         | Local        | No            |
+----------------+-------+---------------+--------------+---------------+

Last modified: January 11, 2023

Was this article helpful?

On this page

Was this article helpful?