Parse recent transactions with bitcoin core in pruned mode

[reardenlife]

I would like to implement the checker of balance with bitcoin core.  For example I have a million of pregenerated addresses and I want to check if balance on any of them had beed changed.  So I got a bitcoin core node running in a pruned mode (there is no point for me to hold the whole blockchain).  I wrote a following POC explorer on bash:

Code:

#!/usr/bin/env bash
set -e
set -o errexit

function process_block() {
  date --rfc-3339=ns
  bhash=${1:-$(bitcoin-cli getbestblockhash)}
  block=$(bitcoin-cli getblock $bhash)
  pbhash=$(echo "$block" | jq -r .previousblockhash)
  height=$(echo "$block" | jq -r .height)
  echo "height: $height"
  nTx=$(echo "$block" | jq -r .nTx)
  echo "$nTx transactions"
  Tx=$(echo "$block" | jq -r .tx[])
  IFS=$'\n'
  for txhash in $Tx; do
    txjson=$(bitcoin-cli getrawtransaction $txhash 1 $bhash)
    addresses=$(echo "$txjson" | jq -r '.vout[].scriptPubKey | select(.type == "pubkeyhash") | .addresses | .[]')
    for a in $addresses; do
      if [[ $a == ${2} ]]; then
        echo "$txjson"
      fi
    done
  done
  process_block $pbhash ${2}
}

process_block "" ${1}

This way I have an access to the destination address and the amount transferred.

The only problem is that it just just too slow.  I have SSD, 2GB RAM and 2x2.8Ghz VPS and I am only able to process about 10 transactions per second.

I haven't run the profiler yet, but I bet it's because of the absence of txindex.

Any idea how my problem can be solved?

[reardenlife]

Thanks for the advice, but it is simply too cumbersome.  
I will look towards third party tools then.

Edit: alright.  We got getblock with verbosity = 2 here.  That should do.  https://bitcoincore.org/en/doc/0.16.0/rpc/blockchain/getblock/

[ABCbits]

Edit: alright.  We got getblock with verbosity = 2 here.  That should do.  https://bitcoincore.org/en/doc/0.16.0/rpc/blockchain/getblock/

That's crude way, but it's good as long as you don't mind additional time to check all transaction.

You might be interested with blocknotify parameter on bitcoin.conf file, it's similar with walletnotify except it's executed when a block mined and %s refers to block hash.

[reardenlife]

Alright.  About 30 transactions per second.  Slow but acceptable.

Code:

#!/usr/bin/env bash
set -e
set -o errexit

#if tty -s; then
export TERM=screen-256color
_bold=$(tput bold)
_underline=$(tput sgr 0 1)
_reset=$(tput sgr0)
_purple=$(tput setaf 171)
_red=$(tput setaf 1)
_green=$(tput setaf 82)
_tan=$(tput setaf 3)
_blue=$(tput setaf 39)
#fi
function _success() { printf "${_green}[ok]${_reset}: %s\n" "$@"; }
function _error() { printf "${_red}[error]${_reset}: %s\n" "$@"; }
function _warning() { printf "${_tan}[warning]${_reset}: %s\n" "$@"; }
function _info() { printf "${_blue}[info]${_reset}: %s\n" "$@" ; }
function _header() { printf '\n%s%s==========  %s  ==========%s\n' "$_bold" "$_purple" "$@" "$_reset" ; }

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd)"
cd "$DIR"

