OH3 doesn't seem to read Persistence Data from Influxdb2

Add-ons Persistence Services
, ,
1

Hi,
I am trying to access Persistence data from Influxdb2. I have verified OH3 (3.1.0.M1) is writing to Influxdb2 (2.0.3). From within Influxdb2 I can query data. I have also confirmed I can manually add data using Item.Persist(“influxdb”). Also no data appears on my “Analyze” graphs in OH3, unless I set the Default Persistence to RRD4J.

I have tried the following:

  • Reinstall of the Influxdb addon

  • Tested the following DSL rule with multiple items, it works with RRD4J, but not with Influxdb2.

rule "PersistanceTest"
when
  Item TestSwitch changed
then
  var Number test = Feeder1_Wt.maximumSince(now.minusMinutes(60),"influxdb")
  //var Number test = Feeder1_Wt.maximumSince(now.minusMinutes(60),"rrd4j")
  logInfo("PersistanceTest.rules", test.toString())
end

The log outputs to some of the “influxdb” runs were as follows:

org.openhab.core.persistence.extensions.PersistenceExtensions$1@58bdcbc0
org.openhab.core.persistence.extensions.PersistenceExtensions$1@4cff1120

The expected outputs to some of the “rrd4j” runs were as follows:

2/23/21, 7:52 PM: Feeder1_Wt -> 4882.0
2/23/21, 7:42 PM: Litterbot1Power_W -> 1210.454
  • Tried a number of other rule variations with Influxdb as default and with RRD4J as default.

Below is also pertinent setup from the Influxdb configuration file. OH3 is running on windows, Influx2.0 is running on a virtual Ubuntu machine on the same computer.

// InfluxDB persistence

Strategies {
	default = everyChange
}
Items {
    PetHealthPersistGroup*   : strategy = everyUpdate
    Litterbots*                         : strategy = everyChange, everyHour
}

Does anyone have any thoughts what I am doing wrong or additional troubleshooting?
Thanks for the help!

2

The Items you want to persist are indeed members of the one of PetHealthPersistGroup or Litterbots Group?

The file is named influxdb.persist? It’s a common mistake to leave off the “db” part.

3

Thank you. I did some double checking and yes the Items I am persisting are in the groups referred to in the influxdb.persist file. The persistence file is also name correctly. All good things to check!

I also went into the API Explorer to see if I could learn anything. A pull of RRD4J data yields time/value points. While a pull from Influxdb yields “0” points, although it looks like it gets to the database:

image
image722×488 8.66 KB

Next step I’m working on is trying to figure out a way to test pulling data from the Influxdb2, outside of OH3, to see if the issue is Influxdb2 driven or OH3.

4

You know that an edited xxx.persist file is not used until you restart the service?

5

I did remember that, thanks for bring it up though. I have restarted numerous times during trouble shooting to see if it help towards resolution.

6

Well, I think all the obvious steps are covered. Someone who actually uses InfluxDB2 will have to chime in at this point.

7

My investigation is leaning towards the issue being related to OH3 and not InfluxDB2 or my firewall.

  • Using the Flux VS Code Extension running on the same windows machine as OH3, I am able to query my openHAB InfluxDB2 database and get data returned.
  • I am also able to create graphs with Grafana of the openHAB InfluxDB2 data. Grafana is running on the same virtual machine as InfluxDB2.

Using the same credentials I used above, I still cannot read from the InfluxDB2 database from OH3.

8

It seems it’s not so much “can’t get data”, more like the persistence add-on is returning you some odd reference instead of a number.
Assuming you put the correct version in your influxdb.cfg, looks like a bug.

Have you tried a single access, xxx.historicState()?

9

Trying Item.historicState(), I get a null returned. Although I know the InfluxDB has a data point in that minute:

  var Number test2 = Feeder1_wtChangeNow.historicState(now.minusMinutes(1),"influxdb")
  logInfo("PersistanceTest.rules", test2.toString())

Returned from log:

Script execution of rule with UID 'Home-1' failed: cannot invoke method public abstract org.openhab.core.types.State org.openhab.core.persistence.HistoricItem.getState() on null in Home
10

You shouldn’t need to “hit” a record, historicState() should just seek back in time for the next youngest.

Error message looks a bit more “normal” … but …
While the HistoricItem type object that should be returned does indeed have a .getState() method, you didn’t specify that in your logInfo, bit of a mystery. Looks more like a malformed return than a genuine null miss. Still,it’s going through the motions of retrieval without complaint.
If you are confident the Item data is there (because you can access it by other means) then it still looks like a bug.

11

Thanks Rich!

Hey OH3 users… Is anyone using OH3 with Influx2 and able to read from an Influx2 database? If you are, it would be great to compare settings.

