🚨 MySQL Error: 'ERROR 1067 (42000): Invalid default value' — Cause, Fix, and Troubleshooting Guide

Overview

ERROR 1067 (42000) is raised at DDL time when a column’s DEFAULT clause is not legal for that column’s type under the server’s current sql_mode:

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

The most common trigger is a DATE/DATETIME/TIMESTAMP column defaulting to a zero date like '0000-00-00 00:00:00' on a server running NO_ZERO_DATE/NO_ZERO_IN_DATE/STRICT_TRANS_TABLES — the defaults on MySQL 5.7+ and 8.0. A dump created on an older, permissive server fails to import on a strict one.

Symptoms

• A CREATE TABLE or ALTER TABLE fails, always naming one column.
• A schema/dump that imported fine on MySQL 5.6 fails on 5.7/8.0.
• ORM auto-migrations fail after a MySQL upgrade.
• The column is a date/time, or a TEXT/BLOB/JSON column with a literal default.

Common Root Causes

1. Zero-date default under strict sql_mode

CREATE TABLE events (
  id INT PRIMARY KEY,
  created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
);

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

NO_ZERO_DATE (on by default) forbids the all-zero placeholder.

2. A literal DEFAULT on TEXT / BLOB / JSON / GEOMETRY

Before MySQL 8.0.13 these types cannot have a literal default at all; a DEFAULT '' on a TEXT column raises 1067.

3. DEFAULT CURRENT_TIMESTAMP on the wrong type

On older MySQL only TIMESTAMP (and later DATETIME) accept DEFAULT CURRENT_TIMESTAMP; using it on a DATE or INT fails.

4. Default value out of range for the type

TINYINT with DEFAULT 999, or an ENUM default that is not one of the listed members.

How to diagnose

Check the active sql_mode — it dictates which defaults are legal:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

+------------------------------------------------------------------+
| @@SESSION.sql_mode                                               |
+------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_...  |
+------------------------------------------------------------------+

Inspect the offending column’s type and version:

SELECT VERSION();
SHOW CREATE TABLE events\G

Fixes

Prefer fixing the schema over loosening sql_mode. For timestamps, use a real default or make the column nullable:

CREATE TABLE events (
  id INT PRIMARY KEY,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

For TEXT/BLOB on older servers, drop the literal default (the column is implicitly nullable) or upgrade to 8.0.13+ where DEFAULT (expression) is allowed:

ALTER TABLE notes MODIFY body TEXT NULL;

If you must import a legacy dump unchanged, relax the mode for that session only:

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
-- run the import, then restore strict mode

Persisting a relaxed mode (last resort) in my.cnf:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

What to watch out for

• Removing NO_ZERO_DATE globally to force an old dump through re-introduces zero-date bugs across the whole server — scope any relaxation to the import session.
• CURRENT_TIMESTAMP as a default only works on TIMESTAMP/DATETIME; do not paste it onto DATE columns.
• Application-level “empty date” conventions (0000-00-00) should become NULL; update code that reads those rows before migrating.
• After a major-version upgrade, dry-run your migrations against the new server — 1067 surfaces at deploy time otherwise.

Related

• ERROR 1064: SQL syntax error — the general DDL/DML parse-error you will also hit while editing schema.
• ERROR 1292: Incorrect datetime value — the runtime counterpart when inserting bad dates under strict mode.
• ERROR 1055: only_full_group_by — another strict-sql_mode behavior change that breaks legacy schemas.