Bitcoin Forum

Bitcoin => Bitcoin Technical Support => Topic started by: reardenlife on September 03, 2019, 03:01:27 AM



Title: Parse recent transactions with bitcoin core in pruned mode
Post by: reardenlife on September 03, 2019, 03:01:27 AM
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?


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: ETFbitcoin on September 03, 2019, 03:49:57 AM
Bitcoin Core isn't designed for such task and there's no indexing for Bitcoin address. If you insist to use Bitcoin Core, then you could try :
1. Import million of pregenerated address (make sure you don't rescan each time you import address)

Code:
bitcoin-cli importaddress "myaddress" "" false

2. Run rescanblockchain to get amount of all imported address. It'll take hours depending on your device specification

Code:
rescanblockchain

3. Make bash script and configure it to be executed when there's wallet changes. %s is the txid

P.S. i never tested it on watch-only wallet

Code:
walletnotify=~/notify.sh %s


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: reardenlife on September 03, 2019, 04:48:47 AM
Thanks for the advice, but it is simply too cumbersome.  ;D
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/


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: ETFbitcoin on September 03, 2019, 05:03:21 PM
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.


Title: bitcoin explorer with mysql database
Post by: reardenlife on September 04, 2019, 12:22:09 AM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: ETFbitcoin on September 04, 2019, 09:24:46 AM
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 (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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: reardenlife on September 06, 2019, 01:59:45 PM
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?


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: Lauda on September 06, 2019, 02:00:36 PM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: bob123 on September 06, 2019, 02:07:37 PM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: Lauda on September 06, 2019, 02:11:33 PM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: bob123 on September 06, 2019, 02:16:19 PM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: Lauda on September 06, 2019, 02:21:32 PM
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.


Title: Mempool notification?
Post by: reardenlife on September 06, 2019, 10:10:17 PM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: reardenlife on September 07, 2019, 01:51:41 AM
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?


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: Lauda on September 07, 2019, 07:20:00 AM
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.


Title: mempool explorer
Post by: reardenlife on September 08, 2019, 04:37:21 AM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: bob123 on September 08, 2019, 10:44:10 AM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: reardenlife on September 08, 2019, 01:17:15 PM
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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: bob123 on September 08, 2019, 01:37:15 PM
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 (https://bitcointalk.org/index.php?topic=5173676.0), where someone used core for their shop.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: reardenlife on September 08, 2019, 02:10:40 PM
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.  ;D

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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: bob123 on September 08, 2019, 02:40:49 PM
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.  ;D

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.


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: reardenlife on September 08, 2019, 03:16:02 PM
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.

Hmm... I had an impression that I have to use seed to generate addresses by certain derivation path.  But it is possible to use just bip44 public key to generate addresses by derivation path as well?


Title: Re: Parse recent transactions with bitcoin core in pruned mode
Post by: bob123 on September 08, 2019, 03:50:21 PM
Hmm... I had an impression that I have to use seed to generate addresses by certain derivation path.  But it is possible to use just bip44 public key to generate addresses by derivation path as well?

Yes, the master public key (xpub / ypub / zpub depending on the address type you want) is enough to derive all public keys and therefore also addresses.
To derive the corresponding private key, you'll need the master private key (xpriv).

The addresses can be derived safely on an online machine while the private keys never touch any online device at all (for example).


The derivation 'hierarchy' is: Seed -> master private key -> master public key -> public key -> address.


Title: mempool explorer
Post by: reardenlife on September 29, 2019, 11:28:34 PM
I am still thinking that I should use the mempool and provide the service as soon as I see the correct amount designated to my address. It was pointed out to me that in rare cases, the transactions may be dropped from the mempool and that is why it is unreliable.  I was wondering if I can save the transaction in my DB and then detect if it disappeared from mempool and re-broadcast it myself.  It is still unreliable since my server can go down and fail to re-broadcast, but such risks are acceptable by me.

The question is - will the owner of the transaction be able to broadcast another transaction to the different address which depletes UTXO from their previous transaction to my address?  What is going to happen if such transactions will appear in the mempool at the same time (which try to spend from the same UTXO more than it actually has)?


Title: mempool processing is important
Post by: reardenlife on October 01, 2019, 01:13:49 PM
I am reading articles like this one: https://www.coindesk.com/charts-determining-ideal-block-size-bitcoin

Quote
A side note on ‘coffee’
Users of the bitcoin network, and in particular businesses, tell us that fees have increased to the point where paying for coffee and other even smaller use cases (such as ad network payments) are not viable anymore.

One can hear about that "pay for a coffee with BTC" argument over and over again even in papers like WJS.
I am failing to understand how come the transaction fees become so high.
Say the Bitcoin block size is 1MB.  Even with a huge fee like 50k Satoshi per 1kb it follows that only 0.5 BTC will go to the miner from the all block transactions.  With a current block reward of 12.5 BTC per block, it is unclear what is the purpose of such high fees from financial perspective.  Simply put, there is not much difference for the miner between high fees or low fees.

I think that fees should be voluntary but progressive, depending on the amount of BTC being transferred.  The guy who transfers say 1000BTC should not rely on say electrum dynamic fee estimation and should have no problem setting up a really big fee like 1m Satoshi per kb, and expect his transaction to be included into the block right away.  At the same time, the guy who buys a coffee for a few $ should put a minimum fee and at the same time expect to get a service (in this case the transaction fees for him will be only about 2 cents).  In this case, the service provider should use a mempool explorer and get the transaction out of there.  If it drops from the mempool, he should pick it up and re-broadcast.  I doubt the coffee buyer will even try to double-spend by waiting for his transaction to be dropped from the mempool and then broadcasting the one with same inputs but different output right away.

The above described sounds like a common sense to me.  So I will continue to use mempool explorer.  I can't see any problem here.


Title: Re: mempool explorer
Post by: nc50lc on October 01, 2019, 01:35:00 PM
The question is - will the owner of the transaction be able to broadcast another transaction to the different address which depletes UTXO from their previous transaction to my address?
By default, Bitcoin core won't allow you to re-spend a UTXO previously used by an unconfirmed tx.
But it's possible using 3rd-party tools to generate the raw TX or by doing some "extra technical work" with the client.

Quote from: reardenlife
What is going to happen if such transactions will appear in the mempool at the same time (which try to spend from the same UTXO more than it actually has)?
Both will stay in the nodes' mempool (if allowed, ex. RBF) until one get mined making the other transaction invalid.
Some Blockexplorer like live.blockcypher.com marks those type of transactions red as "double-spend attempt!" for days even after the other transaction was discarded dropped.


Title: Re: mempool explorer
Post by: reardenlife on October 01, 2019, 01:39:51 PM
The question is - will the owner of the transaction be able to broadcast another transaction to the different address which depletes UTXO from their previous transaction to my address?
By default, Bitcoin core won't allow you to re-spend a UTXO previously used by an unconfirmed tx.

Even if that unconfirmed tx was previously dropped from the mempool?  :-\

Edited:
I am trying to figure out solution which will protect me from double-spend attack in case if I accept the tx from the mempool but then it will be dropped from it.
I wonder if I should re-broadcast such tx myself say every hour in case if they stuck in the mempool.


Title: Re: mempool explorer
Post by: nc50lc on October 01, 2019, 01:44:54 PM
Even if that unconfirmed tx was previously dropped from the mempool?  :-\
If it was dropped, then he can re-spend the UTXO as long as it was removed from his transaction history.
If not, the user can manually delete it using -zapwallettxes command.
I am trying to figure out solution which will protect me from double-spend attack in case if I accept the tx from the mempool but then it will be dropped from it.
I wonder if I should re-broadcast such tx myself say every hour in case if they stuck in the mempool.
What do you mean "stuck in the mempool"?
By default any unconfirmed tx will be dropped from a "node's mempool" after 2weeks (each node have their individual mempool).

The solution is: do not finalize a deal without any confirmation.

An indirect reply to post below: If you're not seeing the txid in any blockexplorer,
chances that it was dropped by most nodes is high.


Title: Re: mempool explorer
Post by: reardenlife on October 01, 2019, 01:48:19 PM
Even if that unconfirmed tx was previously dropped from the mempool?  :-\
If it was dropped, then he can re-spend the UTXO as long as it was removed from his transaction history.
If not, the user can manually delete it using -zapwallettxes command.

Aha.  So what if I will re-broadcast the tx which is stuck in the mempool every hour or so - will there be any penalties? Will it improve the chances of this transaction not to be dropped from the mempool?  Any elegant way to detect the moment when the tx gets dropped?

Edit: I was also wondering why the transactions gets dropped from the mempool.  Why not to store them in DB and simply wait for a good moment to insert them into the block based on the fees specified in them?  There is no risk of DDOS for the bitcoin infrastructure due to the minimum fee of 1k Satoshi per kb.


Title: Re: mempool explorer
Post by: ranochigo on October 01, 2019, 01:58:08 PM
Aha.  So what if I will re-broadcast the tx which is stuck in the mempool every hour or so - will there be any penalties?
No. The node does not keep track of the transactions that has been dropped. They wouldn't know that they've had the transaction before in their mempool.
Will it improve the chances of this transaction not to be dropped from the mempool?
Technically, no. Nodes would still drop your transaction after some time but if you rebroadcast it periodically, you'll ensure that their mempool would contain your transaction for most of the time.
Any elegant way to detect the moment when the tx gets dropped?
No. A mempool is unique to every single node; it is possible for your transaction to exist in only some of the nodes on the network and not on the others. Keeping track of the entire network's mempool would be impossible.


Title: Re: mempool explorer
Post by: reardenlife on October 01, 2019, 02:04:54 PM
What do you mean "stuck in the mempool"?
By default any unconfirmed tx will be dropped from a "node's mempool" after 2weeks (each node have their individual mempool).

I mean if the tx has low fee, it appears to me that it can get stuck in the mempool for the prolonged amount of time and then get dropped from it without any insertion into the block.

The solution is: do not finalize a deal without any confirmation.

That is the solution which works in theory.  In practice people do not want to wait.  They want everything and right now.

An indirect reply to post below: If you're not seeing the txid in any blockexplorer,
chances that it was dropped by most nodes is high.

Txid??  ??? The transaction hash you mean?
 Anyways,  I don't use "any blockexplorers".  I am using a bitcoin core node in pruned mode.


Title: Re: mempool explorer
Post by: reardenlife on October 01, 2019, 02:15:01 PM
Technically, no. Nodes would still drop your transaction after some time but if you rebroadcast it periodically, you'll ensure that their mempool would contain your transaction for most of the time.

Alright. Cool.  So I will periodically re-broadcast the transactions that I accepted (provided the service for) that where not included into the blockchain to ensure the tx stays in the mempool and will get mined sooner or later.  ;)


Title: Re: mempool explorer
Post by: nc50lc on October 01, 2019, 02:17:46 PM
Quote from: reardenlife
[1] Txid??  ??? The transaction hash you mean?
Anyways,  I don't use "any blockexplorers".  [2] I am using a bitcoin core node in pruned mode.
[1] Yes, it's shorter and widely used term.
The reason behind the reply is: blockexplorers usually have more than one nodes to connect to more peers than a regular user's node,
so if your transaction's TXID can't be seen by them, most of the network's node must have dropped that TX.

[2] Then there's no clear indicator if other nodes have already dropped your tx.
At least, after more than two weeks after the last broadcast, it's safe to assume that your tx was dropped.


Title: Re: mempool explorer
Post by: reardenlife on October 01, 2019, 02:26:09 PM
The reason behind the reply is: blockexplorers usually have more than one nodes to connect to more peers than a regular user's node,
so if your transaction's TXID can't be seen by them, most of the network's node must have dropped that TX.

Does the bitcoin core provide the api to:
1.) Enumerate the other nodes connected to mine.
2.) Ask these nodes if they have a specific txid in their mempool.

