pipes-troubleshooting（管道故障排除）

Pipes: Troubleshooting Diagnostic

Specialized agent for diagnosing and fixing runtime errors in blockchain indexers built with Subsquid Pipes SDK.

When to Use This Skill

Activate when:

• User reports an error message
• Indexer crashes or stops unexpectedly
• Data is missing or incorrect in database
• TypeScript compilation fails
• Database connection issues
• Portal API errors or timeouts
• User mentions "error", "not working", "broken", "failed", or "bug"
• User complains indexer is too slow or wants performance optimization
• User asks how to speed up sync or reduce sync time
• User mentions "slow", "performance", "optimize", or "faster"

Important Note

Before diagnosing errors, check if the user followed the mandatory workflow in pipes-new-indexer skill. Many errors are caused by skipping documentation and not using proper setup procedures.

Diagnostic Checklist

1. Identify Error Type

Compilation Errors:

• TypeScript type mismatches
• Missing imports or dependencies
• ABI version conflicts (@subsquid/evm-abi 0.3.1 vs 1.x.x)

Runtime Errors:

• Portal API connection failures
• Database connection issues
• Event decoding errors
• Memory issues or OOM
• Cursor corruption

Data Quality Issues:

• Missing events
• Incorrect event parameters
• Duplicate records
• Wrong block ranges

2. Check Running Processes

If indexer is currently running:

# Check if process is running
ps aux | grep "npm run dev\|tsx src/index.ts\|node"

# Check output if running in background
# Use Bash tool with bash_id

3. Read Error Context

Always read the relevant files:

• src/index.ts - Main pipeline code
• package.json - Dependency versions
• .env - Connection strings
• Error stack traces from Bash

Common Error Patterns

Error Pattern 1: ABI Version Mismatch

Symptoms:

Type 'LogParams' is not assignable to type 'EvmLogParams'
Property 'topics' is missing in type 'LogParams'

Diagnosis: Wrong @subsquid/evm-abi version Root Cause: Using 1.x.x instead of 0.3.1

Fix:

// package.json
{
  "dependencies": {
    "@subsquid/evm-abi": "^0.3.1"  // NOT ^1.0.0
  }
}

Steps:

1. Read package.json
2. Edit to correct version
3. Run npm install or bun install
4. Verify types resolve

Error Pattern 2: Portal API Connection Failed

Symptoms:

Error: connect ECONNREFUSED
Error: Portal request failed with status 429
Error: Portal timeout after 30s

Diagnosis: Network or rate limit issue

Fix Options:

1. Rate Limiting (429): Add delay between requests or reduce block range
2. Connection Refused: Check internet connection, verify Portal URL
3. Timeout: Increase timeout or reduce batch size

Code Changes:

// Reduce block range to avoid rate limits
range: {
  from: 21_000_000,
  to: 21_100_000  // Smaller range
}

// Or adjust from block to be more recent
range: { from: 21_000_000 }  // Last few million blocks only

Error Pattern 3: Database Connection Failed

Symptoms:

Error: connect ECONNREFUSED localhost:5432
Error: ClickHouse authentication failed
Error: Database 'pipes' does not exist

Diagnosis: Database not running or misconfigured

Fix Steps:

1. Check if database is running: # PostgreSQL docker ps | grep postgres # ClickHouse docker ps | grep clickhouse
2. Check connection string in.env: cat.env
3. Start database if needed: # ClickHouse docker start clickhouse # Or start with docker-compose docker-compose up -d
4. Create database if missing: # ClickHouse docker exec -it clickhouse clickhouse-client --query "CREATE DATABASE IF NOT EXISTS pipes"

Note: Sync Table Error on First Run (Harmless)

On the very first start of a new indexer, you will see:

[ERROR][@clickhouse/client][Connection] Query: HTTP request error.
Caused by: ClickHouseError: Unknown table expression identifier 'pipes.sync'

This is expected and harmless. The SDK tries to read the sync table to check for resume state, fails because it doesn't exist yet, then creates it. The indexer will continue normally. Ignore this error on first run.

Error Pattern 4: Event Decoding Failed

Symptoms:

Error: Cannot decode event with signature '0x...'
TypeError: Cannot read property 'from' of undefined

Diagnosis: Wrong ABI or contract address

Fix Steps:

1. Read src/index.ts to check ABI import
2. Verify contract address is correct
3. Check if using correct event ABI: // Wrong: Using wrong common ABI events: {swap: commonAbis.erc20.events.Transfer // Wrong event} // Correct: Use proper ABI events: {swap: uniswapV3.events.Swap // Correct}
4. If custom contract, regenerate ABI: npx @subsquid/evm-typegen@latest src/contracts \ 0xYourContractAddress \ --chain-id 1

Error Pattern 4b: Proxy Contract ABI — Crash on Startup

Symptoms:

TypeError: Cannot read properties of undefined (reading 'topic')
    at evmDecoder (evm-decoder.ts:322:70)

Diagnosis: The contract is a proxy. The CLI/typegen fetched the proxy ABI (only Upgraded event), but index.ts references events like Supply, Borrow, etc. that don't exist in the generated file.

How to confirm: Check the generated contract file:

grep "export const events" src/contracts/*.ts
# If only "Upgraded" → proxy contract

Fix:

1. Find the implementation address on Etherscan → "Read as Proxy" tab
2. Generate types from the implementation: npx @subsquid/evm-typegen@latest src/contracts \ <IMPLEMENTATION_ADDRESS> --chain-id <CHAIN_ID>
3. Update the import in src/index.ts: // Change from proxy file to implementation file import {events} from './contracts/<IMPLEMENTATION_ADDRESS>.js'
4. Keep the proxy address in contracts: array — events emit from the proxy address

Prevention: After CLI generation, always check the generated contract file for proxy-only ABI before running. See pipes-new-indexer/references/ABI_GUIDE.md for full proxy guide.

Error Pattern 5: Missing Data

Symptoms:

• Indexer runs successfully but database is empty
• Only partial data is indexed
• Specific events are missing

Diagnosis: Filtering issue or wrong start block

Fix Steps:

1. Check start block is before events occurred: // Verify on Etherscan when contract was deployed range: {from: 'deployment_block'}
2. Check if contract is a proxy:

- Proxy contracts emit events from implementation address - Need to track implementation, not proxy

1. Verify event names match ABI exactly: // Case-sensitive, must match exactly events: {transfer: erc20Abi.Transfer // Correct case}
2. Check for overly restrictive filters: // May be filtering out too many events.filter((e) => /* check filter logic */)
3. Check for sync table conflict (shared database): All indexers write to {database}.sync with id = 'stream'. If another indexer used this database, your indexer resumes from the wrong block. docker exec <container> clickhouse-client --password <pw> \ --query "SELECT * FROM <database>.sync FORMAT Vertical" Fix: Use a separate database per indexer, or drop the sync table before starting.

Error Pattern 5b: Factory Indexer Shows Zero Data

Symptoms: Factory-pattern indexer runs, syncs blocks, but database has 0 rows for 30-60+ seconds.

Diagnosis: The factory pattern only discovers child contracts from range.from forward. If no new child contracts were created in the synced blocks, there's no data yet.

This is expected, not a bug.

Fix:

