Bug report: Master zabbix server crashes on receipt of invalid string data

[Niels]

I started doing this:

DELETE history_str FROM history_str LEFT JOIN items ON history_str.itemid=items.itemid WHERE items.itemid IS NULL;
DELETE history_uint FROM history_uint LEFT JOIN items ON history_uint.itemid=items.itemid WHERE items.itemid IS NULL;
DELETE history_log FROM history_log LEFT JOIN items ON history_log.itemid=items.itemid WHERE items.itemid IS NULL;
DELETE history FROM history LEFT JOIN items ON history.itemid=items.itemid WHERE items.itemid IS NULL;

But it stopped with "ERROR 1206 (HY000): The total number of locks exceeds the lock table size", which I don't have the time to look into right now.

But I'm not so sure that these deletes are correct after all. New orphan data is still being added to the history tables. So I guess my (our) idea of orphan data isn't wholly correct. A comment from the developers would be nice.

[erozen]

Zabbix devs - Are you out there???

Have you restarted all your agents recently? Perhaps they don't have an up-to-date list of items, and are still pumping in invalid data.
Also - have the run the check on both nodes? I wonder if the databases are out of sync?

I'm pretty sure this data is useless - I find it hard to fathom the worth of a value when you don't what metric it represents; "Your server says '7'". 7 what????

You're right - we don't know the inner workings of zabbix, so perhaps this does make sense. However, the fact that running this delete stopped my server crashing every 2 minutes, and that we have experienced no lost or corrupted data or functionality since indicates otherwise. To me, at least.


I really would like some dev feedback - is there any way we can bring this thread to their attention???

[xs-]

What i personally would really like is a extended list of the forementioned queries which will sanitize / de-orphan the database.

Its nice to clean up the history tables (you can do trends exactly the same way btw), but . . . what about items, triggers, host-group relations, user-group relations, etc, etc.

One of my child nodes apparently had a crappy update from the master node and is now spewing the 'cant find functionid blah' stuff.

This whole thread and others (like the functionid issue) are all a nice indication that zabbix isn't perfect in 'data warehousing' . . . . which on itself is normal and not a big issue. But i would like to be able to to fix this, either by manual periodic action or housekeeping.

In short:
I would like to to completely sanitize / de-orphan my zabbix db's, periodically
I can probably come up with a nice query list myself if i take a couple of hours to dive into the database, but no-one really knows the exact relational structure of the database and no-one wants a borked db.

[JoelG]

DB check utility

I asked for such a utility in this thread

http://www.zabbix.com/forum/showthread.php?t=9077

[Niels]

Have you restarted all your agents recently? Perhaps they don't have an up-to-date list of items, and are still pumping in invalid data.

I only use active checks, but that's still a good idea.

I'm pretty sure this data is useless - I find it hard to fathom the worth of a value when you don't what metric it represents; "Your server says '7'". 7 what????

You're right - we don't know the inner workings of zabbix, so perhaps this does make sense. However, the fact that running this delete stopped my server crashing every 2 minutes, and that we have experienced no lost or corrupted data or functionality since indicates otherwise. To me, at least.

I agree that the values without items look very superfluous, but there's no way to be certain.

I really would like some dev feedback - is there any way we can bring this thread to their attention???

I think the devs are well aware of this thread -- they just can't or won't participate, for some reason.