Or it is just easier to re-broadcast every hour?  ;D


Title: Re: mempool explorer
Post by: nc50lc on October 02, 2019, 03:59:31 AM
Quote from: reardenlife
Or it is just easier to re-broadcast every hour?  ;D
This is unnecessary, nodes will keep it as long as stated in their "mempoolexpiry", 336 (336hrs/14days) default.

If you're planning to do this as a "protection against double spend" like what you've said somewhere above, it won't help.
If the double spend tx's fee is higher and a mining node accepted it/both, the one with the higher fee will be mined 1st.

What you need to do is don't set the TXs with RBF and set mempoolreplacement=0 for you to reject tx with double spent UTXO.
But some nodes will still accept replacement tx with mempoolreplacement=1 which is enabled by default.

Does the bitcoin core provide the api to:
1.) Enumerate the other nodes connected to mine.
2.) Ask these nodes if they have a specific txid in their mempool.
I have no idea, others might.
It seems like the discussion is now off-topic based from the OP, perhaps you need to create a new thread.


Title: Re: mempool explorer
Post by: reardenlife on October 02, 2019, 04:30:27 PM
If you're planning to do this as a "protection against double spend" like what you've said somewhere above, it won't help.
If the double spend tx's fee is higher and a mining node accepted it/both, the one with the higher fee will be mined 1st.

