[LAUNCH-BLOCKER] Indexer migration timestamp collision: two migrations claim version 20260504200000 (#135 + #137 races)

Brouie commented

2026-05-05 03:40:37 +00:00

(Migrated from gitlab.com)

@PlasticDigits — caught while bringing up the local stack for the #144 live wallet walk. fresh indexer DB cannot bootstrap on main at HEAD 16deec2.

Symptom

scripts/start-local-anvil-stack.sh reaches the indexer step, then dies:

=== Reset indexer database ===
=== Start indexer (127.0.0.1:3100) ===
Indexer did not become ready at http://127.0.0.1:3100/v1/status

/tmp/yieldomega_indexer_stack.log:

2026-05-05T03:15:18.652323Z  INFO yieldomega_indexer::db: running pending migrations
Error: 
   0: while executing migrations: error returned from database: duplicate key value violates unique constraint "_sqlx_migrations_pkey"
   1: error returned from database: duplicate key value violates unique constraint "_sqlx_migrations_pkey"
   2: duplicate key value violates unique constraint "_sqlx_migrations_pkey"
Location:
   src/db.rs:16

Root cause

indexer/migrations/ has two files claiming version timestamp 20260504200000:

20260504200000_doub_vesting_rescue_erc20.down.sql
20260504200000_doub_vesting_rescue_erc20.up.sql
20260504200000_warbow_revenge_window_gl135.down.sql
20260504200000_warbow_revenge_window_gl135.up.sql

content distinct — first creates idx_doub_vesting_rescue_erc20 (#137), second creates idx_timecurve_warbow_revenge_window (#135). but sqlx primary-keys _sqlx_migrations on version. first applies fine, second collides on PK.

fix/gitlab-135-warbow-multi-revenge (merged d21855b) and the fix/gitlab-137-presale-rescue branch both picked the same migration timestamp when each was authored independently. when both merged to main neither rebased migration filenames.

Repro

clean DB with all earlier migrations already applied:

docker exec yieldomega-pg psql -U yieldomega -d postgres -c "DROP DATABASE IF EXISTS yieldomega_indexer;"
docker exec yieldomega-pg psql -U yieldomega -d postgres -c "CREATE DATABASE yieldomega_indexer OWNER yieldomega;"
cd indexer && cargo run --release
# fails on duplicate key

verified: _sqlx_migrations table after the script's reset shows 20 migrations applied successfully through 20260504200000 doub vesting rescue erc20 and the second 20260504200000 (warbow_revenge_window_gl135) cannot be inserted.

Impact

indexer cannot bootstrap from a clean DB at HEAD 16deec2
pre-TGE: anyone (ops, QA, auditor, infra replication) standing up a fresh indexer hits this immediately
existing already-migrated DBs that happened to apply only ONE of the two migrations earlier may also be silently missing the other table — risk of indexer running but with incomplete schema

Recommended fix

bump one of the two migrations to a later distinct timestamp. either:

rename 20260504200000_warbow_revenge_window_gl135.{up,down}.sql → 20260504201000_warbow_revenge_window_gl135.{up,down}.sql
or rename 20260504200000_doub_vesting_rescue_erc20.{up,down}.sql → 20260504201000_doub_vesting_rescue_erc20.{up,down}.sql

content of either migration doesn't need to change. the rename alone resolves the PK collision and lets sqlx apply both ordered.

also worth a CI check: ls indexer/migrations/ | awk -F_ '{print $1}' | sort | uniq -d should fail the build if any two migrations share a version timestamp.

blocking #144 live walk on my side until this is patched. happy to walk through any test verification once the fix lands.

@PlasticDigits — caught while bringing up the local stack for the #144 live wallet walk. fresh indexer DB cannot bootstrap on `main` at HEAD `16deec2`. ## Symptom `scripts/start-local-anvil-stack.sh` reaches the indexer step, then dies: ``` === Reset indexer database === === Start indexer (127.0.0.1:3100) === Indexer did not become ready at http://127.0.0.1:3100/v1/status ``` `/tmp/yieldomega_indexer_stack.log`: ``` 2026-05-05T03:15:18.652323Z INFO yieldomega_indexer::db: running pending migrations Error: 0: while executing migrations: error returned from database: duplicate key value violates unique constraint "_sqlx_migrations_pkey" 1: error returned from database: duplicate key value violates unique constraint "_sqlx_migrations_pkey" 2: duplicate key value violates unique constraint "_sqlx_migrations_pkey" Location: src/db.rs:16 ``` ## Root cause `indexer/migrations/` has two files claiming version timestamp `20260504200000`: ``` 20260504200000_doub_vesting_rescue_erc20.down.sql 20260504200000_doub_vesting_rescue_erc20.up.sql 20260504200000_warbow_revenge_window_gl135.down.sql 20260504200000_warbow_revenge_window_gl135.up.sql ``` content distinct — first creates `idx_doub_vesting_rescue_erc20` (#137), second creates `idx_timecurve_warbow_revenge_window` (#135). but sqlx primary-keys `_sqlx_migrations` on `version`. first applies fine, second collides on PK. `fix/gitlab-135-warbow-multi-revenge` (merged `d21855b`) and the `fix/gitlab-137-presale-rescue` branch both picked the same migration timestamp when each was authored independently. when both merged to main neither rebased migration filenames. ## Repro clean DB with all earlier migrations already applied: ``` docker exec yieldomega-pg psql -U yieldomega -d postgres -c "DROP DATABASE IF EXISTS yieldomega_indexer;" docker exec yieldomega-pg psql -U yieldomega -d postgres -c "CREATE DATABASE yieldomega_indexer OWNER yieldomega;" cd indexer && cargo run --release # fails on duplicate key ``` verified: _sqlx_migrations table after the script's reset shows 20 migrations applied successfully through `20260504200000 doub vesting rescue erc20` and the second `20260504200000` (warbow_revenge_window_gl135) cannot be inserted. ## Impact - indexer cannot bootstrap from a clean DB at HEAD `16deec2` - pre-TGE: anyone (ops, QA, auditor, infra replication) standing up a fresh indexer hits this immediately - existing already-migrated DBs that happened to apply only ONE of the two migrations earlier may also be silently missing the other table — risk of indexer running but with incomplete schema ## Recommended fix bump one of the two migrations to a later distinct timestamp. either: - rename `20260504200000_warbow_revenge_window_gl135.{up,down}.sql` → `20260504201000_warbow_revenge_window_gl135.{up,down}.sql` - or rename `20260504200000_doub_vesting_rescue_erc20.{up,down}.sql` → `20260504201000_doub_vesting_rescue_erc20.{up,down}.sql` content of either migration doesn't need to change. the rename alone resolves the PK collision and lets sqlx apply both ordered. also worth a CI check: `ls indexer/migrations/ | awk -F_ '{print $1}' | sort | uniq -d` should fail the build if any two migrations share a version timestamp. blocking #144 live walk on my side until this is patched. happy to walk through any test verification once the fix lands.

Brouie commented

2026-05-05 03:47:41 +00:00

(Migrated from gitlab.com)

mentioned in merge request !374

Brouie commented

2026-05-05 03:48:19 +00:00

(Migrated from gitlab.com)

@PlasticDigits — MR up: !374 https://gitlab.com/PlasticDigits/yieldomega/-/merge_requests/4

pure rename — bumps 20260504200000_warbow_revenge_window_gl135.{up,down}.sql → 20260504201000_warbow_revenge_window_gl135.{up,down}.sql so it applies after the doub vesting one. content untouched.

verified grep -rn '20260504200000_warbow\|warbow_revenge_window_gl135' across .sh/.toml/.yml/.md/.rs returns zero — no path-bound references anywhere. sqlx reads migrations dir by glob at runtime so the rename is fully safe.

once merged, will pull on QA server, bring up stack from clean DB, walk #144 live.

@PlasticDigits — MR up: !374 https://gitlab.com/PlasticDigits/yieldomega/-/merge_requests/4 pure rename — bumps `20260504200000_warbow_revenge_window_gl135.{up,down}.sql` → `20260504201000_warbow_revenge_window_gl135.{up,down}.sql` so it applies after the doub vesting one. content untouched. verified `grep -rn '20260504200000_warbow\|warbow_revenge_window_gl135'` across .sh/.toml/.yml/.md/.rs returns zero — no path-bound references anywhere. sqlx reads migrations dir by glob at runtime so the rename is fully safe. once merged, will pull on QA server, bring up stack from clean DB, walk #144 live.

Brouie commented

2026-05-05 04:35:55 +00:00

(Migrated from gitlab.com)

mentioned in issue #129

Brouie commented

2026-05-05 04:39:46 +00:00

(Migrated from gitlab.com)

mentioned in issue #113

Brouie commented

2026-05-05 04:59:21 +00:00

(Migrated from gitlab.com)

mentioned in issue #151

PlasticDigits commented

2026-05-05 05:35:50 +00:00

(Migrated from gitlab.com)

mentioned in commit 3d20ecf7f0

mentioned in commit 3d20ecf7f0369508246deba3fe0f1e1474f5930b

PlasticDigits commented

2026-05-05 05:35:57 +00:00

(Migrated from gitlab.com)

Fixed on main in 3d20ecf.

Renamed 20260504200000_warbow_revenge_window_gl135.{up,down}.sql → 20260504201000_warbow_revenge_window_gl135.{up,down}.sql so 20260504200000 is only doub_vesting_rescue_erc20 (no PK collision / deterministic checksum for that version).
CI (.github/workflows/unit-tests.yml): fail the job if any migration timestamp has more than two *.sql files (catches duplicate-version races).

Render / existing DBs seeing "was previously applied but has been modified": After deploy, 20260504200000 on disk matches the doub migration only; if _sqlx_migrations still disagrees, repair that row with sqlx migrate repair (same DATABASE_URL) or align the checksum after confirming the applied migration body matches doub_vesting_rescue_erc20.up.sql. Any DB that never ran the warbow migration will pick up 20260504201000 on next indexer start.

Fixed on `main` in `3d20ecf`. - Renamed `20260504200000_warbow_revenge_window_gl135.{up,down}.sql` → `20260504201000_warbow_revenge_window_gl135.{up,down}.sql` so `20260504200000` is only `doub_vesting_rescue_erc20` (no PK collision / deterministic checksum for that version). - CI (`.github/workflows/unit-tests.yml`): fail the job if any migration timestamp has **more than two** `*.sql` files (catches duplicate-version races). **Render / existing DBs seeing "was previously applied but has been modified":** After deploy, `20260504200000` on disk matches the doub migration only; if `_sqlx_migrations` still disagrees, repair that row with `sqlx migrate repair` (same `DATABASE_URL`) or align the checksum after confirming the applied migration body matches `doub_vesting_rescue_erc20.up.sql`. Any DB that never ran the warbow migration will pick up `20260504201000` on next indexer start.

PlasticDigits commented

2026-05-05 05:36:24 +00:00

(Migrated from gitlab.com)

Verification (fix works)

Tests run locally after the fix on main (3d20ecf):

Migration uniqueness guard (same logic as CI): list indexer/migrations/*.sql, take the leading timestamp from each filename, count occurrences — no version has more than two files (no duplicate timestamps).
Indexercargo clippy --all-targets -- -D warnings — clean.
Indexer cargo test — 31 tests passed (29 unit + 2 Postgres integration tests in integration_stage2.rs), including full sqlx migration embedding and DB-backed persist tests.

So sqlx can embed migrations deterministically, fresh DBs get a unique version per pair, and the suite green-lights the change.

What changed (summary)

Root cause: Two migrations shared the same sqlx version 20260504200000 (doub_vesting_rescue_erc20 and warbow_revenge_window_gl135), which breaks clean installs (duplicate primary key on _sqlx_migrations) and can surface as checksum / “modified migration” errors in deployed environments.
Code fix: Renamed the warbow migration pair to 20260504201000_warbow_revenge_window_gl135.{up,down}.sql so 20260504200000 is only the doub–vesting rescue migration; order stays before existing 20260504211000 GL139 migrations.
CI: Added a step in .github/workflows/unit-tests.yml (indexer job) that fails the workflow if any migration timestamp appears on more than two .sql files, preventing this class of collision from merging again.

Closing this issue as resolved.

## Verification (fix works) **Tests run locally** after the fix on `main` (`3d20ecf`): 1. **Migration uniqueness guard** (same logic as CI): list `indexer/migrations/*.sql`, take the leading timestamp from each filename, count occurrences — **no version has more than two files** (no duplicate timestamps). 2. **Indexer**`cargo clippy --all-targets -- -D warnings` — clean. 3. **Indexer** `cargo test` — **31 tests passed** (29 unit + 2 Postgres integration tests in `integration_stage2.rs`), including full sqlx migration embedding and DB-backed persist tests. So sqlx can embed migrations deterministically, fresh DBs get a unique version per pair, and the suite green-lights the change. --- ## What changed (summary) - **Root cause:** Two migrations shared the same sqlx version `20260504200000` (`doub_vesting_rescue_erc20` and `warbow_revenge_window_gl135`), which breaks clean installs (duplicate primary key on `_sqlx_migrations`) and can surface as checksum / “modified migration” errors in deployed environments. - **Code fix:** Renamed the warbow migration pair to **`20260504201000_warbow_revenge_window_gl135.{up,down}.sql`** so `20260504200000` is only the doub–vesting rescue migration; order stays before existing `20260504211000` GL139 migrations. - **CI:** Added a step in `.github/workflows/unit-tests.yml` (indexer job) that **fails the workflow** if any migration timestamp appears on more than two `.sql` files, preventing this class of collision from merging again. Closing this issue as resolved.

PlasticDigits (Migrated from gitlab.com) closed this issue

2026-05-05 05:36:29 +00:00

Brouie commented

2026-05-05 07:02:10 +00:00

(Migrated from gitlab.com)

mentioned in issue #152

PlasticDigits commented

2026-05-13 15:13:33 +00:00

(Migrated from gitlab.com)

mentioned in commit f2aab5371d

mentioned in commit f2aab5371d10826589def804ea5a9ac3ce59fc0d

Rows
Columns

[LAUNCH-BLOCKER] Indexer migration timestamp collision: two migrations claim version 20260504200000 (#135 + #137 races) #150

Symptom

Root cause

Repro

Impact

Recommended fix

Verification (fix works)

What changed (summary)