MySQL Column Default Value Expression Invalid Error After Migration

An "invalid default value" error after a migration usually means either your MySQL version's rules around what counts as a valid column default changed, or the migration is attempting something that was never actually valid syntax to begin with but happened to be silently accepted (or simply not previously attempted) before now.

The Problem

A migration adding or modifying a column with a default value fails:

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

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'description' can't have a default value

Why It Happens

Several distinct rule changes and constraints commonly produce this category of error:

• Historically, TEXT, BLOB, GEOMETRY, and JSON column types couldn't have a literal default value at all in MySQL (this restriction was relaxed for expression-based defaults starting in MySQL 8.0.13, but plain literal defaults for these types remain unsupported even in modern versions)
• A stricter SQL mode (particularly NO_ZERO_DATE or STRICT_TRANS_TABLES) rejecting a default value that was previously silently accepted or auto-corrected under a more lenient mode — a classic example being a zero date like '0000-00-00' as a default, which strict mode explicitly disallows
• A migration written for or generated against a different MySQL version than what's actually running in the target environment, using syntax or default-value patterns that aren't supported by the specific, actual server version being migrated against
• An expression-based default (using DEFAULT (expression) syntax, available from MySQL 8.0.13 onward) being used against an older MySQL version, or against a compatible-but-not-identical database like MariaDB with different expression-default support

The Fix

For TEXT/BLOB/JSON columns that need a "default" value, the standard approach is having your application explicitly provide the intended default value at insert time, rather than relying on the database column definition itself:

-- Not supported: literal default on TEXT
-- description TEXT DEFAULT 'no description provided'

-- Instead: handle the default in application code
INSERT INTO products (name, description) VALUES ('Widget', 'no description provided');

If you're on MySQL 8.0.13 or later and specifically need an expression-based default (like a computed value, rather than a static literal), use the explicit expression-default syntax, which is supported for these column types starting in that version:

ALTER TABLE products
ADD COLUMN metadata JSON DEFAULT (JSON_OBJECT());

Confirm your target MySQL version actually supports this syntax before relying on it, since it's a relatively specific, version-gated feature rather than universally available across all MySQL versions your migration might eventually run against:

SELECT VERSION();

For date-related default value errors under strict mode, use a genuinely valid default rather than a zero-date placeholder — CURRENT_TIMESTAMP is usually the correct choice for a "creation time" style default, rather than attempting to use a sentinel zero-date value that strict mode now explicitly rejects:

ALTER TABLE orders
MODIFY COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

If a migration was written against a different MySQL version or a different database system entirely and needs to run against your actual target server, review the specific syntax for compatibility rather than assuming it will translate directly — checking your target MySQL version's own documentation for supported default value syntax for the specific column type involved clarifies exactly what's valid in your actual environment, rather than what might be valid in whatever environment the migration was originally written or generated for.

If you're using a migration framework or ORM that generates this SQL automatically, check whether it has version-specific configuration or generates different SQL based on a target database version setting, since some frameworks generate SQL assuming a specific baseline version's feature set, which may not match your actual production MySQL version if there's a mismatch between development and production database versions:

# Example: many ORMs have explicit target-version configuration
# affecting exactly what SQL syntax gets generated for defaults

Still Not Working?

If the error persists despite using what appears to be correct, version-appropriate syntax, confirm you're not accidentally running the migration against a different database engine than genuine MySQL — MariaDB, despite broad compatibility with MySQL, has its own separate feature timeline and support for expression-based defaults and certain strict-mode behaviors, and a migration correctly written for MySQL 8.0's specific feature set might not translate identically to a MariaDB target, even when the two are often used somewhat interchangeably in casual conversation about "MySQL-compatible" databases.