item.averageSince() + influxdb = "error parsing query: found 48h, expected identifier at line 1"

Setup, Configuration and Use Scripts & Rules
1

Hello,
last night I finally managed to upgrade to the OH3 from OH 2.5.

Most of things are working fine (or are waiting on me to fix them), but I believe I found a bug.
I am using this in a rule:

var Float outsideLight = (outsideLightSensor.averageSince(now.minusMinutes(15), "influxdb") as Number).floatValue`

And its throwing this error:

2020-12-27 12:22:21.436 [ERROR] [internal.handler.ScriptActionHandler] - Script execution of rule with UID 'LightTemperature-1' failed: error parsing query: found 48h, expected identifier at line 1, char 19 in LightTemperature

It seems like the command averageSince() is not respecting, that I am running InfluxDb v1.
I am missing something?

services/influxdb.cfg:

version=V1

# The database URL, e.g. http://127.0.0.1:8086 or https://127.0.0.1:8084 .
# Defaults to: http://127.0.0.1:8086
url=http://192.168.25.3:8086

# The name of the database user, e.g. openhab.
# Defaults to: openhab
user=openhab

# The password of the database user.
password=somePassword

# The name of the database, e.g. openhab.
# Defaults to: openhab
db=openhab

retentionPolicy=48h

persistence/inclufdb.persist:

Strategies {
    everyMinute : "0 * * * * ?"
    everyHour   : "0 0 * * * ?"
    everyDay    : "0 0 0 * * ?"
    default = everyMinute
}
Items {
	Temperatures* : strategy = everyUpdate
    OutsideTemp : strategy = everyUpdate
	PersistEveryUpdate* : strategy = everyUpdate
}
2

I’m having something similar

2020-12-25 08:41:53.437 [ERROR] [ence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service ‘influxdb’: error parsing query: found DEFAULT, expected identifier at line 1, char 19
org.influxdb.InfluxDBException: error parsing query: found DEFAULT, expected identifier at line 1, char 19

DEFAULT is the retention policy…so what has changed?
I do nothing more than call the data from start up…not used in rules or channels

3

Check the default Policy - I rolled mine back so I can’t check but I have set up a test server and the default policy is autogen.

4

Similar issue here. I query influxDB from a rule and get this error here:
[ERROR] [internal.handler.ScriptActionHandler] - Script execution of rule with UID 'gas_meter-3' failed: error parsing query: found DEFAULT, expected identifier at line 1, char 19 in gas_meter

retentionPolicy in influxdb.cfg is set to default - this is intentional as this is the name of the default policy for the database (it was created before influxDB renamed the default retention policy to autogen).

This used to work without issues on 2.5.11.

5

I had the same issue and was able to solve it as follows.

My situation:

  • influxdb_1.7.9
  • retention policy was named “default” in my “openhab” influx database

Steps:

  • Backup influx using influxd backup -portable path-to-backupdir
  • on a second / test machine: restore the backup using sudo influxd restore -portable -database openhab -rp default -newrp autogen path-to-backupdir
  • use retentionPolicy=autogen in influxdb.cfg

If you want to try: be careful! Make backups and never do this on your production DB

Would appreciate if somebody could confirm …

6

Thank you so much, this did the trick for my upgrade from OH2.5.11 to OH3.1.

I suspect my influx database was created with some early influx version (around OH2.0), before it changed its default to autogen. It seems that the retention policy “default” is somehow banned by influxdb, as I could change to random names and it would work, just not with “default”.

After testing on a spare server, verifying with statements in the influx client:

Query SELECT "value"::field,"item"::tag FROM default.SomeOpenHABItem

and getting errors if the string “default” was indeed default, or if it did not match what I stated in -newrp in your command.

When ready, I stopped OH3, did drop database openhab_db in the influx client, then ran the influxd restore command, chnaged retention policy (to autogen) in /etc/openhab/service/influxdb.cfg and restarted OH3. Everything now works.