[#ZBX-22772] Housekeeper does not delete outdated data from trends after changing its type

Steps to reproduce:

1. Create a data element with type "numeric":

SELECT itemid, type, key_, delay,history, trends, value_type, lifetime FROM `items` WHERE `name`='test';
+--------+------+----------------------+-------+---------+--------+------------+----------+
| itemid | type | key_                 | delay | history | trends | value_type | lifetime |
+--------+------+----------------------+-------+---------+--------+------------+----------+
|  44718 |    0 | system.run[date +%s] | 1m    | 1h      | 1d     |          3 | 30d      |
+--------+------+----------------------+-------+---------+--------+------------+----------+

2. After one day, clearing the history works as intended:

SELECT min(clock), max(clock), count(*) FROM `history_uint` WHERE `itemid` = 44718;
+------------+------------+----------+
| min(clock) | max(clock) | count(*) |
+------------+------------+----------+
| 1679216118 | 1679220798 |       79 |
+------------+------------+----------+

SELECT min(clock), max(clock), count(*) FROM `trends_uint` WHERE `itemid` = 44718;
+------------+------------+----------+
| min(clock) | max(clock) | count(*) |
+------------+------------+----------+
| 1679133600 | 1679216400 |       24 |
+------------+------------+----------+

3. Change the data type to "text":

SELECT itemid, type, key_, delay,history, trends, value_type, lifetime FROM `items` WHERE `name`='test';
+--------+------+----------------------+-------+---------+--------+------------+----------+
| itemid | type | key_                 | delay | history | trends | value_type | lifetime |
+--------+------+----------------------+-------+---------+--------+------------+----------+
|  44718 |    0 | system.run[date +%s] | 1m    | 1h      | 0      |          4 | 30d      |
+--------+------+----------------------+-------+---------+--------+------------+----------+

4. After a month, clearing the history normally deletes the old "text" type data:

SELECT min(clock), max(clock), count(*) FROM `history_text` WHERE `itemid` = 44718;
+------------+------------+----------+
| min(clock) | max(clock) | count(*) |
+------------+------------+----------+
| 1683508578 | 1683514878 |       106 |
+------------+------------+----------+

Also removed the old data type "numeric":

SELECT min(clock), max(clock), count(*) FROM `history_uint` WHERE `itemid` = 44718;
+------------+------------+----------+
| min(clock) | max(clock) | count(*) |
+------------+------------+----------+
|       NULL |       NULL |        0 |
+------------+------------+----------+

But the "numeric" data of trends remained in the database:

SELECT min(clock), max(clock), count(*) FROM `trends_uint` WHERE `itemid` = 44718;
+------------+------------+----------+
| min(clock) | max(clock) | count(*) |
+------------+------------+----------+
| 1679133600 | 1679220000 |       25 |
+------------+------------+----------+

[ZBX-22772] Housekeeper does not delete outdated data from trends after changing its type Created: 2023 May 08 Updated: 2024 Apr 10 Resolved: 2023 Aug 02
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Server (S)
Affects Version/s:	6.0.17, 6.4.2, 7.0.0alpha1
Fix Version/s:	6.0.21rc1, 6.4.6rc1, 7.0.0alpha4, 7.0 (plan)

[ZBX-22772] Housekeeper does not delete outdated data from trends after changing its type Created: 2023 May 08 Updated: 2024 Apr 10 Resolved: 2023 Aug 02