Here is what else I have tried, still no luck:

  • Full fresh test install of OH3 with only InfluxDB persistence add-on -> same read results in both DSL rule and the API Explorer.
  • Installed Influx JavaScript client library (uses Node.js) -> JavaScript outside of OH3 was able to read/write to my influxDB2 openhab database. Unfortunately I have not yet figured out how to integrate this with OH3’s JSR223 scripting. I did some quick unsuccessful tries; I definitely need to search for the required knowledge.
  • I experimented around more with the API Explorer and determined that OH3 is picking up a very few data points from InfluxDB2: ie. 3 of 30,000, etc. I have played with the date range and it always yields the same number of points. On some numeric items it is not picking up anything, although there is data.
    From API Explorer: /persistence/items/
    image
    image1410×702 34.2 KB

From API Explorer: /persistence/items/{itemname}

image
image1418×778 35.1 KB

I am going to try Influx Python client library next to see if I can figure out a workaround to get data from InfluxDB2 back to OH3.

If anyone has other suggestions, please let me know. It is much appreciated.

12

Just upgraded to OH3 and thought I’d upgrade to InfluxDB2 at the same time…
Having exactly the same issue with a historicItem returning null - worked fine on OH2.5 with influxdb 1.
Clearly I’ve upgraded two things at the same time so can’t guarantee where the issue is - but happy to contribute if I can - is there a bug report open?

13

Having a play (warning: I know very little about Java) - it seems to be doing something very odd. It’s returning a null value for the itemName, but is getting the correct value. Because of the null itemName it seems to be coming back as a string when I try to use RestoreOnStartUp judging by this:

2021-04-03 12:41:41.003 [INFO ] [b.internal.InfluxDBStateConvertUtils] - Could not find item 'null' in registry
2021-04-03 12:41:41.004 [ERROR] [org.openhab.core.items.GenericItem  ] - Tried to set invalid state 13.44 (StringType) on item Energy_AgilePrice of type NumberItem, ignoring it

So I tested this in my rules:

 var price=Energy_AgilePrice.historicState(ZonedDateTime.now(),"influxdb")
 logWarn("Rules","Retrieved historicitem:"+price.state)
 Energy_AgilePrice.postUpdate(Float::parseFloat(price.state))
 logWarn("Rules","Agile price:"+price.state)

Which produces:

2021-04-03 13:01:23.180 [DEBUG] [.influxdb.InfluxDBPersistenceService] - Got a query for historic points!
2021-04-03 13:01:23.181 [TRACE] [.influxdb.InfluxDBPersistenceService] - Filter: itemname: Energy_AgilePrice, ordering: DESCENDING, state: null,  operator: EQ, getBeginDate: null, getEndDate: 2021-04-03T13:01:23.180299+01:00[Europe/London], getPageSize: 1, getPageNumber: 0
2021-04-03 13:01:23.183 [TRACE] [.influxdb.InfluxDBPersistenceService] - Query from(bucket:"openhab_db/48h")
        |> range(start:-100y, stop:2021-04-03T12:01:23.180299000Z)
        |> filter(fn: (r) => r["_measurement"] == "Energy_AgilePrice")
        |> sort(desc:true, columns:["_time"])
        |> limit(n:1, offset:0)
2021-04-03 13:01:23.204 [INFO ] [b.internal.InfluxDBStateConvertUtils] - Could not find item 'null' in registry
2021-04-03 13:01:23.205 [WARN ] [org.openhab.core.model.script.Rules ] - Retrieved historicitem:12.222
2021-04-03 13:01:23.206 [ERROR] [internal.handler.ScriptActionHandler] - Script execution of rule with UID 'Energy-3' failed: An error occurred during the script execution: Could not invoke method: java.lang.Float.parseFloat(java.lang.String) on instance: null in Energy

12.22 is the correct value, but for some reason it ends up as a StringType - though the reference to null when I try to parseFloat was confusing me.

I now have it sort-of working with:

 var price=Energy_AgilePrice.historicState(ZonedDateTime.now(),"influxdb")
 Energy_AgilePrice.postUpdate(Float::parseFloat(price.state.toString))
 logWarn("Rules","Agile price:"+price.state)

I still get the error about the null itemName but at least it’s working!

All worked fine on OH2.5 with InfluxDB 1 (though I just retrieved the historicState as Number and postUpdate’d that.)

14

What makes you think that? So far as I can see, you get a state type object. Various DSL functions operate more or less well on raw state object, and it’s generally best to be explicit about .toString or as Number etc.

15 
var price=Energy_AgilePrice.historicState(ZonedDateTime.now(),"influxdb")
logWarn("Rules","Type:"+price.state.getClass().getName())

returns

2021-04-03 14:05:39.993 [INFO ] [b.internal.InfluxDBStateConvertUtils] - Could not find item 'null' in registry
2021-04-03 14:05:39.994 [WARN ] [org.openhab.core.model.script.Rules ] - Type:org.openhab.core.library.types.StringType

