Why Does My MySQL Server Disconnect During Large SQL Imports, and How Can I Fix It?

Addressing MySQL Server Disconnection during Large SQL Import

When importing large SQL files via phpMyAdmin, you may encounter the frustrating error message, "MySQL server has gone away." This indicates an unexpected disconnection between the server and the client.

To resolve this issue, consider the following solutions:

1. Check and Adjust Wait_Timeout:

Ensure that the wait_timeout variable in your MySQL configuration file (my.cnf) is set to a sufficient value. This variable specifies the maximum time the server waits for a client connection before closing it. To adjust it on Debian, use the command:

sudo nano /etc/mysql/my.cnf

Set the variable to a higher value, such as 600 seconds.

2. Increase Max_Allowed_Packet:

The max_allowed_packet variable determines the maximum size of a single packet that the server can receive. If the SQL file exceeds this limit, the server may drop the connection. To increase this limit, modify the my.cnf file:

sudo nano /etc/mysql/my.cnf

Set the variable to a larger value, such as 64MB.

Additional Note:

MySQL configuration files do not contain default comments. Therefore, you must manually add any modifications and place them under the appropriate option group, such as [mysqld].

Example:

[mysqld]
wait_timeout = 600
max_allowed_packet = 64M

Once the changes are saved, restart the MySQL server. To check the current values, use the MySQL client:

> select @@wait_timeout;
> select @@max_allowed_packet;

The above is the detailed content of Why Does My MySQL Server Disconnect During Large SQL Imports, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!