"Garbage Collection" on MySQL persistence: deleting old Item states

Tutorials & Examples
,
11

Out of Date: New Version later in Thread

Based on examples in this Thread I put all hints and further usefull things together:

If you want to cleanup Old Item-Data all code can be send directly in SQL-Database. For jdbc-mysql-persistence all Commands have to be send in SQL (for example in SQL Window in phpmyadmin). has to be changed to your exact table name, you can see in phpmyadmin.

Following Examples are tested with OH 2.5 SNAPSHOT 1680:

1) Alter Table for Items to set maximal history of data

ALTER TABLE `OPENHAB2-TABLE`.`items` 
ADD COLUMN `deleteafterdays` INT(11) NOT NULL DEFAULT -1 AFTER `itemname`;

Explanation: Adds a new column where you can enter after how many days the data will be deleted. -1 means: No data will deleted, 0 means all Data except newest value will be deleted, values from 1 to x means all values older than x days except newest value will be deleted.

2) Create procedure can be scheduled directly in SQL-Database

DELIMITER $$
CREATE PROCEDURE `OPENHAB2-TABLE`.`DeleteOldItemValues`()
BEGIN
# declare variables
DECLARE var_interval VARCHAR(20);
DECLARE var_table, var_itemname VARCHAR(500);
DECLARE var_tableId INT(4) zerofill DEFAULT 0;
DECLARE var_done, var_garbage, var_noEntry INT DEFAULT 0;
# This builds an cursor on all ItemIds, which are marked as "garbage"
DECLARE myCursor CURSOR FOR SELECT ItemId, itemname, deleteafterdays FROM items WHERE deleteafterdays >= 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = 1;
OPEN myCursor;
# go through all ItemIds and delete old values
read_loop: LOOP
	FETCH myCursor INTO var_tableId, var_itemname, var_garbage;
    IF var_done THEN
      LEAVE read_loop;
    END IF;
	SET var_interval = concat(var_garbage, ' DAY');
    SET var_table = concat(lower(var_itemname), '_', var_tableID);
	
    SET @latest_time = concat('SELECT max(time) INTO @latest_var FROM ', var_table);
    PREPARE psmt FROM @latest_time;
    EXECUTE psmt;
    DEALLOCATE PREPARE psmt;
# small workaround as variables won't just fit in as table names - so we have to concat the table_name and then we can PREPARE and EXECUTE the statement
    IF @latest_var is not null THEN
        SET @sql_text = concat('DELETE FROM ', var_table, ' WHERE time < (NOW() - INTERVAL ', var_interval, ') and time <>  "', @latest_var, '"');
        PREPARE stmt FROM @sql_text;
	    EXECUTE stmt;
	    DEALLOCATE PREPARE stmt;
    END IF;
END LOOP;

CLOSE myCursor;
END$$

Explanation: This SQL-Script is stored in Database and can be used by SQL-Scheduler / EventHandler. If you want to change this script later, you can find it in phpmyadmin at database OPENHAB2-TABLE under procedures (To see after at least on procedure is stored in database).

3) Activate Event-Handler in SQL-Database

SET GLOBAL event_scheduler = ON;

Explanation: Turns on event handler in sql-Database. For more information just google for mysql event handler

4) Create “Cron”-Job for calling cleanup-event

CREATE EVENT cleanup_event ON SCHEDULE EVERY 1 DAY STARTS '2018-05-02 00:00:00' DO call DeleteOldItemValues();

Explanation: Starts Procedure to delete old Data every midnight. If you want to change this CRON later, you can find it in phpmyadmin at database OPENHAB2-TABLE under events (To see after at least on event is activated database).

5) Find configured and unconfigured delete-times with amount of stored values

SELECT A.itemid, A.itemname, A.deleteafterdays, B.TABLE_ROWS from openhab2.items as A, `information_schema`.`TABLES` AS B WHERE concat(A.itemname,'_',LPAD(A.ItemID,4,'0')) = B.TABLE_NAME COLLATE utf8_general_ci AND B.`TABLE_SCHEMA` LIKE 'openhab2' ORDER BY TABLE_ROWS DESC

Explanation: Requests a list with item id, item name, configured value to delete old data and ammount of stored data to priority times to delete in dependence of used storage

Changes done to original Script

  • Changed column names to actual names (uppercase, lowercase,…)
  • ‘Speaking name’ for column to configure days after which data is deleted
  • Changed script to get right data-table-names for item-data, now with lowercased itemname, underscore and id with zerofill
  • Changed length of itemnames, now similar to data-table
  • Using -1 for unconfigured delete-times, so Option available to delete everything except last value
  • integrated and corrected code of emilfelix (missing double-quotes) to except newest value equal of age
  • UPDATE 07.05.2019 - Changed Procedure to prevent error if DeleteData configured and Data-Table is empty
  • UPDATE 11.07.2019 - Added SQL-Command for phpmyadmin to find configured values in dependency of stored data
  • UPDATE 10.11.2020 - Add Out of Date Hint
    Open wishes
  • -2 for unconfigured deleting-times, so that -1 could be used all data including last value
3 Likes