Why Does MySQL Show 'MySQL Server Has Gone Away' When Importing Large SQL Files?

MySQL Server Connection Issue: 'MySQL Server has gone away' During Large SQL File Import

When attempting to import a large SQL file through phpMyAdmin, you may encounter an error stating "MySQL server has gone away." This issue can be attributed to two primary causes:

1. Server Timeout

If the server timeout exceeds the wait_timeout variable configured in the mysqld.cnf file, the server will disconnect the connection prematurely. To fix this, increase the wait_timeout value to a higher number (e.g., 600 seconds) using the following steps:

• Open the mysqld.cnf file (e.g., sudo nano /etc/mysql/my.cnf).
• Locate the [mysqld] section and set wait_timeout = 600.
• Save the changes and restart MySQL (e.g., sudo /etc/init.d/mysql restart).

2. Large or Incorrect Packet

Incorrect or excessively large packets received by the server can also trigger a disconnect. Increase the maximum allowed packet size limit by modifying the max_allowed_packet variable in mysqld.cnf:

• Open the mysqld.cnf file.
• Locate the [mysqld] section and set max_allowed_packet = 64M.
• Save the changes and restart MySQL.

Additional Considerations:

• Ensure that the specified value for max_allowed_packet does not exceed the configured memory size.
• If the issue persists, review the server's log files for additional diagnostic information.
• Consider optimizing the SQL file to reduce the number of database operations or splitting it into smaller chunks for import.

The above is the detailed content of Why Does MySQL Show 'MySQL Server Has Gone Away' When Importing Large SQL Files?. For more information, please follow other related articles on the PHP Chinese website!