# Make sure that only one copy of the script is currently running
#
src=$(realpath ${BASH_SOURCE[0]})
filename=${src##*/}
ps=$(o=""; for p in $(pgrep -f "$filename"); do o="${o}$(pstree -lp | grep -vF \($$\) | grep -nF \($p\))\n"; done; echo -e "$o" | cut -f1 -d: | sort -n | grep -vE ^$ | uniq -c | awk '$1>1' | wc -l)
if (( $ps > 0 )); then
  _warning "$(date --rfc-3339=seconds) :: $ps extra processes are running; exiting now"
  #for p in $(pgrep -f "$filename"); do pstree -lp | grep -vF \($$\) | grep -nF \($p\); done;
        exit 1
fi

file="db.config.xml"
mysql_host=$(xmlstarlet sel -t -v config/host "$file")
mysql_db=$(xmlstarlet sel -t -v config/db "$file")
mysql_user=$(xmlstarlet sel -t -v config/user "$file")
mysql_password=$(xmlstarlet sel -t -v config/password "$file")

cookie=~/.bitcoin/.cookie

function process_block() {
  _header "$(date --rfc-3339=seconds)"
  bhash=${1:-$(bitcoin-cli -rpccookiefile=$cookie getbestblockhash)}
  block=$(bitcoin-cli -rpccookiefile=$cookie getblock $bhash)
  pbhash=$(echo "$block" | jq -r .previousblockhash)
  height=$(echo "$block" | jq -r .height)
  echo "height:$height"
  nTx=$(echo "$block" | jq -r .nTx)
  echo "transactions:$nTx"
  mediantime=$(date -d @$(echo "$block" | jq -r .mediantime) --rfc-3339=seconds)
  echo "mediantime:$mediantime"


  # Make sure not to process unrelevant history
  #
  r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT MIN(height) FROM explorer_poc_blocks_processed;
EOF
)
  if (( $height < $r )); then
    exit 1
  fi

  # Make sure such block wasn't already processed
  #
  r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT COUNT(*) FROM explorer_poc_blocks_processed WHERE height = '$height';
EOF
)
  if (( $r == 0 )); then

  block=$(bitcoin-cli -rpccookiefile=$cookie getblock $bhash 2)
  Tx=$(echo "$block" | jq -r .tx[])
  Tx=$(echo "$Tx" | jq . | jq . -c)

  sql="START TRANSACTION;"
  IFS=$'\n'
  aTx=($(echo "$Tx"))
  tsstart=$(date +%s%N | cut -b1-13)
  #i=0
  #for txhash in $Tx; do
  for (( i=0;i<nTx;i++ )); do
    if (( $(($i%100==42)) )); then
      p=$(echo "$i*100/$nTx" | bc)
      tsnow=$(date +%s%N | cut -b1-13)
      opsec=$(echo "scale=2;$i/(($tsnow-$tsstart)/1000)" | bc)
      printf "$p%% ($opsec tx/sec)\r"
    fi

    #txjson=$(bitcoin-cli -rpccookiefile=$cookie getrawtransaction $txhash 1 $bhash)
    txjson="${aTx[$i]}"

    r=$(echo "$txjson" | jq -r '. as $tx | .vout[] | select(.scriptPubKey.type == "pubkeyhash") | $tx.txid, .value, .scriptPubKey.addresses[]' | grep . || true )
    if [ -z "$r" ]; then continue; fi
    r=$(echo "$r" | awk 'NR%3==2 {printf("%d\n", sprintf("%.08f", $0*100000000)); next} {print $0}' | awk '{printf "'\''%s'\''," (NR%3==0?RS:FS),$1}')
    sql=${sql}$(echo "$r" | sed -e "s/^/INSERT INTO explorer_poc_transactions(block_height, hash, amount, address_to) VALUES('$height',/" | sed -e 's/,$/);/')
    #i=$((i+1))
  done
  echo
  sql="${sql}INSERT INTO explorer_poc_blocks_processed(height) VALUES('$height');"
  sql="${sql}COMMIT;"
  mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  $sql
EOF

  # Remove the transactions to the addresses that are not found in the 'addresses_to_check' table
  #
  mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  DELETE explorer_poc_transactions FROM explorer_poc_transactions LEFT JOIN addresses_to_check ON explorer_poc_transactions.address_to = addresses_to_check.address WHERE addresses_to_check.address IS NULL;
EOF

  # Update the 'addresses_to_check'.balance by the information received from the parsed block
  #
  # Get the list of addresses that were updated
  #
  r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT DISTINCT address_to FROM explorer_poc_transactions, addresses_to_check WHERE address_to = addresses_to_check.address AND block_height = '$height';
EOF
)
  for a in $r; do
    _success "  -> address:$a"
    # Get the sum of the amounts of all transaction to the address
    #
    b=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
    SELECT SUM(amount) FROM explorer_poc_transactions WHERE address_to = '$a';
