Analyzer not working with influxdb

fremel75 · July 29, 2021, 10:37pm

Hi,
I need some help understanding what is wrong
I have set persistance to influxdb and i can se data in grafana
But when i open mainui and select analyze i get this error message
.persist is set to every minute
can it be a bug becuse my retentionpolicy is named default?

I have set influxdb to debug here

23:34:15.337 [DEBUG] [e.influxdb.InfluxDBPersistenceService] - Got a query for historic points!
23:34:15.344 [ERROR] [.internal.JSONResponseExceptionMapper] - Unexpected exception occurred while processing REST request.
org.influxdb.InfluxDBException: error parsing query: found DEFAULT, expected identifier at line 1, char 40
        at org.influxdb.InfluxDBException.buildExceptionFromErrorMessage(InfluxDBException.java:161) ~[?:?]
        at org.influxdb.InfluxDBException.buildExceptionForErrorState(InfluxDBException.java:173) ~[?:?]
        at org.influxdb.impl.InfluxDBImpl.execute(InfluxDBImpl.java:827) ~[?:?]
        at org.influxdb.impl.InfluxDBImpl.executeQuery(InfluxDBImpl.java:814) ~[?:?]
        at org.influxdb.impl.InfluxDBImpl.query(InfluxDBImpl.java:714) ~[?:?]
        at org.openhab.persistence.influxdb.internal.influx1.InfluxDB1RepositoryImpl.query(InfluxDB1RepositoryImpl.java:155) ~[?:?]
        at org.openhab.persistence.influxdb.InfluxDBPersistenceService.query(InfluxDBPersistenceService.java:231) ~[?:?]
        at org.openhab.core.io.rest.core.internal.persistence.PersistenceResource.createDTO(PersistenceResource.java:324) ~[?:?]
        at org.openhab.core.io.rest.core.internal.persistence.PersistenceResource.getItemHistoryDTO(PersistenceResource.java:226) ~[?:?]
        at org.openhab.core.io.rest.core.internal.persistence.PersistenceResource.httpGetPersistenceItemData(PersistenceResource.java:176) ~[?:?]
        at jdk.internal.reflect.GeneratedMethodAccessor202.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179) ~[bundleFile:3.4.3]
        at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96) ~[bundleFile:3.4.3]
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:201) ~[bundleFile:3.4.3]
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:104) ~[bundleFile:3.4.3]
        at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59) ~[bundleFile:3.4.3]
        at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96) ~[bundleFile:3.4.3]
        at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:265) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:225) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:298) ~[bundleFile:3.4.3]
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:222) ~[bundleFile:3.4.3]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) ~[bundleFile:3.1.0]
        at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:273) ~[bundleFile:3.4.3]
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:791) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:550) ~[bundleFile:9.4.40.v20210413]
        at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:71) ~[bundleFile:?]
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:602) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1435) ~[bundleFile:9.4.40.v20210413]
        at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:294) ~[bundleFile:?]
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1350) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) ~[bundleFile:9.4.40.v20210413]
        at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:82) ~[bundleFile:?]
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.Server.handle(Server.java:516) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:388) ~[bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:633) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:380) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:383) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:882) [bundleFile:9.4.40.v20210413]
        at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1036) [bundleFile:9.4.40.v20210413]
        at java.lang.Thread.run(Thread.java:829) [?:?]

Wolfgang_S · August 2, 2021, 11:51am

which OH version ( main, minor identifier ) do you use ?
how does your influxdb configuration look like ?
how does your retention policy configuration look like ?

fremel75 · August 2, 2021, 6:44pm

I am currently at 3.2.0-SNAPSHOT - Build #2461
but it was the same in 3.1 release

fredrik@minime5:~$ influx
Connected to http://localhost:8086 version 1.8.5
InfluxDB shell version: 1.8.5
> use openhab
Using database openhab
> show RETENTION POLICIES
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
default 0s       168h0m0s           1        true

influxdb.cfg

url=http://192.168.1.195:8086
user=openhab
password=openhab
db=openhab
retentionPolicy=default

influxdb.persist

// everyChange: persist the state whenever its state has changed
// everyUpdate: persist the state whenever its state has been updated, even if it did not change
// restoreOnStartup:If the state is undefined at startup, the last persisted value is loaded and the item is initialized with this state. This is very handy for all "virtual" items that do not have any binding to real hardware, like "Presence" or similar.
// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
	// for rrd charts, we need a cron strategy
	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 = everyChange
}
Items {
    // persist all items once a day and on every change and restore them from the db at startup
    * : strategy = everyChange, everyDay, restoreOnStartup
    // additionally, persist all temperature and weather values every hour
    g_outtemp*,g_roomtemp*,g_othertemp*,g_pool*,g_humidity*,g_wind*,g_rain* : strategy = everyMinute
}

