Troubleshooting - Query Exporter

This guide helps you diagnose and resolve common issues with Query Exporter.

Configuration Validation

Check Configuration Before Running

Use the --check-only flag to validate your configuration without starting the exporter:

query-exporter --config config.yaml --check-only

This validates:

YAML syntax
Configuration structure
Database DSN format
Query SQL syntax
Metric definitions
Label consistency

Common Configuration Errors

Invalid YAML Syntax

Error:

yaml.scanner.ScannerError: while scanning for the next token

Solution:

Check for proper indentation (use spaces, not tabs)
Ensure colons are followed by spaces
Quote strings containing special characters
Use YAML validators online

Example fix:

# Wrong - missing space after colon
databases:
  db1:
    dsn:postgresql://localhost/db

# Correct
databases:
  db1:
    dsn: postgresql://localhost/db

Missing Required Fields

Error:

Validation error: Field required

Solution: Ensure all required fields are present:

# Databases require 'dsn'
databases:
  mydb:
    dsn: sqlite://

# Metrics require 'type'
metrics:
  my_metric:
    type: gauge

# Queries require 'databases', 'metrics', and 'sql'
queries:
  my_query:
    databases: [mydb]
    metrics: [my_metric]
    sql: SELECT 1 as my_metric

Unknown Database References

Error:

Unknown databases for query "my_query": unknown_db

Solution: Ensure database names in queries match those defined in the databases section:

databases:
  prod_db:  # Name here
    dsn: postgresql://localhost/db

queries:
  my_query:
    databases: [prod_db]  # Must match name above
    metrics: [my_metric]
    sql: SELECT 1 as my_metric

Unknown Metric References

Error:

Unknown metrics for query "my_query": unknown_metric

Solution: Ensure metric names in queries match those defined in the metrics section:

metrics:
  user_count:  # Name here
    type: gauge

queries:
  my_query:
    databases: [mydb]
    metrics: [user_count]  # Must match name above
    sql: SELECT COUNT(*) as user_count FROM users

Database Connection Issues

Connection Refused

Error:

Connection refused

Causes & Solutions:

Database not running:

# Check if database is running
systemctl status postgresql
systemctl status mysql

Wrong host/port:

# Verify correct host and port
databases:
  mydb:
    dsn: postgresql://user:pass@localhost:5432/db  # Check port

Firewall blocking connection:

# Test connectivity
telnet db.example.com 5432
nc -zv db.example.com 5432

Authentication Failed

Error:

authentication failed for user "myuser"

Solutions:

Verify credentials:

# Test credentials directly
psql -h localhost -U myuser -d mydb
mysql -h localhost -u myuser -p mydb

Check environment variables:

# Verify environment variable is set
echo $DATABASE_DSN

# Check .env file is loaded
cat .env | grep DATABASE_DSN

URL-encode special characters:

# Wrong - password contains special chars
dsn: postgresql://user:p@ss!@host/db

# Correct - use object format (auto-encodes)
dsn:
  dialect: postgresql
  user: user
  password: p@ss!  # No need to encode
  host: host
  database: db

Database Not Found

Error:

database "mydb" does not exist

Solution:

Verify database name:

# PostgreSQL
psql -l

# MySQL
mysql -e "SHOW DATABASES;"

Check DSN:

databases:
  mydb:
    dsn: postgresql://user:pass@host:5432/correct_db_name

Driver Not Found

Error:

No module named 'psycopg2'

Solution: Install the required database driver:

# PostgreSQL
pip install psycopg2-binary

# MySQL
pip install mysqlclient

# Oracle
pip install oracledb

# SQL Server
pip install pymssql

Or use Docker image with drivers pre-installed:

docker run -v "$PWD:/config" adonato/query-exporter:latest

SSL/TLS Connection Issues

Error:

SSL SYSCALL error: EOF detected

Solutions:

Verify SSL is required:

databases:
  mydb:
    dsn: postgresql://user:pass@host/db?sslmode=disable  # Test without SSL

Configure SSL mode:

databases:
  mydb:
    # Try different SSL modes
    dsn: postgresql://user:pass@host/db?sslmode=require
    # dsn: postgresql://user:pass@host/db?sslmode=verify-ca
    # dsn: postgresql://user:pass@host/db?sslmode=verify-full

Query Execution Issues

Query Timeout

Error:

Query timeout

Solutions:

Increase timeout:

queries:
  slow_query:
    timeout: 30  # Increase from default
    databases: [mydb]
    metrics: [my_metric]
    sql: SELECT * FROM large_table

Optimize query:
- Add indexes
- Reduce data scanned
- Use WHERE clauses
- Avoid SELECT *

Check query performance:

-- PostgreSQL
EXPLAIN ANALYZE SELECT ...;

-- MySQL
EXPLAIN SELECT ...;

