Issue with missing staking data on Index DB

[hcho112]

Problem

When a user stake against after their first, previous/existing staking information is gone and only showing consecutive staking information. Eg. User staked 0.5N at Validator A and then staked 1.0N at Validator B, it only returns Validator B with total staked 1.0N This behaviour seems consistent across following places: Near Wallet MNW Meteor Wallet Legacy explorer Near block (it even returns 0N which is more broken)

One place that shows correct figure is: Near Scope

Background

I have received several issues from Near Foundation team that they received complain from users that their staking figure is not showing properly and some are panicking about it.

Then I have attempted to replicate the issue myself on mainent environment and unfortunately I was able to replicate the issue. test account I used: monkeypatcher.near First staking at: stake1.poolv1.near with 0.5N Second staking at: hashquark.poolv1.near with 1.0N

On all the places I have mentioned above, it was returning only one record with 1.0N. Eg. Tested from Near Wallet:

Request from kit wallet:

Response from kit wallet:

But on Near scope, it does show both records:

With help from @morgsmccauley , we actually conduct more test on indexer to check if there an issue is happening from query itself or data. After further investigation with query, we conclude that the issue is to do with data. (we couldn't retrieve the data record for first staking.

This issue need to be resolved quickly because some of the users are locked from withdrawing their first (or any staking that are not last staking) amount because it is not showing up on UI itself.

[morgsmccauley]

For reference, here is the query used within near-wallet/kitwallet:

with deposit_in as (
    select SUM(to_number(args ->> 'deposit', '99999999999999999999999999999999999999')) deposit,
        receipt_receiver_account_id validator_id
    from action_receipt_actions
    where
        action_kind = 'FUNCTION_CALL' and
        args ->> 'method_name' like 'deposit%' and
        receipt_predecessor_account_id = 'monkeypatcher.near' and
        receipt_receiver_account_id like ANY(ARRAY[
            '%.poolv1.near', '%.pool.near' -- For mainnet
        ])
    group by receipt_receiver_account_id
), deposit_out as (
    select SUM(to_number(args ->> 'deposit', '99999999999999999999999999999999999999')) deposit,
        receipt_predecessor_account_id validator_id
    from action_receipt_actions
    where
        action_kind = 'TRANSFER' and
        receipt_receiver_account_id = 'monkeypatcher.near' and
        receipt_predecessor_account_id like ANY(ARRAY[
            '%.poolv1.near', '%.pool.near' -- For mainnet
        ])
    group by receipt_predecessor_account_id
)
select sum(deposit_in.deposit - coalesce(deposit_out.deposit, 0)) deposit, deposit_in.validator_id
from deposit_in
left join deposit_out on deposit_in.validator_id = deposit_out.validator_id
group by deposit_in.validator_id;

This should return two rows as the account has staked with two different validators, but it only returns one, the most recent:

          deposit          |     validator_id      
---------------------------+-----------------------
 1000000000000000000000000 | hashquark.poolv1.near

Looking deeper in to action_receipt_actions, the account only has one related receipt:

select *
from action_receipt_actions
where
    action_kind = 'FUNCTION_CALL' and
    args ->> 'method_name' like 'deposit%' and
    receipt_predecessor_account_id = 'monkeypatcher.near'

                  receipt_id                  | index_in_action_receipt |  action_kind  |                                                                     args                                                                     | receipt_predecessor_account_id | receipt_receiver_account_id | receipt_included_in_block_timestamp | is_delegate_action | delegate_parameters | delegate_parent_index_in_action_receipt 
----------------------------------------------+-------------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------------------------+-------------------------------------+--------------------+---------------------+-----------------------------------------
 AT8NjyPb8XZVn4V58xsbQiHELERHuauQx8M6YaG4gxSC |                       0 | FUNCTION_CALL | {"gas": 125000000000000, "deposit": "1000000000000000000000000", "args_json": {}, "args_base64": "e30=", "method_name": "deposit_and_stake"} | monkeypatcher.near             | hashquark.poolv1.near       |                 1702414003701982751 | f                  |                     |                                        
(1 row)

[pkudinov]

The data is present in NEAR Lake. So this is an indexing issue on Explorer side: receipt 3oTBe8Q5TgHpK7sjbuNAmFE2MmtYFeqns4YLxsGY2MR1 from transaction hash 7qnWamhwdhgbFHQg827AnrvcNgAM3wS2H7oV2gXZFmTi is missing from Explorer DB along with corresponding action_receipt_actions record.

Block timestamp: 1702413944408257894 (2023-12-12 20:45:44.408) Block height: 107787122

[morgsmccauley]

Did some more investigation - this ended up being related to the indexer being behind rather than being an issue within the code. The query is now returning the correct results.

indexer-explorer-mainnet is currently configured to start from a specific block. This configuration was left over from the recent fixes we made to cope with NEAT. This node, running in strict mode, can't be updated to start-from-interruption, as that would cause it to start form the latest block overall. With indexer-explorer-mainnet-2 running in non-strict mode, and therefore always ahead, starting from the latest block would cause the strict node to skip blocks.

indexer-explorer-mainnet has been shown to occasionally restart (not sure why), which with its current start-from-block configuration, causes it to jump back introducing the large lag.

I've updated indexer-explorer-mainnets start block to the highest value it had previously seen, and it will now take ~5 hours to catch the tip. Until we have a permanent fix for NEAT in place, I'll keep an eye on indexer-explorer-mainnet to ensure it doesn't fall behind.

[morgsmccauley]

This issue was not resolved for some accounts - after further investigation, I found a sequence of blocks which weren't indexed by Explorer: 106849098 to 106865393. Again, probably related to the work we did with NEAT.

I've restarted Explorer Indexer from 106849098 and will monitor until it fills the gap.