EOF
)
    _success "  -> amount:$b"
    # Update the 'addresses_to_check'.balance with the calculated amount
    #

    mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
                UPDATE addresses_to_check SET last_changes = NOW(), balance = '$b' WHERE address = '$a' AND balance <> '$b';
EOF
  done

  else
    echo "Skipping..."
    # No point of going any further?
    #
    r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
    SELECT MAX(height)-MIN(height)=COUNT(*)-1 FROM explorer_poc_blocks_processed;
EOF
)
    if (( $r == 1 )); then
      echo "No point of going any further."
      exit 2
    fi
  fi
  process_block $pbhash
}

process_block ""

If anyone knows how to speed it up - suggestions are welcome.  The bottleneck right now is a text processing in bash.  As you can see I have a lot of pain converting the BTC into satoshi; keep in mind that jq outputs values in scientific notation, so sprintf is absolutely necessary.

[ABCbits]

If anyone knows how to speed it up - suggestions are welcome.  The bottleneck right now is a text processing in bash.  As you can see I have a lot of pain converting the BTC into satoshi; keep in mind that jq outputs values in scientific notation, so sprintf is absolutely necessary.

There are 3 options :
1. Write your script on another language (such as C, C++ or Go) which have fast performance.
2. Parallel some repetitive task (usually command in for/while statement), but it could get complicated quickly. See https://unix.stackexchange.com/q/103920
3. Micro-optimize your bash script (not recommended)

I'm not linux or bash expert, so someone might have better suggestion.

[reardenlife]

Wait a second.  For my purposes of balance checking I could just scan the mempool (via getrawmempool ) as soon as new transactions are arriving and get the info about it via getrawtransaction.  Once the transaction is in mempool, it is going to be mined sooner or later regardless of the fee.  Correct?

[Lauda]

Wait a second.  For my purposes of balance checking I could just scan the mempool (via getrawmempool ) as soon as new transactions are arriving and get the info about it via getrawtransaction.  Once the transaction is in mempool, it is going to be mined sooner or later regardless of the fee.  Correct?

Not entirely true, but yes. Keep in mind that a transaction might be included in a block before it even gets into your mempool.

[bob123]

Once the transaction is in mempool, it is going to be mined sooner or later regardless of the fee.  Correct?

No.

Transactions can be dropped out of the mempool without being confirmed.
For example if RBF is enabled.

For example:
Transaction A sends 1 BTC to address X.
Transaction B sends the same input (1 BTC) to address Y.

Transaction A reaches your mempool, with RBF enabled. You would then add that balance to address X in your database.
Then transaction B is being broadcasted and will be included. Your database would then show both (address X and Y) to have received 1 BTC each, which is not the case.

You need to wait for at least 1 confirmation before adding/updating balances in your database or you risk having a wrong entries in your database.


RBF-transactions are quite common. Not necessarily with different addresses, but that doesn't change much.
If i send a transaction with a low fee, you would add it in your database.
Then i'll bump the fee and send a new transaction to the same address. You would have twice the amount credited in the database.

This would happen quite often.

If you regard transactions as final or confirmed while they are not included in a block yet, you will end up with wrong entries.

[Lauda]

Transactions can be dropped out of the mempool without being confirmed.
For example if RBF is enabled.

He can trivially bypass that by double-checking the database entries periodically until there are multiple confirmations. Conflicts are marked accordingly by the software anyway. Hence:

Not entirely true, but yes.

[bob123]

He can trivially bypass that by double-checking the database entries periodically until there are multiple confirmations.

If he would be checking confirmations of transactions, he also could simply just get the transactions from each received block.

But OP is having the issue that it is too slow. And i doubt this approach would be faster.

[Lauda]

He can trivially bypass that by double-checking the database entries periodically until there are multiple confirmations.

