[ZBX-10012] Housekeeper tries to delete data which could not exist (0 days keep history/trends) Created: 2015 Oct 27  Updated: 2017 May 30  Resolved: 2015 Nov 05

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 2.2.10, 2.4.6, 3.0.0alpha3
Fix Version/s: None

Type: Incident report Priority: Minor
Reporter: Oleksii Zagorskyi Assignee: Unassigned
Resolution: Won't fix Votes: 1
Labels: housekeeper, sql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

Investigated in zabbix server v 3.0.0 alpha4
Suppose we have numeric items which have keep history or keep trends set to 0 days.
According to documentation, zabbix server will not store history or trends if corresponding keep parameter is 0.
https://www.zabbix.com/documentation/2.4/manual/config/items/history_and_trends

But housekeeper process still each time is trying to delete data for such items, disregarding that they could not store any data. From 2nd and for following executions:

 10286:20151027:175335.549 In housekeeping_history_and_trends() now:1445961215
 10286:20151027:175335.549 In hk_history_delete_queue_prepare_all()
 10286:20151027:175335.550 query [txnlev:0] [select i.itemid,i.value_type,i.history,i.trends from items i,hosts h where i.hostid=h.hostid and h.status in (0,1)]
 10286:20151027:175335.550 End of hk_history_delete_queue_prepare_all()
 10286:20151027:175335.550 query without transaction detected
 10286:20151027:175335.550 query [txnlev:0] [delete from history_uint where itemid=27785 and clock<1445961215]
 10286:20151027:175335.550 query without transaction detected
 10286:20151027:175335.550 query [txnlev:0] [delete from trends where itemid=27796 and clock<1445961215]
 10286:20151027:175335.551 query without transaction detected
...

note that the time stamps in "clock<1445961215" conditions are always
= now(), which is sort of correct.

The same happens if you change some item "keep" parameter to 0 days when server is running - those SQLs will start to appear.

One interesting point that such unneeded delete SQLs performed starting from 2nd housekeeper execution only.

I've tested another case - when an item has history/trends data, but on server start the items settings is 0 for keep history or trends.
In this case housekeeper correctly starts to delete already outdated data in 4*HousekeepingFrequency batches (note, from 1st housekeeper execution).
But would be good if it would stop to do unneeded deletions if it deleted everything (has deleted 0 rows during last batch) and the item has 0 days in settings.

Yeah, I understand that this request will improve housekeeper just a little, but still ...
I always look with suspicion on unneeded SQL queries, and it has happened this time too

Also, taking into account that those deletes performed from 2nd housekeeper execution only, I suppose that there could be a small bug.

 Comments   
Comment by Alexander Vladishev [ 2015 Nov 05 ]

It works as expected! Between housekeeper executions an user can enable these parameters to collect some data and then to set these parameters in 0.

I close the issue.

Comment by Oleksii Zagorskyi [ 2015 Nov 05 ]

Sasha, but the same may happen when, for example, after server is started someone sends data for periods older than keep history/trends.
In this case housekeeper (HK) will not see these outdated data until next server restart.

This is a real case I once saw: ~2000 specific hardware boxes without hardware clock are running zabbix agent active.
After reboot (it happens quite often as I see) the boxes set real time as 2000-01-01 11:00:00 and start to send agent active data with such outdated time stamps and these data are stored in database as is. After 1-6 hours the boxes update real time clock (supposedly by NTP) and then send data with correct time stamps.
HK will notice and delete these data only after server restart (after performing select min(clock)).
This real case is not covered by HK logic and I don't think we can do something with this.

What I want to say by this real example - there always will be edge cases when zabbix server will notice existing data (which should be deleted) only after restart.
So, I still think that all I said in issue description is still actual and worth to be considered.

Related request is ZBXNEXT-2860, which can change existing HK logic a lot.

Comment by Oleksii Zagorskyi [ 2015 Nov 05 ]

I'd reopen it, but don't want to do it myself

Generated at Tue Sep 09 00:39:36 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.