-
Problem report
-
Resolution: Fixed
-
Trivial
-
6.0.17, 6.4.2, 7.0.0alpha1
-
None
-
FreeBSD 12.3, MySQL 8.0.30
-
Sprint 100 (May 2023), Sprint 101 (Jun 2023), Sprint 102 (Jul 2023), Sprint 103 (Aug 2023)
-
0.25
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 | +------------+------------+----------+
