Add MySQL database support to MCP Gateway

[crivetimihai]

Summary

Add comprehensive MySQL database support to MCP Gateway, making it a fully supported database backend alongside SQLite and PostgreSQL.

Background

MCP Gateway currently supports SQLite (default) and PostgreSQL databases. Adding MySQL support would:

• Provide users with more database backend options
• Support enterprise environments that standardize on MySQL/MariaDB
• Enable better compatibility with existing MySQL infrastructure
• Offer production-grade database options for high-scale deployments

Implementation Requirements

1. Database Schema Compatibility

• Fix all VARCHAR columns to include explicit length specifications (MySQL requirement)
• Update SQLAlchemy model definitions in mcpgateway/db.py
• Ensure URL column lengths are within MySQL unique index limits (≤767 characters)
• Convert large text fields to TEXT type instead of VARCHAR(65535)

2. Alembic Migration Compatibility

• Update all migration files to use sa.String(length) instead of sa.String()
• Remove JSON column default values (MySQL restriction: BLOB/TEXT/JSON can't have defaults)
• Fix association table foreign key column specifications
• Test migration path from fresh database

3. Configuration & Environment

• Update .env.example with MySQL connection string examples
• Add MySQL-specific database configuration guidance
• Document connection string format: mysql+pymysql://user:pass@host:port/database

4. Docker Integration

• Enable MySQL service in docker-compose.yml
• Configure proper port mapping and health checks
• Add MySQL container with persistent volume storage
• Update gateway service dependencies to use MySQL

5. Documentation Updates

• Add MySQL setup instructions to deployment guides
• Update database configuration documentation
• Include MySQL examples in quick start guide
• Document MySQL-specific considerations and requirements

6. Testing & Validation

• Verify all 36+ database tables create successfully with MySQL 8.4+
• Test application bootstrap and startup with MySQL backend
• Validate all CRUD operations work correctly
• Ensure foreign key constraints and relationships function properly
• Confirm no VARCHAR length errors during operation

Technical Details

Database Support Matrix

Database	Support Level	Production Ready	Notes
SQLite	✅ Full	✅ Small/Medium	Default, good for development
PostgreSQL	✅ Full	✅ Enterprise	Recommended for large deployments
MySQL 8.0+	✅ Full	✅ Enterprise	New - fully compatible

MySQL-Specific Fixes Applied

1. VARCHAR Length Requirements: All String columns now have explicit lengths
2. Index Key Limits: URL columns limited to 767 chars for unique constraints
3. JSON Column Defaults: Removed server defaults from JSON columns
4. Foreign Key Types: All FK columns properly typed with String(36) for UUIDs
5. Migration Compatibility: All Alembic migrations MySQL-ready

Connection String Examples

# Local MySQL server
DATABASE_URL=mysql+pymysql://mysql:changeme@localhost:3306/mcp

# Docker container
DATABASE_URL=mysql+pymysql://mysql:changeme@mysql:3306/mcp

# Remote MySQL with SSL
DATABASE_URL=mysql+pymysql://user:pass@mysql.example.com:3306/mcp?ssl=true

Testing Performed

• ✅ Fresh database creation and schema initialization
• ✅ All 36 database tables created without errors
• ✅ Application startup and service initialization
• ✅ Admin UI and API functionality
• ✅ Authentication and RBAC systems
• ✅ Database connection pooling and timeout handling
• ✅ Migration path from empty database to latest schema

Files Modified

Core Database Layer

• mcpgateway/db.py - Fixed 60+ VARCHAR column length specifications
• mcpgateway/alembic/versions/*.py - Fixed 5 migration files

Configuration & Documentation

• .env.example - Added MySQL configuration examples
• docs/docs/manage/configuration.md - New comprehensive config guide
• docs/docs/overview/quick_start.md - Added MySQL setup tabs
• docs/docs/deployment/compose.md - Enhanced MySQL documentation
• docs/docs/deployment/local.md - Added MySQL installation guide
• docs/docs/deployment/kubernetes.md - Added MySQL K8s examples

Container Deployment

• docker-compose.yml - Enabled MySQL service with health checks

Validation

The implementation has been thoroughly tested:

# Successful MySQL integration test results:
✅ MySQL Version: 8.4.6
✅ Tables Created: 36
✅ VARCHAR Length Issues: 0 (should be 0)
✅ All key tables verified with proper column counts
✅ Application starts successfully: http://0.0.0.0:4444

Benefits

1. Enterprise Compatibility: Support for MySQL/MariaDB enterprise environments
2. Infrastructure Flexibility: Users can choose their preferred database backend
3. Production Scalability: MySQL clustering and replication support
4. Operational Consistency: Align with existing MySQL-based infrastructure
5. Performance Options: MySQL-specific optimizations and tuning capabilities

Breaking Changes

None - this is purely additive functionality. Existing SQLite and PostgreSQL deployments continue to work unchanged.

Documentation

Complete documentation has been added covering:

• Database selection and configuration
• Docker and Kubernetes deployment with MySQL
• Connection string formats and examples
• Migration and upgrade procedures
• Troubleshooting MySQL-specific issues

🎉 MySQL support is production-ready and fully tested!