explorer-monorepo/scripts/diagnose-blockscout-schema-issue.sh

#!/bin/bash

# Script to diagnose why Blockscout can't see migrations_status table
# even though it exists when checked from postgres

set -euo pipefail

VMID=5000

echo "=========================================="
echo "Blockscout Schema/Connection Diagnosis"
echo "=========================================="
echo ""

# Check if running from Proxmox host or inside container
if [ -f "/proc/1/cgroup" ] && grep -q "lxc" /proc/1/cgroup 2>/dev/null; then
    EXEC_PREFIX=""
    echo "Running inside VMID 5000"
else
    EXEC_PREFIX="pct exec $VMID --"
    echo "Running from Proxmox host, executing in VMID 5000"
fi

# Step 1: Check what schema the table is in
echo "=== Step 1: Checking migrations_status table location ==="
$EXEC_PREFIX docker exec blockscout-postgres psql -U blockscout -d blockscout -c "
SELECT 
  table_schema,
  table_name,
  table_type
FROM information_schema.tables 
WHERE table_name = 'migrations_status';
" 2>&1

echo ""

# Step 2: Check current search_path
echo "=== Step 2: Checking PostgreSQL search_path ==="
$EXEC_PREFIX docker exec blockscout-postgres psql -U blockscout -d blockscout -c "SHOW search_path;" 2>&1

echo ""

# Step 3: Check Blockscout DATABASE_URL
echo "=== Step 3: Checking Blockscout DATABASE_URL ==="
BLOCKSCOUT_CONTAINER=$($EXEC_PREFIX docker ps -a | grep blockscout | grep -v postgres | awk '{print $1}' | head -1)
if [ -n "$BLOCKSCOUT_CONTAINER" ]; then
    $EXEC_PREFIX docker inspect --format='{{range .Config.Env}}{{println .}}{{end}}' $BLOCKSCOUT_CONTAINER | grep -E "DATABASE_URL|POSTGRES" | head -5
else
    echo "⚠️  Blockscout container not found"
fi

echo ""

# Step 4: Try to access table with explicit schema
echo "=== Step 4: Testing table access with different schemas ==="
$EXEC_PREFIX docker exec blockscout-postgres psql -U blockscout -d blockscout << 'SQL'
-- Try public schema
SELECT 'Testing public.migrations_status:' as test;
SELECT COUNT(*) FROM public.migrations_status LIMIT 1;

-- List all schemas
SELECT 'Available schemas:' as info;
SELECT schema_name FROM information_schema.schemata;

-- Check if table exists in public schema
SELECT 'Table in public schema:' as check;
SELECT table_name, table_schema 
FROM information_schema.tables 
WHERE table_schema = 'public' AND table_name = 'migrations_status';
SQL

echo ""

# Step 5: Check if Blockscout is using a different database
echo "=== Step 5: Checking all databases ==="
$EXEC_PREFIX docker exec blockscout-postgres psql -U blockscout -d blockscout -c "\l" 2>&1 | head -15

echo ""

# Step 6: Try to query from Blockscout's perspective
echo "=== Step 6: Testing query from Blockscout container ==="
if [ -n "$BLOCKSCOUT_CONTAINER" ]; then
    echo "Attempting to query from Blockscout container..."
    $EXEC_PREFIX docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout eval "
      case Explorer.Repo.query(\"SELECT table_name FROM information_schema.tables WHERE table_name = 'migrations_status'\") do
        {:ok, %{rows: []}} -> IO.puts(\"❌ migrations_status NOT FOUND by Blockscout\")
        {:ok, %{rows: rows}} -> IO.puts(\"✅ migrations_status FOUND by Blockscout: #{inspect(rows)}\")
        error -> IO.puts(\"❌ Error querying: #{inspect(error)}\")
      end
    " 2>&1 || echo "⚠️  Cannot query from Blockscout container (may not be running)"
else
    echo "⚠️  Cannot test - container not found"
fi

echo ""
echo "=========================================="
echo "Diagnosis Complete"
echo "=========================================="