HeliosDB Nano SQLite Compatibility Troubleshooting Guide

HeliosDB Nano SQLite Compatibility Troubleshooting Guide

This guide helps you quickly resolve common issues when using HeliosDB Nano as a SQLite drop-in replacement.

---

Table of Contents

1. Installation Issues
2. Import and Module Errors
3. Compatibility Issues
4. Performance Problems
5. Data Migration Issues
6. Mode Switching Issues
7. Connection and Threading Issues
8. Testing and Debugging Tips

---

Installation Issues

Problem: pip install fails

Symptoms:

$ pip install heliosdb-sqlite
ERROR: Could not find a version that satisfies the requirement heliosdb-sqlite

Solutions:

1. Update pip and setuptools:

python -m pip install --upgrade pip setuptools wheel
pip install heliosdb-sqlite

2. Check Python version:

python --version  # Must be Python 3.8 or higher

3. Try specific version:

pip install heliosdb-sqlite==1.0.0

4. Install from source (if pre-built wheels unavailable):

git clone https://github.com/dimensigon/HDB-HeliosDB-Nano.git
cd heliosdb-nano/python
pip install .

---

Problem: Import works in terminal but not in IDE

Symptoms:

# Works in terminal
$ python -c "import heliosdb_sqlite"

# Fails in PyCharm/VSCode
ModuleNotFoundError: No module named 'heliosdb_sqlite'

Solutions:

1. Verify IDE is using correct Python interpreter:

   • PyCharm: Settings > Project > Python Interpreter
   • VSCode: Select interpreter from command palette (Ctrl+Shift+P)

2. Install in IDE’s environment:

# Find IDE's Python path
which python

# Install there explicitly
/path/to/ide/python -m pip install heliosdb-sqlite

3. Restart IDE after installation

---

Import and Module Errors

Problem: “No module named ‘heliosdb_sqlite’”

Symptoms:

import heliosdb_sqlite as sqlite3
# ModuleNotFoundError: No module named 'heliosdb_sqlite'

Solutions:

1. Verify installation:

pip list | grep heliosdb
python -c "import heliosdb_sqlite; print(heliosdb_sqlite.__version__)"

2. Check virtual environment:

# Activate correct environment
source venv/bin/activate  # Linux/Mac
venv\Scripts\activate     # Windows

# Verify
which python
pip list

3. Reinstall package:

pip uninstall heliosdb-sqlite
pip install heliosdb-sqlite

---

Problem: AttributeError with HeliosDB Nano methods

Symptoms:

conn = sqlite3.connect('app.db')
conn.some_method()
# AttributeError: 'Connection' object has no attribute 'some_method'

Solutions:

1. Check if method is standard SQLite:

import sqlite3 as std_sqlite

# Check if method exists in standard SQLite
print(dir(std_sqlite.connect(':memory:')))

2. Use HeliosDB Nano specific features correctly:

# Wrong: advanced features need to be enabled
conn = sqlite3.connect('app.db')
conn.create_branch('test')  # May fail

# Right: enable advanced features
conn = sqlite3.connect('app.db', heliosdb_mode='advanced')
branch = conn.create_branch('test')  # Works!

3. Check API documentation:

# Get help on connection object
help(conn)

# List all available methods
print([m for m in dir(conn) if not m.startswith('_')])

---

Compatibility Issues

Problem: Existing code breaks with HeliosDB Nano

Symptoms:

# Code worked with SQLite but fails with HeliosDB Nano
cursor.execute('SELECT * FROM users')
# Some error occurs

Solutions:

1. Enable compatibility mode:

# Maximum SQLite compatibility
conn = sqlite3.connect('app.db', heliosdb_mode='sqlite_compat')

2. Check for unsupported features:

# Test basic operations one by one
import heliosdb_sqlite as sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Test CREATE
cursor.execute('CREATE TABLE test (id INTEGER, name TEXT)')
print("CREATE: OK")

# Test INSERT
cursor.execute('INSERT INTO test VALUES (1, "test")')
conn.commit()
print("INSERT: OK")

# Test SELECT
cursor.execute('SELECT * FROM test')
print(f"SELECT: {cursor.fetchall()}")

# Test UPDATE
cursor.execute('UPDATE test SET name = "updated" WHERE id = 1')
conn.commit()
print("UPDATE: OK")

# Test DELETE
cursor.execute('DELETE FROM test WHERE id = 1')
conn.commit()
print("DELETE: OK")

3. Use fallback pattern:

# Conditional import for safety
try:
    import heliosdb_sqlite as sqlite3
    db_engine = 'heliosdb'
except Exception as e:
    print(f"HeliosDB Nano failed ({e}), falling back to SQLite")
    import sqlite3
    db_engine = 'sqlite'

print(f"Using: {db_engine}")

---

