Skip to content

Troubleshooting

Jump to bottom
Temp edited this page Sep 23, 2025 · 1 revision

Troubleshooting

Last Updated: September 23, 2025 1:48 PM EST

Common issues and solutions for the SQLite MCP Server.

🔧 JSON-Related Issues

JSONB Function Errors

Issue: "no such function: jsonb" Cause: Your SQLite version doesn't support JSONB (requires 3.45.0+)

// Check SQLite version
read_query({"query": "SELECT sqlite_version()"})

Solution: Update SQLite or use legacy JSON functions.

JSON Validation Errors

Issue: "Invalid JSON in column" Cause: The JSON string is malformed

// Use JSON Helper Tools for automatic validation
json_validate_security({"json_data": jsonString})

Issue: "JSON parse error" Cause: JSON syntax is incorrect

// ❌ Incorrect: Single quotes
{'key': 'value'}

// ✅ Correct: Double quotes
{"key": "value"}

Solution: Use JSON Helper Tools which automatically fix common formatting issues.

🔒 Security Issues

SQL Injection Attempts

Issue: Queries being blocked or rejected Cause: Security protection is working correctly

# Test security protection
cd tests && python test_sql_injection.py

Expected Behavior: Critical injection vectors should be blocked.

Parameter Binding Errors

Issue: Parameters not being substituted correctly Cause: Incorrect parameter format or count

// ✅ Correct parameter binding
read_query({
  "query": "SELECT * FROM users WHERE id = ? AND status = ?",
  "params": [123, "active"]
})

// ❌ Incorrect: Parameter count mismatch
read_query({
  "query": "SELECT * FROM users WHERE id = ? AND status = ?",
  "params": [123]  // Missing second parameter
})

🗄️ Database Connection Issues

Database Lock Errors

Issue: "database is locked" Cause: Another connection is holding the database lock

Solutions:

  1. Check for long-running transactions in other processes
  2. Ensure proper connection cleanup
  3. Use WAL mode for better concurrency:
pragma_settings({
  "pragma_name": "journal_mode",
  "value": "WAL"
})

File Permission Issues

Issue: Cannot access database file Cause: Insufficient file system permissions

Solutions:

  1. Check file permissions: ls -la database.db
  2. Ensure directory is writable
  3. For Docker: Check volume mount permissions

🔄 Transaction Issues

Transaction Rollback Errors

Issue: "Error during rollback" Cause: Problem occurred during transaction rollback

Solutions:

  1. Check database integrity: integrity_check()
  2. Restart the MCP server if persistent
  3. Restore from backup if necessary

Deadlock Issues

Issue: Operations hanging or timing out Cause: Circular dependency between transactions

Solutions:

  1. Use shorter transactions
  2. Access tables in consistent order
  3. Implement retry logic with exponential backoff

🔗 Foreign Key Issues

Foreign Key Constraint Failures

Issue: "foreign key constraint failed" Cause: Attempted to violate a foreign key constraint

Solutions:

  1. Verify the referenced record exists:
read_query({
  "query": "SELECT id FROM parent_table WHERE id = ?",
  "params": [parent_id]
})
  1. Use proper cascading delete:
create_table({
  "query": `CREATE TABLE child_table (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE
  )`
})

Foreign Key Enforcement Issues

Issue: Foreign keys not being enforced Cause: Foreign key support not enabled

Solution:

pragma_settings({
  "pragma_name": "foreign_keys",
  "value": "ON"
})

📊 Performance Issues

Slow Query Performance

Issue: Queries taking too long to execute Cause: Missing indexes or inefficient queries

Solutions:

  1. Analyze query performance:
read_query({
  "query": "EXPLAIN QUERY PLAN SELECT * FROM large_table WHERE column = ?",
  "params": ["value"]
})
  1. Create appropriate indexes:
write_query({
  "query": "CREATE INDEX idx_column ON large_table(column)"
})
  1. Update database statistics:
analyze_database()

Large Database Issues

Issue: Database operations becoming slow Cause: Database fragmentation or large size

Solutions:

  1. Vacuum the database:
vacuum_database()
  1. Check database statistics:
database_stats()
  1. Consider partitioning large tables or archiving old data

🔍 Search Issues

FTS5 Search Problems

