📋

Description

This error occurs when you define a generated column in MySQL, and the expression used to calculate its value attempts to reference either a user-defined variable (e.g., `@my_var`) or a system variable (e.g., `@@session.time_zone`). Generated columns must derive their values solely from other columns in the same row or from deterministic, built-in functions, not from dynamic variables.

💬

Error Message

Expression of generated column '%s' cannot refer user or system variables.

🔍

Known Causes

3 known causes

⚠️

User-Defined Variable Usage

The generated column's expression directly incorporates a user-defined session variable (e.g., `@my_variable`) for its calculation.

⚠️

System Variable Usage

The generated column's expression attempts to use a MySQL system variable (e.g., `@@session.time_zone`, `@@sql_mode`) for its value derivation.

⚠️

Non-Deterministic Expression

Attempting to use non-deterministic elements, such as variables, where MySQL requires a generated column's expression to be deterministic and based only on row data or static functions.

🛠️

Solutions

3 solutions available

1. Replace Variable with Literal Value easy

Substitute the user/system variable in the generated column expression with its actual value.

1

Identify the generated column definition that uses a user or system variable. This will be in your `CREATE TABLE` or `ALTER TABLE` statement.

2

Determine the current value of the variable you are referencing. If it's a user variable, you'll need to set it before creating/altering the table. If it's a system variable, you can retrieve its value using `SHOW VARIABLES`.

SELECT @@variable_name;

3

Modify the generated column definition to use the literal value instead of the variable. For example, if your variable was `@my_prefix` and its value was 'PROJ-', change the expression.

-- Original (problematic)
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    full_name VARCHAR(100) AS (CONCAT(@my_prefix, name))
);

-- Modified (using literal value)
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    full_name VARCHAR(100) AS (CONCAT('PROJ-', name))
);

2. Use a Stored Function medium

Encapsulate the logic involving variables within a stored function and reference that function in the generated column.

1

Create a stored function that takes any necessary arguments and returns the desired value. This function can internally use user or system variables.

DELIMITER //
CREATE FUNCTION generate_prefixed_name(base_name VARCHAR(50)) RETURNS VARCHAR(100)
BEGIN
    DECLARE prefix VARCHAR(10);
    SET prefix = @@my_prefix; -- Or a system variable like @@version
    RETURN CONCAT(prefix, base_name);
END //
DELIMITER ;

2

Modify your table definition to call the stored function in the generated column expression.

CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    full_name VARCHAR(100) AS (generate_prefixed_name(name))
);

3

Ensure the user variable (if used) is set before inserting or updating data that triggers the generated column.

SET @my_prefix = 'APP-';

3. Calculate Value Before Insertion/Update easy

Pre-calculate the value for the generated column in your application logic and store it as a regular column.

1

Remove the generated column definition from your table schema. Replace it with a regular column of the appropriate data type.

-- Original (problematic)
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    full_name VARCHAR(100) AS (CONCAT(@my_prefix, name))
);

-- Modified (regular column)
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50),
    full_name VARCHAR(100)
);

2

In your application code (e.g., Python, Java, PHP), retrieve the value of the user or system variable.

# Example in Python using mysql.connector
import mysql.connector

config = {
  'user': 'user',
  'password': 'password',
  'host': 'localhost',
  'database': 'mydatabase'
}

cnx = mysql.connector.connect(**config)
c = cnx.cursor()

c.execute("SELECT @@my_prefix;")
prefix_value = c.fetchone()[0]

c.execute("SELECT name FROM my_table WHERE id = 1;")
name_value = c.fetchone()[0]

full_name_value = prefix_value + name_value

c.execute("INSERT INTO my_table (id, name, full_name) VALUES (%s, %s, %s)", (1, name_value, full_name_value))
c.close()
cnx.close()

3

Construct the final value using the retrieved variable and other data, then insert or update this pre-calculated value into the regular column.

🔗

Related Errors

5 related errors

MySQL Error 3765: Generated Column Variable Reference