My persistence nightmare with SQLite

Using latest OH2 on RPi(3). MQTT running too on same unit and working fine as can see ESP8266 publishing temperature data to MQTT broker. I’ve been trying to get persistence working with SQLite. It seems to be a bit hit and miss. Maybe someone can help me figure out what is going wrong.

I have a test database in /var/libopenhab2/persistence/testSqlite.db

In the /etc/openhab2/service/jdbc.cfg I have:


I have also set this in /etc/openhab2/services/runtime.cfg :


My items file is:

Number Diningroom_Temperature "Dining Room Air Temperature [%.1f °C]" {channel="mqtt:broker:topic:diningroom:temperature" }

My things file is:

Bridge mqtt:broker:mymqtt "My MQTT Broker: Mosquitto"

        Thing mqtt:topic:diningroom "Dining Room Air Sensor" {
                Type number : temperature "Dining Room Air Temperature" [ stateTopic="Home/Ground Floor/Dining Room/temperature"]

Now my persistence files is as follows:

Strategies {
        everyMinute : "0 * * ? * *"
        everyHour : "0 0 * ? * * *"
        everyDay  : "0 0 0 * * ?"

        // if no strategy is specified for an Item entry below, the default list will be used
       default = everyUpdate

Items {
        Diningroom_Temperature : strategy = everyMinute

There is just noway of getting Diningroom_Temperature to persist in the SQLite database. However, if I perform the following in the sequence as described:

  1. Change Diningroom_Temperature to Temperature in the items file
  2. Change Diningroom_Temperature to Temperature in the jdbc.persist file
  3. Delete the testSqlite.db database
  4. Run sudo service openhab2 restart

All of a sudden:

[22:50:57] openhabian@typhon:/var/lib/openhab2/persistence$ sqlite3 testSqlite.db 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
item0001  item0002  item0003  items   
sqlite> SELECT * FROM items;

If I follow the exact same sequence by do the inverse (i.e. Change the Temperature to Diningroom_Temperature), there is just no way I can get the temperature to persist in the database and the items table is empty.

This is a mystery to me … this behaviour is quite buggy in my opinion. Even if I shorten the name from Temperature to Temp it still won’t persist. Maybe I have missed something on my configuration?

EDIT: I just went through the inverse process of changing Temperature to Diningroom_Temperature. This time, though, I added a persistence for the ASTRO binding to record the Sunset_Time once every hour, just to try. After removing the testSqlite.db file and rebooting, the OH2 is recording the sunset time but not the temperature. Put the Diningroom_Temperature back to Temperature and it recording the temperature again. Clueless … it’s as if the item name is being recorded elsewhere and a conflict arises when the item name is changed. May this be the case? This post suggests that clearing the cache should do the job, although it doesn’t seem to have any effect with my installation.

BTW, under ITEMS in PAPER UI, I see the following:

After clearing the cache, the Temperature item should disappear?

Did that solve your SQLite problem?

Indeed it did. Now I undersand persistence a little better.