MySQL 1364 Field Has No Default Value — Fix

Your deploy just went out, the logs look clean, and then support pings you: new signups are failing. The database error is terse — ERROR 1364 (HY000): Field 'plan' doesn't have a default value — and it names a column your code never touches. This error is MySQL's strict mode doing its job. When STRICT_TRANS_TABLES is on (the default since 5.7), MySQL refuses to invent values for NOT NULL columns you left out of an INSERT. Older servers would silently stuff in an empty string or zero and move on; modern MySQL throws 1364 instead. That's a gift, not a bug — silent implicit defaults corrupt data in ways you discover months later.

The trap is that 1364 rarely means what it first appears to say. Engineers read it as a schema problem and reach for ALTER TABLE, when the real culprit is often a query that forgot a column, an ORM that silently dropped a field, or a staging server whose sql_mode doesn't match production. Each cause has a different fix, and picking the wrong one either masks the error or rewrites a schema that was fine.

This guide walks through all five causes in order: reading the error line precisely, understanding strict mode, finding NOT NULL columns with no default, fixing the INSERT, choosing between a DEFAULT and relaxing sql_mode, and reconciling environment differences. You'll leave knowing exactly which fix your situation calls for.

Read the Error Line Before You Touch Anything

MySQL's error line is denser than it looks: ERROR 1364 (HY000): Field 'plan' doesn't have a default value. The number 1364 is the MySQL-specific error code you'll grep for in logs. HY000 is the generic SQLSTATE class — it tells you almost nothing beyond 'general error', so don't chase it. The quoted identifier is the column MySQL wanted a value for, and the table is conspicuously absent — you have to know which INSERT was running to map the column to its table.

The phrase 'doesn't have a default value' is precise: the column is NOT NULL and its definition carries no DEFAULT clause, so MySQL has nothing to fall back on. Under strict mode that's fatal for the statement. Without strict mode the same statement succeeds with a warning and an implicit default (empty string, zero, or a zero date), which is why veterans of older MySQL versions meet 1364 for the first time after an upgrade — their queries were always incomplete, and the server stopped covering for them.

Your first move is always SHOW CREATE TABLE for the target table. Read each column definition and mark every one that's NOT NULL with no DEFAULT. Then put your failing INSERT beside it and check off each required column. The gap between those two lists is your bug in nine cases out of ten. The tenth case — where the INSERT looks complete — means something between your code and MySQL rewrote the query, which is where ORM logging comes in.

-- Which table/columns can throw 1364? Inspect the definition first.
SHOW CREATE TABLE signups;

-- Confirm strict mode is what makes the omission fatal.
SELECT @@SESSION.sql_mode AS session_mode, @@GLOBAL.sql_mode AS global_mode;

-- Failing statement: 'plan' is NOT NULL with no DEFAULT.
INSERT INTO signups (email) VALUES ('a@example.com');
-- ERROR 1364 (HY000): Field 'plan' doesn't have a default value

STRICT_TRANS_TABLES: The Tripwire Behind 1364

STRICT_TRANS_TABLES is a sql_mode flag that tells MySQL to reject bad writes instead of coercing them. With it on, a missing value for a NOT NULL column aborts the statement with 1364; an out-of-range value or a truncated string aborts with its own error. With it off, MySQL issues a warning, invents an implicit default, and writes a row you didn't ask for — empty strings where you expected data, zeroes where you expected measurements, zero-dates where you expected timestamps.

Since MySQL 5.7, strict mode is on by default in every official distribution and Docker image. That's why 1364 clusters around upgrades and new environments: code written against a lenient 5.6-era server, a Homebrew install with custom my.cnf, or a shared host that stripped strict flags suddenly faces a server that enforces the rules. The queries didn't change; the referee did.

This is working as designed, and the MySQL team has only tightened it since — 8.0 keeps strict defaults and adds even less tolerance for zero dates. Treat 1364 as a data-quality alarm, not an obstacle. Every implicit default it blocks is a corrupt row you don't have to clean up later. Teams that disable strict mode to silence one error invariably rediscover this the hard way when a truncated email or a zeroed-out price reaches a customer.

