I’ve done my typical couple of hours scouring the web trying to get persistence to work using rrd4j and jdbc-mysql and I think everything should work, but it doesn’t seem to kick in. Instead of me posting a bunch of snippets of config files, is there a good tutorial out there, or tips on what logs to look at for errors (events and openhab do not say much about this)?
I do see the .rrd4j files (all are around 116k) and don’t look like they are being updated. I’ve tried to present them as a chart, but the chart is blank.
The big trick with rrd4j and charts is you must save updates every minute (it’s complicated and has to do with how rrd4j works) or else charts will be blank. Also, rrd4j can only save numerical data so String Items cannot be saved there.
Good post. Out of curiosity @rlkoshak what would be a “best practice” per say for persistence with OH in your mind? MySQL? I’m using it now, and need to tone it down on logging, but was really just curious and wanted to hear your thoughts!
It depends on what you want to do with the data. I use a three tiered rule of thumb.
Tier 1- use MapDB with restoreOnStartup for everything.
Tier 2 - use rrd4j for items I want to chart or need to check its previous state and the like in rules.
Tier 3 - use a “real” database for Items I want to study or analyze or use outside of OH or want to keep accurate data for long periods of time.
I actually do not use Tier 3 in my setup and only have a dozen or so items in Tier 2.
MapDB is good for Tier 1 because it is fixed size and can store non-numerical data. Rrd4j is nice because it is fixed size and data is accurate enough for most home automation purposes. I don’t really do analysis so I don’t know what I’d use for Tier 3. Probably MySQL because of familiarity or InfluxDB because it looks pretty capable.
The major thing driving my approach is to minimize storage requirements and minimize maintenance. With dbs like MySQL you have to periodically purge old data.
You can easily control which Items gets saved where by creating groups and setting up the .persist files to operate on those groups
Where can I see what is happening with the mysql or jdbc:mysql commands being passed? MySQL is on a different machine if that matters.
Here’s what I’ve done:
using mysql:
show databases;
create database dbopenhab;
show databases;
CREATE USER ‘openhab’@‘localhost’ IDENTIFIED BY ‘openhab’;
GRANT ALL PRIVILEGES ON dbopenhab.* TO ‘openhab’@‘localhost’;
And using the paperUI I’ve enabled:
JDBC Persistence MySQL
and my openhab/conf/services/jdbc.cfg file has this in it:
url=jdbc:mysql://192.168.0.205:3306/dbopenhab
user=‘openhab’@‘localhost’ #I’ve tried with and without the apostrophes
password=openhab
I have the mysql connector in openhab/addons/
mysql-connector-java-5.1.38.jar
and this in openhab/conf/persistence/jdbc.persist
// persistence strategies have a name and a definition and are referred to in the “Items” section
Strategies {
// if no strategy is specified for an item entry below, the default list will be used
default = everyChange
}
items {
// persist all items once a day and on every change and restore them from the db at startup
// careful, this might result in huge databases - that’s why it is in comment here ;
: strategy = everyChange, everyDay, restoreOnStartup
// persist all temperature and weather values at every change and every hour
// * : strategy = everyChange, everyHour
}
My rrd4j persistence seems to be working fine, but I can’t get this to talk to the mysql database.
What am I missing or what logs can I inspect for connection problems?
I’ve gotten a bit further. The items table with two fields gets created at startup, but no data or additional tables ever show up. When I uncommented a bunch of the optional stuff in jdbc.cfg I got additional errors in the logs. Doesn’t seem like persistence is triggering for jdbc.
I’ve also noticed, that the log is running two hours fast from EDT in my location. Looking for a time zone setting next…
Your approach looks good.
Is there an easy way to include ALL items into MapDB like you suggested above?
I guess I don’t nee to put in one by one?
Thanks.
By using Groups you don’t have to put any Items into the persist files at all and controlling which Items get persisted to which Tier is controlled by Group membership in the Items files.
So you mean i just need to put the groups into the mapdb.persist file like I did in my rrd4j ?
Like the one below? Same syntax? Or is there another more conveniet way to edit it in Paper UI or habmin?
If I put all the same Groups into rrd4j AND mapdb - OH will just store the numerical values in rrd4j and in mapdb the last state of each single item in those groups?
Or do I neccessarily need to create new separate groups for all the items?
// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
// for rrd charts, we need a cron strategy
everyMinute : "0 * * * * ?"
}
Items {
// which data to be stored
G_Harmony*, G_Lights*, G_WLAN*, G_Mobiles*, G_Fritz*, G_Strega*, G_Irrigation*, G_Wetter*, G_Netatmo*, G_Abus*, G_Windows*, G_Melder*, G_Sonne*, G_Wecker*, G_Location_Enzio*, G_Location_Dona*, G_Loc_mqtt_Dona*, G_Loc_mqtt_Enzio*, G_Mobiles*, G_Presence*, G_Homecoming*, G_System*, G_Netzwerk*, G_All_OFF* : strategy = everyMinute, everyChange, restoreOnStartup
}
Yes, or you can just put * to represent ALL Items. If you look at the mapdb.persist file on the link I provided you will see that is what I did.
* : strategy = everyChange, restoreOnStartup
That means save every change to all Items and restore their values on startup.
Every minute is way overkill for MapDB. All you really need is everyChange.
However, if there are Items you want to exclude from restoreOnStartup you will need to list each of your groups separately.
All persist files use the same syntax.
That is correct. MapDB doesn’t have the same limitation to numerical data that rrd4j does.
The main point of the post I linked to above is to create three or four new groups, one for each of the different ways you want to persist. For example:
gRestoreOnStartup
gChart
gHistoric
gAnalysis
Then just put those Groups into your .persist files where appropriate (e.g. mapdb.persist would be gRestoreOnStartup* : everyChange, restoreOnStartup and rrd4j would be gChart* : everyChange, everyMinute and so on). Then just assign your Items to the different groups as needed. If you want to do a blanket restoreOnStartup on all Items you don’t need the first Group name, just put * in the mapdb.persist file.
I’m having a problem that seems related to this thread, but I’m not sure that it is.
I have some MQTT items set up and can send a value to them. It displays the value just fine if I am looking at the page, but it won’t retain it for future display (e.g. if I navigate off the page and come back, it will not be displayed).
Is this a persistence thing? A MQTT QOS thing? I don’t get it.
