Zabbix 1.8.1, MySQL 5.1.45, deadlocks caused by tr_status.php

[fascinatedcow]

Hi,

Has anyone else seen this? We frequently see this on our status page, but it has also been seen whilst adding items:

* Error in query [SELECT DISTINCT h.hostid,h.host FROM hosts h WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status=0 AND EXISTS( SELECT i.itemid FROM items i, functions f, triggers t WHERE i.hostid=h.hostid AND i.status=0 AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND t.status=0) ORDER BY h.host] [Deadlock found when trying to get lock; try restarting transaction]
* mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/srv/node03.nms.eu.clara.net/htdocs/include/db.inc.php:544]
* mysql_free_result(): supplied argument is not a valid MySQL result resource[/srv/node03.nms.eu.clara.net/htdocs/include/db.inc.php:546]

I can't get my head around why a select would need to lock a table. We know it's the php doing this bad stuff because we didn't browse to the status page for a few days and we saw no deadlocks.

All our tables are innodb.

These are the queries that caused the last lock:

ID Timestring User Host Victim Time Undo LStrcts Query Text
284 2010-05-21 13:36:47 zabbix node03 No 00:00 40 16 update items set lastclock=1274449004,prevvalue=lastvalue,prevorgva lue=NULL,lastvalue='1' where itemid=46664
140403 2010-05-21 13:36:47 zabbix node03 Yes 00:00 0 29 SELECT DISTINCT h.hostid,h.host FROM hosts h WHERE ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status=0 AND EXISTS( SELECT i.itemid FROM items i, functions f, triggers t WHERE i.h


Thanks for any input.

[edit] actually we're running 1.8.2, but the problem is there in both

[tchjts1]

Not exactly the same as mine, but very similar. Zabbix devs are looking into it.
It seems to happen randomly.


* Error in query [SELECT DISTINCT t.*,hg.groupid,h.host,h.hostid FROM triggers t,functions f,items i,hosts_groups hg,hosts h WHERE ((t.triggerid BETWEEN 000000000000000 AND 099999999999999)) AND hg.hostid=i.hostid AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND (hg.groupid IN (35,29,42,43,31,47,36,16,41,39,37,40,49,48,33,44,3 2,38,17,27,45,23,30,46,25,51,12,2,50,14,3,4)) AND NOT EXISTS ( SELECT ff.functionid FROM functions ff WHERE ff.triggerid=t.triggerid AND EXISTS ( SELECT ii.itemid FROM items ii, hosts hh WHERE ff.itemid=ii.itemid AND hh.hostid=ii.hostid AND ( ii.status<>0 OR hh.status<>0 ) ) ) AND t.status=0 AND t.value=1 AND h.hostid=i.hostid] [Deadlock found when trying to get lock; try restarting transaction]
* mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/var/www/html/include/db.inc.php:544]
* mysql_free_result(): supplied argument is not a valid MySQL result resource[/var/www/html/include/db.inc.php:546]

[jcesario]

Also seeing this.

[tchjts1]

Also seeing this.

What version of MySql are you using?

As the original poster stated he is seeing it with MySql 5.1.45, that is the version we are seeing it in as well.

[jcesario]

mysql-5.1.41-linux-x86_64-glibc23

[jcesario]

It might be worth noting that as of 5.1.40 the InnoDB plugin has come bundled with MySQL.

We use it extensively and have it enabled. However, disabling it (and wiping zabbix and reinstalling) does not seem to solve the deadlock issue.

[fascinatedcow]

Hey guys,

This morning we upgraded our mysql to mysql-5.1.49-linux-x86_64-icc-glibc23.tar.gz and since then have seen absolutely no deadlocks whatsoever! Note the ICC, I think this is the critical difference, not the change from .45 to .49.


Matt

[Alexei]

What the 'icc' stands for?

[tchjts1]

Matt - thanks for the follow-up post. The deadlocks are getting to be very annoying for us. I'll check back with you next week and see if it is still deadlock-free for you, then look into upgrading.

Jeff

[fascinatedcow]

Alexei,

ICC = Intel C Compiler

We use pre-compiled binaries and there are those compiled with gcc and those with ICC.

[fascinatedcow]

tchjts1,

I will post another update next week to let you know how it's looking. Upgrading was pretty trivial though. If you're not changing version stream, i.e. from 5.0 -> 5.1 then you can just stop the old mysql and start the new one, pointing it to the original data files.

[fascinatedcow]

solved

A week has passed and we have seen absolutely no deadlocks. "show innodb status;" shows no deadlocks, and our mysql has been up for 8 days now.

[Alexei]

A week has passed and we have seen absolutely no deadlocks. "show innodb status;" shows no deadlocks, and our mysql has been up for 8 days now.

It must be MySQL-specific issue happening with certain versions only. It probably explains why despite of all our effort we failed to reproduce it in our test environment.