# 🔌 Database Connection Management Guide

## Overview
This document explains how the trading system manages MySQL connections to prevent "Too many connections" errors, especially on shared hosting environments with strict connection limits.

---

## 📊 Connection Architecture

### Singleton Pattern
Each PHP process maintains **exactly ONE** database connection using the singleton pattern:
```php
// app/Support/Database.php
private static $connection = null;

public static function connection() {
    if (self::$connection !== null) {
        return self::$connection;
    }
    return self::connect();
}
```

### Maximum Active Connections

| Process Name | Connection Type | Duration | Count |
|-------------|----------------|----------|-------|
| **Market Data Runner** | Persistent | Continuous | 1 |
| **Telegram Bot Runner** | Persistent | Continuous | 1 |
| **Trade Monitor Runner** | Periodic | 10s intervals | 1 (intermittent) |
| **Order Status Poller** | Periodic | 30s intervals | 1 (intermittent) |
| **Cron Entrypoint** | Brief | 1-2s per minute | 1 (very brief) |
| **API/Webhook Requests** | On-demand | Per request | 1-3 (concurrent) |

**Typical Active Connections**: 2-3  
**Peak Active Connections**: 5-6 (when all periodic tasks fire simultaneously)

---

## 🛡️ Connection Safety Features

### 1. Automatic Close After Tasks
Periodic processes explicitly close connections when idle:

```php
// Example from order-status-poller.php
while (true) {
    $orderStatusService->pollPendingOrders();
    
    // Close DB connection to prevent max_user_connections
    Database::close();
    
    sleep($intervalSeconds);
}
```

### 2. Retry Logic for "Too Many Connections"
The Database class automatically retries with exponential backoff:

```php
// app/Support/Database.php (lines 145-157)
if (
    strpos($errorMsg, 'Too many connections') !== false ||
    strpos($errorMsg, 'max_user_connections') !== false ||
    $e->getCode() == 1040 || 
    $e->getCode() == 1203 ||
    $e->getCode() == 1226
) {
    $backoff = rand(500000, 1500000); // 0.5s to 1.5s
    Logger::log("[DB][WARNING] Max connections reached. Retrying ($attempt/$maxRetries)...");
    usleep($backoff);
    continue;
}
```

### 3. Connection Ping & Auto-Reconnect
Stale connections are automatically detected and reconnected:

```php
if (!self::$connection->ping()) {
    Logger::log('[DB][WARNING] Connection lost (ping failed), attempting reconnect');
    self::$connection = null;
    self::$reconnectCount++;
    return self::connect();
}
```

### 4. Graceful Shutdown
All processes register shutdown handlers to clean up:

```php
register_shutdown_function(function () use ($lockFile) {
    Database::close();
    @unlink($lockFile);
});
```

---

## 📈 Connection Monitoring

### Built-in Statistics Tracking
The Database class tracks connection metrics:

```php
private static int $connectionCount = 0;   // Total connections opened
private static int $reconnectCount = 0;    // Reconnections due to failures
private static int $closeCount = 0;        // Explicit closes
```

### Hourly Statistics Logging
Every hour, the system logs connection statistics:

```
[DB][STATS] Hourly summary
{
    "hour": "2026-01-27 16:00",
    "connections": 42,
    "reconnects": 0,
    "closes": 38,
    "net_open": 4
}
```

### How to Monitor Connections

#### 1. Check Application Logs
```bash
# Search for DB connection events
grep -i "\[DB\]" storage/logs/2026-01-27/market-data.log

# Check for connection errors
grep -i "too many connections" storage/logs/2026-01-27/*.log

# View hourly statistics
grep -i "\[DB\]\[STATS\]" storage/logs/2026-01-27/*.log
```

#### 2. Check MySQL Server
```sql
-- Show current connections
SHOW PROCESSLIST;

-- Show connection limits
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'max_user_connections';

-- Show current connection count
SHOW STATUS LIKE 'Threads_connected';
```

#### 3. Monitor Active Processes
```bash
# Windows
tasklist | findstr php

# Linux
ps aux | grep php
```

---

## ⚙️ Configuration

### Environment Variables
```env
# Database Configuration
DB_HOST=localhost
DB_NAME=crypto_algo
DB_USER=root
DB_PASS=

# Connection Timeout (seconds)
# Set in Database.php: MYSQLI_OPT_CONNECT_TIMEOUT = 10
```

