Skip to content

“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 !

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Grégory Steulet

Grégory Steulet

Grégory Steulet is Partner Manager and CFO at dbi services. He previously served as CFO and COO of the Sequotech Group. Earlier in his career, he spent more than ten years in database and infrastructure management, with experience in Oracle technologies, high availability, Avaloq banking applications and open-source technologies such as MySQL and Unix/Linux. Although his current role is no longer focused on database administration, he continues to share his experience through occasional blog posts. Before joining dbi services, Grégory was a Senior Consultant at Trivadis and an IT Administrator at Box Telecom. He holds an Executive MBA, a Bachelor's degree in Business Administration and Computer Science, and Certificates of Advanced Studies in Mergers and Acquisitions and Data Protection. His industry experience includes telecommunications, financial services, logistics and pharmaceuticals.View Author posts

42 posts since February 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *