📋

Description

MariaDB Error 1229 occurs when you attempt to modify a system variable that has a GLOBAL scope without explicitly using the `SET GLOBAL` command. This error indicates that the variable you are trying to change affects the entire server instance and requires the correct syntax for modification.

💬

Error Message

Variable '%s' is a GLOBAL variable and should be set with SET GLOBAL

🔍

Known Causes

3 known causes

⚠️

Forgetting `GLOBAL` Keyword

Attempting to set a global system variable using `SET variable = value` instead of the required `SET GLOBAL variable = value` syntax.

⚠️

Misunderstanding Variable Scope

Assuming a variable is session-scoped when it is, in fact, a global variable that impacts all connections to the MariaDB server.

⚠️

Using `SET SESSION` for Global Variables

Explicitly trying to set a global-only variable with `SET SESSION variable = value`, which is not permitted by MariaDB.

🛠️

Solutions

3 solutions available

1. Use SET GLOBAL for Global Variables easy

Directly use the 'SET GLOBAL' command to modify global variables.

1

When you encounter the error 'Variable '%s' is a GLOBAL variable and should be set with SET GLOBAL', it means you are trying to change a system-wide setting using the standard 'SET' command, which is intended for session-specific variables. To fix this, use the 'SET GLOBAL' command instead.

SET GLOBAL variable_name = value;

2

Replace `variable_name` with the actual name of the global variable causing the error (e.g., `max_connections`, `innodb_buffer_pool_size`) and `value` with the desired setting.

SET GLOBAL max_connections = 200;

2. Modify Configuration Files for Persistent Global Settings medium

Edit MariaDB configuration files to make global variable changes permanent across restarts.

1

Global variables set with `SET GLOBAL` are temporary and will revert to their default or previously configured values upon server restart. For permanent changes, you need to modify the MariaDB configuration file.

2

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

3

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

sudo nano /etc/my.cnf

4

Find the `[mysqld]` section. If it doesn't exist, create it.

5

Add or modify the global variable within the `[mysqld]` section. For example, to set `max_connections` to 200:

[mysqld]
max_connections = 200

6

Save the file and exit the editor.

7

Restart the MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

3. Check Variable Scope Before Setting easy

Verify if a variable is global or session-specific before attempting to set it.

1

Before attempting to set a variable, it's good practice to check its scope. You can use `SHOW VARIABLES LIKE 'variable_name';` to see its current value and scope.

SHOW VARIABLES LIKE 'max_connections';

2

The output will indicate if the variable is a `GLOBAL` variable or a `SESSION` variable. If the `Scope` column shows `GLOBAL`, you must use `SET GLOBAL`.

3

If the variable is a `SESSION` variable, you can use the standard `SET` command.

SET variable_name = value;

🔗

Related Errors

5 related errors

MariaDB Error 1229: Incorrect Global Variable Setting