### MySQL Server Limits
Recommended settings for shared hosting:

```sql
-- Increase connection limit (if you have access)
SET GLOBAL max_connections = 100;
SET GLOBAL max_user_connections = 20;

-- Increase timeout for long-running queries
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;
```

---

## 🚨 Troubleshooting

### Error: "Too many connections"

**Cause**: MySQL server has reached its connection limit.

**Solutions**:
1. **Check running processes**: Ensure no zombie processes are holding connections
   ```bash
   php bin/stop-all-services.php
   php bin/start-all-services.php
   ```

2. **Verify Database::close() is called**: Check logs for connection close events
   ```bash
   grep "Closing active connection" storage/logs/2026-01-27/*.log
   ```

3. **Increase MySQL limits** (if you have server access):
   ```sql
   SET GLOBAL max_user_connections = 50;
   ```

4. **Use connection pooling** (advanced): Consider implementing a connection pool for high-traffic scenarios

### Error: "Connection lost during query"

**Cause**: Long-running query exceeded `wait_timeout`.

**Solutions**:
1. The system automatically reconnects via `ping()` check
2. Increase MySQL `wait_timeout` if needed
3. Check logs for reconnection events:
   ```bash
   grep "Connection lost" storage/logs/2026-01-27/*.log
   ```

### Stale Lock Files

**Cause**: Process crashed without cleaning up lock file.

**Solution**:
```bash
# Remove stale locks
rm storage/runtime/*.lock

# Restart services
php bin/start-all-services.php
```

---

## 🎯 Best Practices

### For New Features
1. **Always close connections in loops**:
   ```php
   while (true) {
       // Do work
       Database::close();
       sleep($interval);
   }
   ```

2. **Use try-finally for guaranteed cleanup**:
   ```php
   try {
       $db = Database::connection();
       // Do work
   } finally {
       Database::close();
   }
   ```

3. **Avoid holding connections during sleep**:
   ```php
   // ❌ BAD
   $db = Database::connection();
   sleep(60);
   $db->query("SELECT ...");
   
   // ✅ GOOD
   $db = Database::connection();
   $db->query("SELECT ...");
   Database::close();
   sleep(60);
   ```

### For Production Deployment
1. Monitor connection statistics hourly
2. Set up alerts for "Too many connections" errors
3. Use the TEST version of Database.php to verify connection behavior (logs every 5 minutes)
4. Ensure all cron jobs use `Database::close()` before sleeping

---

## 📝 Testing Connection Behavior

### Enable Test Mode (5-Minute Logging)
```bash
# Backup production version
cp app/Support/Database.php app/Support/Database.php.backup

# Use test version
cp app/Support/Database_TEST.php app/Support/Database.php

# Monitor logs
tail -f storage/logs/2026-01-27/market-data.log | grep "DB][STATS"

# After testing, restore production version
cp app/Support/Database.php.backup app/Support/Database.php
```

### Expected Output (Test Mode)
```
[DB][STATS] 5-Minute summary (TEST MODE)
{
    "time": "2026-01-27 16:05",
    "connections": 12,
    "reconnects": 0,
    "closes": 10,
    "net_open": 2,
    "note": "Test mode - logs every 5 minutes"
}
```

---

## 🔍 Advanced Monitoring

### Create a Connection Monitor Script
```php
<?php
// bin/check-db-connections.php
require __DIR__ . '/../vendor/autoload.php';

use App\Support\Database;

$db = Database::connection();
$result = $db->query("SHOW PROCESSLIST");

echo "Active MySQL Connections:\n";
echo str_repeat("-", 80) . "\n";

while ($row = $result->fetch_assoc()) {
    if ($row['User'] === getenv('DB_USER')) {
        echo sprintf(
            "ID: %s | DB: %s | Command: %s | Time: %ss | State: %s\n",
            $row['Id'],
            $row['db'],
            $row['Command'],
            $row['Time'],
            $row['State']
        );
    }
}

Database::close();
```

Run it:
```bash
php bin/check-db-connections.php
```

---

## 📚 Related Documentation
- `docs/SYSTEM_GUIDE.md` - Overall system architecture
- `README.md` - Quick start guide
- `app/Support/Database.php` - Connection implementation

---

**Last Updated**: 2026-01-27  
**Maintained By**: System Architecture Team