[Z3005] query failed: [2013] Lost connection to MySQL server

[asteroidyorkton]

Update: Solved. Just reduce the net.ipv4.tcp_keepalive_time to 300 on Database server. Check the last post for more details.

Lots of query failed in my zabbix logs.
I'm able to telnet to mysql while i see that error.
All the Lost connection errors are while "selecting hostid,key_,state"

[Z3005] query failed: [2013] Lost connection to MySQL server during query [select hostid,key_,state,evaltype,formula,error,lifetime from items where itemid=####]

Its always loose connection when it looks for hostid of key vfs.fs.discovery or net.if.discovery. No other time it has lost connection. Will check older logs now.

This is a fresh setup with 40 hosts.
I've increased the default max_allowed_packet . Any suggestions what to look at?

Slow query logs were enabled on the DB, I've disabled it now just to see if it helps.

innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_method=O_DIRECT
key_buffer = 16M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 2000
query_cache_limit = 4M
query_cache_size = 256M
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M

[asteroidyorkton]

bump..

Lots of errors every few minutes or so....
why is this is happening? mysql seems very fine.

[glebs.ivanovskis]

Which version of Zabbix server do you have?

[asteroidyorkton]

Which version of Zabbix server do you have?

3.0.4 zabbix sever. Fresh install

[asteroidyorkton]

I thought I resolved it when I added skip-name-resolve in my.cnf but no it still occurring.

[glebs.ivanovskis]

Your issue looks similar to ZBX-11004 which was fixed together with ZBX-10753 and the fix will be available in 3.0.5rc1 when it is out. In the meantime you can try to checkout the latest 3.0 from svn repository and re-build zabbix_server from sources.

[kloczek]

bump..

Lots of errors every few minutes or so....
why is this is happening? mysql seems very fine.

It happens because you are not even trying to solve errors which hou have logged.
Pople are not able as well help you because you not presented samples of those errors.

[asteroidyorkton]

Pople are not able as well help you because you not presented samples of those errors.

What? Did you even read the first post? I've presented the sample on the first post.. Its as below. Only item ID changes every time. There is nothing else other than this log...
[Z3005] query failed: [2013] Lost connection to MySQL server during query [select hostid,key_,state,evaltype,formula,error,lifetime from items where itemid=####]
rarely I see error without the query like this. [Z3005] query failed: [2013] Lost connection to MySQL server during query [begin;]

It happens because you are not even trying to solve errors which hou have logged.

What makes you think I haven't tried? I've been looking into this for the last few days and I've tried all ways that could prevent mysql from showing this error and it hasn't solved.
And I feel like it is somewhat a single item issue. Which is why I'm asking why it could be happening because it happens only for Mounted File System Discovery item query. No other items have query failure like this. You tell me now..

This is my observation:
I reduced the WINDOWS OS template Mounted file system item interval to 60 seconds and then I see a lot of errors frequently. I revert the change back to 3600 interval. When its 3000 seconds, errors show up when it checks for the item so around 30 to 50 errors in an hour. If its set to 60 seconds check, then 100's errors every few seconds.

[kloczek]

What? Did you even read the first post? I've presented the sample on the first post.. Its as below. Only item ID changes every time. There is nothing else other than this log...
[Z3005] query failed: [2013] Lost connection to MySQL server during query [select hostid,key_,state,evaltype,formula,error,lifetime from items where itemid=####]
rarely I see error without the query like this. [Z3005] query failed: [2013] Lost connection to MySQL server during query [begin;]

Did you made checks using mysql client command?
Did you check mysqld logs?
If you have error messaeg about issues in the middle of the query it is obvious that it is not the zabbix issue and you should start checking SQL engine.

Look again on the error message:

[Z3005] query failed: [2013] Lost connection to MySQL server during query [select hostid,key_,state,evaltype,formula,error,lifetime from items where itemid=####]

It means that before this error happened zabbix already had opened connection to the mysql server.
Do you have monitoring data of the host where is running mysql? Did you check is everything ok on this host?
Try to google around mysql 2013 error and you would be able to find that in all these cases something was wrong with sql engine or on network layer.
When I've been telling about missing log it was about mysql server side logs.

[asteroidyorkton]

Did you made checks using mysql client command?

Yes I ran the query on the DB server mysql client.

Did you check mysqld logs?

Yes I think I din't mention that my mysql error logs are empty but I did check upstart logs just to make sure and its fine.

If you have error messaeg about issues in the middle of the query it is obvious that it is not the zabbix issue and you should start checking SQL engine.

Look again on the error message:

[Z3005] query failed: [2013] Lost connection to MySQL server during query [select hostid,key_,state,evaltype,formula,error,lifetime from items where itemid=####]

It means that before this error happened zabbix already had opened connection to the mysql server.
Try to google around mysql 2013 error and you would be able to find that in all these cases something was wrong with sql engine or on network layer.
When I've been telling about missing log it was about mysql server side logs.

I've done enough research before I posted it here and still researching and have already tried out all stack exchange/mysq forums suggestions. None of it has fixed this.

Is it really not weird that all the LOST connection query is for vfs.fs.discovery rows. Zabbix does a lot of query but no other queries fails. At least it is not in the logs.
All the tables are InnoDB engine.

Looking into it more.....

Do you have monitoring data of the host where is running mysql? Did you check is everything ok on this host?

yes DB server is being monitored and it all good. I'm also monitoring mysql services using mysql app template. I haven't found anything wrong with the host.

[kloczek]

Yes I ran the query on the DB server MySQL client.

Why you are running test query on the mysql server if you have problem from remote client? Do you have mysqld and zabbix_server running on the same system?

I've done enough research before I posted it here and still researching and have already tried out all stack exchange/mysq forums suggestions. None of it has fixed this.

So just for the record: what exactly you've done to diagnose this issue?

Is it really not weird that all the LOST connection query is for vfs.fs.discovery rows. Zabbix does a lot of query but no other queries fails. At least it is not in the logs.

vfs.fs.discovery it is name of the key provided by the agent.
What is the connection between this agent key and problem logged on the server on executing exact SQL query?

yes DB server is being monitored and it all good. I'm also monitoring mysql services using MySQL app template. I haven't found anything wrong with the host.

Did you check monitoring data about number of connections threads?
Number of running threads with active connections.This metrics is in mysql global status variable 'Threads_connected'
Do you see any unusual drops number of those connections in monitoring data?

In global status you mysqld provides some stats about connections:

"
- Connection_errors_accept
The number of errors that occurred during calls to accept() on the listening port.

- Connection_errors_internal
The number of connections refused due to internal errors in the server, such as failure to start a new thread or an out-of-memory condition.

- Connection_errors_max_connections
The number of connections refused because the server max_connections limit was reached.

- Connection_errors_peer_addr
The number of errors that occurred while searching for connecting client IP addresses.

- Connection_errors_select
The number of errors that occurred during calls to select() or poll() on the listening port. (Failure of this operation does not necessarily mean a client connection was rejected.)"

Do you have to monitor of those metrics? What do you see in monitoring data of those metrics? Especially last one .. ?

Nevertheless, log entry with the error which you've quoted was generated by mysql client library. In other words: if you are observing such errors it has nothing to do with zabbix per se, and it is incredibly low probability that it is a bug in MySQL client library.

And just to be sure ..
Is your zabbix server running on the same system where is running mysqld?

[asteroidyorkton]

Why you are running test query on the mysql server if you have problem from remote client? Do you have mysqld and zabbix_server running on the same system?

No I don't have the mysqld running on zabbix server, its on separate server. I had run the query from remote client mysql multiple times first it never failed. I just ran it from the DB after you asked but you get it, the query neverr fails.

So just for the record: what exactly you've done to diagnose this issue?

2013 Lost connection seemed like common error (while importing stuff). But there were others who had errors in their application logs and some suggested increasing certain things in my.cnf. I had tried them all and monitored it for a while and then reverted it back after seeing the error still happen. My current settings are in the posts.

vfs.fs.discovery it is name of the key provided by the agent.
What is the connection between this agent key and problem logged on the server on executing exact SQL query?

Nothing. My point is that Zabbix does a lot of different queries other and insert operation and there has been no other query failed. It felt weird because it looked like all the failures were happening while querying item tables. I expected if it mysql issue, then there should be more failures, if so then there would be queries related to other than item tables or vfs.fs.discovery.. That was my point, anyway ignore that.
In the latest logs....
[Z3005] query failed: [2013] Lost connection to MySQL server during query [begin;]

Did you check monitoring data about number of connections threads?
Number of running threads with active connections.This metrics is in mysql global status variable 'Threads_connected'
Do you see any unusual drops number of those connections in monitoring data?

The default template I was using din't include monitoring of connection threads. It only monitored read/writes/rollbacks/etc., I'll add a item and monitor it.
Right now, running threads=1, Threads connected=491 (changes to 490 or 488), Threads_created=5911
Will create a item and monitor it.

In global status you mysqld provides some stats about connections:

"
- Connection_errors %

Do you have to monitor of those metrics? What do you see in monitoring data of those metrics? Especially last one .. ?

I'm running 5.5 mysql and it does not have these variables.

[glebs.ivanovskis]

Sorry to interrupt you guys. Have you seen my second message in this thread? leftlanef4, have you tried 3.0.5rc1?

Connection between poller, discovery items and DB is the following. Every Zabbix server process has it's own independent connection to the database and use it if necessary. Pollers don't use their connections most of the time since they communicate mostly with agents and history write cache. By the time they need DB next time DB server usually considers connection timed out and therefore pollers need to reconnect. One of very few number of occasions when poller needs DB is when it processes LLD data. And Zabbix had a reconnection bug on MySQL specifically which has been fixed very recently, I suspect it may be related.

[asteroidyorkton]

I'm running 3.0.4 right now.
Should I just download the 3.0 source from SVN, compile it and replace the zabbix_server binary? is that it?

Btw thanks for explaining how poller works. Good to know when it connects to DB.

[glebs.ivanovskis]

I'm running 3.0.4 right now.
Should I just download the 3.0 source from SVN, compile it and replace the zabbix_server binary? is that it?

Yes, I'm afraid 3.0.5rc1 is not available in repository. Take either svn://svn.zabbix.com/tags/3.0.5rc1 or svn://svn.zabbix.com/branches/3.0

First run ./bootstrap then ./configure ...

Feel free to ask if you're stuck with the compilation.