Zabbix_server.log says [2006] MySQL server has gone away

Question

Environment

CentOS 6.6, Zabbix 2.4

DB MySQL Galera cluster with 3 nodes
mysql-wsrep-server-5.6-5.6.23-25.10.el6.x86_64
galera-3-25.3.10-2.el6.x86_64

Errors

[Z3005] query failed: [2006] MySQL server has gone away [select hostid,status from hosts where host='Zabbix server' and status in (0,1) and flags<>2 and proxy_hostid is null]

[Z3005] query failed: [2006] MySQL server has gone away [begin;]
[Z3005] query failed: [2006] MySQL server has gone away [begin;]

I used to get this error a lot more but the below tweaks have reduced the frequency. I have a feeling this is due to haproxy.

The setup

zabbix connects to mysql server via haproxy which resides locally on zabbix
haproxy resides on zabbix server
everything is on same virtual network under vmware. same subnet etc.

Now in this case host "Zabbix server" does not exist. Or rather I renamed the host and changed the IP etc so its called "DC1 zabbix server".

haproxy.cfg (generous timeout settings)

---

retries 3
timeout queue 45s
timeout connect 60s
timeout client 5m
timeout server 5m
timeout check 30s
maxconn 1020

on mysql server

/etc/my.cnf

wait_timeout = 28000
max_allowed_packet = 64M
max_connections = 512
join_buffer_size=300
table_open_cache=2500
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=2G
query_cache_type=1
query_cache_size=128M

zabbix_server.conf

CacheSize = 128M
Timeout=30
Timeout=29 (on agent node)
ValueCacheSize=128M
StartPollers=100
StartDBSyncers = 8

A related question: If this issue happens and zabbix d/c with mysql. Does it re estalish connection immediately without losing monitoring data?

I have done a lot of Google'ing around this and tried several solutions that pertain to this error but it just wouldn't go away. What can I try?

Thanks!

Answer 1

You have probably problem with persistent Zabbix DB connections. Tune also your TCP stack - see hints for persistent connections http://www.slideshare.net/Severalnines/haproxy-mysql-slides

Answer 2

While max_allowed_packet does usually work in most cases. This problem is actually a bug in the Zabbix database connection.

So to answer the question you have to patch or use Zabbix version 2.5.0 or above.

source: https://support.zabbix.com/browse/ZBX-6163

Answer 3

Let's just stick to the documentation and take notes:

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection.

• Connection is closed if mysqld receives larger or out-of-order packet. You might want to twick max_allowed_packet variable. Set it some what larger than what it is.
• Is your connection closed before you run the query?
• Can you connect to mysql on specified host rather using simple mysql client? Connectivity is not an issue! See if following works: mysql -uUSER -pPASS

Also as per default settings client does the auto-reconnect upon disconnections.