yolo/arr
3
0
Fork 0
Code Issues 12 Pull Requests Actions Packages Projects Releases Wiki Activity

Table-rebuild migrations cascade-delete children under sqlx 0.8 #155

New Issue
Closed
opened 2026-08-23 20:59:19 +01:00 by naps62-yolo · 0 comments
naps62-yolo commented 2026-08-23 20:59:19 +01:00
Owner
Copy Link
Copy Source

Any migration that rebuilds a parent table in this repo silently cascade-deletes
its children, and the usual guard does not work.

sqlx-sqlite 0.8.6 ignores Migration.no_tx and always begins a transaction in
apply(). PRAGMA foreign_keys = OFF is a no-op inside a transaction, so the
standard SQLite table-rebuild recipe — turn foreign keys off, create the new
table, copy, drop the old, rename — deletes every referencing row when the drop
runs. Dropping episodes takes episode_releases with it.

This already shapes the migrations here: 0014 carries a
movie_releases_backup table for exactly this reason, and #153 abandoned an
otherwise correct CHECK (title <> '') after hitting it.

Options, in rough order of preference:

  • Bump the workspace to sqlx 0.9, which honours -- no-transaction on SQLite,
    and drop the backup-table workaround.
  • Keep 0.8 and write the recipe down in CLAUDE.md so each session does not
    rediscover it — copy children out, rebuild, copy back.

Worth deciding before the next table rebuild rather than during one. Filed with
no milestone: this is workspace tooling, not TV tracking.

Any migration that rebuilds a parent table in this repo silently cascade-deletes its children, and the usual guard does not work. `sqlx-sqlite` 0.8.6 ignores `Migration.no_tx` and always begins a transaction in `apply()`. `PRAGMA foreign_keys = OFF` is a no-op inside a transaction, so the standard SQLite table-rebuild recipe — turn foreign keys off, create the new table, copy, drop the old, rename — deletes every referencing row when the drop runs. Dropping `episodes` takes `episode_releases` with it. This already shapes the migrations here: `0014` carries a `movie_releases_backup` table for exactly this reason, and `#153` abandoned an otherwise correct `CHECK (title <> '')` after hitting it. Options, in rough order of preference: - Bump the workspace to sqlx 0.9, which honours `-- no-transaction` on SQLite, and drop the backup-table workaround. - Keep 0.8 and write the recipe down in `CLAUDE.md` so each session does not rediscover it — copy children out, rebuild, copy back. Worth deciding before the next table rebuild rather than during one. Filed with no milestone: this is workspace tooling, not TV tracking.
naps62-yolo added the area/dbtype/choredifficulty/moderate labels 2026-08-23 20:59:19 +01:00
naps62-yolo added this to the Reliability and first run milestone 2026-08-24 17:58:27 +01:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
area/api
arr-api
 area/ci
CI and test harness
 area/compat
arr-compat
 area/core
arr-core
 area/daemon
arr-daemon
 area/db
arr-db
 area/dl
arr-dl
 area/indexer
arr-indexer
 area/infra
workspace and tooling
 area/meta
arr-meta
 area/parse
arr-parse
 area/probe
arr-probe
 area/subs
arr-subs crate: providers, extraction, translation, sync
 area/web
web/
 difficulty/easy
bounded, obvious approach, few files
 difficulty/hard
subtle correctness or cross-cutting
 difficulty/moderate
multiple files, judgement, an interface to design
 difficulty/trivial
one file, mechanical, no design decisions
 phase/1-skeleton
workspace, CI, config, database, empty API and SPA
 phase/2-logic
parsing and policy engine, pure, no network
 phase/3-sourcing
TMDB and Prowlarr, read-only
 phase/4-movies
movies end to end
 phase/5-ui
search, buckets, library views, queues
 phase/6-tv
seasons, episodes, tracking, derived status
 phase/7-people
owner tags and notifications
 phase/8-compat
Jellyseerr shim
 phase/9-subtitles
build order: subtitles, DESIGN.md §15
 type/bug
type/chore
type/feature
type/test
No labels area/db difficulty/moderate type/chore
Milestone
No items
No Milestone Reliability and first run
Projects
Clear projects
No projects
Assignees
Clear assignees
hermes (Hermes (agent)) naps62 (Miguel Palhas) naps62-yolo (naps62-yolo (agent))
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: yolo/arr#155