Troubleshooting

Troubleshooting Overview

This guide covers common issues, error messages, and solutions for the Elasticsearch integration system. Each section includes symptoms, causes, and step-by-step resolution procedures.

Common Issues

1. Database Connection Issues

Symptom: “Database connection failed”

Error: Failed to connect to PostgreSQL database

Causes

• Database server is down
• Network connectivity issues
• Incorrect credentials
• Firewall blocking connection
• Database server overloaded

Solutions

Check Database Server Status:

# Check PostgreSQL service
sudo systemctl status postgresql

# Test connection manually
psql -h 162.55.174.116 -U website -d categories_db

# Check network connectivity
ping 162.55.174.116
telnet 162.55.174.116 5432

Verify Credentials:

# Test with credentials
psql -h 162.55.174.116 -U website -d categories_db -c "SELECT 1;"

Check Firewall:

# Check firewall status
sudo ufw status

# Allow PostgreSQL port if needed
sudo ufw allow from <your-ip> to any port 5432

Database Server Overload:

-- Check active connections
SELECT count(*) FROM pg_stat_activity;

-- Check database size
SELECT pg_size_pretty(pg_database_size('categories_db'));

-- Check slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

2. Elasticsearch Connection Issues

Symptom: “Elasticsearch connection failed”

Error: Elasticsearch cURL error: Connection timeout

Causes

• Elasticsearch server is down
• Network connectivity issues
• Firewall blocking connection
• Elasticsearch cluster issues
• Server overloaded

Solutions

Check Elasticsearch Status:

# Test basic connectivity
curl -X GET "http://91.99.113.45:9200/"

# Check cluster health
curl -X GET "http://91.99.113.45:9200/_cluster/health?pretty"

# Check node status
curl -X GET "http://91.99.113.45:9200/_nodes/stats?pretty"

Network Diagnostics:

# Test network connectivity
ping 91.99.113.45
telnet 91.99.113.45 9200

# Check DNS resolution
nslookup 91.99.113.45

Elasticsearch Cluster Issues:

# Check cluster state
curl -X GET "http://91.99.113.45:9200/_cluster/state?pretty"

# Check index status
curl -X GET "http://91.99.113.45:9200/_cat/indices?v"

# Check shard status
curl -X GET "http://91.99.113.45:9200/_cat/shards?v"

3. Sync Process Issues

Symptom: “Sync process not running”

Error: No sync state found

Causes

• Cron job not configured
• Script permissions issues
• PHP execution errors
• Memory limits exceeded
• State file corruption

Solutions

Check Cron Configuration:

# Check cron service
sudo systemctl status cron

# List cron jobs
sudo crontab -l

# Check cron logs
sudo tail -f /var/log/syslog | grep CRON

Test Script Execution:

# Test script manually
cd /var/www/html/wp-content/themes/products/scripts
php elasticsearch-auto-sync.php

# Check script permissions
ls -la elasticsearch-auto-sync.php
chmod +x elasticsearch-auto-sync.php

Check PHP Errors:

# Check PHP error log
tail -f /var/log/php8.1-fpm.log

# Test PHP configuration
php -m | grep -E "(pgsql|curl|json)"

Memory Issues:

# Check memory usage
free -h

# Increase memory limit
php -d memory_limit=512M elasticsearch-auto-sync.php

4. Search Functionality Issues

Symptom: “Search returns no results”

Error: Search failed: No results found

Causes

• Elasticsearch index empty
• Index mapping issues
• Search query problems
• Data synchronization issues

Solutions

Check Index Status:

# Check document count
curl -X GET "http://91.99.113.45:9200/products/_count"

# Check index mapping
curl -X GET "http://91.99.113.45:9200/products/_mapping?pretty"

# Test simple search
curl -X GET "http://91.99.113.45:9200/products/_search?q=*&size=1"

Verify Data Sync:

-- Check sync status
SELECT
  COUNT(*) as total,
  COUNT(CASE WHEN added_search = true THEN 1 END) as indexed,
  ROUND(COUNT(CASE WHEN added_search = true THEN 1 END)::numeric / COUNT(*) * 100, 1) as progress_pct
FROM product WHERE title IS NOT NULL;

Test Search Query:

# Test search via API
curl -X POST "http://91.99.113.45:9200/products/_search" \
  -H 'Content-Type: application/json' \
  -d '{
    "query": {
      "match_all": {}
    },
    "size": 1
  }'

5. Performance Issues

Symptom: “Slow search response times”

Error: Search query took 5.2 seconds

Causes

• Large result sets
• Complex queries
• Insufficient resources
• Index optimization needed
• Network latency

Solutions

Optimize Queries:

