Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses a DATE, DATETIME or TIMESTAMP column default

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

Short answer

MySQL 8 runs with strict mode plus NO_ZERO_DATE and NO_ZERO_IN_DATE on by default. A default such as '0000-00-00' or '2010-00-00' is then an error. Use NULL, a real date, or CURRENT_TIMESTAMP as the default. For an old dump that you cannot edit, relax the modes for that import session only.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the SQL mode the server runs with. If it lists NO_ZERO_DATE or NO_ZERO_IN_DATE together with a strict mode, a zero-date default will be rejected.

    SELECT @@GLOBAL.sql_mode;
    SELECT @@SESSION.sql_mode;
  2. 2Match what you see, then jump to its fix

    1. 1The column is declared like DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'The default is the zero date 0000-00-00Fix
    2. 2The default is a date like '2010-00-00' or '2010-01-00'The default has a zero month or day, such as 2010-00-00Fix
    3. 3The failing CREATE TABLE sits inside a .sql dump you did not writeAn old dump from a laxer server is imported into strict MySQLFix

What does the error look like?

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

Cause 1 of 3

The default is the zero date 0000-00-00

1Check

With NO_ZERO_DATE and strict mode both on, which is the 8.4 default, a zero date is not permitted and the statement ends in an error.

SELECT @@SESSION.sql_mode LIKE '%NO_ZERO_DATE%' AS no_zero_date;

2Fix

Pick a default that is a real value: NULL for unknown, CURRENT_TIMESTAMP for creation time. For TIMESTAMP and DATETIME columns, CURRENT_TIMESTAMP needs no parentheses.

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

3Verify

The CREATE TABLE succeeds and the default shows in the definition.

SHOW CREATE TABLE posts\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The default has a zero month or day, such as 2010-00-00

1Check

NO_ZERO_IN_DATE covers dates with a non-zero year but a zero month or day. With strict mode, CREATE TABLE reports an error for such a default; without strict mode it is only a warning.

SELECT @@SESSION.sql_mode LIKE '%NO_ZERO_IN_DATE%' AS no_zero_in_date;

2Fix

Use a complete valid date, or NULL.

ALTER TABLE events
  MODIFY starts_on DATE NULL DEFAULT NULL;

3Verify

SHOW CREATE TABLE lists the new default without an error.

SHOW CREATE TABLE events\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

An old dump from a laxer server is imported into strict MySQL

1Check

Old dumps often carry zero-date defaults. The manual states the modes are meant to work with strict mode and are deprecated, so keeping the defaults of the new server is the right long-term setting.

SELECT @@GLOBAL.sql_mode;

2Fix

Best: edit the dump's defaults. If that is not possible, relax the mode in the importing session only, then restore. The session value affects only that client.

SET SESSION sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SOURCE dump.sql;
SET SESSION sql_mode = DEFAULT;

3Verify

The import completes, then check the mode is back to the server default.

SELECT @@SESSION.sql_mode;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use NULL or CURRENT_TIMESTAMP for 'not set yet' dates; never a zero date.
  • Keep staging and production on the same sql_mode so a schema that works on one works on the other.
  • Do not switch the whole server to a looser mode just to get past one import.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.