📋

Description

Error 1204 indicates that you have attempted to assign a non-constant value using the `SET` statement in MariaDB. This typically occurs when setting user-defined or system variables, which strictly require literal values or expressions that can be resolved to a constant at statement parsing time. The database system expects a fixed, known value when `SET` is used.

💬

Error Message

You may only use constant expressions with SET

🔍

Known Causes

3 known causes

⚠️

Assigning Column Values

Attempting to set a user or system variable directly with a value retrieved from a database table column.

⚠️

Using Non-Constant Functions

Employing functions like `NOW()`, `RAND()`, or `UUID()` that generate dynamic, non-deterministic results within a `SET` statement.

⚠️

Subqueries or Complex Expressions

Including a subquery or a complex expression that cannot be evaluated to a single constant value at the time the `SET` statement is parsed.

🛠️

Solutions

3 solutions available

1. Use User-Defined Variables for Dynamic Values easy

Assign dynamic values to user-defined variables and then use those variables in the SET statement.

1

Declare and assign a value to a user-defined variable. This can be a literal value, the result of a query, or a system variable.

SET @my_dynamic_value = (SELECT COUNT(*) FROM your_table WHERE some_condition = 'some_value');

2

Use the user-defined variable in your SET statement.

SET SESSION my_variable = @my_dynamic_value;

2. Re-evaluate Query Logic to Avoid Dynamic SET medium

Identify if the SET statement is truly necessary or if the desired outcome can be achieved through a different SQL construct.

1

Analyze the purpose of the SET statement. Often, dynamic values are intended to be used in subsequent queries or to configure session behavior.

2

If the dynamic value is for a subsequent query, consider incorporating it directly into that query instead of using SET.

SELECT column1, column2 FROM your_table WHERE dynamic_column = (SELECT COUNT(*) FROM another_table);

3

If the dynamic value is for session configuration (e.g., `SET SESSION sql_mode = ...`), explore if there are alternative ways to achieve the same configuration, perhaps at the server level or through connection parameters.

3. Set Configuration Directly in `my.cnf` (or equivalent) medium

For server-wide or global settings, modify the MariaDB configuration file directly.

1

Locate your MariaDB configuration file. This is typically `my.cnf` or `mariadb.cnf` on Linux/macOS, or `my.ini` on Windows.

2

Edit the configuration file and add or modify the desired configuration parameter within the appropriate section (e.g., `[mysqld]` for server defaults, `[client]` for client defaults). Ensure the value is a constant.

[mysqld]
# Example: Setting a global variable
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3

Restart the MariaDB server for the changes to take effect.

sudo systemctl restart mariadb  # On systems using systemd

🔗

Related Errors

5 related errors

MariaDB Error 1204: SET Requires Constant Expressions