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.
Fixes 1204
SET @myvar = NOW(); -- ERROR: Function not allowed
SET @var1 = @var2; -- ERROR: Variable assignment
SET @result = (SELECT ...); -- ERROR: Subquery not allowedSET @myvar = NOW(); -- ERROR: Function not allowedSET @var1 = @var2; -- ERROR: Variable assignmentSET @result = (SELECT ...); -- ERROR: Subquery not allowedERROR 1204: Only constant expressions with SET
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.