📋

Description

This error occurs when you attempt to define a default value for a table column using an expression that refers to a user-defined variable (e.g., `@my_variable`) or a MySQL system variable (e.g., `@@system_variable`). MySQL default column values must be constant expressions or specific functions, not dynamic variables.

💬

Error Message

Default value expression of column '%s' cannot refer user or system variables.

🔍

Known Causes

3 known causes

⚠️

User-Defined Variable in Default

Attempting to assign a user-defined session variable (e.g., `@my_var`) as the default value for a column definition.

⚠️

System Variable in Default

Specifying a MySQL system variable (e.g., `@@hostname` or `@@session.sql_mode`) within a column's default value expression.

⚠️

Incorrect Expression Type

Misunderstanding the distinction between allowed default functions (like `NOW()`, `UUID()`) and disallowed dynamic variable references in column definitions.

🛠️

Solutions

3 solutions available

1. Remove User/System Variables from DEFAULT Clause easy

Modify the table definition to use static values or expressions not dependent on variables.

1

Identify the column causing the error by examining the error message. It will explicitly name the column.

2

Use the `ALTER TABLE` statement to modify the column's default value. Replace the variable-dependent expression with a static literal or a valid constant expression.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype DEFAULT 'your_static_value';
-- Or for a numeric type:
ALTER TABLE your_table_name MODIFY COLUMN your_column_name INT DEFAULT 123;

3

If the original expression was meant to be dynamic but not a variable, consider if a trigger or application logic can achieve the desired outcome.

2. Replace Dynamic Defaults with Application-Level Logic medium

Handle the default value assignment in your application code instead of the database.

1

Identify the column and the variable used in its default value expression.

2

Remove the `DEFAULT` clause entirely from the column definition using `ALTER TABLE`.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype DEFAULT NULL;
-- Or if you want to explicitly allow NULLs and handle it in app:
ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype NULL;

3

In your application's INSERT statements, explicitly provide a value for this column. If no value is provided, ensure your application logic assigns the desired default value before executing the INSERT.

INSERT INTO your_table_name (column1, your_column_name, column3) VALUES ('value1', 'calculated_default_value', 'value3');

3. Use Stored Functions for Complex Defaults (with Caution) advanced

Encapsulate complex default logic in a stored function, but be aware of potential performance implications.

1

Create a stored function that calculates the desired default value. This function can access system information but should not rely on session variables.

DELIMITER $$
CREATE FUNCTION get_dynamic_default() RETURNS INT
BEGIN
    -- Example: Return current year
    RETURN YEAR(CURDATE());
END$$
DELIMITER ;

2

Alter the table to use the newly created stored function as the default value for the column.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name INT DEFAULT get_dynamic_default();

3

Test thoroughly to ensure the function behaves as expected and doesn't introduce performance bottlenecks, especially for frequently inserted rows.

🔗

Related Errors

5 related errors

MySQL Error 3772: Invalid Column Default Value Expression