MySQL Error 2006: The mysql server has disappeared

Question

I run a server at my office to process some files and report the results to a remote MySQL server.

File processing takes some time and the process terminates midway with the following error:

2006, MySQL server has gone away

I've heard about the MySQL setting wait_timeout, but do I need to change that on my office server or a remote MySQL server?

Answer 1

I've encountered this situation many times and usually find the answer is max_allowed_packet.

Raising the value in /etc/my.cnf (under [mysqld]) to 8 or 16M usually fixes the problem. (The default in MySql 5.7 is 4194304, which is 4MB.)

[mysqld]
max_allowed_packet=16M

---

Note: If the row does not exist, just create the row, it must appear as an entry under [mysqld]

NOTE: This can be set while the server is running, but it will be lost after the mysql daemon is restarted. Use SET GLOBAL max_allowed_packet=104857600 (this sets it to 100MB)

Note: On Windows, you may need to save your my.ini or my.cnf file using ANSI instead of UTF-8 encoding.

Answer 2

It might be easier to check if the connection exists and re-establish it if needed.

For information, see PHP:mysqli_ping.