Diagnose NOT NULL column failures across engines

You pushed a schema change. Required column, no default, table already has rows. On SQL Server and PostgreSQL, the quench stops cold and tells you exactly why. On MySQL it comes back exit 0. Green. Done.

Except it isn’t. Every existing row now has an empty string where that required value should be, and nobody warned you.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

This is Module 2 of Course 8, and we’re going into the structure half of the mechanical engine — the phases that create missing tables, add missing columns, and reshape existing ones. Two failure patterns, three engines, and one lesson that matters more than any error code: the engine that fails loud is protecting you.

The structure phases

Before we light anything up, a quick map. The structure half of the quench runs first, in two phases:

MissingTableAndColumnQuench — creates any table that’s in your model but not in the database, and adds any column that’s missing from an existing table.
ModifiedTableQuench — alters columns that already exist but have changed type, size, or nullability. For non-trivial changes this phase drops and recreates via a pair of procedures running back-to-back.

In your Progress.log, these surface as:

[localhost,11433].[diag_structure]   Quenching missing tables and columns
[localhost,11433].[diag_structure]   Quenching modified tables

Both phases run before indexes, constraints, and foreign keys. Get either one wrong and everything downstream stops. That ordering is why structure failures are always the top of the incident — fix the shape first, and the rest of the convergence can run.

Beat 1 — adding a required column over existing data

Here’s the scenario. The Customer table has rows in it. Your schema adds a new column:

{ "Name": "[LoyaltyTier]", "DataType": "NVARCHAR(20)" }

No "Nullable": true, so the column is NOT NULL — that’s the SchemaSmith default. No Default either. Required, with nowhere for the existing rows to get that value. Same package, same change — three engines. Three very different answers.

SQL Server says no

Exit 2. At Quenching missing tables and columns. The FAILED to quench: block reads:

ALTER TABLE only allows columns to be added that can contain nulls,
or have a DEFAULT definition specified, or the column being added is
an identity or timestamp column, or alternatively if none of the previous
conditions are satisfied the table must be empty to allow addition of this
column. Column 'LoyaltyTier' cannot be added to non-empty table 'Customer'
because it does not satisfy these conditions.

Error 4901. SQL Server read the situation — new required column, rows with no value for it, no default to fill the gap — and stopped. No partial change. No silent fill. The table is exactly as it was.

PostgreSQL says no

Exit 2. Same phase. The error is terser but the judgment is identical:

23502: column "loyaltytier" of relation "customer" contains null values

PostgreSQL isn’t pulling punches either. It knows what NOT NULL means and it knows the existing rows can’t satisfy it.

MySQL — and MariaDB — say yes

Exit 0. No error. No warning.

MySQL adds the column and backfills empty string '' into every existing row — and MariaDB does exactly the same. Even with STRICT_TRANS_TABLES enabled, this is the MySQL family’s behavior on a NOT NULL column with no default: both fill with the type’s implicit default rather than refusing. The quench completes, the application proceeds, and you now have a column full of '' where your business logic expects a real tier name.

This is not a SchemaSmith bug. SchemaSmith issues the same correct ALTER TABLE ... ADD COLUMN on every engine. SQL Server and PostgreSQL refuse; MySQL and MariaDB fill. That divergence is entirely the engines’ own behavior. SchemaSmith can’t make MySQL or MariaDB fail loud — but it can give you the trail to find the problem later, which is the whole point of this course.

What the loud engines were telling you

SQL Server and PostgreSQL didn’t fail to be difficult. They refused because honoring your NOT NULL constraint with a blank value is a contradiction — a column that’s required but contains nothing. They held the line.

MySQL and MariaDB made the trade-off the other way. Operationally convenient. Semantically wrong. And silent.

If your quench is MySQL- or MariaDB-green on a structure change, don’t assume clean. Read the data.

The fix

Give the new column a Default so existing rows land with a real value:

{ "Name": "[LoyaltyTier]", "DataType": "NVARCHAR(20)", "Default": "N'Standard'" }

Still NOT NULL — the Default is the SQL literal that fills the existing rows (N'Standard' on SQL Server; 'Standard' on PostgreSQL, MySQL, and MariaDB). Redeploy. Green on all four. On SQL Server and PostgreSQL, every existing row now reads Standard. On MySQL and MariaDB, those rows still read '' — the column was already added in the broken deploy, and a default only applies to new rows going forward. Had MySQL and MariaDB failed loud, you’d have caught the blanks before they landed in your data.

