How to solve General error: 2006 MySQL server has gone away
When you attempt to insert hundreds of records into a MySQL database and hit the SQLSTATE[HY000]: General error: 2006 MySQL server has gone away exception, it is almost certainly not a logic error in your application code. The fact that this occurs precisely after inserting 176 records suggests a deterministic limit was reached, rather than a random timeout caused by network instability. This specific behavior points directly to the net_read_timeout or wait_timeout settings on the MySQL server side, which dictate how long the server will wait for an activity on a client connection before closing it.
In many default configurations, especially in older versions of MariaDB (which shares much DNA with MySQL), the default value for net_read_timeout is often set to 30 seconds. If your PHP script is executing a massive single queryâsuch as inserting hundreds of rows into a single INSERT INTO statement without batchingâthe execution time likely exceeds this window. The server stops listening for input from the client after the timeout period, effectively killing the connection mid-operation. When your PHP application tries to read the result of that now-dead connection or sends the next packet, MySQL throws error 2006 because the session no longer exists.
The immediate fix is often found in the server configuration file, my.cnf or my.ini. You can increase the timeout values to accommodate long-running transactions. Look for the following parameters and adjust them:
[mysqld]
net_read_timeout=300
net_write_timeout=300
wait_timeout=28800
interactive_timeout=28800
Setting net_read_timeout and net_write_timeout to 300 seconds (5 minutes) is a common starting point. However, simply increasing these timeouts is not always the best architectural solution. If your application relies on keeping a single persistent connection open for an extended period while processing large datasets, you risk exhausting server resources if that specific connection hangs indefinitely. Furthermore, relying solely on increased timeouts does not address the underlying inefficiency of sending a massive block of data in one go.
A more robust approach involves batching your insert operations. Instead of constructing one giant SQL statement with hundreds of values, break the operation into smaller chunks. In PHP using PDO, this looks like looping through your data array and committing transactions periodically:
$batchSize = 100;
$chunks = array_chunk($data, $batchSize);
foreach ($chunks as $chunk) {
$sql = "INSERT INTO users (name, email, created_at) VALUES ";
// Build the value placeholders dynamically based on the chunk size
$placeholders = implode(', ', array_fill(0, count($chunk), '(?, ?, ?)'));
$stmt = $pdo->prepare("INSERT INTO users (name, email, created_at) VALUES {$placeholders}");
foreach ($chunk as $row) {
$stmt->execute([$row['name'], $row['email'], $row['created_at']]);
}
// Commit the transaction for this batch
$pdo->commit();
}
By processing data in batches of 100 or fewer, you ensure that no single query execution lasts long enough to trigger a timeout. This approach also keeps each connection's load lighter and allows the application to handle errors in smaller increments rather than failing after hundreds of rows are already queued but not yet written.
There is a specific scenario where increasing timeouts might seem like a quick win but ultimately fails: if your PHP process runs as a single thread managing one persistent database connection. If that single thread takes too long, it blocks the rest of the application. In high-load environments or frameworks like Laravel, it is often better to rely on short-lived connections per request or ensure that the web server (like Apache with mod_php or Nginx with PHP-FPM) restarts workers frequently enough to prevent connection leaks.
If you are working within a larger ecosystem and need reliable database connectivity patterns without managing low-level configuration constantly, resources like https://laravelcompany.com often provide documentation on best practices for handling database connections in modern PHP stacks. The core principle remains the same: respect the limits of the network layer and the server's memory management by breaking large operations into manageable chunks.