Problem: SQL syntax not recognized

Symptoms:

cursor.execute('SELECT * FROM users LIMIT 10 OFFSET 5')
# Syntax error or unexpected behavior

Solutions:

1. Verify SQL is standard SQLite syntax:

# Test query in standard SQLite first
import sqlite3 as std_sqlite
test_conn = std_sqlite.connect(':memory:')
test_conn.execute('CREATE TABLE users (id INTEGER, name TEXT)')
test_conn.execute('INSERT INTO users VALUES (1, "test")')
result = test_conn.execute('SELECT * FROM users LIMIT 10 OFFSET 5')
print(result.fetchall())

2. Check for HeliosDB Nano specific syntax:

# Some advanced features require enabling
conn = sqlite3.connect('app.db', enable_time_travel=True)

# Now time-travel syntax works
cursor.execute('''
    SELECT * FROM users
    AS OF TIMESTAMP '2024-01-01 10:00:00'
''')

3. Use parameterized queries:

# Instead of string formatting
# cursor.execute(f'SELECT * FROM users WHERE id = {user_id}')  # Bad

# Use parameters
cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))  # Good

---

Performance Problems

Problem: Queries are slower than SQLite

Symptoms:

# Query takes longer than expected
start = time.time()
cursor.execute('SELECT * FROM large_table')
results = cursor.fetchall()
print(f"Time: {time.time() - start:.2f}s")  # Slower than SQLite

Solutions:

1. Create appropriate indexes:

# Check if indexes exist
cursor.execute("SELECT name FROM sqlite_master WHERE type='index'")
print("Existing indexes:", cursor.fetchall())

# Create missing indexes
cursor.execute('CREATE INDEX IF NOT EXISTS idx_user_email ON users(email)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_post_date ON posts(created_at)')
conn.commit()

2. Enable query caching:

# Enable result caching for read-heavy workloads
conn = sqlite3.connect('app.db', enable_cache=True, cache_size_mb=100)

3. Use batch operations:

# Slow: individual inserts
for user in users:
    cursor.execute('INSERT INTO users VALUES (?, ?)', user)
    conn.commit()  # Don't commit each insert!

# Fast: batch with single commit
for user in users:
    cursor.execute('INSERT INTO users VALUES (?, ?)', user)
conn.commit()  # Single commit at end

# Fastest: executemany
cursor.executemany('INSERT INTO users VALUES (?, ?)', users)
conn.commit()

4. Analyze query plans:

# See how query is executed
cursor.execute('EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?', ('test@example.com',))
plan = cursor.fetchall()
print("Query plan:", plan)

# Look for "SCAN" (bad) vs "SEARCH" (good)
# If you see SCAN, you probably need an index

5. Adjust connection settings:

# Tune for performance
conn = sqlite3.connect(
    'app.db',
    isolation_level='DEFERRED',  # Less locking
    check_same_thread=False,     # Multi-threaded
    cached_statements=100,       # Cache prepared statements
    timeout=30.0                 # Longer timeout
)

# Set pragmas
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA synchronous=NORMAL')
conn.execute('PRAGMA cache_size=10000')
conn.execute('PRAGMA temp_store=MEMORY')

---

Problem: High memory usage

Symptoms:

# Memory keeps growing
conn = sqlite3.connect('app.db')
# Memory usage increases over time

Solutions:

1. Close connections properly:

