Idempotent EF migration script for 58→64 fails: AddAuditLogHashChain adds ChainSeq and indexes it in one batch (Msg 207) #126

New Issue

2026-11-11T16:51:11-05:00

dohertj2 commented

2026-11-11 16:51:11 -05:00

Found preparing the 2026-11-11 vd03 (DEV) deploy 0e540291 → 424db155 (migrations 58 → 64).

The output of dotnet ef migrations script --idempotent fails on SQL Server with Msg 207 "Invalid column name 'ChainSeq'". AddAuditLogHashChain adds the column and creates indexes on it inside the same batch, and SQL Server compiles the whole batch before the IF NOT EXISTS guard runs. The script deployed to vd03 adds a GO after each guarded block and SET XACT_ABORT ON at the top. Forward, re-run (no-op), rollback and forward again were all rehearsed against a restored copy of the vd03 database.

Fix options:

split the migration so the index creation runs in its own batch, for example with migrationBuilder.Sql and a deferred EXEC; or
document the GO-splitting step in the deploy runbook and ship a post-processing step.

Also check docs/plans/sql/AddAuditLogHashChain.sql, which likely has the same problem (untested). QA/prod deploys will hit this.

Found preparing the 2026-11-11 vd03 (DEV) deploy `0e540291` → `424db155` (migrations 58 → 64). The output of `dotnet ef migrations script --idempotent` fails on SQL Server with **Msg 207 "Invalid column name 'ChainSeq'"**. `AddAuditLogHashChain` adds the column and creates indexes on it inside the same batch, and SQL Server compiles the whole batch before the `IF NOT EXISTS` guard runs. The script deployed to vd03 adds a `GO` after each guarded block and `SET XACT_ABORT ON` at the top. Forward, re-run (no-op), rollback and forward again were all rehearsed against a restored copy of the vd03 database. **Fix options:** - split the migration so the index creation runs in its own batch, for example with `migrationBuilder.Sql` and a deferred `EXEC`; or - document the `GO`-splitting step in the deploy runbook and ship a post-processing step. Also check `docs/plans/sql/AddAuditLogHashChain.sql`, which likely has the same problem (untested). QA/prod deploys will hit this.

dohertj2 added the type:bug priority:p2 labels 2026-11-11 16:51:11 -05:00

dohertj2 referenced this issue

2026-11-12 06:20:06 -05:00

Audit hash chain: signing/anchoring decision, enablement plan, chainer login, migration deploy gate (#41) #107

dohertj2 referenced this issue from a commit

2026-11-12 06:55:11 -05:00

fix(db): idempotent migration script runs clean on SQL Server (#126)

dohertj2 referenced this issue

2026-11-12 06:55:22 -05:00

Full-history idempotent EF script (from empty DB) fails on SQL Server in pre-58 migrations #127

dohertj2 commented

2026-11-12 06:58:01 -05:00

Fixed on main in c7dfc21b (plus test fix 7bc35081).

Root cause. EF 10's --idempotent output puts each migration in one batch (BEGIN TRANSACTION ... COMMIT; GO), with an IF NOT EXISTS (__EFMigrationsHistory) guard per statement. SQL Server compiles the whole batch before any guard runs. In AddAuditLogHashChain, the two raw-SQL filtered indexes (WHERE ChainSeq IS [NOT] NULL) reference a column the same batch adds, which raises Msg 207. database update / Migrate() are unaffected because they send one command at a time.

What changed

20261109211317_AddAuditLogHashChain.cs: both CREATE NONCLUSTERED INDEX statements are now wrapped in EXEC(N'...'), which defers compilation. The index definitions, the model snapshot and the Designer are unchanged. A DB that already has the migration (vd03) never runs it again. GRANT UPDATE (ChainSeq, RowHash) didn't need wrapping because it is checked at execution time.
Audit of migrations 58-64: no other instance. AddConfigGraphRowVersions adds RowVersion and indexes it in one batch, but a plain CREATE INDEX on a new column is not compile-checked. The script from 57 to head now runs clean.
docs/plans/sql/AddAuditLogHashChain.sql (hand-written prod script): had the same problem, now fixed with the same EXEC wrap. It and PrefixSearchIndexes.sql / AddConfigGraphRowVersions.sql also had a stray UTF-8 BOM right after the prepended header comment, so all three failed under sqlcmd with Msg 102 Incorrect syntax near '' before reaching any SQL. The BOMs are removed.
New IdempotentMigrationScriptTests (MSSQL fixture): migrates to 57, runs the generated idempotent script batch by batch, asserts all 64 migrations are applied, then re-runs the script as a no-op.
Also fixed FlipAuditLogClusteredKeyMigrationTests, which was order-dependent since migration 64. Its Down/Up reverted AuditMaintenanceGrants and only went back up to the flip, so the UPDATE (SourceNode) grant assertion failed in one order or the other. It now restores to head.

Evidence (local throwaway mssql/server:2022 container plus the local scadabridge-mssql test server; no wonder box touched)

Positive control: the original idempotent script from AddSiteArtifactRevision (57) on a migration-57 DB fails with Msg 207 ... Invalid column name 'ChainSeq' (batch 4).
After the fix, the same run exits 0 and leaves 64 migrations in history. A second run produces no output and exits 0. Running it against a DB already brought to head with the original migration code (the vd03 equivalent) is also a no-op.
Schema diff (columns, defaults, indexes with filter, key/include and data space, FKs, checks, role permissions incl. column grants, module hashes, migration history) between three DBs: (a) script-from-57, (b) dotnet ef database update with the original migration, (c) database update with the fixed migration. Identical (622 lines each).
Hand scripts: on a 57 DB, ReviewRemediationIndexes -> PrefixSearchIndexes -> AddAuditLogHashChain -> AddConfigGraphRowVersions -> FlipAuditLogClusteredKey (+ EF script for 64) now runs clean, and the schema matches (b) exactly. The old hash-chain script (with the BOM removed) still fails with Msg 207, which was the control.
The new test fails with Invalid column name 'ChainSeq' when the migration change is reverted, and passes with it.
dotnet build ZB.MOM.WW.ScadaBridge.slnx: 0 warnings, 0 errors. ConfigurationDatabase.Tests 536/536; AuditLog.Tests chain filter 26/26; ManagementService VerifyAuditChain 13/13.

Deploy note. QA/prod can now run the generated idempotent script as-is, with no GO insertion. Keep SET XACT_ABORT ON, and run under sqlcmd -I -b.

Left open: the full-history script (from an empty DB) still fails in pre-58 migrations. That is out of scope here because every environment is at >= 57; it is filed as #127.

Fixed on `main` in **c7dfc21b** (plus test fix **7bc35081**). **Root cause.** EF 10's `--idempotent` output puts each migration in **one batch** (`BEGIN TRANSACTION ... COMMIT; GO`), with an `IF NOT EXISTS (__EFMigrationsHistory)` guard per statement. SQL Server compiles the whole batch before any guard runs. In `AddAuditLogHashChain`, the two raw-SQL filtered indexes (`WHERE ChainSeq IS [NOT] NULL`) reference a column the same batch adds, which raises Msg 207. `database update` / `Migrate()` are unaffected because they send one command at a time. **What changed** - `20261109211317_AddAuditLogHashChain.cs`: both `CREATE NONCLUSTERED INDEX` statements are now wrapped in `EXEC(N'...')`, which defers compilation. The index definitions, the model snapshot and the Designer are unchanged. A DB that already has the migration (vd03) never runs it again. `GRANT UPDATE (ChainSeq, RowHash)` didn't need wrapping because it is checked at execution time. - Audit of migrations 58-64: no other instance. `AddConfigGraphRowVersions` adds RowVersion and indexes it in one batch, but a plain `CREATE INDEX` on a new column is not compile-checked. The script from 57 to head now runs clean. - `docs/plans/sql/AddAuditLogHashChain.sql` (hand-written prod script): had the same problem, now fixed with the same EXEC wrap. It and `PrefixSearchIndexes.sql` / `AddConfigGraphRowVersions.sql` also had a **stray UTF-8 BOM** right after the prepended header comment, so all three failed under sqlcmd with Msg 102 `Incorrect syntax near ''` before reaching any SQL. The BOMs are removed. - New `IdempotentMigrationScriptTests` (MSSQL fixture): migrates to 57, runs the generated idempotent script batch by batch, asserts all 64 migrations are applied, then re-runs the script as a no-op. - Also fixed `FlipAuditLogClusteredKeyMigrationTests`, which was order-dependent since migration 64. Its Down/Up reverted `AuditMaintenanceGrants` and only went back up to the flip, so the `UPDATE (SourceNode)` grant assertion failed in one order or the other. It now restores to head. **Evidence** (local throwaway `mssql/server:2022` container plus the local `scadabridge-mssql` test server; no wonder box touched) - Positive control: the original idempotent script from `AddSiteArtifactRevision` (57) on a migration-57 DB fails with `Msg 207 ... Invalid column name 'ChainSeq'` (batch 4). - After the fix, the same run exits 0 and leaves 64 migrations in history. A second run produces no output and exits 0. Running it against a DB already brought to head with the *original* migration code (the vd03 equivalent) is also a no-op. - Schema diff (columns, defaults, indexes with filter, key/include and data space, FKs, checks, role permissions incl. column grants, module hashes, migration history) between three DBs: (a) script-from-57, (b) `dotnet ef database update` with the original migration, (c) `database update` with the fixed migration. **Identical** (622 lines each). - Hand scripts: on a 57 DB, `ReviewRemediationIndexes -> PrefixSearchIndexes -> AddAuditLogHashChain -> AddConfigGraphRowVersions -> FlipAuditLogClusteredKey` (+ EF script for 64) now runs clean, and the schema matches (b) exactly. The old hash-chain script (with the BOM removed) still fails with Msg 207, which was the control. - The new test **fails** with `Invalid column name 'ChainSeq'` when the migration change is reverted, and passes with it. - `dotnet build ZB.MOM.WW.ScadaBridge.slnx`: 0 warnings, 0 errors. ConfigurationDatabase.Tests 536/536; AuditLog.Tests chain filter 26/26; ManagementService VerifyAuditChain 13/13. **Deploy note.** QA/prod can now run the generated idempotent script as-is, with no GO insertion. Keep `SET XACT_ABORT ON`, and run under `sqlcmd -I -b`. **Left open:** the full-history script (from an empty DB) still fails in pre-58 migrations. That is out of scope here because every environment is at >= 57; it is filed as #127.

dohertj2 closed this issue

2026-11-12 06:58:03 -05:00

dohertj2 referenced this issue from a commit

2026-11-12 07:26:16 -05:00

fix(db): full-history idempotent migration script runs clean from an empty DB (#127)

dohertj2 referenced this issue

2026-11-13 02:37:53 -05:00

Audit hash chain: signing/anchoring decision, enablement plan, chainer login, migration deploy gate (#41) #107

dohertj2 referenced this issue

2026-11-13 02:51:01 -05:00

Audit hash chain: signing/anchoring decision, enablement plan, chainer login, migration deploy gate (#41) #107

Sign in to join this conversation.