// Use filters instead of queries when possible
$query = [
    'bool' => [
        'filter' => [
            ['term' => ['category' => '123']],
            ['range' => ['price' => ['gte' => 100, 'lte' => 500]]]
        ]
    ]
];

Check Resource Usage:

# Check CPU usage
top -p $(pgrep -f elasticsearch)

# Check memory usage
free -h

# Check disk I/O
iostat -x 1

Optimize Index:

# Optimize index
curl -X POST "http://91.99.113.45:9200/products/_optimize?max_num_segments=1"

# Check index stats
curl -X GET "http://91.99.113.45:9200/products/_stats?pretty"

Error Messages

Database Errors

”PostgreSQL connection has already been closed”

Error: PostgreSQL connection has already been closed

Solution:

// Implement connection reconnection
private function ensureDatabaseConnection() {
    if ($this->connection === null) {
        $this->connection = $this->getDatabaseConnection();
    }

    // Test connection
    $result = pg_query($this->connection, "SELECT 1");
    if (!$result) {
        $this->connection = $this->getDatabaseConnection();
    }

    return $this->connection;
}

“Database query failed”

Error: Database query failed: relation "product" does not exist

Solution:

-- Check if table exists
\dt product

-- Create table if missing
CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    title TEXT,
    brand VARCHAR(255),
    price NUMERIC(10, 2),
    added_search BOOLEAN DEFAULT FALSE
);

Elasticsearch Errors

”Index not found”

Error: Index [products] not found

Solution:

# Create index
curl -X PUT "http://91.99.113.45:9200/products" \
  -H 'Content-Type: application/json' \
  -d '{
    "settings": {
      "number_of_shards": 3,
      "number_of_replicas": 1
    }
  }'

“Bulk indexing failed”

Error: Bulk indexing failed: Request entity too large

Solution:

// Reduce batch size
private $batch_size = 100; // Instead of 500

// Or increase Elasticsearch limits
curl -X PUT "http://91.99.113.45:9200/products/_settings" \
  -H 'Content-Type: application/json' \
  -d '{
    "index": {
      "max_result_window": 100000
    }
  }'

WordPress Errors

”Template not found”

Error: Template not found: page-search.php

Solution:

# Check file exists
ls -la /var/www/html/wp-content/themes/products/page-search.php

# Check permissions
chmod 644 /var/www/html/wp-content/themes/products/page-search.php
chown www-data:www-data /var/www/html/wp-content/themes/products/page-search.php

“AJAX nonce verification failed”

Error: Invalid nonce

Solution:

// Check nonce generation
wp_create_nonce('elasticsearch_nonce');

// Verify nonce in AJAX handler
check_ajax_referer('elasticsearch_nonce', 'nonce');

Diagnostic Commands

System Diagnostics

Check System Resources

# Check memory usage
free -h

# Check disk space
df -h

# Check CPU usage
top -p $(pgrep -f elasticsearch)

# Check network connections
netstat -tulpn | grep -E "(5432|9200)"

Check Service Status

# Check all services
sudo systemctl status postgresql
sudo systemctl status elasticsearch
sudo systemctl status apache2
sudo systemctl status cron

Application Diagnostics

Check Sync Status

# Check sync state
cat /var/www/html/wp-content/themes/products/scripts/elasticsearch-sync-state.json

# Check recent logs
tail -100 /var/log/dealai/elasticsearch-sync.log

# Check error patterns
grep ERROR /var/log/dealai/elasticsearch-sync.log | tail -10

Check Database Status

-- Check sync progress
SELECT
  COUNT(*) as total,
  COUNT(CASE WHEN added_search = true THEN 1 END) as indexed,
  ROUND(COUNT(CASE WHEN added_search = true THEN 1 END)::numeric / COUNT(*) * 100, 1) as progress_pct
FROM product WHERE title IS NOT NULL;

-- Check recent updates
SELECT COUNT(*) FROM product WHERE updated_at > NOW() - INTERVAL '1 hour';

-- Check for stuck products
SELECT COUNT(*) FROM product WHERE added_search = FALSE AND updated_at < NOW() - INTERVAL '24 hours';

Check Elasticsearch Status

# Check cluster health
curl -X GET "http://91.99.113.45:9200/_cluster/health?pretty"

# Check index stats
curl -X GET "http://91.99.113.45:9200/products/_stats?pretty"

# Check document count
curl -X GET "http://91.99.113.45:9200/products/_count"

# Check index size
curl -X GET "http://91.99.113.45:9200/_cat/indices/products?v"

Recovery Procedures

Complete System Reset

Reset Sync Process

# Stop cron jobs
sudo crontab -r

