Mariadb Set

SET in MariaDB

Last updated: 2026-07-20

This skill covers the delta between generic "assign a variable" SQL and MariaDB's SET — its scope keywords, the SET STATEMENT ... FOR per-query extension, and a persistence model that has no SET PERSIST equivalent.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Features available across current LTS branches (10.6, 10.11, 11.4, 11.8) are shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Syntax

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name (= | :=) expr
    | [GLOBAL | SESSION | LOCAL] system_var_name = expr
    | [@@global. | @@session. | @@local. | @@] system_var_name = expr
    | system_var_name = DEFAULT

• Multiple assignments in one statement are comma-separated and can mix scopes and variable kinds freely: SET @x = 1, GLOBAL max_connections = 500, @@session.sql_mode = DEFAULT;
• GLOBAL, SESSION, and LOCAL are the keyword forms; @@global., @@session., @@local., and bare @@ are the equivalent variable-reference forms — SET @@GLOBAL.max_connections = 500 and SET GLOBAL max_connections = 500 are fully equivalent (same code path).
• Omitting a scope keyword defaults to SESSION if the variable has a session value, otherwise to GLOBAL.
• SET @x = expr and SET @x := expr are both accepted for user variables inside SET — but only := is valid for a user-variable assignment anywhere else (e.g. inside a SELECT expression).

Variable Scopes — GLOBAL / SESSION / LOCAL

• GLOBAL changes the server-wide default. It affects new sessions only — not the session that ran the SET GLOBAL, and not any session already connected.
• SESSION (and its synonym LOCAL) changes the current connection only.
• Setting a variable with the wrong scope is a hard error, not a fallback:

SET innodb_sync_spin_loops = 60;
-- ERROR 1229 (HY000): Variable 'innodb_sync_spin_loops' is a GLOBAL variable
--   and should be set with SET GLOBAL

SET GLOBAL skip_parallel_replication = ON;
-- ERROR 1228 (HY000): Variable 'skip_parallel_replication' is a SESSION variable
--   and can't be used with SET GLOBAL

• Setting GLOBAL requires the SUPER privilege by default (a handful of individual global variables instead gate on a narrower privilege, such as replication/binlog admin variants — check the specific variable if SUPER alone doesn't work).
• SET does not persist a global change across a server restart. There is no SET PERSIST in MariaDB — to survive a restart, also update the value in a configuration file.

SET var = DEFAULT

SET sql_mode = DEFAULT;         -- session var -> resets to the current GLOBAL value
SET GLOBAL sql_mode = DEFAULT;  -- global var  -> resets to the compiled-in server default

SET session_var = @@GLOBAL.session_var achieves the same effect as SET session_var = DEFAULT by explicitly copying the current global value.

SET STATEMENT ... FOR (MariaDB Extension)

SET STATEMENT var1 = value1 [, var2 = value2, ...] FOR statement;

Sets one or more system variables for the duration of a single statement, then automatically restores the prior session value — roughly:

SET @save_value = @@var1;
SET SESSION var1 = value1;
statement;
SET SESSION var1 = @save_value;

Notes and traps:

• value must be a constant literal — it cannot reference a table or a stored routine (subqueries/table references in the SET STATEMENT clause itself are rejected).
• The whole statement is parsed before SET STATEMENT variables take effect, so anything that influences parsing (client/connection charset variables, sql_mode=ANSI_QUOTES, etc.) won't retroactively change how the FOR statement was parsed.
• It works with prepared statements — SET STATEMENT var=val FOR PREPARE ... and SET STATEMENT var=val FOR EXECUTE stmt_name are both valid targets.
• Setting a SESSION variable inside the FOR statement (e.g. SET STATEMENT x=1 FOR SET SESSION x=2) is pointless: the inner SET SESSION takes effect, then gets reverted to the pre-SET STATEMENT value once the outer statement finishes.
• A fixed set of 13 variables cannot be set via SET STATEMENT at all (attempting it raises ER_SET_STATEMENT_NOT_SUPPORTED) — autocommit, character_set_client, character_set_connection, character_set_filesystem, collation_connection, debug_sync, interactive_timeout, profiling, profiling_history_size, query_cache_type, skip_replication, sql_log_off, wait_timeout.

SET STATEMENT max_statement_time=1000 FOR SELECT ...;
SET STATEMENT optimizer_switch='materialization=off' FOR SELECT ...;
SET STATEMENT join_cache_level=6, optimizer_switch='mrr=on' FOR SELECT ...;

Examples

-- multiple scopes and kinds in one statement
SET @request_id = UUID(), SESSION sql_mode = 'STRICT_ALL_TABLES', GLOBAL max_connections = 500;

-- inline user-variable assignment inside an expression (needs :=, not =)
SELECT (@a := 1);
SELECT @a;

-- restore a session variable to the server default
SET GLOBAL max_error_count = 256;
SET SESSION max_error_count = DEFAULT;   -- picks up the new global value, 256

-- bound a single query without touching session state
SET STATEMENT max_statement_time=2 FOR SELECT SLEEP(10);

Read variable state back with SHOW VARIABLES or INFORMATION_SCHEMA.SYSTEM_VARIABLES, which exposes SESSION_VALUE and GLOBAL_VALUE side by side (a NULL in either column means the variable doesn't have that scope).

Related SET Forms (Separate Statements)

These share the SET keyword but are documented — and should be reasoned about — separately:

See Also

• mariadb-set-transaction — isolation level and access mode assignment via SET TRANSACTION, a distinct statement from general SET.
• mariadb-show — SHOW VARIABLES to read GLOBAL/SESSION variable values back.
• Canonical references on mariadb.com/docs:
  • https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set
  • https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-statement
  • https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-sql_log_bin

This page is: Copyright © 2026 MariaDB. All rights reserved.