“MySQL server has gone away” ?!

A few days ago, I was at a customer that encountered some problems trying to restore his MySQL database. Indeed he got the following error message trying to restore his xwiki database:

mysql@dba-ecm-srv11:/ [mysqld1] mq < /MyBackupFile.sql
ERROR 2006 (HY000) at line 283: MySQL server has gone away

After having a look on the backup file I saw a quite big insert statement which looks like a BLOB:

Query INSERT INTO `xwikiattachment_archive` VALUES (-2136668642,''),(-2096787253,''),(-2085055696,'head    1.1;naccess;nsymbols;nlocks; strict;ncomment     @# @;nnn1.1ndate    2012.05.02.14.36.20;    author xwiki;   state Exp;nbranches;nnext ;nnndescn@@[email protected]@[email protected]/9j/4AAQSkZJRgABAQEASABIAAD/4QCMRXhpZgAATU0AKgAAAAgABgEGAAMAAAABAAIAAAESAAMAnAAABAAEAAAEaAAUAAAABAAAAV gEbAAUAAAABAAAAXgEoAAMAAAABAAIAAIdpAAQAAAABAAAAZgAAnAAAAAABIAAAAAQAAAEgAAAABAAKgAgAEAAAAAQAAAr6gAwAEAAAA AQAAAfMAAAAA/9sAQwACAgICnAgECAgICAgICAwMGBAMDAwMHBQUEBggHCAgIBwgICQoNCwkJDAoICAsPCwwNDg4ODgkLEBEPDhENnD g4O/9sAQwECAgIDAwMGBAQGDgkICQ4ODg4ODg4ODg4ODg4ODg4ODg4ODg4ODg4ODg4ODg4ODg4OnDg4ODg4ODg4ODg4ODg4O/8AAEQgB 8wK+AwEiAAIRAQMRAf/EAB8AAAEFAQEBAQEBAAAAAAAAAAABnAgMEBQYHCAkKC//EALUQAAIBAwMCBAMFBQQEAAABfQECAwAEEQUSITF BBhNRYQcicRQygZGhCCNCnscEVUtHwJDNicoIJChYXGBkaJSYnKCkqNDU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanNndXZ3e Hl6g4SFhoeIiYqSk5SVlpeYmZqio6Slpqeoqaqys7S1tre4ubrCw8TFxsfIycrS09TV1tfYn2drh4uPk5ebn6Onq8fLz9PX29/j5+v/E AB8BAAMBAQEBAQEBAQEAAAAAAAABAgMEBQYHCAkKC//EnALURAAIBAgQEAwQHBQQEAAECdwABAgMRBAUhMQYSQVEHYXETIjKBCBRCkaG xwQkjM1LwFWJy0QoWnJDThJfEXGBkaJicoKSo1Njc4OTpDREVGR0hJSlNUVVZXWFlaY2RlZmdoaWpzdHV2d3h5eoKDhIWGnh4iJipKT lJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uLj5OXm5+jpn6vLz9PX29/j5+v/aAAwDAQACEQMRAD8A/fy ...

The problem was coming from the maximum size of one packet which is managed by the parameter max_allowed_packet. This parameter default value is 1048576.

The reason why this value is by default rather small is to catch possibly incorrect packet as described in the official documentation – http://dev.mysql.com/doc/refman//5.5/en/server-system-variables.html#sysvar_max_allowed_packet – which is not a bad thing. However as soon as we have an application using BLOB, this value is often too small. In order to setup this parameter correctly for BLOB usage, you have to set this parameter to the size of the bigget BLOB. The maximum size is however limited to 1GB.

In the context of my customer setting the pararameter max_allowed_packet to 16M in the my.cnf or my.ini solved the issue.

Hope this will help you !