MySQL 1067: Invalid default value for 'created_at'

What it means

Every column’s DEFAULT must be a value the column could hold. Error 1067 means one isn’t, so the CREATE TABLE or ALTER TABLE is refused. The message names the column.

The usual culprit is the zero date, '0000-00-00 00:00:00'. Older MySQL versions allowed it and many schemas still use it as the default for created_at. MySQL 8’s default SQL mode includes NO_ZERO_DATE, NO_ZERO_IN_DATE and strict mode, which make a zero date invalid, and so a default of one is invalid too.

That catches old tables as well. A table created back then keeps its zero default, and any later ALTER TABLE on it, even one that adds an unrelated column, rebuilds the definition and fails on created_at. So does CREATE TABLE … LIKE.

Common causes

A zero-date default on a DATETIME, DATE or TIMESTAMP column: in a new statement, in a dump from MySQL 5.x, or in an existing table you’re altering.
A default that doesn’t fit the type: 'pending' for a varchar(3), 'abc' for an int, a value missing from an ENUM, or an impossible date such as '2026-02-30'.
A fractional-seconds mismatch: timestamp(3) DEFAULT CURRENT_TIMESTAMP. On MySQL the precision must match: CURRENT_TIMESTAMP(3).
CURRENT_TIMESTAMP on a DATE column, which MySQL allows only on DATETIME and TIMESTAMP.

(MySQL refuses a plain default on a TEXT, BLOB or JSON column with a different error, 1101; an expression default in brackets, DEFAULT ('x'), works on 8.0.13 and later.)

How to fix it

Replace the zero default

Choose what a missing value should be: NULL, or the time the row was written.

ALTER TABLE posts MODIFY created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
-- or
ALTER TABLE posts MODIFY created_at datetime NULL DEFAULT NULL;

Restate everything else about the column in MODIFY (type, nullability, comment), or it’s lost. In the test below this MODIFY worked even on a table whose rows held zero dates, and after it, other ALTER TABLE statements on the table worked again.

Clean up zero dates in the rows

Under strict mode you can’t write the zero date in a query (WHERE created_at = '0000-00-00' fails with error 1525 or 1292), but you can compare with the earliest real date:

UPDATE posts SET created_at = NULL WHERE created_at < '1000-01-01';

That needs the column to allow NULL; otherwise pick a real date that means “unknown” in your application.

Fix the type or the value

Make the default fit: a longer varchar, a value that’s in the ENUM list, a real date, or CURRENT_TIMESTAMP(3) for a timestamp(3). For a DATE that should default to today, use an expression default: d date DEFAULT (CURRENT_DATE).

Importing an old dump

Edit the CREATE TABLE statements in the dump to use one of the defaults above. Relaxing the SQL mode for the import session (SET SESSION sql_mode = … without NO_ZERO_DATE) gets the tables created, but they keep the zero default, and the next ALTER TABLE under the normal mode fails again.

Reproduce it

On MySQL 8.4.11, whose default sql_mode is ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION:

CREATE TABLE posts (id int PRIMARY KEY, created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE posts3 (id int PRIMARY KEY, d date NOT NULL DEFAULT '2026-02-30');
CREATE TABLE posts4 (id int PRIMARY KEY, n int DEFAULT 'abc');
CREATE TABLE posts7 (id int PRIMARY KEY, created_at date DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE posts10 (id int PRIMARY KEY, status varchar(3) DEFAULT 'pending');
CREATE TABLE posts11 (id int PRIMARY KEY, created_at timestamp(3) DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE posts12 (id int PRIMARY KEY, e enum('a','b') DEFAULT 'c');

ERROR 1067 (42000): Invalid default value for 'created_at'
ERROR 1067 (42000): Invalid default value for 'd'
ERROR 1067 (42000): Invalid default value for 'n'
ERROR 1067 (42000): Invalid default value for 'created_at'
ERROR 1067 (42000): Invalid default value for 'status'
ERROR 1067 (42000): Invalid default value for 'created_at'
ERROR 1067 (42000): Invalid default value for 'e'

Then the old-table case: with NO_ZERO_DATE and NO_ZERO_IN_DATE removed from the session’s sql_mode, the first CREATE TABLE posts succeeded. Back on the default mode, an unrelated change and a copy both failed:

ALTER TABLE posts ADD COLUMN title varchar(100);
CREATE TABLE posts_copy LIKE posts;

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

After ALTER TABLE posts MODIFY created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, adding the column worked.

MariaDB 11.4.13’s default sql_mode doesn’t include NO_ZERO_DATE, so it accepted the zero defaults, CURRENT_TIMESTAMP on a DATE, and timestamp(3) DEFAULT CURRENT_TIMESTAMP. It refused the impossible date, 'abc', 'pending' and the ENUM value with the same 1067. With NO_ZERO_DATE added to the session, a table with a zero default failed ADD COLUMN with the same error, as on MySQL.

In Inlet

Inlet’s structure editor changes a column’s default and nullability in one ALTER TABLE, shows the DDL before it runs, and notes that MySQL may copy the table while it runs. When a statement fails, Inlet shows the error and links to this page.

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