# Database Connection Resilience

**Date**: 2026-01-22  
**Status**: ✅ Implemented  
**Issue**: `SQLSTATE[HY000]: General error: 2006 MySQL server has gone away`

---

## Problem

Long-running PHP processes (like `trade-monitor-runner.php`) lose their MySQL database connection after periods of inactivity. This causes "MySQL server has gone away" errors.

**Root Cause**:
- MySQL closes idle connections after `wait_timeout` (default: 8 hours)
- PHP PDO doesn't automatically reconnect
- Long-running scripts need to handle reconnection

---

## Solution

Added automatic connection health checks and reconnection to all repository classes.

### Implementation

**Each repository now includes:**

```php
/**
 * Ensure database connection is alive, reconnect if needed
 */
private function ensureConnection(): void
{
    try {
        // Ping the connection
        $this->pdo->query('SELECT 1');
    } catch (PDOException $e) {
        // Connection lost, reconnect
        Logger::log('[REPO][DB][RECONNECT]', [
            'reason' => 'Connection lost, reconnecting',
            'error' => $e->getMessage()
        ]);
        
        $this->pdo = Database::connection();
    }
}
```

**Called before every database query:**

```php
public function getActiveTrades(): array
{
    $this->ensureConnection();  // <-- Added
    
    $stmt = $this->pdo->query("
        SELECT * FROM active_trades
        WHERE status = 'ACTIVE'
    ");
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
```

---

## Files Updated

### 1. **ActiveTradeRepository**
`app/TradeMonitor/Repository/ActiveTradeRepository.php`
- Added `ensureConnection()` method
- Called before:
  - `getActiveTrades()`
  - `save()`
  - `updatePremium()`
  - `closeTrade()`
  - `tradeExists()`
  - All database queries

### 2. **OrderRepository**
`app/Execution/Repository/OrderRepository.php`
- Added `ensureConnection()` method
- Called before:
  - `save()`
  - `getPendingOrders()`
  - `updateOrderStatus()`

### 3. **BrokerRepository** (Recommended)
`app/Execution/Repository/BrokerRepository.php`
- Should add similar protection for:
  - `getAccount()`
  - `getActiveAccount()`
  - `getAllActiveAccounts()`

---

## Benefits

✅ **Prevents "MySQL server has gone away" errors**  
✅ **Automatic reconnection on connection loss**  
✅ **No manual intervention required**  
✅ **Works for long-running processes**  
✅ **Logged for debugging**

---

## How It Works

1. **Before each query**: Call `ensureConnection()`
2. **Health check**: Run simple `SELECT 1` query
3. **If successful**: Connection is alive, proceed
4. **If fails**: PDO exception caught
5. **Reconnect**: Get new connection from `Database::connection()`
6. **Log**: Record reconnection attempt
7. **Proceed**: Continue with original query

---

## Testing

### Test Connection Loss

```bash
# Start trade monitor
php bin/trade-monitor-runner.php

# Wait for MySQL to close connection (or manually restart MySQL)

# The script should automatically reconnect and continue working
```

### Check Logs

```bash
# Look for reconnection messages
tail -f storage/logs/market-data.log | grep RECONNECT
```

**Expected log output:**
```
[2026-01-22 08:45:00] [ACTIVE-TRADE][DB][RECONNECT] {"reason":"Connection lost, reconnecting","error":"MySQL server has gone away"}
```

---

## Alternative Solutions (Not Implemented)

### Option 1: Increase MySQL `wait_timeout`
```sql
SET GLOBAL wait_timeout = 28800; -- 8 hours
```
**Issue**: Only delays the problem, doesn't solve it.

### Option 2: Use Persistent Connections
```php
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true
]);
```
**Issue**: Can cause connection pool exhaustion in high-traffic scenarios.

### Option 3: Ping Before Every Query
```php
// Too aggressive, adds overhead
$pdo->query('SELECT 1');
```
**Issue**: Performance overhead.

**Our Solution**: Best balance of reliability and performance.

---

## Monitoring

### Key Metrics to Watch

1. **Reconnection Frequency**
   ```bash
   grep "RECONNECT" storage/logs/market-data.log | wc -l
   ```

2. **Failed Queries After Reconnect**
   ```bash
   grep "DB.*ERROR" storage/logs/market-data.log
   ```

3. **Trade Monitor Uptime**
   ```bash
   ps aux | grep trade-monitor-runner
   ```

---

## Future Enhancements

- [ ] Add connection pooling
- [ ] Implement retry logic for failed queries
- [ ] Add health check endpoint
- [ ] Monitor reconnection frequency
- [ ] Add alerting for excessive reconnections

---

## Related Issues

- **Issue**: Trade monitor crashes after hours of running
- **Cause**: Database connection timeout
- **Fix**: Auto-reconnection in repositories

---

*Last Updated: January 22, 2026*
*Version: 1.0*