solana snapshot parsing ledger tool

snapshot-ledger-error-investigation.sh

# --- Working with etl snapshot DB ---

# speed up queries with index creation
create index token_account_mint on token_account(mint);
create index token_account_owner on token_account(owner);

# loading token accounts without system account from sqlite DB
SELECT sum(token_account.amount) FROM token_account
LEFT JOIN account ON token_account.owner = account.pubkey
WHERE account.pubkey IS NULL AND token_account.mint = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So'
and token_account.amount > 0;

sqlite3 ./snapshot.db "SELECT * FROM token_account LEFT JOIN account ON token_account.owner = account.pubkey WHERE account.pubkey IS NULL AND token_account.mint = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So' and token_account.amount > 0;" > ./select.query.txt

# see jq-msol.sh
# https://gist.github.com/ochaloup/11cdc2bdac3e92af2b768302b3fd06e3


## --- Working with ledger tool ---

# https://solana.stackexchange.com/questions/7429/how-to-get-accounts-data-from-solana-snapshot-file/7430#7430
# https://stackoverflow.com/questions/72040650/solana-need-to-export-all-addresses-from-the-blockchain-to-the-db

# download the snapshots
wget --retry-connrefused --waitretry=1 --tries=10 --timeout=30 -P . http://api.mainnet-beta.solana.com/snapshot.tar.bz2
wget --retry-connrefused --waitretry=1 --tries=10 --timeout=30 -P . http://api.mainnet-beta.solana.com/incremental-snapshot.tar.bz2

# setup system
sysctl -n vm.max_map_count
sudo sysctl -w vm.max_map_count=1048480

SOLANA_LEDGER="$HOME/tmp/solana-ledger"
mkdir -p "$SOLANA_LEDGER"

cd "$SOLANA_LEDGER"
wget https://api.mainnet-beta.solana.com/genesis.tar.bz2
tar xfj genesis.tar.bz2

mkdir snapshots
cd snapshots
# 'wget' or 'ln -s' when already downloaded (+rename is needed needed for some reason; see not on zst down here)
wget --retry-connrefused --waitretry=1 --tries=10 --timeout=30 -P . http://api.mainnet-beta.solana.com/snapshot.tar.bz2

# needed to update to format of name
## snapshot-<slot>-<hash>.tar.zst ##
# when the downloaded full snapshot is not renamed based on this patter you expect error
## [WARN  solana_ledger::bank_forks_utils] No snapshot package found in directory: ~/tmp/snapshot; will load from genesis
## [INFO  solana_ledger::bank_forks_utils] Processing ledger from genesis
## [INFO  solana_ledger::blockstore_processor] Processing ledger for slot 0...
## thread 'main' panicked at ~/.cargo/registry/src/index.crates.io-6f17d22bba15001f/solana-ledger-1.17.22/src/blockstore_processor.rs:1314:6:
## Failed to process bank 0 from ledger. Did you forget to provide a snapshot?: InvalidBlock(Incomplete)


cd ~/tmp

# using only the full snapshot
solana-ledger-tool --ledger ~/tmp/solana-ledger \
  --snapshot-archive-path ~/tmp/solana-ledger/snapshots \
  accounts --no-account-data  --force-update-to-open
# using snapshot and incremental snapshot
# TODO...