SET and SET SESSION

Sets session variables. The variable’s value is effective for the user’s current connection to the node, until the node is restarted.

Warning

SingleStore recommends using the SET SESSION statement for session variables that are only referenced by the aggregator nodes on which the variable is set. For session variables whose values need to be propagated to leaf nodes from the aggregator nodes, a global setting should be used instead.

Syntax

SET { [SESSION] variable_name } = value;
SET @@SESSION.variable_name = value;
SET {CHARACTER SET 'charset_name' | NAMES {'charset_name'}};
SET PASSWORD FOR 'username'@'host' = PASSWORD('password');

Remarks

  • variable_name is the name of the session variable.

  • The @@SESSION.variable_name syntax is supported for compatibility with some other database systems. This is similar to MySQL behavior.

  • This command can be run on a child or master aggregator node to set sync variables, but must be run on the master aggregator otherwise.

  • See the engine variables overview for information about other ways to set variables.

  • The SESSION keyword is optional. Including it does not change this command’s behavior.

  • charset_name sets session system variables character_set_client and character_set_results to the given character set, and character_set_connection to the value of character_set_server. It may be quoted or unquoted. The default character set mapping can be restored by using value DEFAULT.

  • NAMES sets the session system variables: character_set_client, character_set_connection, and character_set_results to the given character set. charset_name may be quoted or unquoted. The default mapping can be restored by using value DEFAULT.

  • SET PASSWORD sets the user password. See SET PASSWORD topic for details.

Examples

Set Session Variable

The following example sets the session variable net_read_timeout and retrieves its value.

SET net_read_timeout = 5000;
or
SET @@SESSION.net_read_timeout = 5000;

Retrieve the session variable’s value:

SELECT @@net_read_timeout;

Set Character Set

SET CHARACTER SET is similar to SET NAMES. The following example sets the character set of the client to utf8.

SET CHARACTER SET 'utf8';
Query OK, 0 rows affected (13 ms)
SET NAMES 'utf8';
Query OK, 0 rows affected (25 ms)

Refer Assigning Expressions to Variables for more examples.

Last modified: August 21, 2023

Was this article helpful?