Column Name Mismatch

Error:

Column not found in query result: my_metric

Solution: Ensure query returns columns matching metric names:

metrics:
  user_count:  # Metric name
    type: gauge

queries:
  count_users:
    databases: [mydb]
    metrics: [user_count]
    # Column alias must match metric name
    sql: SELECT COUNT(*) as user_count FROM users

Missing Labels

Error:

Missing label in query result: environment

Solution: Query must return columns for all labels:

metrics:
  user_count:
    type: gauge
    labels: [environment, region]  # Labels defined

queries:
  count_users:
    databases: [mydb]
    metrics: [user_count]
    # Must return columns for metric AND all labels
    sql: |
      SELECT
        COUNT(*) as user_count,
        'production' as environment,
        'us-east' as region
      FROM users

SQL Syntax Errors

Error:

syntax error at or near "SELECT"

Solutions:

Test query independently:

psql -h localhost -U user -d db -c "SELECT ..."

Check for parameter escaping:

queries:
  my_query:
    sql: |
      -- Wrong - unescaped colon at word start
      SELECT ':value' as col
      
      -- Correct - escaped colon
      SELECT '\:value' as col
      
      -- Or colon not at word start (no escape needed)
      SELECT 'key:value' as col

Parameter Issues

Error:

Parameter :param1 not provided

Solution: Ensure all query parameters are defined:

queries:
  filtered_query:
    databases: [mydb]
    metrics: [user_count]
    sql: |
      SELECT COUNT(*) as user_count
      FROM users
      WHERE age > :min_age AND age < :max_age
    parameters:
      - min_age: 18
        max_age: 65

Diagnostic Tools

Built-in Metrics

Query Exporter provides metrics to diagnose issues:

database_errors

Counter of database errors:

# Check for database errors
database_errors{database="mydb"}

# Rate of errors
rate(database_errors[5m])

queries

Counter of query executions by status:

# Successful queries
queries{database="mydb", query="my_query", status="success"}

# Failed queries
queries{database="mydb", query="my_query", status="error"}

# Timed out queries
queries{database="mydb", query="my_query", status="timeout"}

# Success rate
rate(queries{status="success"}[5m]) / rate(queries[5m])

query_latency

Histogram of query execution time:

# 95th percentile latency
histogram_quantile(0.95, query_latency_bucket{database="mydb"})

# Average latency
rate(query_latency_sum[5m]) / rate(query_latency_count[5m])

query_timestamp

Timestamp of last query execution:

# Time since last successful query
time() - query_timestamp{database="mydb", query="my_query"}

# Alert if query hasn't run in 5 minutes
time() - query_timestamp > 300

Log Levels

Increase log verbosity for debugging:

# Debug level - very verbose
query-exporter --log-level debug

# Info level - standard operation
query-exporter --log-level info

# Warning level - only warnings and errors
query-exporter --log-level warning

Via environment variable:

export QE_LOG_LEVEL=debug
query-exporter

Log Formats

Plain format (human-readable):

query-exporter --log-format plain

Output:

2024-01-15 10:30:45 INFO Starting query-exporter
2024-01-15 10:30:45 INFO Connected to database: mydb

JSON format (for log aggregation):

query-exporter --log-format json

Output:

{"timestamp": "2024-01-15T10:30:45Z", "level": "info", "message": "Starting query-exporter"}
{"timestamp": "2024-01-15T10:30:45Z", "level": "info", "message": "Connected to database: mydb"}

Environment Variable Issues

Variable Not Set

Error:

while processing 'env' tag
variable DATABASE_DSN undefined

Solutions:

Set environment variable:

export DATABASE_DSN="postgresql://localhost/db"

Use .env file:

# Create .env file
echo 'DATABASE_DSN=postgresql://localhost/db' > .env

# Query Exporter loads it automatically
query-exporter --config config.yaml

Verify .env location:

# Default: looks in current directory
ls -la .env

# Custom location
export QE_DOTENV=/path/to/.env

Wrong Variable Type

Error:

Expected integer, got string

Solution: Environment variables are parsed as YAML/JSON:

# Wrong - quoted string parsed as string, not number
export QUERY_INTERVAL="60"

# Correct - unquoted number parsed as integer
export QUERY_INTERVAL=60

queries:
  my_query:
    interval: !env QUERY_INTERVAL  # Must be a number

File Reference Issues

File Not Found

Error:

while processing 'file' tag
file sql/query.sql not found

Solutions:

Check file exists:
```
ls -la sql/query.sql
```
Verify relative path:
- Paths are relative to the YAML file using the !file tag
- Not relative to current directory

Use absolute path:

queries:
  my_query:
    sql: !file /absolute/path/to/query.sql

Include File Not Found

Error:

while processing 'include' tag
file databases.yaml not found

Solution: Same as file references - check path is correct:

