MySQL refuses a DATE, DATETIME or TIMESTAMP column default
ERROR 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The column is declared like DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'The default is the zero date 0000-00-00Fix - 2
The 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
The failing CREATE TABLE sits inside a .sql dump you did not writeAn old dump from a laxer server is imported into strict MySQLFix
- 1
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\GConfirmed by dev.mysql.com
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\GConfirmed by dev.mysql.com
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses or trims a value: Data truncated for column
Data truncated for column 'status' at row 1
MySQL reports truncation when a value does not fit the column.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix