Skip to content

Latest commit

 

History

History
936 lines (707 loc) · 26.9 KB

MIGRATION-0.7.0.md

File metadata and controls

936 lines (707 loc) · 26.9 KB

Migration Guide: Upgrading to Multi-Tenancy (v0.6.0 to v0.7.0)

This guide walks you through upgrading from MCP Gateway v0.6.0 to v0.7.0 that implements comprehensive multi-tenancy, team management, and RBAC.

Overview

Version 0.7.0 introduces major architectural changes:

  • Multi-tenant architecture with team-based resource isolation
  • Email-based authentication alongside existing basic auth
  • Personal teams automatically created for each user
  • Role-Based Access Control (RBAC) with granular permissions
  • Team visibility controls (private/public teams, private/team/public resources)
  • SSO integration with GitHub, Google, and generic OIDC providers

🛠️ Migration Tools

This migration includes 2 essential scripts to help you:

scripts/verify_multitenancy_0_7_0_migration.py

  • Purpose: Verify v0.6.0 → v0.7.0 migration completed successfully
  • Checks: Admin user, personal team, resource assignments, visibility settings
  • When: Run after migration to confirm everything worked

scripts/fix_multitenancy_0_7_0_resources.py

  • Purpose: Fix resources missing team assignments after v0.6.0 → v0.7.0 upgrade
  • Fixes: Assigns orphaned servers/tools/resources to admin's personal team
  • When: Use if verification shows unassigned resources

Pre-Migration Checklist

1. Backup Your Database & Configuration

⚠️ CRITICAL: Always backup your database AND configuration before upgrading

Database Backup

# For SQLite (default)
cp mcp.db mcp.db.backup.$(date +%Y%m%d_%H%M%S)

# For PostgreSQL
pg_dump -h localhost -U postgres -d mcp > mcp_backup_$(date +%Y%m%d_%H%M%S).sql

# For MySQL
mysqldump -u mysql -p mcp > mcp_backup_$(date +%Y%m%d_%H%M%S).sql

Environment Configuration Backup

# Backup existing .env file before updating
cp .env .env.bak

Configuration Export (Recommended)

💡 Export your current configuration via the Admin UI before migration:

# 1. Start your current MCP Gateway
make dev  # or however you normally run it

# 2. Access the admin UI
open http://localhost:4444/admin

# 3. Navigate to Export/Import section
# 4. Click "Export Configuration"
# 5. Save the JSON file (contains servers, tools, resources, etc.)

# Or use direct API call (if you have a bearer token):
curl -H "Authorization: Bearer YOUR_TOKEN" \
     "http://localhost:4444/admin/export/configuration" \
     -o mcp_config_backup_$(date +%Y%m%d_%H%M%S).json

# Or with basic auth:
curl -u admin:changeme \
     "http://localhost:4444/admin/export/configuration" \
     -o mcp_config_backup_$(date +%Y%m%d_%H%M%S).json

✅ Benefits:

  • Preserves all your servers, tools, resources, and settings
  • Can be imported after migration if needed
  • Human-readable JSON format

2. Setup Environment Configuration

⚠️ CRITICAL: You must setup your .env file before running the migration

The migration uses your .env configuration to create the platform admin user.

If you don't have a .env file:

# Copy the example file
cp .env.example .env

# Edit .env to set your admin credentials
nano .env  # or your preferred editor

If you already have a .env file:

# Backup your current .env (already done above)
cp .env .env.bak

# Update with new settings from .env.example
cp .env.example .env.new
# Then manually merge your existing settings into .env.new and rename:
# mv .env.new .env

# Or check if you have the required settings and add manually
grep -E "PLATFORM_ADMIN_EMAIL|PLATFORM_ADMIN_PASSWORD|EMAIL_AUTH_ENABLED" .env

# If missing, add them or merge from .env.example

3. Configure Required Settings

⚠️ REQUIRED: Configure these settings in your .env file before migration

# Platform Administrator (will be created by migration)
PLATFORM_ADMIN_EMAIL=your-admin@yourcompany.com
PLATFORM_ADMIN_PASSWORD=your-secure-password
PLATFORM_ADMIN_FULL_NAME="Your Name"

# Enable email authentication (required for multi-tenancy)
EMAIL_AUTH_ENABLED=true

# Personal team settings (recommended defaults)
AUTO_CREATE_PERSONAL_TEAMS=true
PERSONAL_TEAM_PREFIX=personal

💡 Tips:

  • Use a real email address for PLATFORM_ADMIN_EMAIL (you'll use this to log in)
  • Choose a strong password (minimum 8 characters)
  • Set EMAIL_AUTH_ENABLED=true to enable the multitenancy features

🔍 Verify your configuration:

# Check your settings are loaded correctly
python3 -c "
from mcpgateway.config import settings
print(f'Admin email: {settings.platform_admin_email}')
print(f'Email auth: {settings.email_auth_enabled}')
print(f'Personal teams: {settings.auto_create_personal_teams}')
"

Migration Process

🚨 IMPORTANT: Before starting the migration, you must have a properly configured .env file with PLATFORM_ADMIN_EMAIL and other required settings. The migration will use these settings to create your admin user. See the Pre-Migration Checklist above.

Step 1: Update Codebase

# Pull the latest changes
git fetch origin main
git checkout main
git pull origin main

# Update dependencies
make install-dev

Step 2: Run Database Migration

The migration process is automated and handles:

  • Creating multi-tenancy database schema
  • Creating platform admin user and personal team
  • Migrating existing servers to the admin's personal team
  • Setting up default RBAC roles

⚠️ PREREQUISITE: Ensure .env file is configured with PLATFORM_ADMIN_EMAIL etc. (see step 3 above) ✅ Configuration: Uses your .env settings automatically ✅ Database Compatibility: Works with SQLite, PostgreSQL, and MySQL

# IMPORTANT: Setup .env first (if not already done)
# Backup existing .env first, then copy new template
cp .env .env.bak
cp .env.example .env  # then edit with your admin credentials

# Run the migration (uses settings from your .env file)
python3 -m mcpgateway.bootstrap_db

# Or using make
make dev  # This runs bootstrap_db automatically

# Verify migration completed successfully
python3 scripts/verify_multitenancy_0_7_0_migration.py

Step 3: Verify Migration Results

After migration, verify the results using our verification script:

# Run comprehensive verification
python3 scripts/verify_multitenancy_0_7_0_migration.py

This will check:

  • ✅ Platform admin user creation
  • ✅ Personal team creation and membership
  • ✅ Resource team assignments
  • ✅ Visibility settings
  • ✅ Database integrity

Expected Output: All checks should pass. If any fail, see the troubleshooting section below.

Post-Migration Configuration

1. Verify Server Visibility

Old servers should now be visible in the Virtual Servers list. They will be:

  • Owned by: Your platform admin user
  • Assigned to: Admin's personal team
  • Visibility: Public (visible to all authenticated users)

2. Import Configuration (If Needed)

If you exported your configuration before migration and need to restore specific settings:

# Access the admin UI
open http://localhost:4444/admin

# Navigate to Export/Import section → Import Configuration
# Upload your backup JSON file from step 1

# Or use API:
curl -X POST "http://localhost:4444/admin/import/configuration" \
     -H "Authorization: Bearer YOUR_TOKEN" \
     -H "Content-Type: application/json" \
     -d @mcp_config_backup_YYYYMMDD_HHMMSS.json

# Or with basic auth:
curl -X POST "http://localhost:4444/admin/import/configuration" \
     -u admin:changeme \
     -H "Content-Type: application/json" \
     -d @mcp_config_backup_YYYYMMDD_HHMMSS.json

📋 Import Options:

  • Merge: Adds missing resources without overwriting existing ones
  • Replace: Overwrites existing resources with backup versions
  • Selective: Choose specific servers/tools/resources to import

2. Configure SSO (Optional)

If you want to enable SSO authentication:

# In .env file - Example for GitHub
SSO_ENABLED=true
SSO_PROVIDERS=["github"]

# GitHub configuration
GITHUB_CLIENT_ID=your-github-app-id
GITHUB_CLIENT_SECRET=your-github-app-secret

# Admin assignment (optional)
SSO_AUTO_ADMIN_DOMAINS=["yourcompany.com"]
SSO_GITHUB_ADMIN_ORGS=["your-org"]

3. Create Additional Teams

After migration, you can create organizational teams:

# Via API (with admin token)
curl -X POST http://localhost:4444/admin/teams \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Engineering Team",
    "description": "Development and engineering resources",
    "visibility": "private"
  }'

# Or use the Admin UI at http://localhost:4444/admin

Understanding the Migration

What Happened to My Old Data?

The consolidated migration automatically handles your existing resources in a single, seamless process:

  1. Schema Creation: Creates all multitenancy tables (users, teams, roles, token management, SSO, etc.)
  2. Column Addition: Adds team_id, owner_email, and visibility columns to existing resource tables
  3. Admin User Creation: Creates platform admin user (from PLATFORM_ADMIN_EMAIL)
  4. Personal Team Creation: Creates personal team for the admin user
  5. Data Population: Automatically assigns old resources to admin's personal team with "public" visibility

Database Tables Created

The migration creates 15 new tables for the multitenancy system:

Core Authentication:

  • email_users - User accounts and authentication
  • email_auth_events - Authentication event logging
  • email_api_tokens - API token management with scoping
  • token_usage_logs - Token usage tracking and analytics
  • token_revocations - Token revocation blacklist

Team Management:

  • email_teams - Team definitions and settings
  • email_team_members - Team membership and roles
  • email_team_invitations - Team invitation workflow
  • email_team_join_requests - Public team join requests
  • pending_user_approvals - SSO user approval workflow

RBAC System:

  • roles - Role definitions and permissions
  • user_roles - User role assignments
  • permission_audit_log - Permission access auditing

SSO Integration:

  • sso_providers - OAuth2/OIDC provider configuration
  • sso_auth_sessions - SSO authentication session tracking

This all happens in the consolidated migration cfc3d6aa0fb2, so no additional steps are needed.

Team Assignment Logic

Old Server (pre-migration):
├── team_id: NULL
├── owner_email: NULL
└── visibility: NULL

Migrated Server (post-migration):
├── team_id: "admin-personal-team-id"
├── owner_email: "your-admin@yourcompany.com"
└── visibility: "public"

Why "Public" Visibility?

Old servers are set to "public" visibility to ensure they remain accessible to all users immediately after migration. You can adjust visibility per resource:

  • Private: Only the owner can access
  • Team: All team members can access
  • Public: All authenticated users can access

Customizing Resource Ownership

Reassign Resources to Specific Teams

After migration, you may want to move resources to appropriate teams:

# Example: Move a server to a specific team
curl -X PUT http://localhost:4444/admin/servers/SERVER_ID \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "team_id": "target-team-id",
    "visibility": "team"
  }'

Change Resource Visibility

# Make a resource private (owner only)
curl -X PUT http://localhost:4444/admin/servers/SERVER_ID \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"visibility": "private"}'

# Make it visible to team members
curl -X PUT http://localhost:4444/admin/servers/SERVER_ID \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"visibility": "team"}'

Troubleshooting

Issue: Servers Not Visible After Migration

Problem: Old servers don't appear in the Virtual Servers list.

Solution: This should not happen with the current migration. If it does, check:

# Check if servers have team assignments
python3 -c "
from mcpgateway.db import SessionLocal, Server
with SessionLocal() as db:
    total_servers = db.query(Server).count()
    servers_without_team = db.query(Server).filter(Server.team_id == None).count()
    print(f'Total servers: {total_servers}')
    print(f'Servers without team: {servers_without_team}')
    if servers_without_team > 0:
        print('ISSUE: Some servers lack team assignment')
        print('Re-run the migration: python3 -m mcpgateway.bootstrap_db')
    else:
        print('✓ All servers have team assignments')
"

Root Cause: The consolidated migration should handle this automatically. If you still see issues:

  1. First, try the fix script (recommended):

    python3 scripts/fix_multitenancy_0_7_0_resources.py

  2. If that doesn't work, ensure PLATFORM_ADMIN_EMAIL is set and re-run migration:

    export PLATFORM_ADMIN_EMAIL="your-admin@company.com"
python3 -m mcpgateway.bootstrap_db

Issue: Migration Uses Wrong Admin Email

Problem: Migration created admin user with default email (admin@example.com) instead of your configured email.

Root Cause: .env file not properly configured before migration.

Solution:

  1. Check your .env configuration:

    # Verify your settings are loaded
python3 -c "
from mcpgateway.config import settings
print(f'Admin email: {settings.platform_admin_email}')
print(f'Email auth enabled: {settings.email_auth_enabled}')
"

  2. If settings are wrong, update .env and re-run:

    # Edit your .env file
nano .env  # Set PLATFORM_ADMIN_EMAIL=your-email@company.com

# Re-run migration
python3 -m mcpgateway.bootstrap_db

Issue: Admin User Not Created

Problem: Platform admin user was not created during migration.

Solution: Check configuration and re-run:

# First, verify .env configuration
python3 -c "
from mcpgateway.config import settings
print(f'Admin email: {settings.platform_admin_email}')
print(f'Email auth: {settings.email_auth_enabled}')
"

# If EMAIL_AUTH_ENABLED=false, the admin won't be created
# Set EMAIL_AUTH_ENABLED=true in .env and re-run:
python3 -m mcpgateway.bootstrap_db

# Or manually create using bootstrap function:
python3 -c "
import asyncio
from mcpgateway.bootstrap_db import bootstrap_admin_user
asyncio.run(bootstrap_admin_user())
"

Issue: Personal Team Not Created

Problem: Admin user exists but has no personal team.

Solution: Create personal team manually:

python3 -c "
import asyncio
from mcpgateway.db import SessionLocal, EmailUser
from mcpgateway.services.personal_team_service import PersonalTeamService

async def create_admin_team():
    with SessionLocal() as db:
        # Replace with your admin email
        admin_email = 'admin@example.com'
        admin = db.query(EmailUser).filter(EmailUser.email == admin_email).first()
        if admin:
            service = PersonalTeamService(db)
            team = await service.create_personal_team(admin)
            print(f'Created personal team: {team.name} (id: {team.id})')

asyncio.run(create_admin_team())
"

Issue: Migration Fails During Execution

Problem: Migration encounters errors during execution, often with SQLite I/O errors.

Solution: Use the comprehensive SQLite troubleshooting guide:

# 1. Run comprehensive SQLite diagnostics
./scripts/diagnose_sqlite.sh

# 2. Test basic database access
python3 scripts/test_db_access.py

# 3. Test with your exact configuration
python3 scripts/test_sqlalchemy.py

Common SQLite issues during migration:

  • "disk I/O error": File permissions or disk space issues
  • "database is locked": Another process using the database
  • macOS version conflicts: Old system SQLite vs newer requirements

Quick fixes:

# Kill any hanging processes
pkill -f mcpgateway

# Remove corrupted WAL files
rm -f mcp.db-wal mcp.db-shm mcp.db-journal

# macOS: Update SQLite
brew install sqlite3 && brew link --force sqlite3

# Check database connectivity
python3 -c "
from mcpgateway.db import engine
try:
    with engine.connect() as conn:
        result = conn.execute('SELECT 1')
        print('Database connection: OK')