That’s the lesson in one sentence: a loud failure is a fast fix; a silent wrong answer is a data problem you find weeks later.

Reading the artifact

The SchemaQuench - Quench Missing Tables And Columns … artifact is the most copy-runnable piece of the whole tool. It declares the table JSON inline — DECLARE @TableDefinitions … ParseTableJson … EXEC — and you can paste it straight into your client to reproduce the column add by hand. When you want to watch a failure happen in isolation, outside the full quench, this is the artifact you open.

Beat 2 — narrowing a column that still holds long data

The second failure is cleaner. You decide to tighten Customer.FullName from NVARCHAR(200) to NVARCHAR(10). A seeded row holds 'Ana Fielding-Reyes' — 18 characters. That doesn’t fit in 10.

This one fails the same way on every engine.

SQL Server

Exit 2. At Quenching modified tables. Error 8152:

String or binary data would be truncated in table 'diag_structure.dbo.Customer',
column 'FullName'.

PostgreSQL

Exit 2. Same phase. Error 22001:

22001: value too long for type character varying(10)

MySQL

Exit 2. Same phase. Error 1406:

Data too long for column 'FullName' at row 1

Three engines, three different messages — same verdict. The data doesn’t fit the shape you asked for.

The fix here is data, not schema. The column definition is fine; the values are too long for it. Shorten them first:

UPDATE Customer SET FullName = LEFT(FullName, 10);

Then redeploy the same narrowed column change. Green on all four. The schema lands, the data fits, and the ModifiedTableQuench artifact in your run gives you the resolved SQL to verify by hand.

The ModifiedTables failure catalog

This lesson induced two of the common ones. The full catalog for the modified-tables phase includes failures you’ll encounter as you push other types of column reshaping:

Type conversion that won’t fit — assigning a value to an incompatible type, e.g., a string that can’t convert to a number (SQL Server 245, 8115).
NOT NULL on an existing nullable column that has nulls — the engine won’t harden a column to NOT NULL while live nulls sit in it (SQL Server 515).

You don’t need to memorize the codes. You need to find the FAILED to quench: block in Progress.log, read what the engine said, and match it to the phase.

Where these errors land

Open SchemaQuench - Failures.log first — it rolls up the structure-change failure in one read: the engine error, the failing phase, and a pointer to the full artifact. Then come back here for the deeper Progress.log walk this module does.

Every structure failure surfaces in the same place: the FAILED to quench: block in SchemaQuench - Progress.log. That’s true on SQL Server, PostgreSQL, MySQL, and MariaDB — same file, same block structure, different error text inside. You’ve met that FAILED to quench: block before; now you know what to look for when the phase name is Quenching missing tables and columns or Quenching modified tables.

The trail is always there. Structure, index, FK, script — every phase writes to the same log in the same format. Read the phase name. Read the error. That’s locate, read, recover.

What’s next

The structure phases covered here are the first two stops on the map. Two more stretches are ahead:

Module 3 · Index, constraint & FK failures — the unique index that won’t take on dirty data (your 2am incident), check constraints, foreign keys. The full constraint surface.
Module 5 · The recovery toolkit — --ResumeQuench, marking a script done, and the full fix-and-continue playbook.

Check yourself: MySQL exits 0 after you add a NOT NULL column with no default to a populated table. SQL Server and PostgreSQL both exited 2. Why is the MySQL green run the more dangerous outcome?

SQL Server and PostgreSQL refused to add the column because the existing rows have no value to satisfy the NOT NULL constraint — so the database is unchanged and you have a clear error to fix. MySQL added the column and backfilled empty string '' into every existing row. The quench succeeded from the tool’s perspective, but the data is now wrong. No error, no warning — the problem lands in production and waits to surface through application behavior instead. The loud failures are recoverable before they matter; the silent fill is a data defect you find later.

A good smith knows the difference between a forge that shuts down the bellows and one that keeps blowing air through a flawed piece. The bellows that stops is the one doing its job. The one that keeps going — that’s the one that lets bad metal reach the customer. Read the loud engines. Fear the quiet ones.

Got a structure change that behaved differently than you expected? Email me at forgebarrett@schemasmith.com — tell me what you changed, what the engine said, and what the data looked like afterward.

Next up: Course 8 · Module 3 — Index, constraint & FK failures, where we finally diagnose that 2am incident with the unique index and dirty data.

Until then, may your columns land with values worth keeping.

— Forge

Mark this lesson complete