// MySQL error reference

MySQL error 1067: Invalid default value

A column's DEFAULT isn't valid for its type or the current SQL mode, typically DEFAULT '0000-00-00' on a DATETIME or TIMESTAMP column.

Error number
1067
Name
ER_INVALID_DEFAULT
SQLSTATE
42000
Category
Data values
Error message
Invalid default value for '%s'

What it means

MySQL checks a column's default when you create or alter the table. 1067 names the column whose default is invalid. The usual case is an old schema with zero-date defaults, which NO_ZERO_DATE and strict mode (both on by default) reject.

Common causes

  • DEFAULT '0000-00-00 00:00:00' on DATETIME or TIMESTAMP columns from an older dump.
  • A DEFAULT expression on a type or version that doesn't allow it (expression defaults need parentheses and MySQL 8.0.13 or later).
  • A default longer than the column or outside an ENUM list.

How to fix it

  1. Use a valid default, or none.

    CREATE TABLE events (
        id INT PRIMARY KEY,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        deleted_at DATETIME NULL DEFAULT NULL
    );
  2. For an old dump, replace zero-date defaults with NULL or CURRENT_TIMESTAMP before importing.

Related errors

More data values errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.