# Use context manager
with sqlite3.connect('app.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    results = cursor.fetchall()
# Connection automatically closed

# Or explicit close
conn = sqlite3.connect('app.db')
try:
    # ... do work ...
finally:
    conn.close()

2. Fetch data in chunks:

# Don't load all data at once
cursor.execute('SELECT * FROM large_table')

# Fetch in batches
batch_size = 1000
while True:
    rows = cursor.fetchmany(batch_size)
    if not rows:
        break
    process_rows(rows)

3. Limit cache size:

conn = sqlite3.connect('app.db', cache_size_mb=50)  # Limit cache to 50MB

4. Vacuum database:

# Reclaim unused space
conn.execute('VACUUM')

---

Data Migration Issues

Problem: Can’t open existing SQLite database

Symptoms:

conn = sqlite3.connect('existing.db')
# Error: database disk image is malformed

Solutions:

1. Check database integrity:

# Use SQLite CLI to check
sqlite3 existing.db "PRAGMA integrity_check;"

2. Repair database:

# Export and reimport
sqlite3 existing.db ".dump" > backup.sql
sqlite3 new.db < backup.sql

# Then use new.db with HeliosDB Nano

3. Check file permissions:

ls -la existing.db  # Should be readable/writable
chmod 644 existing.db

---

Problem: Data looks corrupted after switching to HeliosDB Nano

Symptoms:

# Data appears incorrect or missing
cursor.execute('SELECT * FROM users')
results = cursor.fetchall()
# Results don't match expectations

Solutions:

1. Verify with SQLite first:

import sqlite3 as std_sqlite

# Check with standard SQLite
std_conn = std_sqlite.connect('app.db')
std_cursor = std_conn.cursor()
std_cursor.execute('SELECT COUNT(*) FROM users')
print(f"SQLite sees: {std_cursor.fetchone()[0]} users")

# Check with HeliosDB Nano
import heliosdb_sqlite
hdb_conn = heliosdb_sqlite.connect('app.db')
hdb_cursor = hdb_conn.cursor()
hdb_cursor.execute('SELECT COUNT(*) FROM users')
print(f"HeliosDB Nano sees: {hdb_cursor.fetchone()[0]} users")

2. Check transaction isolation:

# Ensure proper transaction handling
conn = sqlite3.connect('app.db', isolation_level='DEFERRED')
conn.execute('BEGIN')
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
results = cursor.fetchall()
conn.commit()

3. Flush caches:

# Force data to disk
conn.commit()
conn.execute('PRAGMA wal_checkpoint(FULL)')

---

Mode Switching Issues

Problem: Can’t start server mode

Symptoms:

server = sqlite3.start_server('app.db', port=5432)
# Error: Address already in use

Solutions:

1. Check if port is in use:

# Linux/Mac
lsof -i :5432
netstat -tuln | grep 5432

# Windows
netstat -ano | findstr :5432

# Kill existing process or use different port

2. Use different port:

server = sqlite3.start_server('app.db', port=5433)  # Different port

3. Check permissions:

# Ensure you can bind to port (< 1024 needs root)
sudo python your_script.py  # If port < 1024

# Or use port > 1024
server = sqlite3.start_server('app.db', port=8432)

---

Problem: Hybrid mode not working

Symptoms:

conn = sqlite3.connect('app.db', heliosdb_mode='hybrid')
# Can't connect remotely even though server should be running

Solutions:

1. Check server info:

conn = sqlite3.connect('app.db', heliosdb_mode='hybrid')
info = conn.get_server_info()
print(f"Server running: {info.is_running}")
print(f"Port: {info.port}")
print(f"Host: {info.host}")

2. Check firewall:

# Linux
sudo ufw status
sudo ufw allow 5432/tcp

# Check if server is listening
netstat -tuln | grep 5432

3. Test connection:

# Try connecting with psql
psql -h localhost -p 5432 -U helios app

# Or with Python
import psycopg2
conn = psycopg2.connect(host='localhost', port=5432, database='app')

---

Connection and Threading Issues

Problem: “SQLite objects created in a thread can only be used in that same thread”

Symptoms:

conn = sqlite3.connect('app.db')
# Use connection from different thread
# Error: SQLite objects created in a thread can only be used in that same thread

Solutions:

1. Disable thread checking:

conn = sqlite3.connect('app.db', check_same_thread=False)

2. Use connection per thread:

import threading

thread_local = threading.local()

def get_connection():
    if not hasattr(thread_local, 'conn'):
        thread_local.conn = sqlite3.connect('app.db')
    return thread_local.conn

# Each thread gets its own connection
def worker():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')

3. Use connection pooling:

from heliosdb_sqlite import connection_pool

pool = connection_pool.create('app.db', pool_size=10)

def worker():
    with pool.connection() as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM users')

---

Problem: Concurrent write deadlocks

Symptoms:

# Multiple threads trying to write
# Some threads hang or timeout

Solutions:

1. HeliosDB Nano should handle this, but if it happens:

# Increase timeout
conn = sqlite3.connect('app.db', timeout=30.0)

# Use retry logic
import time

def write_with_retry(query, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute(query, params)
            conn.commit()
            return
        except sqlite3.OperationalError as e:
            if 'locked' in str(e) and attempt < max_retries - 1:
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
            else:
                raise

2. Check if you’re actually using HeliosDB Nano:

import heliosdb_sqlite as sqlite3
print(f"Using: {sqlite3.__name__}")  # Should say 'heliosdb_sqlite'

---

Testing and Debugging Tips

Enable debug logging

import heliosdb_sqlite as sqlite3
import logging

# Enable debug logging
logging.basicConfig(level=logging.DEBUG)
sqlite3.enable_debug_logging()

# Now all database operations are logged
conn = sqlite3.connect('app.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

---

Compare behavior with SQLite

import sqlite3 as std_sqlite
import heliosdb_sqlite as hdb_sqlite

def test_query(db_module, name):
    try:
        conn = db_module.connect(':memory:')
        cursor = conn.cursor()

        cursor.execute('CREATE TABLE test (id INTEGER, value TEXT)')
        cursor.execute('INSERT INTO test VALUES (1, "hello")')
        conn.commit()

        cursor.execute('SELECT * FROM test')
        result = cursor.fetchall()

        print(f"{name}: {result}")
        conn.close()
        return True
    except Exception as e:
        print(f"{name} failed: {e}")
        return False

# Test both
test_query(std_sqlite, "SQLite")
test_query(hdb_sqlite, "HeliosDB Nano")

---

Isolate the problem

# Minimal reproducible example
import heliosdb_sqlite as sqlite3

# Test with in-memory database (simpler)
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Test your exact failing query
try:
    cursor.execute('YOUR FAILING QUERY HERE')
    print("Query succeeded!")
except Exception as e:
    print(f"Query failed: {e}")
    import traceback
    traceback.print_exc()

---

Check HeliosDB Nano version and capabilities

import heliosdb_sqlite as sqlite3

print(f"HeliosDB Nano version: {sqlite3.__version__}")
print(f"SQLite version: {sqlite3.sqlite_version}")

# Check available features
conn = sqlite3.connect(':memory:')
features = conn.get_features()
print(f"Vector search: {features.vector_search}")
print(f"Time-travel: {features.time_travel}")
print(f"Branching: {features.branching}")
print(f"Encryption: {features.encryption}")

---

Generate diagnostic report

def generate_diagnostic_report():
    import heliosdb_sqlite as sqlite3
    import sys
    import platform

    print("=== HeliosDB Nano Diagnostic Report ===\n")

    print(f"Python version: {sys.version}")
    print(f"Platform: {platform.platform()}")
    print(f"HeliosDB Nano version: {sqlite3.__version__}")
    print(f"SQLite version: {sqlite3.sqlite_version}")

    print("\nTesting basic operations...")

    try:
        conn = sqlite3.connect(':memory:')
        cursor = conn.cursor()

        cursor.execute('CREATE TABLE test (id INTEGER)')
        cursor.execute('INSERT INTO test VALUES (1)')
        cursor.execute('SELECT * FROM test')
        result = cursor.fetchall()

        print(f"Basic operations: OK")
        print(f"Result: {result}")

        conn.close()
    except Exception as e:
        print(f"Basic operations: FAILED")
        print(f"Error: {e}")

    print("\n=== End Diagnostic Report ===")

# Run diagnostic
generate_diagnostic_report()

---

Getting Help

If you’ve tried these solutions and still have issues:

1. Check GitHub Issues: https://github.com/dimensigon/HDB-HeliosDB-Nano/issues
2. Ask on Stack Overflow: Tag with heliosdb
3. Join Discord Community: Real-time help from users
4. Email Support: support@heliosdb.io

When asking for help, include:

• HeliosDB Nano version (sqlite3.__version__)
• Python version
• Operating system
• Minimal code that reproduces the issue
• Full error message and traceback
• What you’ve already tried

---

Common Error Messages

”database is locked”

Cause: Multiple processes trying to write simultaneously (shouldn’t happen with HeliosDB Nano!)

Fix:

# Verify you're actually using HeliosDB Nano
import heliosdb_sqlite as sqlite3
print(sqlite3.__name__)  # Should be 'heliosdb_sqlite'

# If confirmed HeliosDB Nano, increase timeout
conn = sqlite3.connect('app.db', timeout=30.0)

---

“no such table”

Cause: Table doesn’t exist or transaction issues

Fix:

# List all tables
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print("Available tables:", cursor.fetchall())

# Create table if missing
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)')

---

“disk I/O error”

Cause: Permissions, disk space, or hardware issues

Fix:

# Check disk space
df -h

# Check permissions
ls -la database.db

# Check disk health
# Linux: sudo smartctl -H /dev/sda
# Mac: diskutil verifyVolume /

---

Prevention Tips

1. Always use context managers:

with sqlite3.connect('app.db') as conn:
    # Work with database
    pass
# Automatically closed and committed

2. Use parameterized queries:

cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))  # Safe

3. Handle exceptions:

try:
    conn.execute('INSERT INTO users VALUES (?, ?)', (id, name))
    conn.commit()
except sqlite3.IntegrityError:
    print("Duplicate key")
    conn.rollback()
except Exception as e:
    print(f"Unexpected error: {e}")
    conn.rollback()
    raise

4. Test thoroughly:

# Test suite should cover
pytest tests/ -v --cov

# Include integration tests
# Include concurrent access tests
# Include migration tests

5. Monitor in production:

conn.enable_monitoring()
metrics = conn.get_metrics()
# Alert on anomalies

---

For more help:

• HELIOSDB_SQLITE_DROP_IN_GUIDE.md - Getting started
• HELIOSDB_SQLITE_FAQ.md - Common questions
• HELIOSDB_SQLITE_ADVANCED_FEATURES.md - Advanced usage
• GitHub Issues - Bug reports and discussions