Execution Creates "MySQL server has gone away" Error

Question

I am using a PHP script which is meant to execute continuously for a long time (a one-time mapping script) and insert data into a MySQL database. The script works fine, however, eventually (after a minute or two) it gets an error:

MySQL server has gone away

I have changed the ini settings for both mysql connection timeout, and for the php script execute timeout but neither have changed the outcome.

I have make a VERY similar script in the past that had run on the same server for long amounts of time without ever running into this error.

I thank you for your time, hopefully your help can allow me to solve this problem along with any other frustrated scripter coming across this post in the future.

Answer 1

Did you restarted mysqld after config changes? Do you have enough memory, so that it's not killed by OOM killer?

UPDATE: here is the solution, you need to set "wait timeout" http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

Answer 2

There are many reasons for this to happen : timeout, big packets size etc.

Please check this

Answer 3

check out max_allowed_packet on mysql server settings. Client cannot send packets larger than this or mysql server will close the connection.

This is only related to data inserts when the query string gets very long. This does not affect SELECTs as server will automatically enlarge the sending packet.

Good size would be the size of your data multiplied by two. The multiplication is needed as often data is escaped before sending and header and possible footer added in the SQL QUery.

The use for the max_allowed_packet would be to control the server memory usage and to limit DoS attacks.