Issue: Full-text search not working Cause: FTS5 table not properly configured

Solutions:

  1. Verify FTS5 table exists:
list_virtual_tables()
  1. Rebuild FTS index:
rebuild_fts_index({
  "table_name": "documents_fts"
})

Semantic Search Issues

Issue: Semantic search returning poor results Cause: Embeddings not properly stored or indexed

Solutions:

  1. Check embeddings table structure:
describe_table({"table_name": "embeddings_table"})
  1. Verify embedding dimensions match:
read_query({
  "query": "SELECT LENGTH(embedding) FROM embeddings_table LIMIT 1"
})
  1. Rebuild vector index:
rebuild_vector_index({
  "table_name": "embeddings_table"
})

🐳 Docker Issues

Container Startup Problems

Issue: Container fails to start Cause: Various Docker-related issues

Solutions:

  1. Check Docker logs:
docker logs <container_id>
  1. Verify volume mounts:
docker run -i --rm \
  -v $(pwd):/workspace \
  writenotenow/sqlite-mcp-server:latest \
  --db-path /workspace/sqlite_mcp.db
  1. Ensure database file permissions are correct

Volume Mount Issues

Issue: Database changes not persisting Cause: Incorrect volume mount configuration

Solution: Ensure proper volume mounting:

# Correct volume mount
docker run -v /host/path:/workspace writenotenow/sqlite-mcp-server:latest

# Check mount inside container
docker exec -it <container> ls -la /workspace

🧪 Testing Issues

Test Suite Failures

Issue: Tests failing unexpectedly Cause: Environment or dependency issues

Solutions:

  1. Check test environment:
python test_runner.py --quick
  1. Verify dependencies:
pip list | grep -E "(mcp|sqlite)"
  1. Run specific test categories:
python test_runner.py --json      # JSON helper tools
python test_runner.py --security  # Security features

Version Compatibility Issues

Issue: Features not working as expected Cause: Version mismatch between components

Solutions:

  1. Check all versions:
pragma_compile_options()  // SQLite features
  1. Verify MCP server version:
python start_sqlite_mcp.py --version

🔧 Configuration Issues

MCP Client Configuration

Issue: MCP client cannot connect to server Cause: Incorrect configuration

Solutions:

  1. Verify configuration format:
{
  "mcpServers": {
    "sqlite-mcp-server": {
      "command": "python",
      "args": [
        "/path/to/sqlite-mcp-server/start_sqlite_mcp.py",
        "--db-path", "/path/to/database.db"
      ]
    }
  }
}
  1. Check file paths are absolute
  2. Verify Python environment has required packages

Environment Issues

Issue: Server not finding required dependencies Cause: Python environment not properly configured

Solutions:

  1. Activate correct virtual environment
  2. Install requirements:
pip install -r requirements.txt
  1. For development:
pip install -e .

🆘 Emergency Recovery

Database Corruption

Issue: Database integrity check fails Cause: Database file corruption

Solutions:

  1. Check integrity:
integrity_check()
  1. Attempt repair:
sqlite3 database.db ".recover" > recovered.sql
sqlite3 new_database.db < recovered.sql
  1. Restore from backup:
restore_database({
  "backup_path": "./backups/latest_backup.db",
  "confirm": true
})

Complete System Recovery

Issue: Multiple system failures Cause: Various cascading issues

Recovery Steps:

  1. Stop all processes
  2. Backup current state (even if corrupted)
  3. Restore from known good backup
  4. Verify integrity
  5. Run comprehensive tests
  6. Gradually restore functionality

📞 Getting Help

Before Reporting Issues

  1. Run diagnostics:
python test_runner.py --quick
cd tests && python test_sql_injection.py
  1. Collect system information:
pragma_compile_options()  // SQLite capabilities
database_stats()           // Database status
  1. Check logs for error messages and stack traces

Reporting Issues

When reporting issues, include:

  • SQLite MCP Server version
  • SQLite version (SELECT sqlite_version())
  • Python version
  • Operating system
  • Complete error messages
  • Minimal reproduction steps
  • Expected vs actual behavior

Resources

🔧 Pro Tip: Most issues can be resolved by running the comprehensive test suite and following the error messages. The server includes extensive diagnostics to help identify problems quickly.

Clone this wiki locally