A NOT NULL column was added to the table via ALTER TABLE without specifying a DEFAULT value. Every existing INSERT statement that does not explicitly name that column will fail immediately — including ADF copy activities that had a fixed column mapping set before the migration.. ADF column mapping in the sink dataset is hardcoded to a specific column list. When a new required column is added to the destination table, the mapping does not update automatically, so the column is silently omitted from every write attempt.. A dbt model's SELECT clause does not include the new NOT NULL column. Because dbt generates INSERT ... SELECT or CREATE TABLE AS SELECT statements based on the model SQL, the missing column is never passed to MySQL and 1364 fires for every run.. STRICT_TRANS_TABLES (or STRICT_ALL_TABLES) is enabled in the MySQL sql_mode. In non-strict mode MySQL would insert an implicit empty string or zero; with strict mode enabled it raises 1364 instead of silently inserting a potentially wrong value.. A stored procedure or application INSERT was written against an older version of the table schema and was not updated after a migration. The procedure call omits the new column, causing 1364 inside the stored procedure body, which the calling pipeline reports as an unhandled database error.. The column was defined with NOT NULL but the DEFAULT keyword was intentionally omitted by the database administrator expecting all writers to always supply the value. Any automated pipeline that was not updated after the schema change will hit this constraint on every batch.

Step 1 — Confirm the exact column causing the error: `SHOW COLUMNS FROM your_table WHERE Null = 'NO' AND Default IS NULL;` This lists every NOT NULL column with no default, so you know exactly which column the INSERT is missing.. Step 2 — Add a DEFAULT to the column if a sensible fallback exists: `ALTER TABLE your_table ALTER COLUMN new_col SET DEFAULT 'unknown';` For numeric columns: `ALTER TABLE your_table ALTER COLUMN new_col SET DEFAULT 0;` This immediately unblocks all existing INSERT statements without requiring changes to every writer.. Step 3 — If a default is not appropriate, add the column to every INSERT explicitly. For raw SQL: `INSERT INTO your_table (col1, col2, new_col) VALUES (:v1, :v2, :v3);` Verify with `EXPLAIN INSERT` that the column list is complete.. Step 4 — In ADF, open the Copy Activity → Sink tab → Column mapping. Add the new column to the mapping and map it to the corresponding source field or a literal value using an expression: `@pipeline().parameters.defaultValue`. Re-run a debug execution to confirm zero 1364 errors in the activity output JSON.. Step 5 — In dbt, add the column to the model SELECT with a safe coalesce: `COALESCE(source.new_col, 'unknown') AS new_col`. Run `dbt compile` to inspect the generated SQL before executing `dbt run --select your_model`.. Step 6 — If the table is large and you need to add the column safely in production, use a two-step migration: first add it as nullable (`ALTER TABLE t ADD COLUMN new_col VARCHAR(255) NULL;`), backfill (`UPDATE t SET new_col = 'default' WHERE new_col IS NULL;`), then enforce the constraint (`ALTER TABLE t MODIFY COLUMN new_col VARCHAR(255) NOT NULL DEFAULT 'default';`).. Step 7 — Verify sql_mode to understand strictness: `SELECT @@GLOBAL.sql_mode;` If STRICT_TRANS_TABLES is present and you cannot change schema or pipelines immediately, you can temporarily relax strict mode for a specific session — but treat this as a short-term workaround only, not a fix.

Medium severitydata integrity

MySQL Error:
1364

Impact

Any schema migration that adds a NOT NULL column without a DEFAULT to a table written to by ADF or dbt will break every pipeline run immediately and completely — no rows are written until the fix is deployed. If this table feeds downstream aggregations or reports, those will go stale from the moment of the migration.

Every row in the affected batch is rejected — MySQL does not perform a partial commit under STRICT_TRANS_TABLES. ADF marks the Copy Activity as Failed and stops the pipeline; downstream activities that depend on it are skipped entirely, leaving staging tables empty or stale. dbt exits the run immediately for the failing model and marks all dependent models as skipped. If the pipeline has no retry logic, the SLA breach begins from the first failed execution — not from when the migration was applied.

What does this error mean?

MySQL error 1364 occurs when an INSERT statement omits a column that is defined as NOT NULL without a DEFAULT value. MySQL cannot substitute a fallback value and rejects the entire row. In a data pipeline context this surfaces when a schema migration adds a required column after ADF copy activities or dbt models were already written — those pipelines continue writing without the new column, triggering 1364 on every row. The symptom is a hard pipeline failure: ADF reports a sink write error with the MySQL error code embedded in the activity output, dbt run exits with a database error on the affected model, and no rows are committed for that batch.

Common causes