Interesting... So RBF allows a specification of a different destination address, which means that the attacker can successfully implement double-spend.
.. should I look into nSequence of the transaction I am accepting in order to determine if RBF is enabled on it perhaps?

It seems like the discussion is now off-topic based from the OP, perhaps you need to create a new thread

Uh oh.
The blockchain explorer on bash (as well as mempool explorer BTW) that I published above works just fine.  So the problem is solved.  Now we just talking about deployment-related issues which is somewhat within topic. :)


Title: Re: mempool explorer
Post by: reardenlife on October 02, 2019, 05:20:08 PM
What you need to do is don't set the TXs with RBF and set mempoolreplacement=0 for you to reject tx with double spent UTXO.
But some nodes will still accept replacement tx with mempoolreplacement=1 which is enabled by default.

I've got a better idea.  Here is the main code of my mempool explorer:


Code:
# Get hashes of transactions in the mempool
#
sql="START TRANSACTION;"
Tx=$(bitcoin-cli -rpccookiefile=$cookie getrawmempool | jq -r .[])
sql=${sql}$(echo "$Tx" | sed -e "s/^/INSERT IGNORE INTO exp_mempool(hash) VALUES('/" | sed -e "s/$/');/");
sql="${sql}COMMIT;"

mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  $sql
EOF

