How to Fix MySQL Error 1067: Invalid Default Value for Column

ERROR 1067 (42000): Invalid default value for '<column_name>' is raised when a DEFAULT clause in a CREATE TABLE or ALTER TABLE statement specifies a value that is not valid for the column's data type or is prohibited under the current SQL mode. The error symbol is ER_INVALID_DEFAULT.

Impact

This error is a DDL-level failure — the CREATE TABLE or ALTER TABLE statement is rejected entirely and no schema change is made. No data is lost or corrupted, but the operation does not complete. Applications that run migrations on startup (Rails, Django, Flyway, Liquibase, and similar tools) will fail to boot if they encounter this error during a pending migration.

The error appears most commonly when migrating a schema from a MySQL instance running in a permissive SQL mode to one running with STRICT_TRANS_TABLES or STRICT_ALL_TABLES enabled, or when upgrading from MySQL 5.6 to 5.7 or later, which tightened default validation rules significantly.

Common Causes

Zero date as default for a DATE, DATETIME, or TIMESTAMP column. The value '0000-00-00' or '0000-00-00 00:00:00' is rejected when the NO_ZERO_DATE SQL mode flag is active (included in strict mode since MySQL 5.7).
DATETIME or TIMESTAMP column defaulting to NOW() or CURRENT_TIMESTAMP on MySQL 5.5 and earlier. Only TIMESTAMP columns could use CURRENT_TIMESTAMP as a default before MySQL 5.6.5; applying it to a DATETIME column raises 1067 on older versions.
A string literal default that is too long for the column. For example, a DEFAULT 'active' on a CHAR(3) column fails because the value exceeds the declared length.
A numeric default outside the column's range. Assigning DEFAULT 300 to a TINYINT UNSIGNED (max 255) or DEFAULT -1 to an INT UNSIGNED column violates the type constraint.
A non-constant expression used as a default on MySQL 5.x. MySQL 5.x requires column defaults to be literal constants (except CURRENT_TIMESTAMP). Using a function call such as DEFAULT UUID() or DEFAULT (RAND()) raises this error. MySQL 8.0.13+ allows parenthesized expressions as defaults.
Importing a dump with sql_mode differences. A dump generated on a server with sql_mode='' may include zero-date defaults that are invalid on a target server with strict mode enabled.

Troubleshooting and Resolution Steps

Identify the exact column and default value causing the error.

The error message names the column: ERROR 1067 (42000): Invalid default value for 'created_at'. Inspect the DDL statement and locate the DEFAULT clause for that column.
Check the active SQL mode on both source and target servers.
```
SELECT @@sql_mode;
```
Compare the output between environments. Look for STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and NO_ZERO_DATE.
Fix zero-date defaults. Replace '0000-00-00' or '0000-00-00 00:00:00' with NULL (if the column is nullable) or a valid sentinel date such as '1970-01-01'. Alternatively, allow NULL and drop the default entirely.
```
-- Before (fails under NO_ZERO_DATE)
ALTER TABLE orders ADD COLUMN shipped_at DATETIME DEFAULT '0000-00-00 00:00:00';

-- After
ALTER TABLE orders ADD COLUMN shipped_at DATETIME NULL DEFAULT NULL;
```
Fix CURRENT_TIMESTAMP on DATETIME columns (MySQL < 5.6.5). Use a TIMESTAMP column instead, or upgrade MySQL. On 5.6.5+ the following works:
```
ALTER TABLE events ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;
```
Fix expression defaults on MySQL 5.x. Replace function-call defaults with application-side logic or triggers. On MySQL 8.0.13+, wrap the expression in parentheses:
```
-- MySQL 8.0.13+ only
CREATE TABLE items (
  id CHAR(36) DEFAULT (UUID()),
  name VARCHAR(100)
);
```

Temporarily relax SQL mode when replaying a legacy dump (use with caution — fix the dump afterward):

SET SESSION sql_mode = '';
SOURCE /path/to/legacy_dump.sql;
SET SESSION sql_mode = @@GLOBAL.sql_mode;

Inspect existing table definitions for columns that may have been created with zero-date defaults:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND COLUMN_DEFAULT IN ('0000-00-00', '0000-00-00 00:00:00');

Additional Information

Related error codes: Error 1292 (ER_TRUNCATED_WRONG_VALUE) is raised for invalid date values in INSERT/UPDATE statements under strict mode, as opposed to 1067 which applies at the DDL level. Error 1101 (ER_BLOB_CANT_HAVE_DEFAULT) is raised when a BLOB, TEXT, or JSON column is given a non-NULL default.
MySQL 5.7 stricter defaults: MySQL 5.7 changed the default sql_mode to include STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, and ERROR_FOR_DIVISION_BY_ZERO. Schemas that ran cleanly on 5.6 frequently hit error 1067 after upgrading.
MySQL 8.0 expression defaults: MySQL 8.0.13 introduced support for expressions (wrapped in parentheses) as column defaults, removing a common source of this error for generated values.
ORM behavior: Hibernate, ActiveRecord, and Django ORM do not catch 1067 specially — the error surfaces as a generic database exception during migrate or schema:update. Check your migration logs for the exact column name.
mysqldump and --compatible flag: When dumping from a strict-mode server, mysqldump includes the source sql_mode in the dump header. If you need the dump to be replayed on a permissive server (or vice versa), you may need to edit or override the SET sql_mode line near the top of the dump file.

Frequently Asked Questions

Why did this start failing after upgrading from MySQL 5.6 to 5.7?

MySQL 5.7 changed the default sql_mode to enable strict mode and NO_ZERO_DATE. Zero-date defaults ('0000-00-00', '0000-00-00 00:00:00') that were silently accepted on 5.6 are now rejected. The fix is to replace zero-date defaults with NULL or a valid date value in your schema.

Can I just disable strict mode to make the error go away?

You can set sql_mode = '' or remove NO_ZERO_DATE and STRICT_TRANS_TABLES, but this is not recommended for production. It hides real data integrity problems. The correct fix is to update the DDL to use valid defaults that work under any mode.

The error says "Invalid default value for 'updated_at'" but I never set a default — why?

Some ORMs or migration tools auto-generate DEFAULT '0000-00-00 00:00:00' for DATETIME or TIMESTAMP columns when no explicit default is specified. Check the generated SQL in your migration tool's output or log before the error, and configure the ORM to emit DEFAULT NULL or DEFAULT CURRENT_TIMESTAMP instead.

Does this error affect INSERT or UPDATE statements?

No. Error 1067 is raised only during DDL operations (CREATE TABLE, ALTER TABLE). Invalid values in INSERT or UPDATE statements raise different errors such as 1292 or 1366, depending on the column type and SQL mode.