except Exception as e:
    print(f'Database error: {e}')
"

# Run migration with verbose output
export LOG_LEVEL=DEBUG
python3 -m mcpgateway.bootstrap_db

📋 For complete SQLite troubleshooting: See scripts/troubleshoot-sqlite.md

Rollback Procedure

If you need to rollback the migration:

1. Restore Database Backup

# For SQLite
cp mcp.db.backup.YYYYMMDD_HHMMSS mcp.db

# For PostgreSQL
dropdb mcp
createdb mcp
psql -d mcp < mcp_backup_YYYYMMDD_HHMMSS.sql

# For MySQL
mysql -u mysql -p -e "DROP DATABASE mcp; CREATE DATABASE mcp;"
mysql -u mysql -p mcp < mcp_backup_YYYYMMDD_HHMMSS.sql

2. Revert Environment Configuration

# Restore previous environment from backup
cp .env.bak .env

# Or manually disable email auth if you want to go back to basic auth only
# Edit .env file and set:
# EMAIL_AUTH_ENABLED=false

3. Use Previous Codebase Version

# Check out the previous version
git checkout v0.6.0  # or your previous version tag

# Reinstall dependencies
make install-dev

Verification Checklist

After completing the migration, verify using the automated verification script:

# Run comprehensive verification
python3 scripts/verify_multitenancy_0_7_0_migration.py

Manual checks (if needed):

  • Database migration completed without errors
  • Platform admin user created successfully
  • Personal team created for admin user
  • Old servers are visible in Virtual Servers list
  • Admin UI accessible at /admin endpoint
  • Authentication works (email + password)
  • Basic auth still works (if AUTH_REQUIRED=true)
  • API endpoints respond correctly
  • Resource creation works and assigns to teams

If verification fails: Use the fix script:

python3 scripts/fix_multitenancy_0_7_0_resources.py

Getting Help

If you encounter issues during migration:

  1. Check the logs: Set LOG_LEVEL=DEBUG for verbose output
  2. Review troubleshooting section above for common issues
  3. File an issue: https://github.com/IBM/mcp-context-forge/issues
  4. Include information: Database type, error messages, relevant logs

Next Steps

After successful migration:

  1. Review team structure: Plan how to organize your teams
  2. Configure SSO: Set up integration with your identity provider
  3. Set up RBAC: Configure roles and permissions as needed
  4. Train users: Introduce team-based workflows
  5. Monitor usage: Use the new audit logs and metrics

The multi-tenant architecture provides much more flexibility and security for managing resources across teams and users. Take time to explore the new admin UI and team management features.

Quick Reference

Essential Commands

# 1. BACKUP (before migration)
cp mcp.db mcp.db.backup.$(date +%Y%m%d_%H%M%S)
cp .env .env.bak
curl -u admin:changeme "http://localhost:4444/admin/export/configuration" -o config_backup.json

# 2. SETUP .ENV (required)
cp .env.example .env  # then edit with your admin credentials

# 3. VERIFY CONFIG
python3 -c "from mcpgateway.config import settings; print(f'Admin: {settings.platform_admin_email}')"

# 4. MIGRATE
python3 -m mcpgateway.bootstrap_db

# 5. VERIFY SUCCESS
python3 scripts/verify_multitenancy_0_7_0_migration.py

# 6. FIX IF NEEDED
python3 scripts/fix_multitenancy_0_7_0_resources.py

Important URLs

SQLite Troubleshooting Guide

Common Issue: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) disk I/O error

This section helps diagnose and fix SQLite database issues, especially with MCP Gateway v0.7.0 multitenancy.

Quick Diagnosis Commands

1. Check if database is corrupted

sqlite3 mcp.db "PRAGMA integrity_check;"
# Expected: "ok"
# If corrupted: Shows specific errors

2. Check if database is locked

sqlite3 mcp.db "PRAGMA locking_mode;"
# Expected: "normal"
# Problem: "exclusive" or hangs

