December 29, 2019, 10:15am
When you have persistence configured you come to a point that you want to know how much data you have in your openHAB2 data base or you want to delete old item states like discussed for example here:
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 ri…
I created two python scripts which could be helpful also for others, so decided to make them public. You can use them for
Creation of a CSV file with an overview over your data in the database
Deleting of item data older than a configured number of days
You can find them on gitHub:
A detailed description how to use them you find here:
They are tested with my mariaDB installation on openhabianpi but should work also with other SQL data bases.
Would be nice to hear your opinion!
November 9, 2020, 8:42pm
I prefer value deletion individual by age per item as in those mentioned thread automatically with sql-procedure.
But what I’m still searching for is a tool, checking all items from persostence table items against actual active items from openhab2, so i can decide / mark items in sql to delete and drop the tables not needed anymore.
November 10, 2020, 7:28am
Please have a look in the link in my post:
This script goes over all data base tables and link them with the actual items in openhab. If you filter for “-.-” in the sheet for example in column label. you see all tables in the db which don’t have an item in openHAB anymore.
Is it that what you are searching for?
November 10, 2020, 8:06am
I searched for a more automatic way. But I nearly got my solution. A rule increasing in items table how many cycles the item is inactive allready working.
I ll write a sql procedure now deleting items older x month next.
UPDATE: Script with Tutorial ready and checked. Automatically deleting old unused tables of actual inactive / earlier Items.
November 11, 2020, 9:23pm
I yesterday did it with a mix of mysql Procedure and an openhab-rule!
Part One: Deleting Old Values
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.1) Alter Table for Items to set maximal history of data
Thanks for sharing it’s solution, it worked out for me