# Cleanup
#
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  DELETE FROM exp_mempool WHERE tstamp < NOW() - INTERVAL 3 HOUR;
  DELETE FROM exp_mempool_tx WHERE tstamp < NOW() - INTERVAL 3 HOUR;
EOF

# Select only unprocessed transactions
#
Tx=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT hash FROM exp_mempool WHERE is_processed = FALSE ORDER BY tstamp ASC LIMIT 500;
EOF
)

# Process mempool transactions (populate exp_mempool_tx table)
#
_header "$(date --rfc-3339=seconds)"
sql="START TRANSACTION;"
IFS=$'\n'
aTx=($(echo "$Tx"))
nTx=$(echo "$Tx" | wc -l)
echo "transactions:$nTx"
if (( $nTx == 0 )); then
  exit 2
fi

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

set +e
  txjson=$(bitcoin-cli -rpccookiefile=$cookie getrawtransaction $txhash 1 $bhash 2>/dev/null)
  if (( $? != 0 )); then
set -e
    # Gone from the mempool already?
    #
    #echo "Deleting tx:$txhash"
    sql=${sql}"DELETE FROM exp_mempool WHERE hash = '$txhash' LIMIT 1;"$'\n'
    i=$((i+1))
    continue
  fi
set -e

  # Make sure the transaction doesn't support RBF
  #
  rbf=$(echo "$txjson" | jq -r '.vin[] | select(.sequence < 4294967294)' | wc -l)
  if (( $rbf > 0 )); then
    #echo "$txjson" > /tmp/tx/$txhash
    sql="${sql}UPDATE exp_mempool SET is_processed = TRUE WHERE hash = '$txhash' LIMIT 1;"$'\n'
    i=$((i+1))
    continue
  fi

  r=$(echo "$txjson" | jq -r '. as $tx | .vout[] | select(.scriptPubKey.type == "pubkeyhash") | $tx.txid, .value, .scriptPubKey.addresses[]' | grep . || true )
  if [ ! -z "$r" ]; then
    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 exp_mempool_tx(hash, amount, address_to) VALUES(/" | sed -e 's/,$/);/')
  fi
  sql="${sql}UPDATE exp_mempool SET is_processed = TRUE WHERE hash = '$txhash' LIMIT 1;"$'\n'
  i=$((i+1))
done
tsnow=$(date +%s%N | cut -b1-13)
opsec=$(echo "scale=2;$i/(($tsnow-$tsstart)/1000)" | bc)
p=100
printf "$p%% ($opsec tx/sec)\r"
echo
sql="${sql}COMMIT;"

Note the condition regarding RBF. I simply skipping such transactions so they never have a chance to be parsed and inserted into exp_mempool_tx from which I am doing my balance monitoring.

So the transactions with RBF supported should wait to be fully inserted into blockchain and picked up by my blockchain explorer.


Title: Re: mempool explorer
Post by: reardenlife on October 02, 2019, 05:27:33 PM
Quote from: reardenlife
Or it is just easier to re-broadcast every hour?  ;D
This is unnecessary, nodes will keep it as long as stated in their "mempoolexpiry", 336 (336hrs/14days) default.

Alright. I see.  So it should mean that the transactions should never get dropped from the mempool in practice.

Yet the users like KittenBob trying to convince me that the transactions are dropping from the mempool all the time.  I am still trying to figure out when and why though.


Title: Re: mempool explorer
Post by: nc50lc on October 03, 2019, 03:04:39 AM
Quote from: reardenlife
Or it is just easier to re-broadcast every hour?  ;D
This is unnecessary, nodes will keep it as long as stated in their "mempoolexpiry", 336 (336hrs/14days) default.

Alright. I see.  So it should mean that the transactions should never get dropped from the mempool in practice.

Yet the users like KittenBob trying to convince me that the transactions are dropping from the mempool all the time.  I am still trying to figure out when and why though.
You mean bob? He didn't, based on the quoted message "wasn't mined" scenario,
he meant if there's too much unconfirmed transactions in the mempool,
a low fee tx might get dropped after the default timeout as most nodes are using default relay settings.

Example: December 2017 bubble. hover over dec 2017 (https://www.blockchain.com/charts/mempool-count?timespan=all)
During that time, a lot of unconfirmed transactions got dropped after 2 weeks.

-edit-
It seems like the discussion is now off-topic based from the OP, perhaps you need to create a new thread
Uh oh.
The blockchain explorer on bash (as well as mempool explorer BTW) that I published above works just fine.  So the problem is solved.  Now we just talking about deployment-related issues which is somewhat within topic. :)
Seriously, others who are interested in your topic will be mislead by the changing title of each post and the OP.
Start a new topic with what you're actually trying to achieve with this.

Something like a mempool_explorer/API for low-value instant Bitcoin payment?
AFAIK, there's a few discussions in the past about a no-confirmation "coffee payments" and I've said something like this:
"If it's a coffee shop and the customer is there, he wont dare to double spend as his face is in plain sight (people/CCTV)"

BTW, even if you prevented "them" in your mempool, you have no control over other nodes.