Check the mode with SELECT @@SESSION.sql_mode and note that session and global values can differ — connectors, ORMs, and init scripts can and do override it per connection.

NOT NULL Without DEFAULT: Find Every Risky Column

A column throws 1364 only when two conditions hold at once: it's NOT NULL and it has no DEFAULT. Either condition alone is harmless — a nullable column accepts the omission as NULL, and a NOT NULL column with DEFAULT fills in the fallback. The dangerous combination usually arrives via migration: someone adds plan VARCHAR(20) NOT NULL to a live table without a DEFAULT, or scaffolding generates a model whose migration omits defaults entirely.

MySQL's implicit-default rules make this sneakier than it sounds. On a lenient server, omitting the column appears to work — the row lands with an empty string or zero — so the missing DEFAULT goes unnoticed for months. The day anyone enables strict mode, upgrades MySQL, or points the app at a properly configured replica, every one of those INSERTs starts failing at once. The schema was always broken; strictness just exposed it.

TIMESTAMP columns deserve special attention because they historically behaved differently: the first TIMESTAMP in a table auto-defaulted to CURRENT_TIMESTAMP, which masked omissions. MySQL 5.6.5+ removed most of that magic behind explicit_defaults_for_timestamp, so TIMESTAMP columns now throw 1364 like everything else. If your legacy schema leans on implicit timestamp defaults, an upgrade will surface them all as 1364s.

Audit the whole table with one INFORMATION_SCHEMA query that lists every NOT NULL column lacking a default. Run it after every migration — it takes a second and catches the exact columns that will fail next.

-- Find every column in a table that can throw 1364.
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'signups'
  AND IS_NULLABLE = 'NO'
  AND COLUMN_DEFAULT IS NULL;

-- Same audit across a whole schema, grouped by table.
SELECT TABLE_NAME, GROUP_CONCAT(COLUMN_NAME) AS risky_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND IS_NULLABLE = 'NO'
  AND COLUMN_DEFAULT IS NULL
  AND EXTRA NOT LIKE '%auto_increment%'
GROUP BY TABLE_NAME;

Fix the INSERT: Name Every Required Column

The most common 1364 has nothing wrong with the schema — the INSERT is just incomplete. INSERT INTO signups (email) VALUES (...) skips plan, and strict MySQL refuses to guess. The fix at the query level is to name every required column and supply a value: INSERT INTO signups (email, plan) VALUES ('a@example.com', 'free'). Explicit column lists also protect you against column-order changes from later migrations, which bare VALUES lists silently absorb until types shift and stranger errors appear.

ORMs add a layer of indirection that makes this harder to see. You set user.plan = 'free' in code, but a mass-assignment guard, a serializer field list, or a renamed form parameter strips it before the SQL is built. The generated INSERT omits the column, MySQL throws 1364, and the application log shows a model that clearly had the value. The only reliable evidence is the SQL itself — enable query logging in staging and read the actual INSERT.

Bulk paths deserve the same scrutiny. LOAD DATA, INSERT ... SELECT, and multi-row VALUES lists all throw 1364 when a required target column has no source. With INSERT ... SELECT, the column mapping is positional, so a SELECT that returns three columns into a four-column target fails even when names look right. Always write the target column list explicitly and count both sides.

Make the explicit INSERT your default habit: list columns, bind values, and let code review catch omissions before strict mode has to.

-- Minimal reproduction: NOT NULL with no DEFAULT, INSERT omits it.
CREATE TABLE signups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  plan VARCHAR(20) NOT NULL
);

-- This fails under strict mode with ERROR 1364.
-- INSERT INTO signups (email) VALUES ('a@example.com');

-- Fix at the query level: name every required column.
INSERT INTO signups (email, plan) VALUES ('a@example.com', 'free');
SELECT * FROM signups;

