explorer-monorepo/scripts/fix-blockscout-vmid5000.sh

#!/bin/bash

# Script to fix Blockscout initialization issues in VMID 5000
# Fixes: Database migrations, static assets, and proper startup

set -euo pipefail

VMID=5000

echo "=========================================="
echo "Blockscout Initialization Fix for VMID 5000"
echo "=========================================="
echo ""
echo "This script fixes:"
echo "  1. Database migrations (creates missing tables)"
echo "  2. Static assets (builds and digests assets)"
echo "  3. Proper Blockscout startup command"
echo "  4. Public API stability under indexer load"
echo ""

cat << 'COMMANDS'
# ============================================================
# COMMANDS FOR ROOT USER IN VMID 5000
# ============================================================
# Run these commands from Proxmox host: pct exec 5000 -- bash
# Or if inside VMID 5000, run directly
# ============================================================

# ============================================================
# STEP 1: Check Current Status
# ============================================================

echo "=== Checking Blockscout Status ==="
docker ps -a | grep blockscout
docker logs blockscout 2>&1 | tail -20

# ============================================================
# STEP 2: Access Blockscout Container
# ============================================================

# Find Blockscout container name
BLOCKSCOUT_CONTAINER=$(docker ps -a | grep blockscout | grep -v postgres | awk '{print $1}' | head -1)

if [ -z "$BLOCKSCOUT_CONTAINER" ]; then
    echo "ERROR: Blockscout container not found"
    exit 1
fi

echo "Blockscout container: $BLOCKSCOUT_CONTAINER"

# ============================================================
# STEP 3: Run Database Migrations
# ============================================================

echo ""
echo "=== Running Database Migrations ==="

# Run migrations inside Blockscout container
docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout eval "Explorer.Release.migrate()" || \
docker exec -it $BLOCKSCOUT_CONTAINER mix ecto.migrate || \
docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout migrate

