[SOLVED] MySQL vs JDBC MySQL (and restoreOnStartup)

Add-ons Persistence Services
1

What’s the difference between MySQL and JDBC MySQL Persistence, and which one should I be using, if they are functionally equivalent.

I’ve just started playing with persistence, mostly for the purposes of charting power consumption, and eventually for the purpose of identifying usage patterns of various Z-wave devices (so I am interested in saving all history, not just the most recent value of each item). This seems to be working fine for the most part, though I am having issues with restoreOnStartup (tried using MapDB for that… still no luck… but I still need to research that more, and is for discussion in another topic… I’d love to use just one persistence service).

2

I tried using the MySQL in OH2 - but couldn’t get it working correctly… switched to the JDBC with MySQL, works like a charm :slight_smile:

3

I am using MySQL with OH2 and it is working just fine. I choosed MySQL bacause i used it in my old OH1 setup. Now I can use old persisted data in OH2.

4

Looking at jdbc.cfg that gets automatically dropped in the services directory when installing the JDBC MySQL persistence service via Paper UI, it has become clear to me that JDBC MySQL is newer than the MySQL service… maybe it’s the way forward for new installations of OH2 that are not migrating…

My installation has always been OH2, but having just started using persistence, I was not sure which to use. I’ve successfully migrated everything over to JDBC MySQL, but unfortunately, restoreOnStartUp is still not working… I think I found out what I was looking for with this topic. I’ll start a new one for the restoreOnStartUp issue if my searches don’t uncover anything useful.

5

I use mySQL to store lots of time series data, anything I want to see a history of, I’ll store in mySQL. However, for restoreOnStartUp, it makes more sense to use the mapDB database service, since it only stores the last value per item. This way, you don’t flood your mySQL database with entries you’ll never need to know the history of, whilst maintaining a reliable restoreOnStartUp. There is also nothing wrong with storing the state of the item in both databases.

6

Thanks @Benjy. That’s actually where my research led me, and I tried persisting to both MapDB and JDBC (and set MapDB as the default), with only mapdb.persist specifying restoreOnStartup., and still, nothing was restored when I stopped and started OH2. I even went as far as to ONLY configure MapDB, and got rid of JDBC all together… still no success.

Also, to be clear, when I say that items are not restored on Startup, I’m ignoring all of the unitialized / null object state complaints from my rules, and looking directly at the state of the items in the rest interface. For example, I set Sunset once every 24 hours by POSTing to http://openhab2:8080/rest/items/time_sunset, and if I send a GET request to the same URL after restart, it’s state stays NULL, no matter how long I wait or how many times I hit that URL. Once I get these items to restore, then I will worry about using workarounds in my rules, if it turns out that I need them.

I must be missing something in my configuration, but it’s not terribly obvious.

7

Do you get any errors in the logs concerning persistance, using time_sunset as your example, what does your mapdb.persist look like?

8

well, my current state is using only JDBC, but when I had oinly MapDB configured, it’s mapdb.persist looked like the following:

Strategies {
  default = everyChange
}

Items {
  * : strategy = default, restoreOnStartup
}

When I had both MapDB and JDBC, mapdb.persist was the same as above, and jdbc.persist looked like the following:

Strategies {
  default = everyUpdate
}

Items {
  * : strategy = default
}

Right now, with only JDBC, jdbc.persist looks like the following:

Strategies {
  default = everyUpdate
}

Items {
  * : strategy = default, restoreOnStartup
}

I tried cranking up the logging to DEBUG for org.openhab.persistence and org.openhab.persistence.<provider>, and did not see anything obvious, but then again, I haven’t tried this recently since changing things around. I don’t want to turn off JDBC, because it’s persisting the historical data that I want (really don’t want to lose my power consumption history), but I’d be happy to troubleshoot just the JDBC persistence provider, added MapDB back in, or stand up a clean testing instance with only MapDB and one or two Items for testing (this was what I planned on doing next, regardless).

9

I’ve not seen default used in the Items config like that. In general, the default strategy is used when you don’t assign anything. Try explicitly stating everyChange, for example my setup for mapdb is:

Strategies {
    default = everyChange
}

Items {
    Group_RestoreOnStartup* : strategy = everyChange, restoreOnStartup
}

I have a lot of items, so I always assign an item to the “Group_RestoreOnStartup” group if it needs restoring on a reset. Otherwise a lot of data will be pushed constantly.

1 Like
10

Thanks @Benjy!

I changed jdbc.persist from:

* : strategy = default, restoreOnStartup

to:

* : strategy = everyUpdate, restoreOnStartup

and restarted OH2… and guess what… restoration of values took place! And, of course, the persisted/restored values are returned when I request the respective item states via the rest api.

For anyone else who might be trying to troubleshoot this kind of thing, before restarting, I enabled DEBUG level logging for org.openhab.persistence and org.openhab.persistence.jdbc, and I saw the following happening in the logs while running tail -f /var/log/openhab2/*. You can clearly see each query to restore the persisted value in openhab.log, immediately followed by the event changing from NULL to the expected value in the events.log.

==> /var/log/openhab2/openhab.log <==
2016-11-22 15:52:57.754 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is time_sunrise
2016-11-22 15:52:57.756 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='time_sunrise_6' item='time_sunrise (Type=NumberItem, State=Uninitialized)' itemName='time_sunrise'
2016-11-22 15:52:57.758 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = org.openhab.core.persistence.FilterCriteria@18dfc30, numberDecimalcount = 3, table = time_sunrise_6, simpleName = time_sunrise
2016-11-22 15:52:57.759 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM time_sunrise_6 ORDER BY time DESC  LIMIT 0,1
2016-11-22 15:52:57.760 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM time_sunrise_6 ORDER BY time DESC  LIMIT 0,1
2016-11-22 15:52:57.766 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '2.4394E7', getClass = 'class java.lang.Double', clazz = 'Double'
2016-11-22 15:52:57.768 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: query for time_sunrise returned 1 rows in 12 ms

==> /var/log/openhab2/events.log <==
2016-11-22 15:52:57.787 [ItemStateChangedEvent     ] - time_sunrise changed from NULL to 24394000

==> /var/log/openhab2/openhab.log <==
2016-11-22 15:52:57.789 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is time_sunset
2016-11-22 15:52:57.790 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='time_sunset_7' item='time_sunset (Type=NumberItem, State=Uninitialized)' itemName='time_sunset'
2016-11-22 15:52:57.791 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = org.openhab.core.persistence.FilterCriteria@429315, numberDecimalcount = 3, table = time_sunset_7, simpleName = time_sunset
2016-11-22 15:52:57.792 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM time_sunset_7 ORDER BY time DESC  LIMIT 0,1
2016-11-22 15:52:57.793 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM time_sunset_7 ORDER BY time DESC  LIMIT 0,1
2016-11-22 15:52:57.796 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '5.8787E7', getClass = 'class java.lang.Double', clazz = 'Double'
2016-11-22 15:52:57.798 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: query for time_sunset returned 1 rows in 8 ms

==> /var/log/openhab2/events.log <==
2016-11-22 15:52:57.800 [ItemStateChangedEvent     ] - time_sunset changed from NULL to 58787000

Thanks again!

1 Like