EC
1067
MariaDBERRORNotableDDLHIGH confidence

Invalid default value for column

What this means

Error 1067 (SQLSTATE 42000) is raised during CREATE TABLE or ALTER TABLE when a column's DEFAULT value is incompatible with its data type, violates a constraint, or is a non-deterministic function that is not permitted as a default in that context.

Why it happens
  1. 1DEFAULT value literal does not match the column's data type (e.g., DEFAULT 'abc' on an INT column)
  2. 2DEFAULT NOW() or DEFAULT CURRENT_TIMESTAMP used on a DATE column in older MySQL/MariaDB versions
  3. 3Strict SQL mode rejects values that would otherwise be silently coerced
  4. 4DATETIME column has DEFAULT '0000-00-00 00:00:00' with NO_ZERO_DATE mode active
How to reproduce

Creating a table with an invalid default value.

trigger — this will error
trigger — this will error
CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_date DATE DEFAULT NOW()  -- NOW() is not valid for DATE default
);

expected output

ERROR 1067 (42000): Invalid default value for 'event_date'

Fix 1

Use a valid literal default for DATE columns

WHEN When the column type is DATE or another type that does not accept NOW().

Use a valid literal default for DATE columns
CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_date DATE DEFAULT (CURRENT_DATE)  -- MariaDB 10.2.1+ expression default
);
-- Or simply omit the default and set it in application logic

Why this works

MariaDB 10.2.1+ supports expression defaults enclosed in parentheses. Older versions require a literal or NULL.

Fix 2

Replace zero-date default

WHEN When '0000-00-00' is rejected by NO_ZERO_DATE mode.

Replace zero-date default
-- Change default to NULL (if column is nullable):
ALTER TABLE events MODIFY event_date DATE NULL DEFAULT NULL;

Why this works

NULL is always a valid default for nullable columns and avoids zero-date issues.

What not to do

✕ Disable strict SQL mode to work around this

Strict mode protects data integrity; disabling it masks real data type mismatches.

Version notes
MariaDB 10.2.1+

Expression defaults (parenthesised expressions) are supported, allowing DEFAULT (CURRENT_DATE), DEFAULT (UUID()), etc.

MySQL 8.0.13+

Expression defaults also supported.

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors