[PR #313] [MERGED] fix(db): self-heal sso_providers duplicate-column crash on upgrade (#312) #4662

New Issue

2026-06-13T07:25:59-05:00

GiteaMirror commented

2026-06-13 07:25:59 -05:00

📋 Pull Request Information

Original PR: https://github.com/RayLabsHQ/gitea-mirror/pull/313
Author: @arunavo4
Created: 6/5/2026
Status: ✅ Merged
Merged: 6/5/2026
Merged by: @arunavo4

Base: main ← Head: fix/sso-duplicate-column-312

📝 Commits (1)

3ff1219 fix(db): self-heal sso_providers duplicate-column crash on upgrade (#312)

📊 Changes

3 files changed (+228 additions, -1 deletions)

View changed files

📝 scripts/validate-migrations.ts (+102 -1)
📝 src/lib/db/index.ts (+9 -0)
➕ src/lib/db/migration-repairs.ts (+117 -0)

📄 Description

Summary

Fixes #312 — after upgrading to v3.17.0 (PR #307) some instances crash-loop on boot with:

DrizzleError: Failed to run the query 'ALTER TABLE `sso_providers` ADD `saml_config` text;'
SQLiteError: duplicate column name: saml_config

Root cause

Migration 0013_slim_galactus.sql runs as a single transaction that (a) rebuilds the organizations table and (b) ALTER TABLE sso_providers ADD saml_config / ADD domain_verified. On affected DBs those columns already exist physically — they were declared in schema.ts and entered the database outside Drizzle (via db:push or an SSO-register round-trip on an intermediate build), while __drizzle_migrations never recorded a 0013 row.

Because the ADD hits a pre-existing column, SQLite throws duplicate column, which rolls back the entire 0013 transaction (including the organizations rebuild) and never records 0013. Drizzle then re-attempts 0013 on every boot and fails identically → crash loop.

This is the mirror image of the case the existing repairFailedMigrations() (the 0009 fix) already handles: there the record is present but the column is missing; here the column is present but the record is missing.

Fix

A pre-migrate repair repairDuplicateSsoColumns() (+ restoreSsoDataAfter0013()), wired into runDrizzleMigrations() right after repairFailedMigrations():

No-op unless __drizzle_migrations exists, 0013 is unrecorded, and sso_providers already has the columns.
Preserve any real data (SAML providers store JSON in saml_config; domain_verified may be explicitly false).
DROP COLUMN the stranded columns so the canonical 0013 runs in full — the organizations rebuild is deliberately not skipped.
After migrate(), restore the preserved values (OIDC-only rows keep the 0013 defaults and need no restore).

Affected instances recover automatically on the next boot after upgrading — no manual SQLite surgery.

Safety

Fresh install → early-returns (no migrations table yet).
Clean upgrade (columns absent) → no-op, 0013 adds them normally.
Already-migrated DB → no-op, columns left intact.
SAML provider with real config → preserved across the drop/re-add.

All repair steps are wrapped in try/catch and logged non-fatally, so they never make a recoverable database worse.

Testing

bun scripts/validate-migrations.ts — extended with the #312 broken-upgrade scenario (OIDC + real-SAML + explicitly-unverified rows; asserts columns dropped, 0013 runs clean, SAML JSON & domain_verified=0 preserved, idempotent second pass).
bun test — 289 pass, 0 fail. The repair is exercised through the existing subprocess-based migrations.test.ts.

Notes

Version bump (e.g. 3.17.1) intentionally left out of this PR — kept as a separate chore: commit per the repo's convention.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/RayLabsHQ/gitea-mirror/pull/313 **Author:** [@arunavo4](https://github.com/arunavo4) **Created:** 6/5/2026 **Status:** ✅ Merged **Merged:** 6/5/2026 **Merged by:** [@arunavo4](https://github.com/arunavo4) **Base:** `main` ← **Head:** `fix/sso-duplicate-column-312` --- ### 📝 Commits (1) - [`3ff1219`](https://github.com/RayLabsHQ/gitea-mirror/commit/3ff121931cfc12ba1fa4da9969baafca43fbbef9) fix(db): self-heal sso_providers duplicate-column crash on upgrade (#312) ### 📊 Changes **3 files changed** (+228 additions, -1 deletions) <details> <summary>View changed files</summary> 📝 `scripts/validate-migrations.ts` (+102 -1) 📝 `src/lib/db/index.ts` (+9 -0) ➕ `src/lib/db/migration-repairs.ts` (+117 -0) </details> ### 📄 Description ## Summary Fixes #312 — after upgrading to v3.17.0 (PR #307) some instances crash-loop on boot with: ``` DrizzleError: Failed to run the query 'ALTER TABLE `sso_providers` ADD `saml_config` text;' SQLiteError: duplicate column name: saml_config ``` ### Root cause Migration `0013_slim_galactus.sql` runs as a **single transaction** that (a) rebuilds the `organizations` table and (b) `ALTER TABLE sso_providers ADD saml_config` / `ADD domain_verified`. On affected DBs those columns already exist physically — they were declared in `schema.ts` and entered the database outside Drizzle (via `db:push` or an SSO-register round-trip on an intermediate build), while `__drizzle_migrations` never recorded a 0013 row. Because the `ADD` hits a pre-existing column, SQLite throws `duplicate column`, which rolls back the **entire** 0013 transaction (including the organizations rebuild) and never records 0013. Drizzle then re-attempts 0013 on every boot and fails identically → crash loop. This is the mirror image of the case the existing `repairFailedMigrations()` (the 0009 fix) already handles: there the *record* is present but the *column* is missing; here the *column* is present but the *record* is missing. ### Fix A pre-migrate repair `repairDuplicateSsoColumns()` (+ `restoreSsoDataAfter0013()`), wired into `runDrizzleMigrations()` right after `repairFailedMigrations()`: 1. No-op unless `__drizzle_migrations` exists, 0013 is **unrecorded**, and `sso_providers` already has the columns. 2. Preserve any real data (SAML providers store JSON in `saml_config`; `domain_verified` may be explicitly `false`). 3. `DROP COLUMN` the stranded columns so the canonical 0013 runs **in full** — the organizations rebuild is deliberately **not** skipped. 4. After `migrate()`, restore the preserved values (OIDC-only rows keep the 0013 defaults and need no restore). Affected instances recover **automatically on the next boot after upgrading** — no manual SQLite surgery. ### Safety - Fresh install → early-returns (no migrations table yet). - Clean upgrade (columns absent) → no-op, 0013 adds them normally. - Already-migrated DB → no-op, columns left intact. - SAML provider with real config → preserved across the drop/re-add. All repair steps are wrapped in try/catch and logged non-fatally, so they never make a recoverable database worse. ## Testing - `bun scripts/validate-migrations.ts` — extended with the #312 broken-upgrade scenario (OIDC + real-SAML + explicitly-unverified rows; asserts columns dropped, 0013 runs clean, SAML JSON & `domain_verified=0` preserved, idempotent second pass). - `bun test` — **289 pass, 0 fail**. The repair is exercised through the existing subprocess-based `migrations.test.ts`. ## Notes - Version bump (e.g. 3.17.1) intentionally left out of this PR — kept as a separate `chore:` commit per the repo's convention. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

GiteaMirror added the pull-request label 2026-06-13 07:25:59 -05:00

GiteaMirror closed this issue

2026-06-13 07:26:00 -05:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea-mirror#4662