📋

Description

MySQL Error 3769 occurs when you attempt to define a column with a `DEFAULT` value expression that includes a function not permitted by the database. MySQL has strict rules regarding which functions can be used in default value expressions, typically restricting them to literal constants or specific deterministic functions like `CURRENT_TIMESTAMP` or `NOW()` for datetime types. This error commonly arises during `CREATE TABLE` or `ALTER TABLE` statements when attempting to define a column's default behavior.

💬

Error Message

Default value expression of column '%s' contains a disallowed function.

🔍

Known Causes

4 known causes

⚠️

Non-deterministic Function Usage

Attempting to use functions like `RAND()`, `UUID()`, or other non-deterministic functions within a column's `DEFAULT` expression, which are not allowed because default values must be constant or predictably determined.

⚠️

Context-Dependent Functions

Employing functions that rely on session variables, other column values, or complex logic that MySQL cannot evaluate at the time of table creation or alteration for a `DEFAULT` value.

⚠️

Mismatch with Generated Column Functions

Confusing the capabilities of `DEFAULT` expressions with those of `GENERATED ALWAYS AS` columns, which support a wider range of functions for dynamic value generation.

⚠️

Custom or Stored Function in Default

Attempting to use a user-defined function (UDF) or a stored function (e.g., `my_custom_func()`) as a `DEFAULT` value, which is not supported by MySQL's default value syntax.

🛠️

Solutions

3 solutions available

1. Remove Disallowed Function from Default Value easy

Modify the column definition to use a static value or a different function.

1

Identify the column causing the error. The error message usually specifies the column name.

2

Examine the `DEFAULT` clause for the identified column in your `CREATE TABLE` or `ALTER TABLE` statement. Look for functions like `NOW()`, `CURRENT_TIMESTAMP()`, `UUID()`, `RAND()`, etc., which are often disallowed in default expressions in certain MySQL versions or configurations.

3

Replace the disallowed function with a static literal value or a different allowed function. If you need a timestamp, consider setting it in your application logic or using a trigger if allowed.

ALTER TABLE your_table MODIFY COLUMN your_column INT DEFAULT 0;
-- or for a timestamp, you might need application logic or a trigger

4

If you are creating a new table, adjust the `CREATE TABLE` statement accordingly.

CREATE TABLE your_table (
  id INT PRIMARY KEY,
  your_column VARCHAR(255) DEFAULT 'some_static_value'
);

2. Use a Trigger to Set the Default Value medium

Employ a trigger to populate the column with the desired value before insertion.

1

Identify the column that requires a dynamic default value that is currently disallowed.

2

Create a `BEFORE INSERT` trigger for the table.

DELIMITER //
CREATE TRIGGER set_default_for_your_column
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  IF NEW.your_column IS NULL THEN
    SET NEW.your_column = FUNCTION_YOU_WANT_TO_USE(); -- e.g., NOW(), UUID()
  END IF;
END; //
DELIMITER ;

3

In the trigger, check if the column is `NULL` and then set it to the desired value using the disallowed function. This bypasses the `DEFAULT` clause restriction.

4

Ensure the column in your table definition does not have a `DEFAULT` clause that conflicts or is redundant with the trigger's logic. It's often best to leave it as `NULL` or `DEFAULT NULL` if the trigger handles the default.

ALTER TABLE your_table MODIFY COLUMN your_column DATETIME DEFAULT NULL;

3. Update MySQL Configuration or Version advanced

Allow specific functions in default expressions by adjusting server settings or upgrading MySQL.

1

Determine if the disallowed function is universally disallowed or dependent on specific MySQL configurations or versions.

2

Consult the MySQL documentation for your specific version to understand which functions are allowed in default value expressions. Newer versions of MySQL generally have broader support for functions in default values.

3

If a configuration setting allows enabling functions in default values, locate and modify your `my.cnf` or `my.ini` file. Be cautious when changing server configurations, as it can have global impacts.

# Example: This is hypothetical, check your MySQL version's docs.
[mysqld]
sql_mode = "IGNORE_SPACE,NO_ENGINE_SUBSTITUTION"
# Potentially a specific setting related to default functions, if one exists.

4

Restart the MySQL server after making configuration changes.

sudo systemctl restart mysql

5

If the function is disallowed due to an older MySQL version, consider upgrading to a newer, supported version where this functionality is permitted. This is a more significant undertaking.

🔗

Related Errors

5 related errors

MySQL Error 3769: Disallowed Function in Column Default