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
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'onDATETIMEorTIMESTAMPcolumns from an older dump.- A
DEFAULTexpression 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
ENUMlist.
How to fix it
-
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 ); -
For an old dump, replace zero-date defaults with
NULLorCURRENT_TIMESTAMPbefore importing.
Related errors
More data values errors
1366Incorrect value for column1406Data too long for column1264Out of range value for column1265Data truncated for column
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.
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.