📋

Description

MariaDB Error 1067, 'Invalid default value for '%s'', indicates that a column definition attempts to assign a default value that is incompatible with the column's data type or other defined constraints. This error commonly occurs during `CREATE TABLE` or `ALTER TABLE` statements when defining new columns or modifying existing ones.

💬

Error Message

Invalid default value for '%s'

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

The specified default value does not match the column's defined data type, such as attempting to assign a string to an integer column.

⚠️

Length or Range Violation

The default value provided exceeds the maximum length allowed for character types (e.g., VARCHAR) or falls outside the valid range for numeric types (e.g., INT, DECIMAL).

⚠️

Invalid Default Expression

The default value uses an expression or function that is not allowed for the specific column type or MariaDB version, such as a non-deterministic function.

⚠️

NOT NULL Column Violation

Attempting to set a `NULL` default value for a column explicitly defined as `NOT NULL` without an appropriate implicit or explicit default.

🛠️

Solutions

4 solutions available

1. Correct Invalid Default Value Syntax easy

Identify and correct the syntax of the default value for the specified column.

1

Examine the `CREATE TABLE` or `ALTER TABLE` statement that is failing. Look for the column name mentioned in the error message and its associated `DEFAULT` clause.

Example of a problematic statement:
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    status VARCHAR(50) DEFAULT 'active' NOT NULL
);

-- Or an ALTER TABLE statement:
ALTER TABLE my_table MODIFY COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

2

Ensure the default value's data type matches the column's data type and that any special keywords (like `CURRENT_TIMESTAMP`, `NULL`, etc.) are correctly formatted and supported by MariaDB for that column type.

Corrected example for a TIMESTAMP column:
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Corrected example for a VARCHAR column:
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    status VARCHAR(50) DEFAULT 'active'
);

3

If the default value is a string literal, ensure it is enclosed in single quotes. If it's a numeric literal, it should not be quoted. For temporal types, use keywords like `CURRENT_TIMESTAMP` or `NULL`.

2. Remove or Modify Default Value for Incompatible Types easy

Remove the default value or change it to a compatible value if the current one is not supported for the column type.

1

Identify the column causing the error and its data type. For instance, trying to set a default string value for an `INT` column will fail.

Example of an incompatible default:
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2) DEFAULT 'N/A'
);

2

Either remove the `DEFAULT` clause entirely or change the default value to something that is compatible with the column's data type. If `NULL` is acceptable and the column allows it, `DEFAULT NULL` is often a safe choice.

Option 1: Remove default value
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2)
);

Option 2: Set a compatible default value (e.g., 0.00 for DECIMAL)
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2) DEFAULT 0.00
);

Option 3: Allow NULL
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2) DEFAULT NULL
);

3. Update MariaDB Server Version medium

Upgrade MariaDB to a newer version where default value handling might be improved or bugs fixed.

1

Check your current MariaDB server version by connecting to the server and running the following command:

SELECT VERSION();

2

Consult the MariaDB release notes for the specific version you are running and for newer versions. Look for changes related to data types, default values, or SQL syntax compliance.

3

If your version is significantly outdated, plan and execute a MariaDB upgrade. This typically involves backing up your data, installing the new version, and restoring your data. Refer to the official MariaDB documentation for detailed upgrade procedures for your operating system.

Example command to check package version (Debian/Ubuntu):
sudo apt-cache policy mariadb-server

Example command to check package version (RHEL/CentOS):
sudo yum info mariadb-server

4. Use `ALTER TABLE` to Correct Existing Table easy

Modify an existing table's column definition to fix an invalid default value.

1

Identify the table and column that has the invalid default value. You can often find this information in the error message or by inspecting the table structure.

To inspect table structure:
SHOW CREATE TABLE your_table_name;

2

Use the `ALTER TABLE` statement to modify the column's default value. This can involve changing the value, removing it, or setting it to `NULL`.

Example: Changing an invalid string default for an INT column to 0
ALTER TABLE your_table_name
MODIFY COLUMN your_column_name INT DEFAULT 0;

Example: Removing a default value
ALTER TABLE your_table_name
ALTER COLUMN your_column_name DROP DEFAULT;

Example: Setting default to NULL for a VARCHAR column that previously had an invalid default
ALTER TABLE your_table_name
MODIFY COLUMN your_column_name VARCHAR(255) DEFAULT NULL;

🔗

Related Errors

5 related errors

MariaDB Error 1067: Invalid Column Default Value