MySQL Problem – “MySQL server has gone away” (but it hasn’t)

By | 4 April, 2013

Had this problem a couple of times already and i better write what i did to solve it. Quite simple really, but it cost me a few hours of searching.

This problem showed up when i was trying to restore large dumps of data into a Database. A few minutes into the restore, it crashed with this error, but the server never went away at any time.

Anyway, to fix this go to your my.ini file, and edit/add this line:

max_allowed_packet = 64M

On my MySQL install, i already had that line, but with “1M” as the value.

Cant recall the last time i got the error, but today, while restoring a dump with more than 80mb, i got the error and noticed the inserts where huge lines of multi data (logically to accelerate insertion), instead of tons of different inserts.

Hope this helps someone, and me if i forget how its fixed next time it happens – which i probably will.

UPDATE && || NOTE: Also keep in mind a few other parameters that you can tweak (do so very carefully, and first do it on a development machine):

key_buffer = 384M
thread_cache_size = 8
query_cache_size = 32M
max_allowed_packet =128M
table_cache = 4096
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 10M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 180