MySQL Error 1204 occurs when you try to assign a non-constant expression (function, subquery, or variable) to a SET statement that requires a literal value. Use constant values directly or SELECT...INTO syntax instead.
MySQL Error 1204 (ER_SET_CONSTANTS_ONLY) enforces a strict requirement that certain SET statements can only use constant expressions. This error is raised when you attempt to assign a non-constant expression—such as a function call, subquery result, or another variable—where MySQL expects a literal constant value. The error typically occurs with system variables that have restrictions on what types of expressions can be assigned to them, or when using user-defined variables in contexts that require compile-time constants.
Locate the exact line causing Error 1204 in your MySQL logs or application output. Look for patterns like:
SET @myvar = NOW(); -- ERROR: Function not allowed
SET @var1 = @var2; -- ERROR: Variable assignment
SET @result = (SELECT ...); -- ERROR: Subquery not allowedNote which expression (function, subquery, or variable) is being used.
When you need to store a query result in a variable, use SELECT...INTO instead of SET:
Before (causes Error 1204):
SET @maxId = (SELECT MAX(id) FROM users);After (correct syntax):
SELECT MAX(id) INTO @maxId FROM users;This syntax is specifically designed for storing single values from queries into variables.
For functions like NOW(), UUID(), or CONCAT(), use SELECT assignment or use the value directly in your query:
Option 1: SELECT assignment (if you need to reuse the value):
SELECT NOW() INTO @currentTime;
SELECT UUID() INTO @newId;Option 2: Use the function directly in the query (preferred):
INSERT INTO events (id, timestamp) VALUES (UUID(), NOW());
UPDATE users SET last_login = NOW() WHERE id = 1;Using functions directly avoids variable assignment overhead and is more efficient.
User-defined variables cannot be assigned to other variables using SET. If you need to copy a variable:
Before (causes Error 1204):
SET @sourceVar = 'some value';
SET @destVar = @sourceVar; -- ERROR!After (correct):
SET @sourceVar = 'some value';
SET @destVar = 'some value'; -- Use constantOr use SELECT to reference the value:
SELECT @sourceVar INTO @destVar;If you need to set a system variable with a dynamic value, use prepared statements:
Before (causes Error 1204):
SET @value = 1000;
SET GLOBAL max_connections = @value; -- ERROR!After (using prepared statements):
SET @sql = CONCAT('SET GLOBAL max_connections = ', 1000);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;Prepared statements allow you to dynamically construct and execute SET statements that would otherwise require constants.
MySQL Version Differences: Restrictions on constant expressions vary slightly across MySQL versions. Newer versions (8.0+) may have relaxed some constraints compared to earlier versions, so testing your code against your specific MySQL version is important.
User-Defined Variables vs System Variables: User-defined variables (starting with @) have different rules than system variables (like max_connections, global_max, etc.). System variables often have stricter requirements that only allow constant expressions. Always check MySQL documentation for the specific variable you're working with.
Performance Consideration: Using SELECT...INTO instead of SET with functions is generally more efficient because MySQL can optimize it better. When possible, use functions directly in INSERT/UPDATE/SELECT statements rather than storing them in variables first.
Transactions and Variable Scope: Variables set within a transaction remain in scope after COMMIT, but using expressions within a transaction that require constants follows the same rules. If you're working with multiple statements, consider the order and ensure each statement follows the constant expression rules.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions