Good Tutorial On Persistence (RRD4J and JDBC-MYSQL)?

I’m still pretty new to this…

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 most complete documentation for persistence is the wiki.




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.


Thanks Rich - I’ll give it another shot and look at those tips. If I can’t get over the hump, I’ll post some config file snippets…


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! :slight_smile:

Thanks Rich

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


Thanks Rich and Rob - great conversation.

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:

user=‘openhab’@‘localhost’ #I’ve tried with and without the apostrophes

I have the mysql connector in openhab/addons/


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’m no expert on ON2 nor have I actually set up MySQL. I’m afraid I have no advice to offer.

I think you can put MySQL into a mode where it logs all the commands set to it.

Thanks Rich,

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…

Make sure that you have enabled NTP on both the OH server, and MySQL box if they are separate, and of course set the right TZ :wink:

Thanks Rob.

The pi’s date is correct, but the OH log is off by 4h.

pi@pi-openhab:~/openhab/userdata/logs $ cat openhab.log

2016-04-01 11:20:00.287 [INFO ] [.eclipse.smarthome.model.script.Test] - successful
pi@pi-openhab:~/openhab/userdata/logs $ date
Fri 1 Apr 07:20:18 EDT 2016

the MySQL server is in a VM and seems to drift even with NTP running. Not sure why.

Hi @rlkoshak

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?

See the following:

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:


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.

1 Like

Thanks for your detailed desciption.
That helps a lot to setup a new (and better) persistence handling than I used to have.

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.

Which openHAB, 1.8 or 2.0?

This is a sitemap thing and not related to persistence or MQTT.

It is OH2, and this thread seems to be mostly about persistence. Maybe you are replying to multiple threads tonight, in the helpful way you always do?

Oops - I just reread and clearly misunderstood. Yes, my issue may be a sitemap problem, as it doesn’t seem to be able to get the value of the item.

and I figured it out. I think.