# Reset state file
rm /var/www/html/wp-content/themes/products/scripts/elasticsearch-sync-state.json

# Reset database flags
psql -h 162.55.174.116 -U website -d categories_db -c "UPDATE product SET added_search = FALSE;"

# Delete Elasticsearch index
curl -X DELETE "http://91.99.113.45:9200/products"

# Restart services
sudo systemctl restart postgresql
sudo systemctl restart elasticsearch

# Recreate index
curl -X PUT "http://91.99.113.45:9200/products" -H 'Content-Type: application/json' -d '{"settings":{"number_of_shards":3,"number_of_replicas":1}}'

# Restart cron
sudo crontab -e
# Add: */15 * * * * /usr/bin/php /var/www/html/wp-content/themes/products/scripts/elasticsearch-auto-sync.php >> /var/log/dealai/elasticsearch-sync.log 2>&1

Partial Reset

# Reset only failed batches
psql -h 162.55.174.116 -U website -d categories_db -c "
UPDATE product SET added_search = FALSE
WHERE id IN (
  SELECT id FROM product
  WHERE added_search = FALSE
  AND updated_at < NOW() - INTERVAL '1 hour'
  LIMIT 1000
);"

Data Recovery

Recover from Backup

# Restore database from backup
psql -h 162.55.174.116 -U website -d categories_db < backup_20230915.sql

# Restore Elasticsearch index
curl -X POST "http://91.99.113.45:9200/_reindex" \
  -H 'Content-Type: application/json' \
  -d '{
    "source": {
      "index": "products_backup"
    },
    "dest": {
      "index": "products"
    }
  }'

Manual Data Repair

-- Fix orphaned records
UPDATE product SET added_search = FALSE WHERE id NOT IN (
  SELECT DISTINCT id FROM product WHERE added_search = TRUE
);

-- Fix duplicate records
DELETE FROM product WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY external_id ORDER BY id) as rn
    FROM product
  ) t WHERE rn > 1
);

Prevention

Monitoring Setup

Set Up Alerts

# Create monitoring script
cat > /usr/local/bin/elasticsearch-monitor.sh << 'EOF'
#!/bin/bash

# Check Elasticsearch health
if ! curl -s http://91.99.113.45:9200/_cluster/health | grep -q '"status":"green"'; then
    echo "Elasticsearch health check failed" | mail -s "Elasticsearch Alert" [email protected]
fi

# Check database connectivity
if ! psql -h 162.55.174.116 -U website -d categories_db -c "SELECT 1" > /dev/null 2>&1; then
    echo "Database connectivity check failed" | mail -s "Database Alert" [email protected]
fi

# Check sync progress
if [ -f /var/www/html/wp-content/themes/products/scripts/elasticsearch-sync-state.json ]; then
    LAST_RUN=$(jq -r '.last_run' /var/www/html/wp-content/themes/products/scripts/elasticsearch-sync-state.json)
    LAST_RUN_TS=$(date -d "$LAST_RUN" +%s)
    CURRENT_TS=$(date +%s)

    if [ $((CURRENT_TS - LAST_RUN_TS)) -gt 3600 ]; then
        echo "Sync process hasn't run in over 1 hour" | mail -s "Sync Alert" [email protected]
    fi
fi
EOF

chmod +x /usr/local/bin/elasticsearch-monitor.sh

# Add to crontab
echo "*/5 * * * * /usr/local/bin/elasticsearch-monitor.sh" | sudo crontab -

Log Monitoring

# Set up log monitoring
tail -f /var/log/dealai/elasticsearch-sync.log | grep -E "(ERROR|WARNING)" | while read line; do
    echo "$line" | mail -s "Elasticsearch Log Alert" [email protected]
done

Regular Maintenance

Daily Maintenance

# Create daily maintenance script
cat > /usr/local/bin/daily-maintenance.sh << 'EOF'
#!/bin/bash

# Clean old logs
find /var/log/dealai -name "*.log" -mtime +7 -delete

# Optimize database
psql -h 162.55.174.116 -U website -d categories_db -c "VACUUM ANALYZE product;"

# Check disk space
if [ $(df /var/log | tail -1 | awk '{print $5}' | sed 's/%//') -gt 80 ]; then
    echo "Disk space warning: $(df /var/log | tail -1 | awk '{print $5}')" | mail -s "Disk Space Alert" [email protected]
fi
EOF

chmod +x /usr/local/bin/daily-maintenance.sh

# Add to crontab
echo "0 2 * * * /usr/local/bin/daily-maintenance.sh" | sudo crontab -

Next Steps

• Performance Optimization - Performance tuning
• Monitoring & Management - Console and monitoring
• Deployment - Installation and setup
• Configuration - System configuration