OH3: Error trying to retrieve historical data from influxdb

Add-ons Persistence Services
, ,
1

Hello everyone,

ever since I migrated to OH3 (now on OH3.1M2), I am no longer able to retrieve historical data from influxdb (1.8). OH3 does, however, persist all the data into the database.

My Testquery is just a simple query in a rule for testing:

item.historicState(now.minusMinutes(10),"influxdb")

With the binding in DEBUG, I only get:

19:19:18.280 [DEBUG] [e.influxdb.InfluxDBPersistenceService] - Got a query for historic points!
19:19:18.284 [ERROR] [.internal.handler.ScriptActionHandler] - Script execution of rule with UID 'influxtest-1' failed: error parsing query: found 1m, expected identifier at line 1, char 19 in influxtest

Nothing more. Line 1 char 19 does not exist in the rule.
I should mention that my default retention policy is called “1m_for_24h”, which is also defined as such in influxdb.cfg. So that is where that “1m” might be coming from. Could it be a missing escape for the underscores somewhere? The rule/binding worked flawlessly in OH2.x.

I was expecting that setting the binding to DEBUG would give me the actual query that OH3 sends to the database, but that does not seem to be the case…

Any ideas?

2

So, after discovering that there is a TRACE log setting, I found that it indeed is caused by a lack of escaping.

The following query is sent by the binding:

SELECT value FROM 1m_for_24h.item WHERE time <= '2021-03-26T01:41:27.811737Z' ORDER BY time DESC LIMIT 1;

That causes the error. However, the following query works:

SELECT value FROM "1m_for_24h".item WHERE time <= '2021-03-26T01:41:27.811737Z' ORDER BY time DESC LIMIT 1;

I thought it was pretty safe to use underscores in the retention policy names (1m_for_24h is the default policy), as it is within openhab, but obviously not. Is there any way to modify the query so I don’t have to re-do the whole database?

Funny, though, that the binding does not have a problem with the name when persisting items to the database…

Thanks!

3

Appreciate this is an old thread but I’ve just hit this problem, so thought I’d post my resolution.

My default retention policy was “24hours” so even numbers throw a spanner in the works.

I created a new default 24 hour policy with only lowercase characters “twentyfourhours”, then deleted and recreated all by continuous queries.

Updated influxdb.service and all was working again.

4

Interesting, thanks for the follow-up.
I still have my “numbers and underscores” retention name because influxdb does not allow retention policies to be renamed. So as you said, the only way is to create a new one and update all continuous queries that may point to it.
I might actually do that at some point as (without copying all of the data) I would only lose 24h worth of data. To be truly consistent, however, (and one might come up with the idea of trying to retrieve historical data further back from openhab) one would have to re-create the other policies, too…

So I was actually hoping that openhab would fix the issue at some point, but that hasn’t happened yet. Probably way too few people are impacted by it.