dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!
"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 @# @;\n\n\n1.1\ndate 2012.05.02.14.36.20; author xwiki; state Exp;\nbranches;\nnext ;\n\n\ndesc\n@@\n\n\n1.1\nlog\\n@\ntext\n@\n
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 !