# Verify migrations
echo ""
echo "=== Verifying Migrations ==="
docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout eval "
  case Explorer.Repo.query(\"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'\") do
    {:ok, %{rows: [[count]]}} -> IO.puts(\"Tables in database: #{count}\")
    error -> IO.puts(\"Error checking tables: #{inspect(error)}\")
  end
"

# Check for critical tables
echo ""
echo "=== Checking Critical Tables ==="
docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout eval "
  tables = [\"blocks\", \"transactions\", \"migrations_status\", \"addresses\", \"smart_contracts\"]
  for table <- tables do
    case Explorer.Repo.query(\"SELECT 1 FROM information_schema.tables WHERE table_name = '\#{table}'\") do
      {:ok, %{rows: []}} -> IO.puts(\"❌ Table '\#{table}' MISSING\")
      {:ok, %{rows: [_]}} -> IO.puts(\"✅ Table '\#{table}' exists\")
      error -> IO.puts(\"⚠️  Error checking '\#{table}': #{inspect(error)}\")
    end
  end
"

# ============================================================
# STEP 4: Build and Digest Static Assets
# ============================================================

echo ""
echo "=== Building Static Assets ==="

# Build assets
docker exec -it $BLOCKSCOUT_CONTAINER mix phx.digest || \
docker exec -it $BLOCKSCOUT_CONTAINER npm run deploy || \
docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout eval "Mix.Tasks.Phx.Digest.run([])"

# Verify assets
echo ""
echo "=== Verifying Assets ==="
docker exec -it $BLOCKSCOUT_CONTAINER ls -la priv/static/cache_manifest.json || \
docker exec -it $BLOCKSCOUT_CONTAINER find priv/static -name "cache_manifest.json" || \
echo "⚠️  cache_manifest.json not found - assets may need manual build"

# ============================================================
# STEP 5: Fix Docker Compose Configuration
# ============================================================

echo ""
echo "=== Updating Docker Compose Configuration ==="

# Check current docker-compose.yml
BLOCKSCOUT_DIR="/opt/blockscout"
if [ -f "$BLOCKSCOUT_DIR/docker-compose.yml" ]; then
    echo "Found docker-compose.yml at $BLOCKSCOUT_DIR"
    
    # Backup original
    cp "$BLOCKSCOUT_DIR/docker-compose.yml" "$BLOCKSCOUT_DIR/docker-compose.yml.backup"
    
    # Update command to explicitly start Blockscout, use the local Docker-network
    # Postgres path, and apply safer DB pool defaults.
    sed -i 's|command:.*|command: bin/blockscout start|g' "$BLOCKSCOUT_DIR/docker-compose.yml" || \
    sed -i '/blockscout:/a\    command: bin/blockscout start' "$BLOCKSCOUT_DIR/docker-compose.yml"

    grep -q 'DATABASE_URL=' "$BLOCKSCOUT_DIR/docker-compose.yml" && \
      sed -i 's#DATABASE_URL=.*#DATABASE_URL=postgresql://blockscout:blockscout@postgres:5432/blockscout?sslmode=disable#' "$BLOCKSCOUT_DIR/docker-compose.yml" || \
      sed -i '/environment:/a\      - DATABASE_URL=postgresql://blockscout:blockscout@postgres:5432/blockscout?sslmode=disable' "$BLOCKSCOUT_DIR/docker-compose.yml"

    grep -q 'POOL_SIZE=' "$BLOCKSCOUT_DIR/docker-compose.yml" && \
      sed -i 's/POOL_SIZE=.*/POOL_SIZE=50/' "$BLOCKSCOUT_DIR/docker-compose.yml" || \
      sed -i '/SECRET_KEY_BASE=/a\      - POOL_SIZE=50' "$BLOCKSCOUT_DIR/docker-compose.yml"

    grep -q 'POOL_SIZE_API=' "$BLOCKSCOUT_DIR/docker-compose.yml" || \
      sed -i '/POOL_SIZE=50/a\      - POOL_SIZE_API=50' "$BLOCKSCOUT_DIR/docker-compose.yml"

    grep -q 'DATABASE_QUEUE_TARGET=' "$BLOCKSCOUT_DIR/docker-compose.yml" || \
      sed -i '/POOL_SIZE_API=50/a\      - DATABASE_QUEUE_TARGET=5s' "$BLOCKSCOUT_DIR/docker-compose.yml"

    grep -q 'INDEXER_DISABLE_PENDING_TRANSACTIONS_FETCHER=' "$BLOCKSCOUT_DIR/docker-compose.yml" || \
      sed -i '/DATABASE_QUEUE_TARGET=5s/a\      - INDEXER_DISABLE_PENDING_TRANSACTIONS_FETCHER=true' "$BLOCKSCOUT_DIR/docker-compose.yml"

    grep -q 'ECTO_USE_SSL=' "$BLOCKSCOUT_DIR/docker-compose.yml" && \
      sed -i 's/ECTO_USE_SSL=.*/ECTO_USE_SSL=false/' "$BLOCKSCOUT_DIR/docker-compose.yml" || \
      sed -i '/INDEXER_DISABLE_PENDING_TRANSACTIONS_FETCHER=true/a\      - ECTO_USE_SSL=false' "$BLOCKSCOUT_DIR/docker-compose.yml"

    echo "✅ Updated docker-compose.yml"
else
    echo "⚠️  docker-compose.yml not found at $BLOCKSCOUT_DIR"
    echo "You may need to update it manually to include:"
    echo "  command: bin/blockscout start"
    echo "  POOL_SIZE=50"
    echo "  POOL_SIZE_API=50"
    echo "  DATABASE_QUEUE_TARGET=5s"
    echo "  INDEXER_DISABLE_PENDING_TRANSACTIONS_FETCHER=true"
    echo "  ECTO_USE_SSL=false"
    echo "  DATABASE_URL=postgresql://blockscout:blockscout@postgres:5432/blockscout?sslmode=disable"
fi

# ============================================================
# STEP 6: Restart Blockscout with Proper Command
# ============================================================

echo ""
echo "=== Restarting Blockscout ==="

# Stop current container
docker stop $BLOCKSCOUT_CONTAINER 2>/dev/null || true
docker rm $BLOCKSCOUT_CONTAINER 2>/dev/null || true

# Restart using docker-compose (if available)
if [ -f "$BLOCKSCOUT_DIR/docker-compose.yml" ]; then
    cd "$BLOCKSCOUT_DIR"
    docker compose up -d blockscout || docker-compose up -d blockscout
else
    # Manual start with correct command
    echo "Starting Blockscout manually..."
    EXTRA_ENV_FILE=()
    if [ -f "$BLOCKSCOUT_DIR/.env" ]; then
        EXTRA_ENV_FILE+=(--env-file "$BLOCKSCOUT_DIR/.env")
    else
        echo "⚠️  $BLOCKSCOUT_DIR/.env not found; relying on explicit fallback env only"
    fi
    docker run -d \
        --name blockscout \
        --restart unless-stopped \
        --network blockscout_blockscout-network \
        "${EXTRA_ENV_FILE[@]}" \
        -p 4000:4000 \
        -e DATABASE_URL=postgresql://blockscout:blockscout@postgres:5432/blockscout?sslmode=disable \
        -e POOL_SIZE=50 \
        -e POOL_SIZE_API=50 \
        -e DATABASE_QUEUE_TARGET=5s \
        -e INDEXER_DISABLE_PENDING_TRANSACTIONS_FETCHER=true \
        -e ECTO_USE_SSL=false \
        blockscout/blockscout:latest \
        bin/blockscout start
fi

# Wait for startup
echo "Waiting for Blockscout to start..."
sleep 10

# Check status
echo ""
echo "=== Checking Blockscout Status ==="
docker ps | grep blockscout
docker logs blockscout 2>&1 | tail -30

# ============================================================
# STEP 7: Verify Everything is Working
# ============================================================

echo ""
echo "=== Final Verification ==="

# Check if container is running
if docker ps | grep -q blockscout; then
    echo "✅ Blockscout container is running"
else
    echo "❌ Blockscout container is not running"
    echo "Check logs: docker logs blockscout"
    exit 1
fi

# Check database tables
echo ""
echo "Checking database tables..."
docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout eval "
  tables = [\"blocks\", \"transactions\", \"migrations_status\"]
  missing = []
  for table <- tables do
    case Explorer.Repo.query(\"SELECT 1 FROM information_schema.tables WHERE table_name = '\#{table}'\") do
      {:ok, %{rows: []}} -> missing = missing ++ [table]
      _ -> :ok
    end
  end
  if length(missing) > 0 do
    IO.puts(\"❌ Missing tables: #{inspect(missing)}\")
  else
    IO.puts(\"✅ All critical tables exist\")
  end
" 2>/dev/null || echo "⚠️  Could not verify tables (container may still be starting)"

# Check assets
echo ""
echo "Checking static assets..."
if docker exec -it $BLOCKSCOUT_CONTAINER test -f priv/static/cache_manifest.json 2>/dev/null; then
    echo "✅ cache_manifest.json exists"
else
    echo "⚠️  cache_manifest.json not found - assets may need manual build"
fi

echo ""
echo "=========================================="
echo "✅ Blockscout initialization complete!"
echo "=========================================="
echo ""
echo "If issues persist, check logs:"
echo "  docker logs blockscout"
echo ""
echo "To access Blockscout console:"
echo "  docker exec -it blockscout bin/blockscout remote"
echo ""

COMMANDS

echo ""
echo "=========================================="
echo "Alternative: One-Line Migration Command"
echo "=========================================="
echo ""
echo "If you just need to run migrations quickly:"
echo ""
echo "pct exec 5000 -- bash -c 'BLOCKSCOUT_CONTAINER=\$(docker ps -a | grep blockscout | grep -v postgres | awk \"{print \\\$1}\" | head -1); docker exec -it \$BLOCKSCOUT_CONTAINER bin/blockscout eval \"Explorer.Release.migrate()\"'"
echo ""