{"id":2583,"date":"2012-06-25T03:48:00","date_gmt":"2012-06-25T01:48:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/"},"modified":"2012-06-25T03:48:00","modified_gmt":"2012-06-25T01:48:00","slug":"mysql-server-has-gone-away","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/","title":{"rendered":"&#8220;MySQL server has gone away&#8221; ?!"},"content":{"rendered":"<p>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:<\/p>\n<p><code><a href=\"mailto:mysql@dba-ecm-srv11:\/\">mysql@dba-ecm-srv11:\/<\/a> [mysqld1] mq &lt; \/MyBackupFile.sql<br \/>\n<strong>ERROR 2006 (HY000) at line 283: MySQL server has gone away<\/strong><\/code><\/p>\n<p>After having a look on\u00a0the backup file I saw a quite big insert statement which looks like a BLOB:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">Query INSERT INTO `xwikiattachment_archive` VALUES (-2136668642,''),(-2096787253,''),(-2085055696,'head\u00a0\u00a0\u00a0 1.1;naccess;nsymbols;nlocks; strict;ncomment\u00a0\u00a0\u00a0\u00a0 @# @;nnn1.1ndate\u00a0\u00a0\u00a0 2012.05.02.14.36.20;\u00a0\u00a0\u00a0 author xwiki;\u00a0\u00a0 state Exp;nbranches;nnext ;nnndescn@@nnn1.1nlogn@RPO-RTO-schema.jpgn@ntextn@nnRPO-RTO-schema.jpgn72278nXWiki.n1.1n\/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 ...<\/pre>\n<p>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.<\/p>\n<p>The reason why this value is by default rather small is to catch possibly incorrect packet as described in the official documentation &#8211; <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/\/5.5\/en\/server-system-variables.html#sysvar_max_allowed_packet\">http:\/\/dev.mysql.com\/doc\/refman\/\/5.5\/en\/server-system-variables.html#sysvar_max_allowed_packet<\/a>\u00a0&#8211; 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.<\/p>\n<p>In the context of my customer setting the pararameter max_allowed_packet to 16M in the my.cnf or my.ini\u00a0solved the issue.<\/p>\n<p>Hope this will help you !<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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:<\/p>\n<p><code><a href=\"mailto:mysql@dba-ecm-srv11:\/\">mysql@dba-ecm-srv11:\/<\/a> [mysqld1] mq &lt; \/MyBackupFile.sql<br \/><strong>ERROR 2006 (HY000) at line 283: MySQL server has gone away<\/strong><\/code><\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[144,44],"type_dbi":[],"class_list":["post-2583","post","type-post","status-publish","format-standard","hentry","category-database-management","tag-mysql","tag-troubleshooting"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>&quot;MySQL server has gone away&quot; ?! - dbi Blog<\/title>\n<meta name=\"description\" content=\"Solution to solve issue trying to restore MySQL database with the following error message ERROR 2006 (HY000) at line 283: MySQL server has gone away. Setting max_allowed_packet to bigger value solve the problem\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"&quot;MySQL server has gone away&quot; ?!\" \/>\n<meta property=\"og:description\" content=\"Solution to solve issue trying to restore MySQL database with the following error message ERROR 2006 (HY000) at line 283: MySQL server has gone away. Setting max_allowed_packet to bigger value solve the problem\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2012-06-25T01:48:00+00:00\" \/>\n<meta name=\"author\" content=\"Gr\u00e9gory Steulet\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Gr\u00e9gory Steulet\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/\"},\"author\":{\"name\":\"Gr\u00e9gory Steulet\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098\"},\"headline\":\"&#8220;MySQL server has gone away&#8221; ?!\",\"datePublished\":\"2012-06-25T01:48:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/\"},\"wordCount\":201,\"commentCount\":0,\"keywords\":[\"MySQL\",\"Troubleshooting\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/\",\"name\":\"\\\"MySQL server has gone away\\\" ?! - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2012-06-25T01:48:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098\"},\"description\":\"Solution to solve issue trying to restore MySQL database with the following error message ERROR 2006 (HY000) at line 283: MySQL server has gone away. Setting max_allowed_packet to bigger value solve the problem\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"&#8220;MySQL server has gone away&#8221; ?!\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098\",\"name\":\"Gr\u00e9gory Steulet\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g\",\"caption\":\"Gr\u00e9gory Steulet\"},\"description\":\"Gr\u00e9gory Steulet has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix\/Linux, etc.). Gr\u00e9gory Steulet is \\\"Oracle Certified Professional 10g\\\", \\\"MySQL Cluster 5.1 Certified\\\", and \\\"Avaloq Certified Professional 2.6\\\". Prior to joining dbi services, Gr\u00e9gory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Gr\u00e9gory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services \/ Banking, Logistics, Pharma etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/gregory-steulet\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"\"MySQL server has gone away\" ?! - dbi Blog","description":"Solution to solve issue trying to restore MySQL database with the following error message ERROR 2006 (HY000) at line 283: MySQL server has gone away. Setting max_allowed_packet to bigger value solve the problem","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/","og_locale":"en_US","og_type":"article","og_title":"\"MySQL server has gone away\" ?!","og_description":"Solution to solve issue trying to restore MySQL database with the following error message ERROR 2006 (HY000) at line 283: MySQL server has gone away. Setting max_allowed_packet to bigger value solve the problem","og_url":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/","og_site_name":"dbi Blog","article_published_time":"2012-06-25T01:48:00+00:00","author":"Gr\u00e9gory Steulet","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Gr\u00e9gory Steulet","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/"},"author":{"name":"Gr\u00e9gory Steulet","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098"},"headline":"&#8220;MySQL server has gone away&#8221; ?!","datePublished":"2012-06-25T01:48:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/"},"wordCount":201,"commentCount":0,"keywords":["MySQL","Troubleshooting"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/","url":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/","name":"\"MySQL server has gone away\" ?! - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2012-06-25T01:48:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098"},"description":"Solution to solve issue trying to restore MySQL database with the following error message ERROR 2006 (HY000) at line 283: MySQL server has gone away. Setting max_allowed_packet to bigger value solve the problem","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-server-has-gone-away\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"&#8220;MySQL server has gone away&#8221; ?!"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098","name":"Gr\u00e9gory Steulet","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g","caption":"Gr\u00e9gory Steulet"},"description":"Gr\u00e9gory Steulet has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix\/Linux, etc.). Gr\u00e9gory Steulet is \"Oracle Certified Professional 10g\", \"MySQL Cluster 5.1 Certified\", and \"Avaloq Certified Professional 2.6\". Prior to joining dbi services, Gr\u00e9gory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Gr\u00e9gory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services \/ Banking, Logistics, Pharma etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/gregory-steulet\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2583","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2583"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2583\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2583"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2583"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2583"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2583"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}