Add a DEFAULT vs Relax sql_mode: Choose Correctly

When the INSERT is correct and complete yet 1364 persists, the schema needs a DEFAULT. ALTER TABLE signups ALTER COLUMN plan SET DEFAULT 'free' gives MySQL an explicit fallback, so any future INSERT that omits plan succeeds deterministically instead of depending on server mood. For new columns, prefer defining the default inline: ADD COLUMN plan VARCHAR(20) NOT NULL DEFAULT 'free'. Defaults are documentation — they state what the value means when nobody says otherwise.

The alternative — removing STRICT_TRANS_TABLES from sql_mode — is the most damaging popular advice for this error. It doesn't fix anything; it reverts MySQL to guessing. Truncated strings land silently, invalid dates become zero-dates, and the next strict replica or upgraded server resurrects every hidden bug at once. Worse, sql_mode can be set globally, per session, and in client flags, so a 'quick' relaxation in one place creates behavior that differs by connection and vanishes on restart.

There's exactly one legitimate use for touching sql_mode around 1364: diagnosis. SET SESSION sql_mode to a lax value on a scratch connection to confirm strictness is the trigger, then revert and fix properly. Never SET GLOBAL on production, never bake the relaxation into my.cnf, and never let a Stack Overflow snippet talk you into sql_mode=''.

Choose defaults deliberately: a sentinel like 'free' or 0 that downstream code already handles. A default nobody recognizes is just corruption with a stamp on it.

-- Lasting schema fix: give the column an explicit DEFAULT.
ALTER TABLE signups ALTER COLUMN plan SET DEFAULT 'free';

-- Equivalent for new columns: declare the default inline.
-- ALTER TABLE signups ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- Verify: omitting the column now uses the default, no error.
INSERT INTO signups (email) VALUES ('b@example.com');
SELECT email, plan FROM signups;

-- Diagnose-only: confirm strictness is the trigger on a SCRATCH session.
-- SET SESSION sql_mode = '';
-- Remember to revert: SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

Reconcile Environments: Dev, Staging, and Prod

Error 1364 loves environment gaps because sql_mode is configuration, not code. Your laptop's Homebrew MySQL, the CI container's official image, staging's managed database, and production's tuned instance can each carry a different sql_mode — and the same INSERT passes on three and fails on one. Docker images default to strict, while some managed providers and legacy AMIs historically shipped laxer modes, so containerizing an old app is a classic 1364 trigger.

Upgrades are the other great revealer. Moving from 5.6 to 5.7+ flips strict on by default; moving to 8.0 tightens timestamp handling on top. The queries didn't change — the contract did. Any upgrade runbook should include a strict-mode audit: dump the schema, list NOT NULL-without-DEFAULT columns, and run the app's write paths against a strict staging instance before cutover.

Reconcile environments with three habits. First, pin sql_mode explicitly in my.cnf under [mysqld] on every server and deploy that file with the same automation — never rely on compiled-in defaults. Second, compare modes as part of deploy checks: SELECT @@GLOBAL.sql_mode on each tier and fail the deploy on drift. Third, make CI strict: run tests against the official MySQL image with default flags so lax local settings can't hide 1364s that production will throw.

Parity beats debugging. Every hour spent aligning environments saves three spent chasing errors that only exist in one place.

# Compare schema + mode across two environments before blaming code.
mysqldump --no-data shop > /tmp/schema-staging.sql
ssh prod-db 'mysqldump --no-data shop' > /tmp/schema-prod.sql
diff /tmp/schema-staging.sql /tmp/schema-prod.sql

mysql -h staging-db -e "SELECT @@GLOBAL.sql_mode;"
mysql -h prod-db -e "SELECT @@GLOBAL.sql_mode;"

# Pin the mode in my.cnf so defaults can't drift.
# /etc/mysql/my.cnf  (or /etc/my.cnf.d/server.cnf)
# [mysqld]
# sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION