📋

Description

This error occurs when you attempt to modify a MariaDB system variable that is scoped only to the current session using the `SET GLOBAL` command. Session variables are specific to an individual client connection and cannot be altered globally for all connections and future sessions. It indicates a mismatch between the desired scope of change and the variable's actual scope.

💬

Error Message

Variable '%s' is a SESSION variable and can't be used with SET GLOBAL

🔍

Known Causes

3 known causes

⚠️

Incorrect Variable Scope

Attempting to use `SET GLOBAL` on a system variable that only exists or is modifiable at the `SESSION` level. MariaDB variables have distinct scopes (GLOBAL, SESSION).

⚠️

Misunderstanding SET Command

The user might not be aware that `SET GLOBAL` is specifically for affecting all current and future connections, while `SET SESSION` (or simply `SET`) targets only the current connection.

⚠️

Typographical Error in Command

Accidentally typing `SET GLOBAL` instead of `SET SESSION` (or just `SET`) when intending to adjust a session-specific parameter.

🛠️

Solutions

3 solutions available

1. Set the Variable for the Current Session Only easy

Modify the session variable directly for the active connection.

1

Instead of using `SET GLOBAL`, use `SET SESSION` or simply `SET` to apply the variable change only to your current connection.

SET SESSION variable_name = value;
-- or simply
SET variable_name = value;

2

Replace `variable_name` with the actual session variable causing the error (e.g., `session_track_schema`) and `value` with your desired setting.

SET SESSION session_track_schema = 1;

2. Use the Correct Variable Scope medium

Identify and use global variables if a server-wide setting is intended.

1

Determine if the variable you are trying to set is indeed a session-only variable. Many system variables have both session and global scopes.

SHOW VARIABLES LIKE '%variable_name%';

2

If the variable has a global scope, you can set it using `SET GLOBAL`. Consult the MariaDB documentation for specific variable scopes.

SET GLOBAL variable_name = value;

3

If the variable is strictly a session variable and you need to enforce it for all future connections, you will need to configure it in the MariaDB configuration file.

3. Configure the Variable in MariaDB's Configuration File advanced

Permanently set session variables for all new connections by editing the MariaDB configuration.

1

Locate your MariaDB configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or files within `/etc/my.cnf.d/`.

2

Open the configuration file with a text editor (e.g., `nano`, `vim`).

3

Within the `[mysqld]` or `[mariadb]` section, add or modify the line to set the session variable. Since it's a session variable, you cannot set it globally here, but you can set a default for new sessions.

[mysqld]
session_track_schema = 1

4

Save the changes and restart the MariaDB service for the configuration to take effect.

sudo systemctl restart mariadb

🔗

Related Errors

5 related errors

MariaDB Error 1228: Global SET for Session Variable