If you’re using OpenHAB2’s persistence for some reason or another, you will sooner or later come to one question: why do I save all those old states, which I don’t Need for. My OH2 was running for nearly a year now and I persist nearly all items and the size of the DB growing by the day. So I wrote basically a stored procedure in MySQL to come up with a garbage collection. Why directly in the database? I don’t use other persistences and I don’t want to mess around with connections, the right script languae, cron jobs on one Server or another - so I decided to put the garbage collector, where my data resides.
Persistence in MySQL
Basically I assume, everybody is aware of how MySQL-persistance in OH2 is handled. In short:
- you get a table “Items” within your database (configured in mysql.cfg)
- for every Item you add (and your .persist strategy meets!), you will get a new row in Items and a new table Itemxxx
- the row in Items will get a ItemID (Integer) and the new row will have the Name Item+ItemID
What to do to delete old unwanted data
- add column to distinguish “garbage” input from wanted input, which shouldn’t been deleted
- create an stored procedure
- activate MySQL Event Scheduler
- delete garbage entries daily
ALTER TABLE `openHAB2`.`Items` ADD COLUMN `ItemGarbage` INT(11) NOT NULL DEFAULT 0 AFTER `ItemName`;
this adds a column “ItemGarbage” to your Items-Repository in the MySQL database. To be on the “safe” side, the default value for this column is “0”, so if OH2 adds another column to your MySQL via the strategy in
mysql.persist it won’t be deleted. You have to manually change the value to > 0 to activate the daily garbage collection!
As a goodie (see 2), you can choose your days, you want the garbage collector to delete old data:
1: just one day
2: two days
… and so on.
just browse through your “Items”-table an mark those Items you don’t need persisted with a timeframe in days.
Hint: my database for oh2 reads “openHAB2”, of yours differ, just change that name in the SQL (also down there in the stored procedure)
I prefer to write a stored procedure, because you can execute it for testing purposes better than written all SQLs within the event scheduler itself:
DELIMITER $$ CREATE PROCEDURE `openHAB2`.`DeleteOldItemValues`() BEGIN # declare variables DECLARE var_table, var_interval VARCHAR(20); DECLARE var_tableId, var_done, var_garbage INT DEFAULT 0; # This builds an cursor on all ItemIds, which are marked as "garbage" DECLARE myCursor CURSOR FOR SELECT ItemId, ItemGarbage FROM Items WHERE ItemGarbage > 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_garbage; IF var_done THEN LEAVE read_loop; END IF; SET var_interval = concat(var_garbage, ' DAY'); # Set the interval for SQL-Statement in days, e.g. ItemGarbage == 1 => SQL-WHERE: ' WHERE TIME < (NOW() - INTERVAL 1 DAY) ' SET var_table = concat('Item', var_tableID); # 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 SET @sql_text = concat('DELETE FROM ', var_table, ' WHERE TIME < (NOW() - INTERVAL ', var_interval, ')'); PREPARE stmt FROM @sql_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; # close everything and go to sleep CLOSE myCursor; END$$
Basically this procedure will find all Item-Tables with an entry other than 0 and will then delete all values older than the days, you specified in the garbage - column.
now it’s time to activate the MySQL event scheduler_
SET GLOBAL event_scheduler = ON;
with this SQL statement, the MySQL Event Scheduler is activated (something like a cronjob, but within the database itself)
lastly you have to decide, when the scheduler should run your procedure, in that case, it’ll run at 4:00 in the morning everyday. (you could leave out the “STARTS”-part, then MySQL will propably start the procedure at midnight I guess.
CREATE EVENT cleanup_event ON SCHEDULE EVERY 1 DAY STARTS '2017-09-21 04:00:00' DO call DeleteOldItemValues();
that’s it, the database will now delete everything you flagged with a INT higher than 0, saving you some space on your DB-Server over the years…