Organization of synchronization of the mempool bitcoin and pocketnet db

[andyoknen]

Implement the removal of transactions from sqlite db, payloads and outpoots that are not included in blocks and are missing in mempool.

Reproducing the problem

Turn on the node to the network, activate staking, activate the blocksonly=0 parameter. After a while, transactions that are not included in the chain will appear in the sqlite database.

Possible causes:

• Transactions with type 3 are the result of staking, when the created block is not accepted by the network and remains in the database for possible subsequent inclusion in the chain.
• As a result of network delays or orphans, valid transactions get into the mempull before the next block is assembled. After the block is connected to the chain, such transactions cease to be valid, for example, the limit was exceeded.

Task

It is necessary to understand the source of these transactions (additional logging?) and implement database cleanup mechanisms. Since many transactions can be included in the chain later, after the limit is released, for example, I propose to implement the deletion together with the bitcoin mechanisms - after the expiration of the transaction lifetime (1 hour by default for the pocket network)

This commit 0b0494ffd9fc2f3aaf41cebcfa6cf7f8ec1faf02 will show the code to immediately delete the transaction from the mempool in various situations - when the block is connected to the chain, when the service life expires or during reorganization. The approach to immediately deleting the transaction from the sqlite database has led to the problem of losing the transaction payload. For example, a situation where an accepted block is rolled back, some transactions are deleted from the mempool (also from sqlite db), but the new block contains one of the deleted transactions, which causes the connection of the new block to fail.

Thorough testing in the test network is necessary, with the generation of many transactions and a large number of staking in the network.

[tawmaz]

Progress on this task will be on this branch: https://github.com/tawmaz/pocketnet.core/commits/mempoolclean At present I have a query which will clear all orphaned transactions which have not made it onto a block after one hour. On testnet there are not enough transactions or block rollbacks to verify the code. Some additional tasks I would to complete before testing on mainnet:

1. Re-enable the mempool_tests.cpp unit test to verify mempool edge cases are not violated by this change and add pocketnet transactions as part of this test.
2. Create a private "validatedb" RPC API to go through transactions in the database and make sure there are no orphan transactions in the database which have not actually made it onto blocks.

[andyoknen]

@tawmaz Is there any progress on this task?

[tawmaz]

I am now testing my changes on mainnet because there are not enough block rollbacks to adequately test on testnet. Recent changes have been pushed to my mempoolclean branch in my repo.

[tawmaz]

Currently testing fix on a mainnet node, however it does not appear the node sqlite db has any expired mempool transactions or transaction records which are not on the block chain. It is possible that the first run of the code cleaned out those transactions. Will continue letting it run for a week to verify stability and that all stray records are removed from the database.

[tawmaz]

I have added some verification code to ensure all transactions in the Transactions table with assigned block hashes are also in the block chain. Need to add an additional check to verify all mempool transactions are accurately represented in the Transactions table.

[tawmaz]

I wrote a function here to find orphan transactions in the Transactions database which are in neither the BlockChain or mempool: https://github.com/tawmaz/pocketnet.core/blob/mempoolclean/src/rpc/blockchain.cpp#L1069 There are a few hundred transactions in the Transactions database on my node which the function reports as orphans. As a next step I can try deleting these and see if additional orphan transactions accumulate in the database. More investigation is needed to find the specific case causing these transactions to be orphaned.

[andyoknen]

Cool

[tawmaz]

An update on progress of this task. I wrote an RPC method which brute force deletes any orphan transactions from the Transactions table which are not in the mempool or the blockchain. Letting it run overnight I see some new orphan transactions are added to the Transactions table, so somehow there are still some transactions getting in. I seem to have introduced an issue where the node crashes silently so that will require further investigation.

[tawmaz]

I am going to attempt a different approach with this issue, as it is too difficult to debug on a live network. I plan to enable regtest tests, then modify some tests to add transactions into the mempool, move them to blocks, then roll back the blocks, and verify the DB is in the correct state at each step to see if we can detect where the extra transactions are being left.

[andyoknen]

The problem is still present. Help is needed in the solution.