MySQL raises error 1067 (ER_INVALID_DEFAULT) when a column definition contains a default value that conflicts with its data type, constraints, or SQL mode settings.
MySQL Error 1067: ER_INVALID_DEFAULT occurs when a column’s DEFAULT clause is incompatible with its data type or the server’s SQL mode. Check the column type, remove or correct the DEFAULT value, and re-run the ALTER or CREATE statement to resolve the issue.
Invalid default value for '%s'
MySQL throws "Error 1067: Invalid default value for 'column'" while executing CREATE TABLE or ALTER TABLE statements. The server rejects the column definition because its DEFAULT clause violates data-type rules or active SQL modes such as STRICT or NO_ZERO_DATE.
This schema definition error stops table creation or modification, blocking deployments and migrations.
Resolving it quickly keeps CI pipelines and applications healthy.
The error fires when MySQL detects a mismatch between the declared data type and the supplied default.
STRICT mode tightens checks, so formerly accepted defaults may now fail.
Typical triggers include invalid date strings, zero dates under NO_ZERO_DATE, non-numeric strings in numeric columns, functions not allowed as defaults before MySQL 8.0, and mismatched length or collation settings.
Identify the offending column in the server output.
Verify its data type and NULL policy, then supply a literal value that conforms to both or drop the DEFAULT clause entirely.
For date columns, replace "0000-00-00" with a real date like "1970-01-01" or allow NULL. For TIMESTAMP or DATETIME in MySQL 8.0+, use CURRENT_TIMESTAMP if permitted.
Auto-increment primary keys should not carry a default. Remove DEFAULT from INT AUTO_INCREMENT columns.
VARCHAR columns cannot default to values longer than their declared length.
Adjust the length or shorten the default string.
Always test DDL in a development clone running the same SQL modes as production.
Use CHECK constraints or domain tables for validated defaults.
Galaxy’s schema-aware autocomplete flags invalid DEFAULT clauses while you type, preventing commits that would fail in CI.
Error 1293 (HY000) - Incorrect table definition can appear when foreign key defaults misalign.
Error 1366 (22007) - Incorrect string value may accompany invalid default character data. Fixing the default often resolves both.
.
You can remove STRICT modes from sql_mode in my.cnf, but this risks data quality. Prefer fixing schema definitions.
Yes. MySQL 8.0 lets you use expressions like CURRENT_TIMESTAMP or JSON_OBJECT() as defaults under certain rules.
Galaxy’s parser knows SQL modes and column metadata, so it warns about incompatible defaults as you type, saving round-trips.
Depending on storage engine and MySQL version, some ALTER statements perform in-place changes while others copy the table. Check EXPLAIN ALTER output.
.avif)
.avif){kind=logo}
