Troubleshooting & Debugging

# Step 1: Check if Cloud SQL instance is running
gcloud sql instances describe web-app-db --format='value(state)'
# Expected: RUNNABLE

# Step 2: Check VM-to-database connectivity via Cloud SQL Auth Proxy
# On the VM:
cloud-sql-proxy -instances=PROJECT:us-central1:web-app-db=tcp:5432 &

# Step 3: Test connection
psql -h 127.0.0.1 -U app_user -d app_db
# If prompted for password, enter: SecurePassword123

# Step 4: If still fails, check IAM permissions
gcloud sql instances describe web-app-db --format='value(settings.backupConfiguration.enabled)'

# Step 5: Verify Cloud SQL Proxy can reach database
gcloud sql connect web-app-db --user=app_user
# Should prompt for password without timeout

# Step 6: Check Cloud SQL logs
gcloud sql operations list --instance=web-app-db --limit=10

# Step 1: Identify the principal (user, service account, group)
# Check which user/SA made the API call
gcloud logging read 'protoPayload.methodName="storage.objects.get"' \
  --limit=5 --format='value(protoPayload.authenticationInfo.principalEmail)'

# Step 2: Check what role the principal has
PRINCIPAL="user:alice@company.com"
gcloud projects get-iam-policy PROJECT \
  --flatten='bindings[].members' \
  --filter="bindings.members:${PRINCIPAL}"

# Step 3: Grant missing role
gcloud projects add-iam-policy-binding PROJECT \
  --member=${PRINCIPAL} \
  --role=roles/storage.objectViewer

# Step 4: Verify (may take 1-2 minutes to propagate)
gcloud projects get-iam-policy PROJECT \
  --flatten='bindings[].members' \
  --filter="bindings.members:${PRINCIPAL}"

# Step 5: Test access
gsutil ls gs://my-bucket/

# Step 1: Verify VM is running
gcloud compute instances describe INSTANCE_NAME \
  --format='value(status)'
# Expected: RUNNING

# Step 2: Check firewall allows SSH (port 22)
gcloud compute firewall-rules list \
  --filter='direction:INGRESS AND allowed:tcp=22'

# Step 3: Verify OS login is not required
gcloud compute instances describe INSTANCE_NAME \
  --format='value(metadata.items.block-project-ssh-keys)'

# Step 4: Use gcloud SSH wrapper (handles keys automatically)
gcloud compute ssh INSTANCE_NAME --zone=us-central1-a

# Step 5: If still fails, check service account has compute.instances.osLogin role
gcloud projects get-iam-policy PROJECT \
  --flatten='bindings[].members' \
  --filter='bindings.roles:roles/compute.osLoginInstanceAccessRole'

# Step 6: As last resort, use Serial Console (requires monitoring.metricWriter role)
gcloud compute instances get-serial-port-output INSTANCE_NAME --zone=us-central1-a

# Step 1: Check health check configuration
gcloud compute health-checks describe my-health-check \
  --format='value(httpHealthChecks)'
# Expected: port=8080, requestPath=/health

# Step 2: SSH to instance and test health endpoint manually
gcloud compute ssh web-instance-abc123 --zone=us-central1-a
# On remote instance:
curl -v http://localhost:8080/health
# Expected: HTTP 200 OK

# Step 3: Check app is actually listening on port 8080
# On remote instance:
netstat -tlnp | grep 8080
# Should show: tcp        0      0 0.0.0.0:8080            0.0.0.0:*               LISTEN

# Step 4: Verify firewall allows health check IPs (GCP's health checker)
# GCP health checks originate from 35.191.0.0/16 and 130.211.0.0/22
gcloud compute firewall-rules create allow-health-checks \
  --allow=tcp:8080 \
  --source-ranges='35.191.0.0/16,130.211.0.0/22'

# Step 5: Check backend service health
gcloud compute backend-services get-health my-backend --global

# Step 6: Retry health check manually
gcloud compute health-checks update my-health-check \
  --check-interval=10s --timeout=5s --unhealthy-threshold=1

# Step 1: Enable Query Insights
gcloud sql instances patch web-app-db \
  --insights-config-enabled

# Step 2: View slowest queries (via Cloud Console or API)
# Console: Cloud SQL → web-app-db → Query Insights → Top 10 Queries

# Step 3: Identify missing indexes
# Run EXPLAIN ANALYZE on slow query:
cloud-sql-proxy -instances=PROJECT:us-central1:web-app-db=tcp:5432 &
psql -h 127.0.0.1 -U app_user -d app_db
# In psql:
EXPLAIN ANALYZE SELECT * FROM users WHERE email='test@example.com';
# Look for "Seq Scan" (full table scan) = missing index

# Step 4: Add index
CREATE INDEX idx_users_email ON users(email);

# Step 5: Re-run EXPLAIN ANALYZE
# Should show "Index Scan" (much faster)

# Step 6: Monitor performance
gcloud sql instances describe web-app-db --format='value(settings.insightsConfig)'