3. Check database file permissions and size

ls -la mcp.db
file mcp.db
# Expected: readable/writable by user, "SQLite 3.x database"
# Problem: no write permissions, 0 bytes, or "data" instead of SQLite

4. Try to open database manually

sqlite3 mcp.db ".tables"
# Expected: List of tables (gateways, tools, etc.)

# Test basic queries
sqlite3 mcp.db "SELECT COUNT(*) FROM gateways;"
sqlite3 mcp.db "SELECT * FROM email_users LIMIT 1;" # v0.7.0 only

5. Check for WAL/Journal files

ls -la mcp.db*
# Look for: mcp.db-wal, mcp.db-shm, mcp.db-journal
# These can cause corruption if process was killed

6. Check SQLite versions

# System SQLite version
/usr/bin/sqlite3 --version
sqlite3 --version

# Python SQLite version
python3 -c "import sqlite3; print(f'Python SQLite: {sqlite3.sqlite_version}')"
python3 -c "import sqlite3; print(f'Python module: {sqlite3.version}')"

System Resource Checks

7. Check disk space

df -h .
df -i .  # Check inodes too

8. Check file descriptor limits (ulimit)

ulimit -n    # File descriptors
ulimit -u    # Max processes
ulimit -a    # All limits

# Increase if needed (add to ~/.zshrc or ~/.bash_profile)
ulimit -n 4096
ulimit -u 2048

9. Check for process locks

# Check if any process has the DB open
lsof mcp.db
fuser mcp.db  # Linux only

# Kill hanging processes
pkill -f mcpgateway
pkill -f gunicorn
pkill -f python

Platform-Specific Checks

macOS Specific

# Check for quarantine attributes
xattr -l mcp.db
xattr -l .

# Remove quarantine if present
xattr -d com.apple.quarantine mcp.db 2>/dev/null || true

# Check if in sandboxed directory
pwd  # Avoid ~/Desktop, ~/Documents

# Check SQLite version conflict (common on macOS)
/usr/bin/sqlite3 --version     # System SQLite (usually old)
/opt/homebrew/bin/sqlite3 --version 2>/dev/null || echo "Homebrew SQLite not installed"

# Update SQLite via Homebrew (recommended)
brew install sqlite3
brew link --force sqlite3

# Verify updated version
which sqlite3
sqlite3 --version

# Use Homebrew Python with updated SQLite
brew install python3
/opt/homebrew/bin/python3 -c "import sqlite3; print(f'Python SQLite: {sqlite3.sqlite_version}')"
Filesystem location pitfalls (macOS)
  • Avoid storing mcp.db under synced or special folders (iCloud Drive, Dropbox, OneDrive, Google Drive), external exFAT drives, or network shares. These can break SQLite's journaling/locking and trigger disk I/O error.
  • Prefer a local APFS path and an absolute DATABASE_URL:
mkdir -p "$HOME/Library/Application Support/mcpgateway"
# Note the four slashes for absolute path and the space in the folder name
export DATABASE_URL="sqlite:////Users/$USER/Library/Application Support/mcpgateway/mcp.db"

Alternatively, keep the repository and database in a simple home directory folder (avoids iCloud-managed Documents/Desktop):

mkdir -p "$HOME/mcp-context-forge/data"
export DATABASE_URL="sqlite:////Users/$USER/mcp-context-forge/data/mcp.db"

Common Fixes

Fix 1: Remove corrupted WAL files

# Stop all processes first
pkill -f mcpgateway

# Remove WAL/journal files
rm -f mcp.db-wal mcp.db-shm mcp.db-journal

# Force WAL checkpoint if DB is accessible
sqlite3 mcp.db "PRAGMA wal_checkpoint(FULL);" || echo "DB not accessible"

Fix 2: Repair corrupted database

# Backup first
cp mcp.db mcp.db.corrupted

