How to Fix MySQL ERROR 2006 (HY000): MySQL Server Has Gone Away

Error Cause Analysis

ERROR 2006 (HY000): MySQL server has gone away typically occurs in the following situations:

• When importing or executing a large SQL file
• When executing a long-running query
• When connection is terminated due to MySQL server configuration limits

Specifically for your case, the error message indicates it happened while importing a large database backup file (.sql). This is because the default MySQL server configuration cannot handle oversized data packets or excessively long execution times.

Solution

To resolve this issue, you need to adjust MySQL server configuration parameters. Here are the detailed steps:

Step 1: Edit MySQL Configuration File

The configuration file location varies depending on your operating system and MySQL installation:

• Linux systems: Usually /etc/my.cnf, /etc/mysql/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf
• Windows systems: Usually my.ini, located in the MySQL installation directory

Open the configuration file with a text editor. For example, on Linux:

sudo vi /etc/my.cnf

Step 2: Modify Key Configuration Parameters

Add or modify the following parameters in the [mysqld] section:

[mysqld]
# Increase maximum allowed packet size (in bytes)
max_allowed_packet = 256M

# Increase connection timeout (in seconds)
wait_timeout = 600
interactive_timeout = 600

# Increase maximum connections (optional)
max_connections = 200

Parameter explanation:

• max_allowed_packet: Controls the maximum size of a packet that can be transmitted between the MySQL server and client. For large SQL files, set to 256M or higher.
• wait_timeout and interactive_timeout: Control timeout for non-interactive and interactive connections. Default is usually 28800 seconds (8 hours), but adjustment may be needed.

Step 3: Restart MySQL Service

After modifying the configuration, restart the MySQL service:

# Linux system (using systemd)
sudo systemctl restart mysql
# or
sudo systemctl restart mysqld

# Windows system
# Restart MySQL service via Services Manager

Step 4: Verify Configuration

Connect to MySQL and check if the configuration is active:

mysql -u root -p

-- Check max_allowed_packet setting
SHOW VARIABLES LIKE 'max_allowed_packet';

-- Check timeout settings
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

Additional Considerations

1. Import Large SQL Files in Chunks

If the SQL file is very large (over 1GB), you might still encounter issues. Consider these methods:

# Split SQL file using split command (Linux/Mac)
split -l 10000 large_file.sql chunk_

# Or use dedicated tools
# Use --skip-extended-insert with mysqldump for row-by-row inserts

2. Use MySQL Command-Line Tool Options

Specify a larger packet size during import:

mysql -u username -p --max_allowed_packet=512M database_name < file.sql

3. Check Server Resources

Ensure the server has sufficient memory and disk space for large import operations.

Troubleshooting Common Issues

• Permission issues: Ensure write permissions for the config file and sufficient permissions for MySQL data directory.
• Configuration not applied: Verify MySQL service restart and check for conflicting configuration files.
• Version differences: Configuration parameters may vary between MySQL versions (e.g., 5.7, 8.0).

Following these steps should resolve ERROR 2006 (HY000): MySQL server has gone away and allow successful import of large SQL files.