I’m currently using rrd4j and my.openhab for persistence. rrd4j only storing numbers is a huge limitation IMO and I’m noticing the connection to my.openhab drops out from time to time and sometimes, although infrequent, i notice the current state of an item on my.openhab does not match the actual state of the item.
With that said i’m planing on switching persistence to JDBC. Of the supported databases i’m trying to figure out which database is optimal and what others experiences are?
At the moment i’m torn between MySQL and SQlite. I’d rather MySQL but i’m concerned about loading up my openhab VM with MySQL server. Also, when the system starts do I now have to stagger the two services loading somehow so the MySQL instance loads before openhab? Although I do have an existing MySQL server on the network, I’m worried about what happens if I ever have to reboot it but not openhab, Will network latency affect the speed at which states are stored and ultimately rules can be triggered. For that matter I’m now down to just one application that still uses the MySQL server and it’ll be going away shortly anyway. 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.
I figured with SQlite it’s just a db file sitting on the file system locally without the overhead of a DB server. However I’m not really savvy in SQLite and i’m under the impression that it’s not as flexible as MySQL.
For the sake of conversation there are all the other databases that JDBC supports…
Does anyone care to share their experiences with JDBC?
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).
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.
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.
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.
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.
