Mysql persistence: maintenance scripts

[pascal]

Hi,

I’m using Mysql Persistence and I was wondering if anyone could share how they maintain their openhab Mysql database.
The amount of entries in the items tables keeps on growing, so I’m looking for some hints an tips on how to maintain them.
for example: a sql script to erase all data older then… from all tables. a script to delete unused item tables, etc …

Thanks for your help and suggestions

[JensH]

Some late, just saw your post while searching something else. Here is an example for deleting old values from persistence (configured or some later: all older than x days):

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

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…

[JensH]

Just took the Quest and added Tutorial for deletion of tables from unused items after some time of Inactivity. Same Thread:

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

Intro 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…