# Try to dump and restore
sqlite3 mcp.db.corrupted ".dump" | sqlite3 mcp.db.recovered
mv mcp.db.recovered mcp.db

# Or recreate from scratch (LOSES DATA)
rm mcp.db
python3 -m mcpgateway.bootstrap_db

Fix 3: Fix permissions

# Fix file permissions
chmod 664 mcp.db
chmod 755 $(dirname mcp.db)
chown $USER:$USER mcp.db

# For macOS - remove extended attributes
xattr -c mcp.db 2>/dev/null || true

Fix 4: Update SQLite on macOS

# macOS often ships with old SQLite - update via Homebrew
brew install sqlite3
brew link --force sqlite3

# Update Python to use newer SQLite
brew install python3

# Recreate virtual environment with updated Python/SQLite
deactivate  # if in venv
rm -rf ~/.venv/mcpgateway
/opt/homebrew/bin/python3 -m venv ~/.venv/mcpgateway
source ~/.venv/mcpgateway/bin/activate
pip install -e ".[dev]"

# Verify versions
python3 -c "import sqlite3; print(f'SQLite: {sqlite3.sqlite_version}')"
sqlite3 --version

Fix 5: Reduce startup concurrency (single worker)

SQLite is file-based. Running migrations and first-queries concurrently across multiple Gunicorn workers can stress macOS filesystems:

# Run production server with a single worker while validating the environment
GUNICORN_WORKERS=1 make serve

# Or run the dev server (single-process)
make dev

If this eliminates errors, the underlying issue is filesystem/locking. Keep the DB on a safe path and gradually raise workers.

Environment Configuration for v0.7.0

Enhanced SQLite settings for multitenancy

# In .env file - v0.7.0 needs WAL mode for better concurrency
DATABASE_URL="sqlite:///./mcp.db?check_same_thread=false&journal_mode=WAL&synchronous=NORMAL&cache_size=10000&timeout=60"

# Connection pool settings
DB_POOL_SIZE=10
DB_MAX_OVERFLOW=5
DB_POOL_TIMEOUT=60
DB_POOL_RECYCLE=3600
DB_MAX_RETRIES=5
DB_RETRY_INTERVAL_MS=2000

macOS Specific .env Settings

# Increase timeouts for macOS filesystem
DATABASE_URL="sqlite:///./mcp.db?timeout=60&journal_mode=WAL"
DB_POOL_TIMEOUT=60
DB_MAX_RETRIES=10
DB_RETRY_INTERVAL_MS=5000
# Optionally reduce pool pressure during troubleshooting
DB_POOL_SIZE=10
DB_MAX_OVERFLOW=0

Optional: In-process mode (bypass file-lock leadership)

To simplify troubleshooting and remove file-lock leadership competition on macOS, temporarily use in-process mode for cache/session management:

export CACHE_TYPE=none

Test Database Access

Run the comprehensive test script:

python3 scripts/test_sqlite.py --verbose
# If using a custom path, pass it explicitly
python3 scripts/test_sqlite.py --database-url "sqlite:////Users/$USER/Library/Application Support/mcpgateway/mcp.db" --verbose

This script tests:

  • Direct SQLite access
  • SQLAlchemy engine with MCP Gateway settings
  • System diagnostics and recommendations

Quick Reference Table

Issue Quick Fix
"disk I/O error" Check permissions/disk space: ls -la mcp.db && df -h .
"database is locked" pkill -f mcpgateway && rm -f mcp.db-wal mcp.db-shm
macOS version conflicts brew install sqlite3 && brew link --force sqlite3
WAL file corruption rm -f mcp.db-wal mcp.db-shm && sqlite3 mcp.db "PRAGMA wal_checkpoint(FULL);"
Low ulimits ulimit -n 8192 && ulimit -u 128000
v0.7.0 multitenancy issues Add WAL mode: DATABASE_URL="sqlite:///./mcp.db?journal_mode=WAL"