If he would be checking confirmations of transactions, he also could simply just get the transactions from each received block.

But OP is having the issue that it is too slow. And i doubt this approach would be faster.

Depends. We're talking about checking transactions individually vs. bulk checking from blocks. Alternatively, he could differently handle RBF enabled transactions from the mempool.

[reardenlife]

Is there any way to get a notification about incoming transaction in the mempool?

Edit: There seems to be zmqpubrawtx.  I wonder if I should try to use it or just sort the new transactions with the means of my DB.

[reardenlife]

Transactions can be dropped out of the mempool without being confirmed.
For example if RBF is enabled.

He can trivially bypass that by double-checking the database entries periodically until there are multiple confirmations. Conflicts are marked accordingly by the software anyway. Hence:

Not entirely true, but yes.

Huh oh.
So if I will pick up the transaction from the mempool and update the balance in my DB.  Then if due to RBF, I will pick up another transaction with a higher fee.  The hash of that transaction will be different (since fee is kept inside the transaction).  But how I will determine if this new transaction should replace the prior one so instead of increasing the balance in my DB I would just keep it as it is?

[Lauda]

Transactions can be dropped out of the mempool without being confirmed.
For example if RBF is enabled.

He can trivially bypass that by double-checking the database entries periodically until there are multiple confirmations. Conflicts are marked accordingly by the software anyway. Hence:

Not entirely true, but yes.

Huh oh.
So if I will pick up the transaction from the mempool and update the balance in my DB.  Then if due to RBF, I will pick up another transaction with a higher fee.  The hash of that transaction will be different (since fee is kept inside the transaction).  But how I will determine if this new transaction should replace the prior one so instead of increasing the balance in my DB I would just keep it as it is?

You check whether each transactions have any confirmations after X time. The replaced transaction will not have any.

[reardenlife]

Alright.

So in addition to abovepublished blockchain explorer I made another bash script - mempool explorer.
The script starts periodically (every minute) and moves all mempool transactions into DB, takes a batch of 500 from unprocessed/unflagged ones and through getrawtransaction retrieves an info on each of them; all successfully retrived addresses along with amounts and transaction hashes are dumped into another table and the respective mempool transactions are flagged as processed.  Then I am determining if any of the dumped transactions have outputs on my addresses and if they do, I am incrementing the balance in my DB by the value of the output.  Since RBF are possible I am waiting until blockchain explorer will discover a block and in case RBF indeed occurred, it will return the balance to the right value.  Blockchain explorer also deletes transactions that it is found in the block from the table with which mempool explorer works.

Everything seems to be working nice and smooth.  Every minute the mempool explorer picks up a few hundred new transactions, quickly processes them and updates the balance table if anything new appeared.

* I also noticed that LEFT JOIN between the table with addresses picked up from the block/mempool and between the table with addresses which balance is checked can take quite a while even with indexes on hashes of transactions.  So I decided to perform this operation on the separate server.

Thanks everyone for the help.

[bob123]

I hope you are using that tool just as a personal project without much more use.

You should - under no circumstances - rely on this tool if you are running a webshop or any other kind of service where the payments are automatically handled.

Adding something to your DB first, and later checking whether it really confirmed is highly error-prone. It for sure is good enough for a personal research project, but is definitely not suited for a business / something business-like.

[reardenlife]

I hope you are using that tool just as a personal project without much more use.

You should - under no circumstances - rely on this tool if you are running a webshop or any other kind of service where the payments are automatically handled.

Adding something to your DB first, and later checking whether it really confirmed is highly error-prone. It for sure is good enough for a personal research project, but is definitely not suited for a business / something business-like.

You are talking about RBF case? If so, please provide the info how the situation can be handled properly.  Personally, I cannot see how.  The node should provide some kind of pool of revoked transactions, but I cannot find it anywhere.

At least for now I can only display the balance as unconformed (which was grabbed by mempool explorer) until it will be validated by the blockchain explorer.

And, actually, I am using this tool for the shop.  At least by now it is much better than it was before - relying on third party to get the balance.

[bob123]