if i browse openhab:8080/rest/persistence/items/1WireOutsideTempSensor_Temperature?serviceId=rrd4j
i get

{"name":"1WireOutsideTempSensor_Temperature","datapoints":"1440","data":[{"time":1627511700000,"state":"17.1875"},{"time":1627511760000,"state":"17.1875"},{"time":1627511820000,"state":"17.125"},{"time":1627511880000,"state":"17.0625"},{"time":1627511940000,"state":"17.125"},{"time":1627512000000,"state":"17.0625"},{"time":1627512060000,"state":"17"},{"time":1627512120000,"state":"17"},{"time":1627512180000,"state":"17"},{"time":1627512240000,"state":"17"},{"time":1627512300000,"state":"17"},{"time":1627512360000,"state":"17"}
and so on

but if i browse if i browse openhab:8080/rest/persistence/items/1WireOutsideTempSensor_Temperature?serviceId=influxdb

"{\"error\":{\"message\":\"error parsing query: found DEFAULT, expected identifier at line 1, char 40\",\"http-code\":500,\"exception\":{\"class\":\"org.influxdb.InfluxDBException\",\"message\":\"error parsing query: found DEFAULT, expected identifier at line 1, char 40\",\"localized-message\":\"error parsing query: found DEFAULT, expected identifier at line 1, char 40\"}}}"

fremel75 · August 3, 2021, 1:45pm

I tested some more on this
I checked influxdb log for the query openhab use
and then i tested that in influx cli and got the same error as openhab says

fredrik@minime5:~$ influx
Connected to http://localhost:8086 version 1.8.5
InfluxDB shell version: 1.8.5
> use openhab
Using database openhab
> SELECT "value"::field,"item"::tag FROM default.aquarium_Temperature WHERE time >= '2021-08-02T13:14:46.624Z' AND time <= '2021-08-03T13:14:46.623Z' ORDER BY time ASC;
ERR: error parsing query: found DEFAULT, expected identifier at line 1, char 40

So then i used " " around the retention name

fredrik@minime5:~$ influx
Connected to http://localhost:8086 version 1.8.5
InfluxDB shell version: 1.8.5
> use openhab
Using database openhab
> SELECT "value"::field,"item"::tag FROM "default".aquarium_Temperature WHERE time >= '2021-08-02T13:14:46.624Z' AND time <= '2021-08-03T13:14:46.623Z' ORDER BY time ASC;
name: aquarium_Temperature
time                value item
----                ----- ----
1627913294742000000 28.9  aquarium_Temperature
1627913333542000000 28.8  aquarium_Temperature
1627913411658000000 28.9  aquarium_Temperature
1627916843650000000 29    aquarium_Temperature
1627916960561000000 28.9  aquarium_Temperature
...

from influx documentation:

Quoting

Identifiers must be double quoted if they contain characters other than [A-z,0-9,_], if they begin with a digit, or if they are an InfluxQL keyword. While not always necessary, we recommend that you double quote identifiers.

So i guess this really is a bug
Maybe i will circumenvent it by creating a new retention and copy over usefull data

fremel75 · August 3, 2021, 2:28pm

Made a new db with autgen as retention name and this seems to work ok

I was a bit hasty and wrote this:

deleted

but since my items are named with underscore that also qualifies for needing to be double quoted

> select * from autogen.1WireOutsideTempSensor_Temperature
ERR: error parsing query: found .1, expected ; at line 1, char 22

> select * from autogen."1WireOutsideTempSensor_Temperature"
name: 1WireOutsideTempSensor_Temperature
time                item                               value
----                ----                               -----
1627597777932000000 1WireOutsideTempSensor_Temperature 14.75
1627597838763000000 1WireOutsideTempSensor_Temperature 14.8125
1627597899412000000 1WireOutsideTempSensor_Temperature 14.75
1627597959816000000 1WireOutsideTempSensor_Temperature 14.4375
1627598020404000000 1WireOutsideTempSensor_Temperature 14.3125
1627999011021000000 1WireOutsideTempSensor_Temperature 18.4375
1627999071446000000 1WireOutsideTempSensor_Temperature 18.5625
1627999191657000000 1WireOutsideTempSensor_Temperature 18.625
1627999252501000000 1WireOutsideTempSensor_Temperature 18.875

and with items without _

> select * from PoolpumpW
name: PoolpumpW
time                item      value
----                ----      -----
1627999026872000000 PoolpumpW 849.13
1627999202001000000 PoolpumpW 849.15