# In config.yaml
databases: !include ./config/databases.yaml  # Relative to config.yaml

Docker-Specific Issues

Config File Not Found

Error:

Config file not found: /config/config.yaml

Solution: Mount config directory correctly:

# Mount current directory to /config
docker run -v "$PWD:/config" adonato/query-exporter:latest

# Or specific file
docker run -v "$PWD/config.yaml:/config/config.yaml" adonato/query-exporter:latest

Database Host Connectivity

Error:

Connection refused to localhost

Solution: Use correct host from container perspective:

# Wrong - localhost refers to container, not host
databases:
  mydb:
    dsn: postgresql://user:pass@localhost:5432/db

# Correct - use host.docker.internal or actual host IP
databases:
  mydb:
    dsn: postgresql://user:[email protected]:5432/db

In docker-compose:

services:
  query-exporter:
    image: adonato/query-exporter:latest
    depends_on:
      - postgres
  
  postgres:
    image: postgres:15

# Use service name as hostname
databases:
  mydb:
    dsn: postgresql://user:pass@postgres:5432/db

Performance Issues

High Memory Usage

Causes & Solutions:

Too many concurrent queries:

# Reduce connection pool size
databases:
  mydb:
    connection-pool:
      size: 2  # Reduce from default
      max-overflow: 0

Large result sets:
- Add LIMIT to queries
- Use aggregation instead of raw data
- Process data in database, not exporter

Metric series explosion:

metrics:
  my_metric:
    type: gauge
    labels: [env, region]
    expiration: 1h  # Clear stale series

High CPU Usage

Causes & Solutions:

Too frequent queries:

queries:
  my_query:
    interval: 60  # Increase from lower value

Expensive queries:
- Optimize SQL queries
- Add database indexes
- Use materialized views

Slow Startup

Solution: Use --check-only to identify slow configuration:

time query-exporter --config config.yaml --check-only

Check for:

Slow database connections
Large included files
Many database definitions

Getting Help

Enable Debug Logging

Capture detailed logs:

query-exporter --config config.yaml --log-level debug --log-format json > debug.log 2>&1

Collect Diagnostic Information

Version:
```
query-exporter --version
```

Configuration (sanitized):

# Remove sensitive data before sharing
cat config.yaml | sed 's/password:.*/password: REDACTED/'

Metrics:
```
curl http://localhost:9560/metrics
```

Logs:

query-exporter --log-level debug 2>&1 | tee debug.log

Report Issues

When reporting issues, include:

Query Exporter version
Database type and version
Sanitized configuration
Complete error message
Debug logs (if applicable)
Steps to reproduce

GitHub Issues: https://github.com/albertodonato/query-exporter/issues Discussions: https://github.com/albertodonato/query-exporter/discussions

Get Started

Configuration

Deployment

Guides

Examples

​Configuration Validation

​Check Configuration Before Running

​Common Configuration Errors

​Invalid YAML Syntax

​Missing Required Fields

​Unknown Database References

​Unknown Metric References

​Database Connection Issues

​Connection Refused

​Authentication Failed

​Database Not Found

​Driver Not Found

​SSL/TLS Connection Issues

​Query Execution Issues

​Query Timeout

​Column Name Mismatch

​Missing Labels

​SQL Syntax Errors

​Parameter Issues

​Diagnostic Tools

​Built-in Metrics

​database_errors

​queries

​query_latency

​query_timestamp

​Log Levels

​Log Formats

​Environment Variable Issues

​Variable Not Set

​Wrong Variable Type

​File Reference Issues

​File Not Found

​Include File Not Found

​Docker-Specific Issues

​Config File Not Found

​Database Host Connectivity

​Performance Issues

​High Memory Usage

​High CPU Usage

​Slow Startup

​Getting Help

​Enable Debug Logging

​Collect Diagnostic Information

​Report Issues

​Further Reading

Build docs developers (and LLMs) love

Configuration Validation

Check Configuration Before Running

Common Configuration Errors

Invalid YAML Syntax

Missing Required Fields

Unknown Database References

Unknown Metric References

Database Connection Issues

Connection Refused

Authentication Failed

Database Not Found

Driver Not Found

SSL/TLS Connection Issues

Query Execution Issues

Query Timeout

Column Name Mismatch

Missing Labels

SQL Syntax Errors

Parameter Issues

Diagnostic Tools

Built-in Metrics

database_errors

queries

query_latency

query_timestamp

Log Levels

Log Formats

Environment Variable Issues

Variable Not Set

Wrong Variable Type

File Reference Issues

File Not Found

Include File Not Found

Docker-Specific Issues

Config File Not Found

Database Host Connectivity

Performance Issues

High Memory Usage

High CPU Usage

Slow Startup

Getting Help

Enable Debug Logging

Collect Diagnostic Information

Report Issues

Further Reading