1A NOT NULL column was added to the table via ALTER TABLE without specifying a DEFAULT value. Every existing INSERT statement that does not explicitly name that column will fail immediately — including ADF copy activities that had a fixed column mapping set before the migration.
2ADF column mapping in the sink dataset is hardcoded to a specific column list. When a new required column is added to the destination table, the mapping does not update automatically, so the column is silently omitted from every write attempt.
3A dbt model's SELECT clause does not include the new NOT NULL column. Because dbt generates INSERT ... SELECT or CREATE TABLE AS SELECT statements based on the model SQL, the missing column is never passed to MySQL and 1364 fires for every run.
4STRICT_TRANS_TABLES (or STRICT_ALL_TABLES) is enabled in the MySQL sql_mode. In non-strict mode MySQL would insert an implicit empty string or zero; with strict mode enabled it raises 1364 instead of silently inserting a potentially wrong value.
5A stored procedure or application INSERT was written against an older version of the table schema and was not updated after a migration. The procedure call omits the new column, causing 1364 inside the stored procedure body, which the calling pipeline reports as an unhandled database error.
6The column was defined with NOT NULL but the DEFAULT keyword was intentionally omitted by the database administrator expecting all writers to always supply the value. Any automated pipeline that was not updated after the schema change will hit this constraint on every batch.

How to fix it

1Step 1 — Confirm the exact column causing the error: `SHOW COLUMNS FROM your_table WHERE Null = 'NO' AND Default IS NULL;` This lists every NOT NULL column with no default, so you know exactly which column the INSERT is missing.
2Step 2 — Add a DEFAULT to the column if a sensible fallback exists: `ALTER TABLE your_table ALTER COLUMN new_col SET DEFAULT 'unknown';` For numeric columns: `ALTER TABLE your_table ALTER COLUMN new_col SET DEFAULT 0;` This immediately unblocks all existing INSERT statements without requiring changes to every writer.
3Step 3 — If a default is not appropriate, add the column to every INSERT explicitly. For raw SQL: `INSERT INTO your_table (col1, col2, new_col) VALUES (:v1, :v2, :v3);` Verify with `EXPLAIN INSERT` that the column list is complete.
4Step 4 — In ADF, open the Copy Activity → Sink tab → Column mapping. Add the new column to the mapping and map it to the corresponding source field or a literal value using an expression: `@pipeline().parameters.defaultValue`. Re-run a debug execution to confirm zero 1364 errors in the activity output JSON.
5Step 5 — In dbt, add the column to the model SELECT with a safe coalesce: `COALESCE(source.new_col, 'unknown') AS new_col`. Run `dbt compile` to inspect the generated SQL before executing `dbt run --select your_model`.
6Step 6 — If the table is large and you need to add the column safely in production, use a two-step migration: first add it as nullable (`ALTER TABLE t ADD COLUMN new_col VARCHAR(255) NULL;`), backfill (`UPDATE t SET new_col = 'default' WHERE new_col IS NULL;`), then enforce the constraint (`ALTER TABLE t MODIFY COLUMN new_col VARCHAR(255) NOT NULL DEFAULT 'default';`).
7Step 7 — Verify sql_mode to understand strictness: `SELECT @@GLOBAL.sql_mode;` If STRICT_TRANS_TABLES is present and you cannot change schema or pipelines immediately, you can temporarily relax strict mode for a specific session — but treat this as a short-term workaround only, not a fix.

Example log output

ERROR 1364 (HY000): Field 'ingestion_timestamp' doesn't have a default valuecom.microsoft.sqlserver.jdbc.SQLServerException: MySQL sink write failed — [MySQL][ODBC 8.0 Driver][mysqld-8.0.32] Field 'ingestion_timestamp' doesn't have a default valueDatabase Error in model stg_orders (models/staging/stg_orders.sql): (1364, "Field 'ingestion_timestamp' doesn't have a default value") compiled Code at target/compiled/project/models/staging/stg_orders.sql

Frequently asked questions

MySQL 1364 fix — what is the fastest way to unblock a failing pipeline?

The fastest fix is adding a DEFAULT to the column: `ALTER TABLE your_table ALTER COLUMN new_col SET DEFAULT 'value';`. This requires no changes to ADF, dbt, or application code and takes effect immediately on the next INSERT. Only use a meaningful default — not an empty string — so downstream data quality is not silently compromised.

Why does 1364 only appear after a schema migration and not during development?

Development databases often run without STRICT_TRANS_TABLES in sql_mode, so MySQL silently inserts an empty string or zero instead of raising an error. Production databases typically have strict mode enabled. Always check `SELECT @@sql_mode;` in both environments to confirm they match — a difference here is the most common cause of errors that only appear in production.

MySQL 1364 in ADF — where exactly is the error logged?

Open the ADF pipeline run → click the failed Copy Activity → select Output. The errorCode field in the JSON output will contain 1364 and the message field will name the specific column. You can also find it in the ADF Monitor tab under Activity Runs filtered by Status = Failed.

How do I safely add a NOT NULL column to a large production MySQL table without downtime?

Use a three-step migration: (1) add the column as nullable so existing writes continue: `ALTER TABLE t ADD COLUMN col VARCHAR(255) NULL;` — (2) backfill all existing rows: `UPDATE t SET col = 'default' WHERE col IS NULL;` — (3) add the NOT NULL constraint with a DEFAULT once backfill is complete: `ALTER TABLE t MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT 'default';`. On large tables use pt-online-schema-change or gh-ost to avoid locking.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html