My persistence nightmare with SQLite

[geotux (Antonio)]

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:

url=jdbc:sqlite:/var/lib/openhab2/persistence/testSqlite.db

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

org.eclipse.smarthome.persistence:default=jdbc

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"
[
    host="192.168.2.76",
    port=1883,
    secure="AUTO",
    username="OH2",
    password="PASSWORD"
]

{
        Thing mqtt:topic:diningroom "Dining Room Air Sensor" {
        Channels:
                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;
1|Temperature
sqlite> 

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?

[vzorglub (Vincent Regaud)]

@geotux

After clearing the cache, the Temperature item should disappear?

[SOLVED] Getting rid of orphan items

In this post I describe the nightmare that I have been going through the last week or so with trying to set up a working persistence with SQLite. The details can be found in that post but in short, when I rename my MQTT item from Temperature to Diningroom_Temperature, OH2 stops logging the MQTT temperature to the SQLite database. As soon as I rename it back to Temperature, it starts logging again. There is only one line in my *.item file as such: Number Diningroom_Temperature "Dining Room Air…

Did that solve your SQLite problem?

[geotux (Antonio)]

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