JDBC - What's the best database to use for persistence?

[rlkoshak (Rich Koshak)]

See this posting I just made yesterday for a high level approach that I recommend. tl;dr Don’t settle on just one persistence, use multiple ones and use each for the job they are best suited (e.g. MapDB is perfect for restoreOnStartup).

Automation/Orchestration Design Patterns

One more design pattern to add here so I can find it again and reference is future posts. #Group Persistence Problem Statement There are several reasons why a home automation enthusiast would want to use openHAB persistence: charting, using historical Item states in rules logic, restore previous values on openHAB restart, and my.openhab integration, detailed analysis, access to the data with external tools, etc. However, different databases are more or less well suited to each of these use ca…

@sc1982

Also, when the system starts do I now have to stagger the two services loading somehow so the MySQL instance loads before openhab?

Probably but that is trivial. If you are on a systemd based system (e.g. Ubuntu) you just say that openHAB depends on MySQL in the openhab.service file in /etc/systemd/servers (or some path like that). There is a similar way to do it with upstart and init.d but I don’t remember them off the top of my head.

If you decide on MySQL, use the MySQL persistence addon, not the generic JDBC one as it will be much easier to get up and running.

@sc1982

I’m worried about what happens if I ever have to reboot it but not openhab,

I doubt that would be a problem but can’t say for sure. The only reason it is a problem at system start is if you are using restoreOnStartup and the DB is down the persistence engine will be unable to initialize your Items with their previous values and you will end up with a bunch of uninitialized Items which defeats the purpose of restoreOnStartup. But this is one reason why I recommend using MApDB for restoreOnStartup, its embedded so it will always be there, it supports all Item types, and it never grows.

@sc1982

So now i’ll be maintaining another VM just to store item states, is it worth it? I just don’t know about this one.

You might consider a Dockerized MySQL server. I had one running for awhile to support my experiments with ZoneMinder and it was pretty trivial to get up and running and maintain.

@sc1982

Does anyone care to share their experiences with JDBC?

I helped one person try to get SQL Server working through JDBC and he had lots of trouble getting it to work. So I would warn you that it may not be easy or straight forward to get it running.

Personally, to reiterate the linked post above, don’t settle on just one persistence. Use MapDB for restoreOnStartup, rrd4j for numerical Items for which you want recent values for charting or in your rules, and use MySQL or InfluxDB for those Items you want to expose to external tools or are not suitable for rrd4j.

There is no reason you are stuck with just one DB and you should make the DB that requires maintenance (i.e. those that grow forever) contain as few Items as necessary to achieve what you want to do.

That’s my advice at least.