{"id":10238,"date":"2023-09-18T11:32:29","date_gmt":"2023-09-18T10:32:29","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=10238"},"modified":"2023-09-18T11:40:39","modified_gmt":"2023-09-18T10:40:39","slug":"error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/","title":{"rendered":"[ERROR] Error reading packet from server: Lost connection to MySQL server during query"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Salut \u00e0 toutes et tous !<\/p>\n

Au sein de notre p\u00f4le d’administration \u00e0 distance AllDB<\/a>, nous traitons depuis presque 20 ans des incidents de base de donn\u00e9es. Notre base d’incidents renferme un peu plus d’1 million de probl\u00e8mes divers et vari\u00e9s sur 5 \u00e0 6 moteurs de base de donn\u00e9es diff\u00e9rents. Chaque incident s’\u00e9tant produit une \u00e0 plusieurs fois, pour vous donner un ordre d’id\u00e9e rien que sur la partie MySQL \/ MariaDB qui est la moins repr\u00e9sent\u00e9e dans le volume d’incidents g\u00e9n\u00e9r\u00e9s, nous avons recens\u00e9 1.2 milliard d’occurrences de codes erreurs depuis 2005, sur un \u00e9chantillon de versions allant de la 3.23 \u00e0 la 8.0 aujourd’hui. <\/p>\n

Ce qu’on voulait vous proposer c’est une petite vulgarisation des codes erreurs principaux que nous avons rencontr\u00e9 au fil des ann\u00e9es, de ce qu’ils signifient et comment corriger ces erreurs. Cette s\u00e9rie va commencer avec MySQL\/MariaDB mais se poursuivra avec les autres moteurs SQL Server, Oracle, PostgreSQL, etc… <\/p>\n

Comprendre l’erreur Lost connection to MySQL server during query<\/h2>\n

Cette erreur g\u00e9n\u00e9rique indique simplement que le serveur MySQL ne per\u00e7oit plus de signe du programme \u00e0 l’autre bout du canal d’une connexion cliente. Le probl\u00e8me peut survenir \u00e0 toutes les \u00e9tapes du cycle de vie d’une connexion : \u00e0 son \u00e9tablissement , au milieu de l’ex\u00e9cution d’une requ\u00eate, lorsque la connexion est inactive depuis un certain temps, lorsque l’instance est satur\u00e9e et ne prend plus de connexions, ou que le serveur est injoignable (stopp\u00e9 : MySQL Server has gone away<\/em>), ou enfin lors d’un transfert de donn\u00e9es massives entre le client et le serveur. <\/p>\n

Elle sera souvent accompagn\u00e9e d’un autre message qui pr\u00e9cise l’origine du probl\u00e8me, par exemple ici il s’agit de la connexion entre un slave de r\u00e9plication et le master:<\/p>\n

\r\n121010 17:32:18 [Note] Start binlog_dump to slave_server(2), pos(mysql-bin.000020, 4137470)\r\n121010 17:34:02 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)\r\n121010 17:34:02 [Note] Slave I_O thread: Failed reading log event, reconnecting to retry, log  mysql-bin.000012  at position 107\r\n<\/pre>\n
Dans ce cas il se peut que le r\u00e9seau sont coup\u00e9 entre les 2 machines ou services, ou que le master soit lui-m\u00eame stopp\u00e9. Cela vient du fonctionnement de type pull de la r\u00e9plication o\u00f9 le slave IO<\/a> streame les positions ou GTIDs depuis les binlogs du master vers des relaylogs locaux. Il est connect\u00e9 en permanence via une connexion classique et est donc sujets aux m\u00eames probl\u00e8mes que les connections clientes. <\/p>\n
R\u00e9soudre l’erreur Lost connection to MySQL server during query<\/h2>\n
Il faut commencer par identifier la cause, qui on l’a vu peut \u00eatre multiple:<\/p>\n
    \n
  1. Le serveur MySQL est-il d\u00e9marr\u00e9 et en capacit\u00e9 de r\u00e9pondre \u00e0 une demande de connexion ? utiliser mysqlping par exemple pour valider que le serveur est disponible<\/li>\n
  2. Le r\u00e9seau est-il coup\u00e9 entre le client et le serveur : firewall, panne r\u00e9seau, etc… Utiliser un client telnet par exemple avec le nom de l’h\u00f4te et le port (par d\u00e9faut TCP3306) pour valider que le flux est ouvert.<\/li>\n
  3. Si vous \u00e9tiez en train de transf\u00e9rer des donn\u00e9es (mysqldump, mysqlpump ou LOAD DATA INFILE) il se peut qu’une partie des donn\u00e9es d\u00e9passe le payload r\u00e9seau client ou serveur, v\u00e9rifier vos valeurs de max_allowed_packet des 2 c\u00f4t\u00e9s. <\/li>\n
  4. C\u00f4t\u00e9 m\u00e9trologie, v\u00e9rifier la valeur de ‘Aborted Connects’ \u00e0 intervalles r\u00e9guliers pour \u00e9valuer s’il s’agit d’un probl\u00e8me isol\u00e9 ou d’un blocage global.\n
    \r\nMariaDB [(none)]> show global status like 'Aborted_connects' ;\r\n+------------------+-------+\r\n| Variable_name    | Value |\r\n+------------------+-------+\r\n| Aborted_connects | 2245  |\r\n+------------------+-------+\r\n1 row in set (0.000 sec)\r\n<\/pre>\n<\/li>\n<\/ol>\n
    Pour retrouver toute la s\u00e9rie sur les codes erreurs, c’est par ici : https:\/\/blog.capdata.fr\/index.php\/category\/codes-erreur\/<\/a> ! <\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
    Salut \u00e0 toutes et tous ! Au sein de notre p\u00f4le d’administration \u00e0 distance AllDB, nous traitons depuis presque 20 ans des incidents de base de donn\u00e9es. Notre base d’incidents renferme un peu plus d’1 million de probl\u00e8mes divers et… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":10243,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[460,4],"tags":[461,462],"class_list":["post-10238","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-codes-erreur","category-mysql","tag-connexion","tag-timeout"],"yoast_head":"\n[ERROR] Error reading packet from server: Lost connection to MySQL server during query - Capdata TECH BLOG<\/title>\n<meta name=\"description\" content=\"Comment r\u00e9soudre l'erreur MySQL [ERROR] Error reading packet from server: Lost connection to MySQL server during query\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[ERROR] Error reading packet from server: Lost connection to MySQL server during query - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Comment r\u00e9soudre l'erreur MySQL [ERROR] Error reading packet from server: Lost connection to MySQL server during query\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2023-09-18T10:32:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-09-18T10:40:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/09\/code_erreur.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"680\" \/>\n\t<meta property=\"og:image:height\" content=\"438\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"David Baffaleuf\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"David Baffaleuf\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/\"},\"author\":{\"name\":\"David Baffaleuf\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\"},\"headline\":\"[ERROR] Error reading packet from server: Lost connection to MySQL server during query\",\"datePublished\":\"2023-09-18T10:32:29+00:00\",\"dateModified\":\"2023-09-18T10:40:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/\"},\"wordCount\":638,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"connexion\",\"timeout\"],\"articleSection\":[\"Codes erreur\",\"MySQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/\",\"name\":\"[ERROR] Error reading packet from server: Lost connection to MySQL server during query - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2023-09-18T10:32:29+00:00\",\"dateModified\":\"2023-09-18T10:40:39+00:00\",\"description\":\"Comment r\u00e9soudre l'erreur MySQL [ERROR] Error reading packet from server: Lost connection to MySQL server during query\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[ERROR] Error reading packet from server: Lost connection to MySQL server during query\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf\",\"name\":\"David Baffaleuf\",\"sameAs\":[\"http:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[ERROR] Error reading packet from server: Lost connection to MySQL server during query - Capdata TECH BLOG","description":"Comment r\u00e9soudre l'erreur MySQL [ERROR] Error reading packet from server: Lost connection to MySQL server during query","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:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/","og_locale":"fr_FR","og_type":"article","og_title":"[ERROR] Error reading packet from server: Lost connection to MySQL server during query - Capdata TECH BLOG","og_description":"Comment r\u00e9soudre l'erreur MySQL [ERROR] Error reading packet from server: Lost connection to MySQL server during query","og_url":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2023-09-18T10:32:29+00:00","article_modified_time":"2023-09-18T10:40:39+00:00","og_image":[{"width":680,"height":438,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/09\/code_erreur.jpeg","type":"image\/jpeg"}],"author":"David Baffaleuf","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"David Baffaleuf","Dur\u00e9e de lecture estim\u00e9e":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/"},"author":{"name":"David Baffaleuf","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf"},"headline":"[ERROR] Error reading packet from server: Lost connection to MySQL server during query","datePublished":"2023-09-18T10:32:29+00:00","dateModified":"2023-09-18T10:40:39+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/"},"wordCount":638,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["connexion","timeout"],"articleSection":["Codes erreur","MySQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/","url":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/","name":"[ERROR] Error reading packet from server: Lost connection to MySQL server during query - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2023-09-18T10:32:29+00:00","dateModified":"2023-09-18T10:40:39+00:00","description":"Comment r\u00e9soudre l'erreur MySQL [ERROR] Error reading packet from server: Lost connection to MySQL server during query","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/error-error-reading-packet-from-server-lost-connection-to-mysql-server-during-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"[ERROR] Error reading packet from server: Lost connection to MySQL server during query"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/136297da9f61d6e4878abe0f48bc5fbf","name":"David Baffaleuf","sameAs":["http:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/dbaffaleuf\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/10238","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=10238"}],"version-history":[{"count":6,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/10238\/revisions"}],"predecessor-version":[{"id":10245,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/10238\/revisions\/10245"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/10243"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=10238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=10238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=10238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}