Raju Ginni

wordpress tutorials seo hosting etc

#2006 – mysql server has gone away xampp localhost windows

By

mysql server has gone away while import

The “MySQL server has gone away” error (error code 2006) during an import often indicates that the connection between the MySQL client and the server was unexpectedly closed, typically due to a timeout or a large packet size.

wait_timeout   default 1 hour so no issue  but with php timeout when using phpmyadmin

Problem: uploading 22MB CSV file

 

Config settings

QuiCk fix  increse max_allowed_packet

[mysqld]

#max_allowed_packet=1M to increase it
max_allowed_packet = 100M

after

Import has been successfully finished, 2 queries executed.

 

explaination:

    • max_allowed_packet defines the maximum size of a network packet that MySQL can send or receive.
  • This includes queries, results, and data being replicated. 

1st fix:

Increase Timeout Settings:

wait_timeout and interactive_timeout: These server variables control how long a connection can remain idle before being closed. Increase these values in your my.cnf or my.ini file, especially if you have long-running queries or need more time for user interaction.

Xampp >>> Mysql>Config>> mysql.ini
net_read_timeout and net_write_timeout: These variables control the read and write timeouts for network communication. Check and adjust these if you’re experiencing network-related issues.
Increase Max Packet Size:
max_allowed_packet: Increase this variable in your configuration file to handle larger data packets, especially if you’re dealing with BLOBs or other large data types.
Restart MySQL: After modifying max_allowed_packet, you need to restart the MySQL server for the changes to take effect.

 

Resolving the “MySQL Server Has Gone Away” (Error 2006) During Import

One of the most common yet frustrating errors database administrators and developers encounter when importing large SQL files is the “MySQL server has gone away (Error 2006).” This error message is a generic indicator that the connection to the MySQL server was lost. The interruption can happen for several reasons, but it most frequently occurs during lengthy operations like importing a large database. Understanding the root causes is the first step toward implementing a permanent fix.

The primary reasons for this error during an import process are typically server timeouts or packet size limitations. Essentially, the server either closes the connection because it has been idle for too long, or the data packet being sent (e.g., a very large INSERT statement) exceeds the server’s configured limit.

Common Causes for the “MySQL Server Has Gone Away” Error

  1. Server Timeout: The server’s wait_timeout variable determines how long it will wait for activity on a non-interactive connection before closing it. If your import process has a long-running query and no data is transmitted during the timeout period, the server will drop the connection.
  2. Packet Size Exceeded: The max_allowed_packet variable in MySQL defines the maximum size of a single packet that can be sent to or from the server. If your .sql dump file contains very large INSERT statements with extensive BLOB or TEXT data, the packet size can easily exceed the default limit, causing the server to reject it and close the connection.
  3. Lost Connection: The connection to the MySQL server could have been physically lost due to network issues.
  4. Incorrect Dump File: The import might fail if the dump file was created with the --extended-insert option, which groups multiple rows into single, large INSERT statements.

How to Fix the Error: Step-by-Step Solutions

To resolve this error, you will likely need to adjust some configuration variables in your MySQL server’s configuration file, which is typically named my.cnf on Linux/macOS or my.ini on Windows.

Solution 1: Increase max_allowed_packet

This is the most frequent solution. You need to increase the value of max_allowed_packet to accommodate the large data packets from your SQL file.

  1. Locate your MySQL configuration file:
    • Linux: /etc/mysql/my.cnf, /etc/my.cnf
    • Windows: C:\ProgramData\MySQL\MySQL Server X.X\my.ini
    • XAMPP/WAMP: Look within the bin directory of your MySQL installation.
  2. Edit the configuration file: Open the file in a text editor and find the [mysqld] section. Add or modify the max_allowed_packet variable. A good starting point is to set it to a higher value like 256M or 512M.

    ini

    [mysqld]
max_allowed_packet = 512M
  3. Restart the MySQL Server: For the changes to take effect, you must restart the MySQL service.
    • Linux (systemd): sudo systemctl restart mysql
    • Windows: Open services.msc, find the MySQL service, and restart it.
    • XAMPP/WAMP: Use the control panel to stop and start the MySQL service.

Solution 2: Increase Timeout Variables

If the import process involves long pauses between commands, increasing timeout variables can prevent the server from dropping the connection.

  1. Edit the configuration file (my.cnf or my.ini): Under the [mysqld] section, add or adjust the following variables.

    ini

    [mysqld]
wait_timeout = 28800
interactive_timeout = 28800
net_read_timeout = 300
net_write_timeout = 600
    • wait_timeout and interactive_timeout: Setting these to 28800 seconds (8 hours) is often sufficient for very long imports.
    • net_read_timeout and net_write_timeout: These control the timeout for reading from and writing to a connection. Increasing them can help with slow networks.
  2. Restart the MySQL Server: As with the previous solution, restart the MySQL service to apply the new configuration.

Solution 3: Re-establish Connection During Import (Client-side fix)

If you do not have access to the server’s configuration file (e.g., on shared hosting), you can try a client-side fix. When using the mysql command-line client, you can set some of these variables for the current session.

bash

mysql --max_allowed_packet=512M -u your_username -p your_database < your_dump_file.sql

This command sets the max_allowed_packet for this specific import session.

Solution 4: Modify the SQL Dump File

As a last resort, if you cannot modify the server configuration, you can alter the SQL dump file itself.

  • Avoid Extended Inserts: If the file was created with mysqldump, you can recreate it without the --extended-insert option. This will create a separate INSERT statement for each row, resulting in smaller individual packets, though the import process will be slower.

    bash

    mysqldump --skip-extended-insert -u your_username -p your_database > your_dump_file.sql
  • Manually Split Large Inserts: You can manually edit the .sql file to break up extremely large INSERT statements into several smaller ones.

Frequently Asked Questions (FAQs)

Q1. Why does “MySQL server has gone away” happen even with small databases?
This can happen if the wait_timeout on the server is set to a very low value, or if there is network instability that causes the connection to drop.

Q2. How can I check the current value of max_allowed_packet?
You can log in to your MySQL client and run the following query:

sql

SHOW VARIABLES LIKE 'max_allowed_packet';

Q3. Do I need to keep these high values after the import is complete?
While you can revert the changes, keeping a higher max_allowed_packet (e.g., 256M) is generally safe and can prevent future issues. However, extremely high timeout values might not be ideal for a production environment as they can lead to an accumulation of sleeping connections. You can lower the wait_timeout back to a reasonable value (e.g., 300 seconds) after the import.

Q4. Can I fix this error from a GUI tool like phpMyAdmin?
phpMyAdmin often has its own execution time and file upload limits defined in the PHP configuration (php.ini), such as upload_max_filesize, post_max_size, and max_execution_time. While phpMyAdmin might still be affected by the server’s max_allowed_packet limit, you would first need to ensure your PHP settings are high enough to handle the import. For large files, using the command-line client is almost always more reliable.