Looking through the influxdb code it appears that it defaults to a StringType if it can’t find the itemName - which for some reason is ending up as null? (I’ll repeat my disclaimer that I don’t know Java)

I’m assuming the reason it does this is line 108 of InfluxDBStateConvertUtils.java where it goes for a StringType if it can’t find the itemName?

16

It is nice to know that the issue I was experiencing is reproducible (and is not just me). I too upgraded to Influxdb 2 at the same time as OH3. As you stated, this creates an issue know where the problem lies. Nice work on your investigation; when I have a moment I hope to reproduce your results. I was (and maybe still am) heading down the route of using a curl command and bypassing the Influxdb binding for reading the database. I know an external curl command can successfully read from the database. Using Git Bash the following command has the following return (which looks correct):

curl --request POST \
http://10.14.56.15:8086/api/v2/query?org=openHAB \
--header 'Authorization: Token {...your token - don't include brackets...} \
--header 'Accept: application/csv' --header 'Content-type: application/vnd.flux' \
--data 'from(bucket:"openHABdata") 
|> range(start: -3h) 
|> filter(fn: (r) => r._measurement == "Feeder1.Wt") 
|> last()'

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   350    0   237  100   113   6771   3228 --:--:-- --:--:-- --:--:-- 10294,result,table,_start,_stop,_time,_value,_field,_measurement,item,label,type
,_result,0,2021-04-03T11:24:05.355281348Z,2021-04-03T14:24:05.355281348Z,2021-04-03T14:21:36.279Z,2744,value,Feeder1.Wt,Feeder1_Wt,Feeder 1 Mass,Number:Mass
17

You can do it direct from OH3 (example from when I wanted to do something more complex - I’m using the influxdb v1 compatibility api):

 var ZonedDateTime tomorrow=now.plusDays(1).truncatedTo(ChronoUnit.DAYS).plusHours(7).plusMinutes(59)
 var String query='SELECT bottom("value",'+((Energy_PPHours.state as Number)*2).intValue+') FROM "Energy_AgilePrice" WHERE time>now() AND time<'+tomorrow.toInstant().toEpochMilli().toString+'ms'
 var headers=newHashMap("Authorization" -> "Token [TOKEN]")
 var json=sendHttpGetRequest("http://localhost:8086/query?db=openhab_db&rp=48h&precision=ms&q="+URLEncoder::encode(query),headers,2000)

Though it looks like (at least in my case) the native historicItem interface works acceptably - just with the need to parse the string, and the ‘null’ error in the log every time the rule runs.

18

Thanks Tom. Your input looks like the piece I was missing; I think you just saved me a bunch of time! I am looking forward to trying it out using Flex statements. I will post back when I have something working.

19

Regarding the issue of OH3 not seeming to read data from Influxdb2, it appears the 3.1.0.M3 update has fixed the issue.

This morning (before I updated), I was doing additional investigation (using logging TRACE level on influxdb). I discovered that the Flux statement was requesting the item name from InfluxDB with an “_”, instead of a “.” . I had selected “Replace Underscore” in the Influxdb settings. This is why it was not reading (or populating Analyze Graphs).

Incorrect (before update):

10:14:22.001 [TRACE] [e.influxdb.InfluxDBPersistenceService] - Query from(bucket:"openHABdata")
        |> range(start:-100y)
        |> filter(fn: (r) => r["_measurement"] == "Feeder1_Wt")
        |> sort(desc:true, columns:["_time"])
        |> limit(n:1, offset:0)

Correct (After update):

10:14:22.001 [TRACE] [e.influxdb.InfluxDBPersistenceService] - Query from(bucket:"openHABdata")
        |> range(start:-100y)
        |> filter(fn: (r) => r["_measurement"] == "Feeder1.Wt")
        |> filter(fn: (r) => r["item"] == "Feeder1_Wt")
        |> sort(desc:true, columns:["_time"])
        |> limit(n:1, offset:0)

A big thanks to whoever found and corrected the issue!

20

Hello,
I’m on 3.1.0M3 with influxdb2 and having issues with historicState as well.

maximum/minimum Since is working, but this is making bit of pain for now

val lastYear = now.getYear - 1

val lstItem  = ScriptServiceUtil.getItemRegistry.getItem(lstItemName.name)
val last = ((lstItem.historicState(now.withYear(lastYear).toInstant).state) as Number).floatValue

logInfo("x", last.toString)

do I have wrong date somehow? it looks fine when I logInfo now.withYear(lastYear).toInstant …

getting this error:

2021-05-03 15:28:51.014 [ERROR] [internal.handler.ScriptActionHandler] - Script execution of rule with UID 'weather-2' failed: Could not invoke method: org.openhab.core.persistence.extensions.PersistenceExtensions.historicState(org.openhab.core.items.Item,java.time.ZonedDateTime) on instance: null in weather