📋

Description

This error occurs when you attempt to define a generated column whose expression includes a function that is not permitted. MySQL generated columns require deterministic functions that do not depend on external state, user variables, or non-deterministic behavior to ensure consistent data integrity.

💬

Error Message

Expression of generated column '%s' contains a disallowed function: %s.

🔍

Known Causes

4 known causes

⚠️

Use of Non-Deterministic Functions

Generated columns require expressions that are deterministic, meaning they produce the same result for the same input. Functions like NOW(), RAND(), or UUID() are non-deterministic and forbidden.

⚠️

Functions Accessing External State

Functions that depend on external server state, user variables, or session variables are disallowed as they can lead to inconsistent column values across different contexts.

⚠️

Use of Stored Functions or Subqueries

Expressions for generated columns cannot include calls to user-defined stored functions or subqueries, as these introduce complexity and potential for non-determinism.

⚠️

Explicitly Disallowed Built-in Functions

Some specific built-in functions, even if seemingly deterministic, are explicitly forbidden in generated column definitions due to security or design constraints.

🛠️

Solutions

3 solutions available

1. Replace Disallowed Function with Permitted Equivalent medium

Identify the disallowed function and substitute it with a function that is allowed in generated columns.

1

Examine the error message to identify the specific disallowed function being used in your generated column's expression. The error message will usually provide the name of the function.

ERROR 3763 (42000): MySQL Error 3763: Disallowed Function in Generated Column
ERROR MESSAGE: Expression of generated column 'column_name' contains a disallowed function: 'disallowed_function()'.

2

Consult the MySQL documentation for a list of functions that are permitted in generated columns. Look for alternative functions that achieve the same or a similar result. For example, if `UUID()` is disallowed, you might need to generate the UUID outside the table and insert it, or use a different approach.

NULL

3

Modify the `CREATE TABLE` or `ALTER TABLE` statement to replace the disallowed function with its permitted equivalent. If no direct equivalent exists, you may need to rethink the logic of your generated column.

ALTER TABLE your_table_name MODIFY COLUMN generated_column_name INT AS (permitted_function(existing_column));
-- Or during table creation:
CREATE TABLE your_table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    existing_column VARCHAR(255),
    generated_column_name INT AS (permitted_function(existing_column))
);

2. Remove the Disallowed Function from the Generated Column Expression easy

If the function is not essential, remove it from the generated column's definition.

1

Review the expression for your generated column and determine if the disallowed function is critical for its calculation. If it's not strictly necessary, the simplest solution is to remove it.

NULL

2

Modify the `CREATE TABLE` or `ALTER TABLE` statement to simplify the generated column's expression by removing the problematic function.

ALTER TABLE your_table_name MODIFY COLUMN generated_column_name INT AS (expression_without_disallowed_function);
-- Or during table creation:
CREATE TABLE your_table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    existing_column VARCHAR(255),
    generated_column_name INT AS (expression_without_disallowed_function)
);

3. Store Calculation Results in a Separate Column (and Update Manually or via Triggers) advanced

If the function is truly essential and cannot be replaced, consider storing the result in a regular column and managing its updates.

1

Remove the generated column that uses the disallowed function. Instead, create a regular column to store the calculated value.

ALTER TABLE your_table_name DROP COLUMN generated_column_name;
ALTER TABLE your_table_name ADD COLUMN calculated_column INT;

2

Implement a mechanism to update the new `calculated_column`. This can be done in two primary ways:

NULL

3

Option A: Use triggers. Create `BEFORE INSERT` and `BEFORE UPDATE` triggers that calculate the value using the disallowed function and assign it to the `calculated_column`.

DELIMITER $$
CREATE TRIGGER update_calculated_column_insert
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    SET NEW.calculated_column = disallowed_function(NEW.existing_column);
END$$

CREATE TRIGGER update_calculated_column_update
BEFORE UPDATE ON your_table_name
FOR EACH ROW
BEGIN
    SET NEW.calculated_column = disallowed_function(NEW.existing_column);
END$$

4

Option B: Manual updates or application-level logic. Ensure that whenever rows are inserted or updated, the `calculated_column` is explicitly populated with the correct value, potentially by your application code or through scheduled jobs.

INSERT INTO your_table_name (existing_column, calculated_column)
VALUES ('some_value', disallowed_function('some_value'));

UPDATE your_table_name
SET calculated_column = disallowed_function(existing_column)
WHERE id = some_id;

🔗

Related Errors

5 related errors