Foreign keys were only enforced on one pooled connection, orphaning rows and breaking the 0.9.0 upgrade #163

New issue

Closed

opened 2026-09-09 14:08:29 +02:00 by qwc · 0 comments

qwc commented

2026-09-09 14:08:29 +02:00

Owner

Found while recovering a production instance that could not start after the upgrade to 0.9.0.

Symptom

The instance failed at startup with:

running migrations: Dirty database version 11. fix and force version.

Migration 011_project_access_source_unique is the only one in the 010 → 017 range that rebuilds a table (the SQLite repair from #133). The rebuild inserts into a new table with live FK references, so it enforces constraints that the old table had not been enforcing. Production had 11 rows that violated them, the INSERT failed, golang-migrate rolled the transaction back and left schema_migrations dirty at 11.

The data itself was intact — the driver wraps each migration in a transaction and SQLite has transactional DDL — but the instance was down.

The 11 orphans

All eleven were children of deleted projects, across four tables, every one of them under an ON DELETE CASCADE that never fired:

Table	Rows	FK
`project_access`	3	`project_id` → `projects`
`versions`	3	`project_id` → `projects`
`upload_logs`	4	`project_id` → `projects`
`api_tokens`	1	`project_id` → `projects`

uploaded_by violations: none.

Cause

internal/database/database.go did this after opening:

db.MustExec("PRAGMA journal_mode=WAL")
db.MustExec("PRAGMA foreign_keys=ON")

db is a *sqlx.DB — a pool, not a connection. The pragma reached whichever connection happened to serve it; every connection the pool opened afterwards kept SQLite's default of foreign keys OFF. foreign_keys is a per-connection setting, so enforcement was effectively a coin toss.

ProjectStore.Delete (internal/store/sql/project.go:151) is a bare DELETE FROM projects WHERE id = ? that relies entirely on cascade for its children. Served by a connection with foreign keys off, it deleted the project and silently left everything that pointed at it behind.

Two details worth recording:

Migration 009_uploaded_by_nullable performs the same kind of table rebuild on versions and upload_logs, and it succeeded on this database. So the orphans were created after 009 — during 0.8.x — which dates the rot and confirms the mechanism.
The damage was invisible for months. Nothing reads orphan rows, so the only thing that ever noticed was a migration that had to rebuild a table.

Recovery applied to production

DELETE FROM project_access WHERE project_id NOT IN (SELECT id FROM projects);
DELETE FROM versions       WHERE project_id NOT IN (SELECT id FROM projects);
DELETE FROM upload_logs    WHERE project_id NOT IN (SELECT id FROM projects);
DELETE FROM api_tokens     WHERE project_id IS NOT NULL
                             AND project_id NOT IN (SELECT id FROM projects);
UPDATE schema_migrations SET version = 10, dirty = 0;

PRAGMA foreign_key_check is clean afterwards and the instance starts.

Fix

Move both pragmas into the DSN so the driver applies them in its connection-open path (applyQueryParams, called per connection), and assert at startup that foreign keys are actually enabled rather than trusting that they are.

Note for other installations

Any SQLite database that ran 0.8.x may carry the same orphans and will fail the same way on the 011 rebuild. PRAGMA foreign_key_check; reports them; the SQL above clears them. A new migration cannot help here, since a database stuck dirty at 11 never reaches one.

Assisted-by: Claude Opus 5

Found while recovering a production instance that could not start after the upgrade to 0.9.0. ## Symptom The instance failed at startup with: ``` running migrations: Dirty database version 11. fix and force version. ``` Migration `011_project_access_source_unique` is the only one in the 010 → 017 range that rebuilds a table (the SQLite repair from #133). The rebuild inserts into a new table with live FK references, so it enforces constraints that the old table had not been enforcing. Production had 11 rows that violated them, the INSERT failed, golang-migrate rolled the transaction back and left `schema_migrations` dirty at 11. The data itself was intact — the driver wraps each migration in a transaction and SQLite has transactional DDL — but the instance was down. ## The 11 orphans All eleven were children of **deleted projects**, across four tables, every one of them under an `ON DELETE CASCADE` that never fired: | Table | Rows | FK | |---|---|---| | `project_access` | 3 | `project_id` → `projects` | | `versions` | 3 | `project_id` → `projects` | | `upload_logs` | 4 | `project_id` → `projects` | | `api_tokens` | 1 | `project_id` → `projects` | `uploaded_by` violations: none. ## Cause `internal/database/database.go` did this after opening: ```go db.MustExec("PRAGMA journal_mode=WAL") db.MustExec("PRAGMA foreign_keys=ON") ``` `db` is a `*sqlx.DB` — a **pool**, not a connection. The pragma reached whichever connection happened to serve it; every connection the pool opened afterwards kept SQLite's default of foreign keys **OFF**. `foreign_keys` is a per-connection setting, so enforcement was effectively a coin toss. `ProjectStore.Delete` (`internal/store/sql/project.go:151`) is a bare `DELETE FROM projects WHERE id = ?` that relies entirely on cascade for its children. Served by a connection with foreign keys off, it deleted the project and silently left everything that pointed at it behind. Two details worth recording: - Migration `009_uploaded_by_nullable` performs the same kind of table rebuild on `versions` and `upload_logs`, and it **succeeded** on this database. So the orphans were created after 009 — during 0.8.x — which dates the rot and confirms the mechanism. - The damage was invisible for months. Nothing reads orphan rows, so the only thing that ever noticed was a migration that had to rebuild a table. ## Recovery applied to production ```sql DELETE FROM project_access WHERE project_id NOT IN (SELECT id FROM projects); DELETE FROM versions WHERE project_id NOT IN (SELECT id FROM projects); DELETE FROM upload_logs WHERE project_id NOT IN (SELECT id FROM projects); DELETE FROM api_tokens WHERE project_id IS NOT NULL AND project_id NOT IN (SELECT id FROM projects); UPDATE schema_migrations SET version = 10, dirty = 0; ``` `PRAGMA foreign_key_check` is clean afterwards and the instance starts. ## Fix Move both pragmas into the DSN so the driver applies them in its connection-open path (`applyQueryParams`, called per connection), and assert at startup that foreign keys are actually enabled rather than trusting that they are. ## Note for other installations Any SQLite database that ran 0.8.x may carry the same orphans and will fail the same way on the 011 rebuild. `PRAGMA foreign_key_check;` reports them; the SQL above clears them. A new migration cannot help here, since a database stuck dirty at 11 never reaches one. Assisted-by: Claude Opus 5

qwc referenced this issue

2026-09-09 14:08:40 +02:00

Deleting a project leaves its documentation on disk forever #164

qwc referenced this issue from a commit

2026-09-09 14:08:46 +02:00

Foreign keys apply to every connection, not just the lucky one (#163)

qwc referenced this issue from a pull request that will close it,

2026-09-09 14:09:01 +02:00

Foreign keys apply to every connection, not just the lucky one (#163) #165

qwc referenced this issue

2026-09-09 14:12:18 +02:00

Deleting a project takes its documentation with it (#164) #166