Reward table off by a few lovelaces

[xdzurman]

I could not send a transaction through cardano-submit-api, it said "WithdrawalsNotInRewardsDELEGS", implying wrong rewards, or trying to withdraw not all rewards. I checked all parts, where it could have gone wrong. After that, I compared rewards for random addresses gotten from db-sync and from yoroi api. These are the mismatches for a few (in lovelaces):

Each row represents db-sync rewards from this SQL:
SELECT (SELECT COALESCE(SUM(rewards.amount), 0) FROM ( SELECT amount FROM reward WHERE addr_id=$1 UNION ALL SELECT amount FROM reserve WHERE addr_id=$1 ) rewards ) - ( SELECT COALESCE(SUM(amount), 0) FROM withdrawal WHERE addr_id=$1 ) AS "remainingRewards"
minus rewards from yoroi api.

What's going on @erikd ?

Edit: yoroi also gets it from db-sync, so these are possibly diffs of two wrong values. Either way, the error originates in db-sync

[SebastienGllmt]

We've also noticed this in Yoroi

Here are the results for 7 different instances of cardano-db-sync where the real value is 30428426

backend#1 30428424
backend#2 30428424
backend#3 30428424
backend#4 30428423
backend#5 30428430
backend#6 30428424
backend#7 30428426

As you can see, only 1/7 backends got the correct result

We've double-checked that these incorrect values are stored in the SQL database, so we know this issue isn't in our part of the stack

[erikd]

@SebastienGllmt What? Different db-sync instances give different results??? Are they all running the same version?

[erikd]

@xdzurman Would you be able to simplify that SQL query? It would be nice to have three columns, reward, reserve and difference.

[erikd]

I suspect that query is actually wrong. The rewards table looks like this (my instance is not currently synced):

cexplorer=# select * from reward ; 
 id | addr_id | amount | epoch_no | pool_id | block_id 
----+---------+--------+----------+---------+----------
(0 rows)

So for each epoch there is a reward amount (ie a snapshot of the rewards for the epoch) and without exploring it properly, i am not even sure what is in the reserve table.

[SebastienGllmt]

@erikd This is the query we ran on the 7 different backends that gave different results. They're all the same version of cardano-db-sync and same node version

  select stake_address.hash_raw as "stakeAddress"
       , "totalReward".*

  from stake_address

  left outer join (
    SELECT addr_id, amount
    FROM reward
  ) as "totalReward" on stake_address.id = "totalReward".addr_id

  where encode(stake_address.hash_raw, 'hex') = 'e1567298bc998d188d936f105f9e9e616f1980adacc06fc5732a57f9cc'

The address specified in this SQL query has only ever earned rewards once so it's easier to work with.

[xdzurman]

I suspect that query is actually wrong. The rewards table looks like this (my instance is not currently synced):

cexplorer=# select * from reward ; 
 id | addr_id | amount | epoch_no | pool_id | block_id 
----+---------+--------+----------+---------+----------
(0 rows)

So for each epoch there is a reward amount (ie a snapshot of the rewards for the epoch) and without exploring it properly, i am not even sure what is in the reserve table.

I assume that - The reserve holds ITN rewards. The rewards table specifies rewards received at the start of that epoch. So the remaining rewards is the sum of all rewards and itn rewards minus the withdrawals.

[tobg]

We discovered similar issues. 137 delegators has withdraw exactly 500 ADA. Shown inside db-sync db in the table withdrawal. Some of them got a negative balance after that. Either this is connected to this issue or we are doing something wrong on our end.

For example:

One has withdrawn 500 ADA but got only 200 ADA according to db-sync. My Example is for Epoche at or after 214

[mmahut]

Does #413 fixes this as well?

[erikd]

@mmahut I am not sure. The bug fixed on #413 could easily have caused this problem as well. Or there might be more than one issue. @xdzurman ?

[erikd]

@mmahut This is across separate instances? I need an easy way to produce this and having more than one instance is not an easy reproduction case.

[mmahut]

Sorry, I did call it prematurely. It looks like the problem is fixed, I'm monitoring it and will report back in few days.

[xdzurman]

It looks fixed for me as well, closing for now. Feel free to reopen if any problems occur.