How to Fix: MySQL server has gone away error in PHP

The ‘MySQL server has gone away’ error occurs when the MySQL connection is lost during a query or operation. This error indicates the connection timed out or was closed.

---

Common Causes and Fixes

1. Query Timeout

<?php
// ❌ Error: Long-running query
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$stmt = $pdo->prepare("SELECT * FROM huge_table WHERE complex_condition");  // May timeout
$stmt->execute();
?>

<?php
// ✅ Fixed: Optimize query or increase timeout
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$pdo->exec("SET SESSION wait_timeout=600");  // 10 minutes
$stmt = $pdo->prepare("SELECT * FROM huge_table WHERE complex_condition LIMIT 1000");
$stmt->execute();
?>

2. Packet Size Too Large

<?php
// ❌ Error: Large data packet
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$largeData = str_repeat('x', 50000000);  // Very large string
$stmt = $pdo->prepare("INSERT INTO table (data) VALUES (?)");
$stmt->execute([$largeData]);  // Error!
?>

<?php
// ✅ Fixed: Split data or increase max_allowed_packet
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$stmt = $pdo->prepare("INSERT INTO table (data) VALUES (?)");
$stmt->execute([substr($largeData, 0, 1000000)]);  // Smaller chunks
?>

3. Idle Connection Timeout

<?php
// ❌ Error: Connection idle too long
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
sleep(600);  // Wait 10 minutes
$stmt = $pdo->prepare("SELECT * FROM users");  // Error!
$stmt->execute();
?>

<?php
// ✅ Fixed: Reconnect or ping
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
// Check connection before use
try {
    $pdo->query('SELECT 1')->fetch();
} catch (PDOException $e) {
    if ($e->getCode() == 'HY000') {
        // Reconnect
        $pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
    }
}
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();
?>

4. Large Result Set

<?php
// ❌ Error: Fetching large result set
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$stmt = $pdo->prepare("SELECT * FROM huge_table");
$stmt->execute();
$results = $stmt->fetchAll();  // May cause timeout
?>

<?php
// ✅ Fixed: Process in chunks
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$offset = 0;
$limit = 1000;
do {
    $stmt = $pdo->prepare("SELECT * FROM huge_table LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $results = $stmt->fetchAll();
    
    // Process results
    $offset += $limit;
} while (count($results) == $limit);
?>

5. Connection Limits

<?php
// ❌ Error: Too many connections
for ($i = 0; $i < 1000; $i++) {
    $pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
    // Not closing connections
}
?>

<?php
// ✅ Fixed: Close connections properly
for ($i = 0; $i < 1000; $i++) {
    $pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
    // Do work
    $pdo = null;  // Close connection
}
?>

6. Using MySQLi

<?php
// ❌ Error: MySQLi connection timeout
$mysqli = new mysqli("localhost", "user", "pass", "db");
sleep(600);
$result = $mysqli->query("SELECT * FROM users");  // Error!
?>

<?php
// ✅ Fixed: Check connection before query
$mysqli = new mysqli("localhost", "user", "pass", "db");
if ($mysqli->ping() === false) {
    $mysqli = new mysqli("localhost", "user", "pass", "db");
}
$result = $mysqli->query("SELECT * FROM users");
?>

7. Persistent Connections

<?php
// ❌ Error: Persistent connection issues
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass", [
    PDO::ATTR_PERSISTENT => true
]);
// Connection may be stale
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();
?>

<?php
// ✅ Fixed: Use non-persistent or validate
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");  // No persistent
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();
?>

8. Large INSERT/UPDATE

<?php
// ❌ Error: Large bulk operation
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$stmt = $pdo->prepare("INSERT INTO table (col1, col2) VALUES (?, ?)");
for ($i = 0; $i < 100000; $i++) {
    $stmt->execute(["value$i", "data$i"]);  // May timeout
}
?>

<?php
// ✅ Fixed: Batch operations
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO table (col1, col2) VALUES (?, ?)");
for ($i = 0; $i < 100000; $i++) {
    $stmt->execute(["value$i", "data$i"]);
    
    if ($i % 1000 === 0) {
        $pdo->commit();
        $pdo->beginTransaction();
    }
}
$pdo->commit();
?>

9. Network Issues

<?php
// ❌ Error: Network interruption
$pdo = new PDO("mysql:host=remote-server;dbname=db", "user", "pass");
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();  // May fail due to network
?>

<?php
// ✅ Fixed: Add retry logic
function executeWithRetry($pdo, $sql, $params = [], $maxRetries = 3) {
    for ($i = 0; $i < $maxRetries; $i++) {
        try {
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), 'server has gone away') !== false && $i < $maxRetries - 1) {
                sleep(1);  // Wait before retry
                $pdo = new PDO("mysql:host=remote-server;dbname=db", "user", "pass");
                continue;
            }
            throw $e;
        }
    }
}
?>

10. MySQL Configuration

<?php
// ❌ Error: Default MySQL settings too restrictive
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
// Default timeouts may be too short
?>

<?php
// ✅ Fixed: Adjust MySQL settings via connection
$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$pdo->exec("SET SESSION wait_timeout=600");
$pdo->exec("SET SESSION interactive_timeout=600");
$pdo->exec("SET SESSION max_allowed_packet=64*1024*1024");  // 64MB
?>

---

Quick Debugging Steps

1. Check MySQL error log for details
2. Verify server is running and responsive
3. Test connection with MySQL client
4. Check query complexity and size
5. Review MySQL configuration settings
6. Monitor connection count and limits

---

Prevention Tips

• Optimize queries to run faster
• Use connection pooling carefully
• Monitor and adjust MySQL timeout settings
• Process large datasets in chunks
• Implement retry logic for critical operations
• Close connections when done
• Use appropriate packet size limits
• Check network stability for remote servers

Remember: This error often relates to timeouts, packet sizes, or connection limits. Optimize queries and adjust MySQL settings accordingly.