Resync from genesis after HF crashes at Epoch 505

[tobg]

OS
Your OS: Ubuntu 22.04

Versions
db-sync: 13.5.0.2
PostgreSQL version: 14
Cardano node: 9.1.1

Build/Install Method
official docker image

Run method
docker

Additional context
Add any other context about the problem here.

Problem Report
[db-sync-node:Info:89] [2024-09-10 12:26:14.90 UTC] Found snapshot file for slot 132796784, hash aaec72aef649abdfc93221389e9b83f8583f1b6ca2aaf1434836423023891fae
[db-sync-node:Info:89] [2024-09-10 12:26:14.90 UTC] Setting ConsistencyLevel to DBAheadOfLedger
[db-sync-node:Info:89] [2024-09-10 12:26:20.93 UTC] File /var/lib/cexplorer/132796784-aaec72aef6-504.lstate exists
[db-sync-node:Info:89] [2024-09-10 12:26:24.24 UTC] Reached EpochNo 505
[db-sync-node:Info:89] [2024-09-10 12:26:55.60 UTC] Received block which is not in the db with HeaderFields {headerFieldSlot = SlotNo 133054113, headerFieldBlockNo = BlockNo 10751243, headerFieldHash = 704f6e581c781aa617a19ddbb9062c9c7f92fee282ce7a284dbf6c780beaa16a}. Time to restore consistency.
[db-sync-node:Error:89] [2024-09-10 12:26:55.69 UTC] runDBThread: DbInsertException "Many Rewards" (SqlError {sqlState = "42703", sqlExecStatus = FatalError, sqlErrorMsg = "record "new" has no field "id"", sqlErrorDetail = "", sqlErrorHint = ""})
[db-sync-node:Error:119] [2024-09-10 12:26:55.69 UTC] recvMsgRollForward: AsyncCancelled
[db-sync-node:Error:114] [2024-09-10 12:26:55.69 UTC] ChainSyncWithBlocksPtcl: AsyncCancelled
[db-sync-node.Subscription:Error:110] [2024-09-10 12:26:55.69 UTC] Identity Application Exception: LocalAddress "/node-ipc/node.socket" SubscriberError {seType = SubscriberWorkerCancelled, seMessage = "SubscriptionWorker exiting", seStack = []}
cardano-db-sync: DbInsertException "Many Rewards" (SqlError {sqlState = "42703", sqlExecStatus = FatalError, sqlErrorMsg = "record "new" has no field "id"", sqlErrorDetail = "", sqlErrorHint = ""})

[Cmdv]

we are just investigating this as it seems that the db is missing an id field on the reward table. Will report back with more info. 👍

[kderme]

How did you run db-sync? Did you sync from genesis with a single release or upgraded from a different release or did you start from a snapshots?

[Fell-x27]

How did you run db-sync? Did you sync from genesis with a single release or upgraded from a different release or did you start from a snapshots?

I can confirm. I've tested it with both snapshot and genesis-sync. reward.id is missed.

[tobg]

How did you run db-sync? Did you sync from genesis with a single release or upgraded from a different release or did you start from a snapshots?

From genesis with single release 13.5.0.2. Fresh db.

[rdlrt]

Tho reward.id was intentionally dropped in PR #1607 ?

[kderme]

Tho reward.id was intentionally dropped in PR #1607 ?

Yes since it's never really used

[Cmdv]

When I ran cabal run cardano-db-tool -- create-migration --mdir schema/ it adds:

EXECUTE 'ALTER TABLE "reward" ADD COLUMN "id" INT8 NOT NULL' ;
EXECUTE 'ALTER TABLE "reward_rest" ADD COLUMN "id" INT8 NOT NULL' ;

Is there an issue of our haskell representation of the schema, which then tries to insert an id?!?

[Cmdv]

Is this still an issue?

[Fell-x27]

Yes since it's never really used

I use it...