• Wait 60-90 seconds — if the factory is active, data will appear
• To track ALL child contracts, set range.from to the factory's deployment block
• Check the SQLite file size to confirm contracts are being discovered: ls -la <project>/*.sqlite # size > 0 = contracts found

Error Pattern 5c: Timestamps Show 1970 Dates

Symptoms: All dates in ClickHouse show as 1970-01-28 or similar

Diagnosis: Passing milliseconds instead of seconds to a DateTime column

Fix: Divide getTime() by 1000 in your .pipe() transform:

// WRONG
timestamp: d.timestamp.getTime(),

// CORRECT
timestamp: Math.floor(d.timestamp.getTime() / 1000),

Note: The auto-generated enrichEvents helper handles this correctly. This only happens with manual .pipe() transforms.

Recovery: Drop tables + sync, delete SQLite (if factory), restart.

Error Pattern 6: Memory Issues

Symptoms:

Error: JavaScript heap out of memory
Process killed (signal 9)

Diagnosis: Indexer processing too much data at once

Fix Options:

1. Reduce block range
2. Reduce number of contracts tracked
3. Process data in smaller batches
4. Increase Node.js memory limit: NODE_OPTIONS="--max-old-space-size=4096" npm run dev

Error Pattern 7: ClickHouse Schema Issues

Symptoms:

Error: Table already exists
Error: Column type mismatch
Error: Cannot insert NULL into NOT NULL column

Fix Steps:

1. Drop and recreate table: docker exec clickhouse clickhouse-client --password=default \ --query "DROP TABLE IF EXISTS pipes.table_name"
2. Verify schema matches data types:

- Addresses: String - Amounts: Float64 (after dividing by decimals) - Block numbers: UInt64 - Timestamps: DateTime(3)

1. Ensure sync table is cleared for fresh starts: docker exec clickhouse clickhouse-client --password=default \ --query "DROP TABLE IF EXISTS pipes.sync"

Error Pattern 8: Process Crashed / Indexer Died Mid-Sync

Symptoms:

• Terminal shows process exited
• npm run dev was killed (OOM, Ctrl+C, machine restart)
• Partial data in database

Diagnosis: Normal crash recovery scenario. The sync table tracks progress.

Fix Steps:

1. Simply restart — it will resume automatically: cd <project-folder> npm run dev
2. Verify the "Resuming from X" log line shows a block near where it crashed. This is the one scenario where "Resuming" is expected and correct.
3. If data looks corrupted, drop sync + data tables and start fresh: docker exec <container> clickhouse-client --password <pw> \ --query "DROP TABLE IF EXISTS pipes.sync; DROP TABLE IF EXISTS pipes.<your_table>"

Error Pattern 9: Node.js Version Compatibility Issues

Symptoms:

ZSTD_error_prefix_unknown
TypeError: terminated (ZstdDecompress)

Or random crashes during large syncs.

Diagnosis: Using Node.js v25+ which has known zstd decompression bugs

Fix:

# Check version
node --version

# If v25.x, switch to LTS:
# Option 1: nvm
nvm install 22 && nvm use 22

# Option 2: Homebrew (macOS)
brew install node@22
export PATH="/opt/homebrew/opt/node@22/bin:$PATH"

# Option 3: Download from https://nodejs.org/

# Restart indexer
npm run dev

If you can't switch versions: The zstd bug tends to crash on large syncs (millions of blocks). For quick tests with recent blocks (~100K), v25 often works fine.

Hyperliquid-specific note: Node.js v25 crashes are especially common during large Hyperliquid fills syncs (50M+ blocks). However, checkpoint/resume works reliably — the indexer will pick up from where it crashed. If switching Node versions is not feasible, let it crash and restart repeatedly until sync completes. Each restart resumes from the last checkpoint.

Prevention: Always use Node.js LTS (v20 or v22) for Pipes SDK projects.

Error Pattern 10: Hyperliquid Validation — SDK vs Portal Block Batching

Symptoms: validate.ts Portal cross-reference shows wildly different fill counts compared to ClickHouse, even though spot-checks pass and data looks correct.

Diagnosis: The Pipes SDK batches Hyperliquid blocks differently from raw Portal queries. The SDK may merge, split, or reorder blocks internally for efficiency. A Portal query for fromBlock: X, toBlock: Y may return a different number of fills than what the SDK indexed for the same nominal range, because the SDK's actual block boundaries don't align 1:1 with Portal's.

This is NOT a bug. The data is correct — the counting methodology differs.

Fix — use spot-checks as primary truth verification:

// DON'T rely on block-range count comparison for Hyperliquid:
// ClickHouse count: 15,234  vs  Portal count: 14,891  → misleading 2.3% diff

// DO use transaction-level spot-checks as primary verification:
// 1. Pick 3-5 specific fills from ClickHouse (by hash or tid)
// 2. Query Portal for the same block
// 3. Verify field-level match: coin, px, sz, side, dir, user

validate.ts pattern for Hyperliquid:

// Phase 2 (Portal cross-ref): Use as a SANITY CHECK only, not exact match
// Accept wider tolerance (20-30%) or skip count comparison entirely
// Phase 3 (Spot-checks): This is the PRIMARY truth verification
// Pick fills from ClickHouse, query Portal for same block, verify fields match exactly

Prevention: When writing validate.ts for Hyperliquid indexers, make spot-checks the authoritative verification and treat count comparisons as approximate sanity checks only.

Error Pattern 10b: Hyperliquid addFill Missing Range

Symptoms:

TypeError: Cannot read properties of undefined (reading 'from')
    at parsePortalRange
    at HyperliquidFillsQueryBuilder.addRequest
    at HyperliquidFillsQueryBuilder.addFill

Diagnosis: The addFill() method requires a range parameter. Unlike EVM decoders where range is set once, each Hyperliquid fill filter needs its own range.

Note: In SDK 1.0+, use hyperliquidFillsQuery() instead of new HyperliquidFillsQueryBuilder(). The error behavior is the same.

Fix:

// WRONG
.addFill({ request: { coin: ['BTC'] } })

// CORRECT
.addFill({ range: { from: 920000000 }, request: { coin: ['BTC'] } })

Prevention: Always pass range in addFill(). The dataset starts at block 750,000,000.

Error Pattern 11: CLI Crashes on init — ora ESM/CJS Error

Symptoms:

[PIPES SDK] Error: (0 , import_ora.default) is not a function

Diagnosis: The CLI is bundled as CJS but imports ora v6+ which is ESM-only. Only init is affected; --schema and --version work fine.

Fix: Patch the CLI bundle to replace ora with a no-op spinner:

CLI_PATH=$(find ~/.npm/_npx -name "index.cjs" -path "*pipes-cli*" 2>/dev/null | head -1)
sed -i.bak 's/var import_ora = __toESM(require("ora"), 1);/var import_ora = { default: function(opts) { var t = typeof opts === "string" ? opts : (opts \&\& opts.text) || ""; return { start: function(m) { console.log(m || t); return this; }, succeed: function(m) { console.log(m || t); return this; }, fail: function(m) { console.log(m || t); return this; }, stop: function() { return this; }, text: t }; } };/' "$CLI_PATH"

Then re-run the init command.

WARNING: npx may silently re-download and overwrite the patch. Always verify before running init:

CLI_PATH=$(find ~/.npm/_npx -name "index.cjs" -path "*pipes-cli*" 2>/dev/null | head -1)
grep -q 'import_ora = { default: function' "$CLI_PATH" && echo "Patched" || echo "Needs patching"

Data Validation & Quality Checks

After an indexer completes successfully, validate the data quality to ensure production readiness.

Validation Levels

Level 1: Schema Validation (CRITICAL)

Verify table structure is correct:

-- Check table exists
SELECT count() FROM system.tables
WHERE database = '<database>' AND name = '<table_name>'

-- Check column types
DESCRIBE <database>.<table_name>

Checks:

• Table exists
• All expected columns present
• Column data types match design
• Indexes created
• Table engine correct

Level 2: Data Quality (HIGH PRIORITY)

Validate individual data values:

-- Address format validation
SELECT
  countIf(length(pool_address) != 42) as invalid_length,
  countIf(pool_address NOT LIKE '0x%') as missing_prefix,
  countIf(NOT match(pool_address, '^0x[0-9a-fA-F]{40}$')) as invalid_format
FROM <table_name>

-- Transaction hash format
SELECT
  countIf(length(transaction_hash) != 66) as invalid_length,
  countIf(transaction_hash NOT LIKE '0x%') as missing_prefix
FROM <table_name>

-- BigInt values validation
SELECT
  countIf(amount = '') as empty_amounts,
  countIf(NOT match(amount, '^-?[0-9]+$')) as invalid_numbers
FROM <table_name>

-- NULL checks
SELECT
  countIf(from_address IS NULL) as null_from,
  countIf(to_address IS NULL) as null_to,
  countIf(value IS NULL) as null_value
FROM <table_name>

Checks:

• Addresses are 42 characters (0x + 40 hex)
• Transaction hashes are 66 characters (0x + 64 hex)
• BigInt values are valid numbers
• No unexpected NULL values
• Block numbers in expected range

Level 3: Completeness (MEDIUM PRIORITY)

Ensure no missing data:

-- Block range coverage
SELECT
  MIN(block_number) as min_block,
  MAX(block_number) as max_block,
  COUNT(DISTINCT block_number) as unique_blocks
FROM <table_name>

-- Check for block gaps
SELECT
  block_number,
  block_number - lag(block_number) OVER (ORDER BY block_number) as gap
FROM (
  SELECT DISTINCT block_number
  FROM <table_name>
  ORDER BY block_number
)
WHERE gap > 1

-- Event count per block
SELECT
  block_number,
  COUNT(*) as event_count
FROM <table_name>
GROUP BY block_number
HAVING event_count > 1000
ORDER BY event_count DESC
LIMIT 10

Checks:

• Block range matches expected
• No gaps in block sequence
• Event counts are reasonable
• No duplicate events (same tx_hash + log_index)

Level 4: Consistency (MEDIUM PRIORITY)

Verify logical relationships:

-- Block timestamps are monotonic
SELECT
  block_number,
  block_timestamp,
  lag(block_timestamp) OVER (ORDER BY block_number) as prev_timestamp
FROM (
  SELECT DISTINCT block_number, block_timestamp
  FROM <table_name>
  ORDER BY block_number
)
WHERE block_timestamp < prev_timestamp

Checks:

• Block timestamps increase with block numbers
• Log indexes sequential within transactions

Common Data Quality Issues

Issue 1: NULL Values in Required Fields

Symptom: Critical fields contain NULL

Cause: Missing .toString() on BigInt values

Fix:

// Wrong
amount: transfer.event.value,

// Correct
amount: transfer.event.value.toString(),

Issue 2: Invalid Address Formats

Symptom: Addresses not 42 characters or missing 0x

Cause: Incorrect data extraction or transformation

Fix: Validate address format in transformation pipeline

Issue 3: Block Gaps

Symptom: Missing blocks in sequence

Cause: Indexer crashed and didn't resume properly

Fix: Clear sync table and restart from affected block

Validation Checklist

Before declaring success:

• Table structure matches design
• No NULL values in required fields
• All addresses are valid (42 chars, 0x prefix, hex)
• All transaction hashes valid (66 chars)
• Block range complete (no gaps)
• Data count increasing over time
• Sample transactions match block explorer

Diagnostic Workflow

1. Read error message - Get exact error text
2. Identify pattern - Match to known patterns above
3. Read relevant files - Check src/index.ts, package.json,.env
4. Verify environment - Check database, network, dependencies
5. Apply fix - Edit files or run commands
6. Test fix - Restart indexer and verify
7. Validate data - Run quality checks above
8. Monitor - Watch logs to confirm resolution

Prevention Tips

1. Always use Pipes CLI - Never manually create files
2. Follow workflow - See pipes-new-indexer for the standard workflow
3. Start with recent blocks - Test faster, iterate quicker
4. Verify setup - See ENVIRONMENT_SETUP.md before starting
5. Check patterns - See PATTERNS.md for common solutions

Related Skills

• pipes-new-indexer - Create new indexers
• PERFORMANCE.md - Optimize slow indexers
• pipes-new-indexer ABI_GUIDE - Fetch contract ABIs and handle proxies
• pipes-new-indexer SCHEMA_GUIDE - Design ClickHouse schemas

Related Documentation

This skill includes comprehensive reference documentation in the references/ directory:

• PATTERNS.md - Common indexing patterns, performance optimization, error patterns, and best practices
• STREAM_RESILIENCE.md - Keeping long-running indexers alive: retry patterns, process supervisors (pm2), nohup for dev sessions
• CLICKHOUSE_ANALYTICS.md - Query patterns for dashboards: time bucketing, conditional aggregation, parameterized WHERE clauses, performance tips
• PERFORMANCE.md - Sync speed optimization: start block adjustment, contract filtering, benchmarks

How to Access

# Read patterns and best practices
cat pipes-sdk/pipes-troubleshooting/references/PATTERNS.md
cat pipes-sdk/pipes-troubleshooting/references/STREAM_RESILIENCE.md
cat pipes-sdk/pipes-troubleshooting/references/CLICKHOUSE_ANALYTICS.md
cat pipes-sdk/pipes-troubleshooting/references/PERFORMANCE.md

Or use Claude Code's Read tool:

Read: pipes-sdk/pipes-troubleshooting/references/PATTERNS.md

Additional Resources

• ENVIRONMENT_SETUP.md - Setup prerequisites
• RESEARCH_CHECKLIST.md - Protocol research workflow

Official Subsquid Documentation

• llms-full.txt - Complete troubleshooting and error references
• skill.md - Comprehensive Pipes SDK guide
• EVM OpenAPI Schema - Portal API specification for debugging EVM issues
• Available Datasets - Verify network names and endpoints