github-featured / learningcircuit--local-deep-research

发布

RSS 订阅源

v1.6.10 c2a47a83b3

比较
fix(db): unblock multi-migration upgrades blocked by FK mismatch + orphan _alembic_tmp_* tables (#4000)

frostbyte_neo 发布于 2026-05-12 22:13:02 +00:00
- fix(db): unblock multi-migration upgrades — toggle FK + scrub orphan temp tables outside the alembic transaction
Closes #3990 and unblocks #3817. Real users at revisions 0001–0005
upgrading to 0009 hit two failure modes that left their account
unable to log in:
1. foreign key mismatch — "download_attempts" referencing "download_tracker" (#3990)
  
  Migration 0007's defensive PRAGMA foreign_keys = OFF is silently a
  no-op once the sqlite3/sqlcipher3 driver has auto-begun the migration
  transaction (per sqlite.org/pragma.html#pragma_foreign_keys). With the
  chained 0002–0006 upgrade, earlier migrations issue DML before 0007
  runs, freezing FK in the connect-time ON state for the rest of the
  upgrade. The orphan-scrub DELETE FROM download_attempts ... then
  fails with "foreign key mismatch" because the pre-fix
  download_tracker.url_hash lacks the UNIQUE backing the FK requires
  for the cascade machinery to compile.
  
  The fix issues PRAGMA foreign_keys = OFF in
  alembic_runner.run_migrations BEFORE opening the migration
  transaction (via exec_driver_sql, which doesn't trigger driver
  auto-begin), then re-enables FK on the same connection after the
  upgrade commits and before the connection returns to the pool — so
  subsequent checkouts see the production-default ON state.
2. table _alembic_tmp_journals already exists (#3817)
  
  op.batch_alter_table rebuilds a table by creating
  _alembic_tmp_<table>, copying data, dropping the original, and
  renaming. On a clean run alembic drops the temp table automatically.
  If a previous attempt failed in a way that bypassed transaction
  rollback (e.g., an older migration runner that auto-committed each
  migration), the temp table persists and the next attempt fails with
  "table alembic_tmp* already exists".
  
  The fix drops orphan _alembic_tmp_* tables in
  alembic_runner.run_migrations before opening the migration
  transaction. This runs at the SQLite level under autocommit; if a
  concurrent run_migrations is mid-batch_alter_table, our DROP blocks
  on the SQLite write lock until the rename consumes the temp table,
  making our DROP IF EXISTS a no-op — the race is benign.
Tests: two new fixture-driven regression tests
(TestUpgradeFromBuggyV16xUserDbProductionEngine,
TestOrphanAlembicTempTableCleanup) reproduce the production failure
modes verbatim — isolation_level="" matching the sqlcipher3 engine
in encrypted_db.py, FK ON at connect via the same event handler
apply_performance_pragmas installs, and a chained 0005→head
upgrade so DML auto-begins before 0007. Both tests fail without the
runner fix with the exact production error messages and pass with it.

Migration 0007's misleading comment ("no DML has opened the implicit
transaction yet") is also corrected — that statement was true when
the migration was written against a single-revision test fixture but
never held for real multi-migration upgrades.
- test(no-raw-sql): allow alembic_runner.py — same exception class as initialize.py
alembic_runner.py is migration infrastructure (drops orphan
_alembic_tmp_* tables in #3817, toggles PRAGMA foreign_keys in
#3990). The single DROP TABLE IF EXISTS f-string trips the
["\']DROP\s+TABLE\s+' regex in the raw-SQL guard. Add the file to
the same exclusion list database/initialize.py lives in — both are
catalog-derived DDL on migration infrastructure, not application
code touching user-controllable SQL.

Precedent: commit 0b82064fd added database/initialize.py with the
same justification.

The catalog-derived identifier in _drop_orphan_alembic_temp_tables
already carries # noqa: S608 and # bearer:disable markers, so
static analysis (ruff/bearer) still flags any new violations in the
file — the test exclusion only suppresses the project-local raw-SQL
guard.
下载附件
- 源代码 (ZIP)
- 源代码 (TAR.GZ)

0 发布 174 Git 标签

fix(db): unblock multi-migration upgrades blocked by FK mismatch + orphan _alembic_tmp_* tables (#4000)