You are talking about RBF case? If so, please provide the info how the situation can be handled properly.  Personally, I cannot see how. 

You should wait for at least one confirmation before inserting it into your database.

The node should provide some kind of pool of revoked transactions, but I cannot find it anywhere.

There are no 'revoked' transactions.
Transactions are only final once they got included into a block. Afterwards they can not be 'revoked'.
Transactions which aren't confirmed yet, are by far not final. They also don't have to confirm at all. They just have been broadcasted which basically means "i want to do the following: send X from A to B".

And, actually, I am using this tool for the shop.  At least by now it is much better than it was before - relying on third party to get the balance.

Why do you need to check the balance of 1.000.000 pregenerated addresses ?
To me, it seems like that is the first design flaw.

Usually, you don't need to pregenerate so much addresses. Just generate them whenever needed.
And then just use walletnotify and blocknotify to get notified whenever the status changes (i.e. transaction received / transaction got 1st confirmation).
You can take a look at this thread, where someone used core for their shop.

[reardenlife]

You are talking about RBF case? If so, please provide the info how the situation can be handled properly.  Personally, I cannot see how.  

You should wait for at least one confirmation before inserting it into your database.

In order to avoid 51% attack on blockchain?  What is the reason behind it?

The node should provide some kind of pool of revoked transactions, but I cannot find it anywhere.

There are no 'revoked' transactions.
Transactions are only final once they got included into a block. Afterwards they can not be 'revoked'.
Transactions which aren't confirmed yet, are by far not final. They also don't have to confirm at all. They just have been broadcasted which basically means "i want to do the following: send X from A to B".

They are kinda revoked from my perspective.  The idea that one transaction can replace another one because it has a higher fee is a mess.  

Was there a case when the valid signed transaction that appeared in the mempool wasn't mined I wonder?

And, actually, I am using this tool for the shop.  At least by now it is much better than it was before - relying on third party to get the balance.

Usually, you don't need to pregenerate so much addresses. Just generate them whenever needed.

Edit:
That would require two servers to be online in order for the service to accept the payments - the one with Bitcoin Core and another one with the DB of a service.  And if, for some reason the server with Bitcoin Core goes down, ... I will not be able to accept the payments?

Edit2:
* Of course I can't hold Bitcoin Core on the same server with the payment service itself - that would be a huge security flaw.

[bob123]

You are talking about RBF case? If so, please provide the info how the situation can be handled properly.  Personally, I cannot see how. 

You should wait for at least one confirmation before inserting it into your database.

In order to avoid 51% attack on blockchain?  What is the reason behind it?

51% attack ?
That's not related to a 51% attack.

The transaction did not take place until it has been confimed.

They are kinda revoked from my perspective.  The idea that one transaction can replace another one because it has a higher fee is a mess. 

Broadcasting a transaction does not mean that it will be confirmed. Therefore replacing such a transaction is not revoking.
You also don't need to use RBF to get a different transactoin confirmed.

There are multiple ways of double spending, mostly with 1 actor believing only 1 transactions will confirm, while in fact a different one confirms making the first one invalid.

A transaction did only happen, if it has been included into a block. Anything else is just expressing the intention to transact.

Was there a case when the signed transaction that appeared in the mempool wasn't mined I wonder?

For sure. Especially transactions with a low fee (during a time with a lot of transactions) which then either got replaced or entirely dropped by the mempool.

That would require two servers to be online in order for the service to accept the payments - the one with Bitcoin Core and another one with the DB of a service.  And if, for some reason the server with Bitcoin Core goes down, ... I will not be able to accept the payments?

You can still accept payments, simply use the master public key to derive addresses.
Once your core-server goes back up, all your payments (received when it was down) will get processed again.

* Of course I can't hold Bitcoin Core on the same server with the payment service itself - that would be a huge security flaw.

Not necessarily. You shouldn't run your website / webserver and core on the same machine.
But your implementation of the payment service and core can run on the same server.

Or are you - with "payment service" - referring to your web server ? Then yes, it would be better to use 2 different server.