{"id":348,"date":"2012-07-12T14:11:18","date_gmt":"2012-07-12T17:11:18","guid":{"rendered":"http:\/\/wagnerbianchi.com\/blog\/?p=348"},"modified":"2012-07-12T15:18:49","modified_gmt":"2012-07-12T18:18:49","slug":"got-an-error-reading-communication-packets","status":"publish","type":"post","link":"http:\/\/wagnerbianchi.com\/blog\/?p=348","title":{"rendered":"Got an error reading communication packets"},"content":{"rendered":"

O nome desse post \u00e9 exatamente a mensagem de erro que voc\u00ea provavelmente poder\u00e1 receber ao verificar o estado de sa\u00fade do seu servidor de bancos de dados MySQL, nesse caso, um MySQL 5.0. Na semana atual estou trabalhando com um cliente localizado no Brasil que tem cerca de 1502 conex\u00f5es simult\u00e2neas no MySQL, este que \u00e9 o reposit\u00f3rio de informa\u00e7\u00f5es de um ERP que centraliza as opera\u00e7\u00f5es da empresa. S\u00e3o v\u00e1rias lojas acessando um mesmo MySQL configurado com um reposit\u00f3rio central – obviamente, anteriormente, este cliente passou a operara com servidores em replica\u00e7\u00e3o, onde temos um servidor MASTER e outros 7 SLAVEs, cada qual com fun\u00e7\u00f5es distintas.<\/p>\n

Enfim, independentemente da arquitetura do cliente, encontramos um problema logo depois que a mesma come\u00e7ou a rodar. Ao consultar o logo de erro do MySQL, encontramos o seguinte cen\u00e1rio:<\/p>\n

root@master1:\/var\/log# tail -f \/var\/log\/mysql\/mysql.err
\n120712 14:22:55 [Warning] Aborted connection 173570 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
\n120712 14:23:15 [Warning] Aborted connection 173025 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
\n120712 14:27:48 [Warning] Aborted connection 169655 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
\n120712 14:29:00 [Warning] Aborted connection 165547 to db: 'sqldados' user: 'root' host: '' (Got an error reading communication packets)
\n120712 14:29:23 [Warning] Aborted connection 172752 to db: 'unconnected' user: 'sink02' host: '' (Got an error reading communication packets)
\n120712 14:30:27 [Warning] Aborted connection 173886 to db: 'unconnected' user: 'sink01' host: '' (Got an error reading communication packets)
\n120712 14:31:54 [Warning] Aborted connection 174079 to db: 'unconnected' user: 'sink18' host: '' (Got an error reading communication packets)
\n120712 14:34:16 [Warning] Aborted connection 171530 to db: 'sqldados' user: 'root' host: '' (Got an error reading communication packets)<\/code><\/p>\n

Inicialmente, pensamos ser um problema de lat\u00eancia de rede onde a conex\u00e3o para leitura e escrita estavam sen fechadas, mesmo com o status da thread continuando em SLEEP. Sendo assim, ajustamos as vari\u00e1veis net_% do MySQL. O primeiro passo foi resetar todas elas:<\/p>\n

mysql> set net_buffer_length = DEFAULT;
\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/p>\n

mysql> show global variables like 'net%';
\n+-------------------+-------+
\n| Variable_name \u00a0 \u00a0 | Value |
\n+-------------------+-------+
\n| net_buffer_length | 16384 |
\n| net_read_timeout \u00a0| 60 \u00a0 \u00a0|
\n| net_retry_count \u00a0 | 10 \u00a0 \u00a0|
\n| net_write_timeout | 60 \u00a0 \u00a0|
\n+-------------------+-------+
\n4 rows in set (0.01 sec)<\/code><\/p>\n

Para testarmos a elimina\u00e7\u00e3o do erro, configuramos as vari\u00e1veis net_read_timeout e net_write_timeout com um valor maior:<\/p>\n

mysql> set global net_write_timeout=360;
\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/p>\n

mysql> set global net_write_timeout=360;
\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/p>\n

mysql> show global variables like 'net%';
\n+-------------------+-------+
\n| Variable_name \u00a0 \u00a0 | Value |
\n+-------------------+-------+
\n| net_buffer_length | 16384 |
\n| net_read_timeout \u00a0| 360 \u00a0 |
\n| net_retry_count \u00a0 | 10 \u00a0 \u00a0|
\n| net_write_timeout | 360 \u00a0 |
\n+-------------------+-------+
\n4 rows in set (0.00 sec)<\/code><\/p>\n

Mesmo assim, o erro n\u00e3o foi corrigido e acompanhando o log de erro com tail -f, ele voltou a aparecer…a solu\u00e7\u00e3o foi ajustar o max_allowed_packet para suportar pacotes maiores e ent\u00e3o o erro foi corrigido.<\/p>\n

mysql> select concat(format(@@max_allowed_packet\/1024\/1024,2),'MB') \"max_allowed_packet\";
\n+--------------------+
\n| max_allowed_packet |
\n+--------------------+
\n| 16.00MB \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n+--------------------+
\n1 row in set (0.01 sec)<\/code><\/p>\n

mysql> set max_allowed_packet=128*1024*1024;
\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/p>\n

mysql> select concat(format(@@max_allowed_packet\/1024\/1024,2),'MB') \"max_allowed_packet\";
\n+--------------------+
\n| max_allowed_packet |
\n+--------------------+
\n| 128.00MB \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n+--------------------+
\n1 row in set (0.01 sec)<\/code><\/p>\n

Ap\u00f3s isto, observamos o log por mais 2 horas e n\u00e3o houve mais ocorr\u00eancia do erro “Got an error reading communication packets”. Vale salientar tamb\u00e9m que este erro pode ser causado quando o aplicativo que se conecta ao MySQL n\u00e3o finaliza uma conex\u00e3o de maneira apropriada (sem um mysql_close(), por exemplo), incrementando a vari\u00e1vel de status Aborted_clients.<\/p>\n

mysql> show global status like 'Aborted%';
\n+------------------+-------+
\n| Variable_name \u00a0 \u00a0| Value |
\n+------------------+-------+
\n| Aborted_clients \u00a0| 2866 \u00a0|
\n| Aborted_connects | 17 \u00a0 \u00a0|
\n+------------------+-------+
\n2 rows in set (0.00 sec)<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

O nome desse post \u00e9 exatamente a mensagem de erro que voc\u00ea provavelmente poder\u00e1 receber ao verificar o estado de sa\u00fade do seu servidor de bancos de dados MySQL, nesse caso, um MySQL 5.0. Na semana atual estou trabalhando com um cliente localizado no Brasil que tem cerca de 1502 conex\u00f5es simult\u00e2neas no MySQL, este […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,17],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/348"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=348"}],"version-history":[{"count":3,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/348\/revisions"}],"predecessor-version":[{"id":351,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/348\/revisions\/351"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=348"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}