Update InfluxDB PersistenceService to 0.9 once the Java API is published

[couchounou]

Hello,

InfluxDB 0.9 Is released,
I can't achieve to make it work with openhab 1.7.0

2015-06-17 15:23:01.970 [ERROR] [i.i.InfluxDBPersistenceService] - database connection failed throwing an exception
2015-06-17 15:23:02.000 [ERROR] [i.i.InfluxDBPersistenceService] - database error: null
2015-06-17 15:23:02.028 [ERROR] [i.i.InfluxDBPersistenceService] - database connection does not work for now, will retry to use the database.

Relating to issue #2420, Is there any plan having 0.9 working ?

[theoweiss]

The InfluxDB 0.9 java api is not yet released: influxdata/influxdb-java#24 (comment)

[teichsta]

I've changed the title of this issue. We could leave it open as reference. It would be great if you all could have an eye on the Java API developments. Thanks, Thomas E.-E.

[itn3rd77]

As far as I can see the the InfluxDB 0.9 java api was released.
https://github.com/influxdb/influxdb-java

Hope you can jump right in 😄

[theoweiss]

Thanks for the hint. I'll give it a try this weekend. Unfortunately the api is not compatible with influxdb <0.9.

[theoweiss]

I started diving into influxdb 0.9.
Oops: docs are in alpha state, influxdb-java is not yet released, unlike stated in the Readme.md :-(( . Nevertheless I compiled the java api on my own and gave it a try. There are lots of api changes and the api is not backwards compatible with influxdb < 0.9. Furthermore there is not yet a migration path from versions. Therefore I decided to put the influxdb 0.9 support into its own plugin. The plugin is named org.openhab.persistence.influxdb1. As a result users with an influxdb <0.9 installation can use the "old" org.openhab.persistence.influxdb bundle and are not forced to upgrade.
You can find my current devs on this in the influxdb-09 branch here: https://github.com/theoweiss/openhab/tree/influxdb-09/bundles/persistence/org.openhab.persistence.influxdb1
Stay tuned, things will need some time.

[theoweiss]

influxdb-java 2.0 is still not released: influxdata/influxdb-java#37 . I think the new binding won't make it into 1.7.1 :-((

[m4ci3k2]

@theoweiss
it seems that the version you've commited doest not build. The offending line is https://github.com/theoweiss/openhab/blob/influxdb-09/bundles/persistence/org.openhab.persistence.influxdb1/java/org/openhab/persistence/influxdb1/internal/InfluxDBPersistenceService.java 415, and commenting it out fixes this. Otherwise works for me :-).

[theoweiss]

@m4ci3k2 the code is quite unfinished, good to know that it works so far.
The release of influxdb-java 2.0 is still in the pipeline as you can see here: influxdata/influxdb-java#37 (comment) . As far as I know there were some other api changes and a bunch of bugfixes since I took the snapshot. Maybe I have to rework some parts due to these changes. I'm on holiday for the next weeks and hopefully will start to work on the binding again in the mid of September.

[theoweiss]

I've just uploaded a first version of the influxdb1 persistence service to my bintray repo. You can download it from this url:
https://bintray.com/artifact/download/theoweiss/generic/org.openhab.persistence.influxdb1-1.8.0-SNAPSHOT.jar

The service is name influxdb1. Therefore use these settings in openhab.cfg:
persistence:default=influxdb1
influxdb1:user=openhab
influxdb1:password=openhab
influxdb1:db=openhab

and influxdb1.persist for the persistence configuration file.

I appreciate your feedback.

Thanks,
Theo

[m4ci3k2]

I'll test it and let you know around Thursday afternoon.

[theoweiss]

@m4ci3k2 thanks, that's great. Can you also check if restoring of values works as expected.

[couchounou]

Tested for several days. Look like to work well !!
Thanks for this great work !

Le 19 oct. 2015 à 00:22, Theo Weiss notifications@github.com a écrit :

I've just uploaded a first version of the influxdb1 persistence service to my bintray repo. You can download it from this url:
https://bintray.com/artifact/download/theoweiss/generic/org.openhab.persistence.influxdb1-1.8.0-SNAPSHOT.jar

The service is name influxdb1. Therefore use these settings in openhab.cfg:
persistence:default=influxdb1
influxdb1:user=openhab
influxdb1:password=openhab
influxdb1:db=openhab

and influxdb1.persist for the persistence configuration file.

I appreciate your feedback.

Thanks,
Theo

—
Reply to this email directly or view it on GitHub.

[adamotte]

Influxdb-java 2.0 is out. Read @rossmcdonald last comment influxdata/influxdb-java#37

[theoweiss]

Just in time �👍

[theoweiss]

I've uploaded a newer version of the jar, which should fix HSBType handling and adds support for PointType. It is available from here:
https://bintray.com/artifact/download/theoweiss/generic/3/org.openhab.persistence.influxdb1-1.8.0-SNAPSHOT.jar

[dohnalj]

Hi Theo, I've also tested for several days and everything works fine except one thing. I have several charts on my sitemap. It seems like the first value in every chart is kind of different than it should be. The line should be smooth - it's outside temperature - but there is a big step at the begining.

Thanks a lot
Jan

[theoweiss]

Hi Jan,

unfortunately I don't know how the charting works. Could first have a look into the database and check if the values are stored correctly in the db?

Regards,
Theo

[dohnalj]

Yes, they are. Storing values works perfectly. And when I look at the same chart but for the whole week, it is perfectly smooth.

[theoweiss]

May be we can get some insights having a trace log. Could you enable trace logging for influxdb by adding this line to your logbook.xml and post the log output?

<logger name="org.openhab.persistence.influxdb1" level="TRACE" />

[dohnalj]

OK, here you go...

2015-11-04 09:28:06.316 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:264]- got a query
2015-11-04 09:28:06.318 TRACE o.o.p.i.i.InfluxDBPersistenceService[:292]- filter itemname: Teplota_Venku
2015-11-04 09:28:06.320 TRACE o.o.p.i.i.InfluxDBPersistenceService[:293]- filter ordering: DESCENDING
2015-11-04 09:28:06.322 TRACE o.o.p.i.i.InfluxDBPersistenceService[:294]- filter state: null
2015-11-04 09:28:06.325 TRACE o.o.p.i.i.InfluxDBPersistenceService[:295]- filter operator: EQ
2015-11-04 09:28:06.327 TRACE o.o.p.i.i.InfluxDBPersistenceService[:296]- filter getBeginDate: null
2015-11-04 09:28:06.330 TRACE o.o.p.i.i.InfluxDBPersistenceService[:297]- filter getEndDate: Wed Nov 04 08:28:06 CET 2015
2015-11-04 09:28:06.332 TRACE o.o.p.i.i.InfluxDBPersistenceService[:298]- filter getPageSize: 1
2015-11-04 09:28:06.334 TRACE o.o.p.i.i.InfluxDBPersistenceService[:299]- filter getPageNumber: 0
2015-11-04 09:28:06.336 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:345]- descending ordering 
2015-11-04 09:28:06.338 TRACE o.o.p.i.i.InfluxDBPersistenceService[:350]- appending limit 1
2015-11-04 09:28:06.340 TRACE o.o.p.i.i.InfluxDBPersistenceService[:356]- startEntryNum 0
2015-11-04 09:28:06.341 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:358]- query string: select value from Teplota_Venku where  time < 1446622086s  ORDER BY time DESC limit 1
2015-11-04 09:28:06.360 TRACE o.o.p.i.i.InfluxDBPersistenceService[:373]- series Series [name=Teplota_Venku, tags=null, columns=[time, value], values=[[1.446422077619E12, 5.2]]]
2015-11-04 09:28:06.363 TRACE o.o.p.i.i.InfluxDBPersistenceService[:380]- columns [time, value]
2015-11-04 09:28:06.365 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.367 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Mon Nov 02 00:54:37 CET 2015 value 5.2
2015-11-04 09:28:06.370 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:264]- got a query
2015-11-04 09:28:06.372 TRACE o.o.p.i.i.InfluxDBPersistenceService[:292]- filter itemname: Teplota_Venku
2015-11-04 09:28:06.374 TRACE o.o.p.i.i.InfluxDBPersistenceService[:293]- filter ordering: ASCENDING
2015-11-04 09:28:06.375 TRACE o.o.p.i.i.InfluxDBPersistenceService[:294]- filter state: null
2015-11-04 09:28:06.377 TRACE o.o.p.i.i.InfluxDBPersistenceService[:295]- filter operator: EQ
2015-11-04 09:28:06.379 TRACE o.o.p.i.i.InfluxDBPersistenceService[:296]- filter getBeginDate: Wed Nov 04 08:28:06 CET 2015
2015-11-04 09:28:06.381 TRACE o.o.p.i.i.InfluxDBPersistenceService[:297]- filter getEndDate: Wed Nov 04 09:28:06 CET 2015
2015-11-04 09:28:06.382 TRACE o.o.p.i.i.InfluxDBPersistenceService[:298]- filter getPageSize: 2147483647
2015-11-04 09:28:06.384 TRACE o.o.p.i.i.InfluxDBPersistenceService[:299]- filter getPageNumber: 0
2015-11-04 09:28:06.386 TRACE o.o.p.i.i.InfluxDBPersistenceService[:350]- appending limit 2147483647
2015-11-04 09:28:06.387 TRACE o.o.p.i.i.InfluxDBPersistenceService[:356]- startEntryNum 0
2015-11-04 09:28:06.389 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:358]- query string: select value from Teplota_Venku where  time > 1446622086s  and time < 1446625686s  limit 2147483647
2015-11-04 09:28:06.403 TRACE o.o.p.i.i.InfluxDBPersistenceService[:373]- series Series [name=Teplota_Venku, tags=null, columns=[time, value], values=[[1.446622116612E12, 1.1], [1.44662274896E12, 1.2], [1.446623010455E12, 1.3], [1.446623796325E12, 1.4], [1.446623961353E12, 1.5], [1.446624370134E12, 1.6], [1.446624728059E12, 1.6], [1.446624841181E12, 1.7], [1.446625107864E12, 1.7], [1.446625146792E12, 1.8]]]
2015-11-04 09:28:06.405 TRACE o.o.p.i.i.InfluxDBPersistenceService[:380]- columns [time, value]
2015-11-04 09:28:06.407 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.409 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 08:28:36 CET 2015 value 1.1
2015-11-04 09:28:06.412 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.414 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 08:39:08 CET 2015 value 1.2
2015-11-04 09:28:06.416 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.418 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 08:43:30 CET 2015 value 1.3
2015-11-04 09:28:06.420 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.422 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 08:56:36 CET 2015 value 1.4
2015-11-04 09:28:06.424 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.426 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 08:59:21 CET 2015 value 1.5
2015-11-04 09:28:06.427 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.429 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 09:06:10 CET 2015 value 1.6
2015-11-04 09:28:06.431 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.433 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 09:12:08 CET 2015 value 1.6
2015-11-04 09:28:06.435 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.437 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 09:14:01 CET 2015 value 1.7
2015-11-04 09:28:06.439 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.441 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 09:18:27 CET 2015 value 1.7
2015-11-04 09:28:06.443 DEBUG o.o.p.i.i.InfluxDBPersistenceService[:518]- objectToState found a NumberItem
2015-11-04 09:28:06.445 TRACE o.o.p.i.i.InfluxDBPersistenceService[:398]- adding historic item Teplota_Venku: time Wed Nov 04 09:19:06 CET 2015 value 1.8

The resulting chart is here:

[theoweiss]

It seems like this query gets the oldest entry of the measurement instead of the newest descending from 1446622086s :

select value from Teplota_Venku where  time < 1446622086s  ORDER BY time DESC limit 1

Which version of influxdb are you using?
There seem to be some weird influxdb bugs concerning the ordering:
influxdata/influxdb#4235

My 0.9.4.1 influxdb does not return any values for a similar query, but it should :-((.
May be you can also check the query results using the influx client.

I think we have to wait for influxdb 0.9.5 with tsm1 and hope they fixed this issue.

[rossmcdonald]

@theoweiss Yes, the incorrect ordering is a known issue, and will be resolved with InfluxDB 0.9.5. It's also fixed in the nightlies when using tsm1 if anyone wants to test it out early, but we should have a release candidate available within the next week or so.

[theoweiss]

@rossmcdonald sounds good, thanks.

[smerschjohann]

small issue related to the current influxdb-java implementation: in the latest release, the integer type is not supported, I made a small fix to support that: influxdata/influxdb-java#115

Otherwise all Integer types will be added as floats (and you cannot import integer values from your old influxdb without patching the insert statements)

[theoweiss]

Hi Simon,

I've seen your java-influxdb PR has been merged 👍. Hopefully we soon get a new release of influxdb-java.
As far as I understand it is an issue when upgrading from 0.8 influxdb. It would be great to have a brief upgrade description for our wiki. Would you mind to contribute this piece of documentation?

Regards,
Theo

[smerschjohann]

Yes, it happens if you already have data in your database or want to import them later on. The problem is that influxdb "guesses" the datatype by the first value that is written to the database. In influxdb0.8 we used integer types which is sufficient for a lot of openhab types. In Influxdb 0.9.3 they changed the wire format a little bit which leads to the problem that every value type is added as float type. So we now have a data type mismatch. My change allows us to use integer types again by enabling the feature by "useInteger(true)".

The importer documentation is quiet good, I think it is enough to reference it: https://github.com/influxdb/influxdb/blob/master/importer/README.md

But I guess we need at least this fix otherwise it will likely fail on a lot of data measurements.

[theoweiss]

I already asked the influxdb people when we could expect a patch release.
Thanks for your efforts on influxdb-java!

[dohnalj]

Hi, I have upgraded to InfluxDB 0.9.5 and was looking forward to see that the "ORDER BY DESC" bug was fixed. But now the query
select value from Teplota_Venku where time < 1448635140s ORDER BY time ASC limit 1
returns no data (and they ARE in the database).
When I change "DESC" to "ASC" it returns one row correctly.
On InfluxDB Github I found this:
influxdata/influxdb#4861
So we again have to wait until this is fixed.

[vsaw]

I just gave the influxdb1 addon by @theoweiss a try against InfluxDB 0.9.6.1. While I got data into InfluxDB using this plugin restoreOnStartup is not working for me.

Looking at the traces I see the following

00:08:48.489 [DEBUG] [xDBPersistenceServiceActivator:27   ] - InfluxDB persistence bundle has been started.
00:08:48.499 [DEBUG] [i.i.InfluxDBPersistenceService:103  ] - influxdb1 persistence service activated
00:08:49.127 [DEBUG] [i.i.InfluxDBPersistenceService:170  ] - database status is OK
00:08:49.131 [DEBUG] [.p.internal.PersistenceManager:147  ] - Initializing influxdb1 persistence service.
00:08:49.239 [DEBUG] [i.i.InfluxDBPersistenceService:264  ] - got a query
00:08:49.243 [TRACE] [i.i.InfluxDBPersistenceService:292  ] - filter itemname: All
00:08:49.245 [TRACE] [i.i.InfluxDBPersistenceService:293  ] - filter ordering: DESCENDING
00:08:49.245 [TRACE] [i.i.InfluxDBPersistenceService:294  ] - filter state: null
00:08:49.246 [TRACE] [i.i.InfluxDBPersistenceService:295  ] - filter operator: EQ
00:08:49.247 [TRACE] [i.i.InfluxDBPersistenceService:296  ] - filter getBeginDate: null
00:08:49.248 [TRACE] [i.i.InfluxDBPersistenceService:297  ] - filter getEndDate: null
00:08:49.249 [TRACE] [i.i.InfluxDBPersistenceService:298  ] - filter getPageSize: 1
00:08:49.250 [TRACE] [i.i.InfluxDBPersistenceService:299  ] - filter getPageNumber: 0
00:08:49.251 [DEBUG] [i.i.InfluxDBPersistenceService:345  ] - descending ordering 
00:08:49.252 [TRACE] [i.i.InfluxDBPersistenceService:350  ] - appending limit 1
00:08:49.253 [TRACE] [i.i.InfluxDBPersistenceService:356  ] - startEntryNum 0
00:08:49.254 [DEBUG] [i.i.InfluxDBPersistenceService:358  ] - query string: select value from All ORDER BY time DESC limit 1

Trying the query string directly in influx results in

> select value from All ORDER BY time DESC limit 1
ERR: error parsing query: found ALL, expected identifier at line 1, char 19

Looking at InfluxDBPersistenceService.java it seems that /.*/ is the proper wildcard to use. This also works in influx

> select value from /.*/ ORDER BY time DESC limit 1
name: DummyNumber
------------------------------
time            value
1451521097559000000 23


name: DummyContact
-------------------
time            value
1451521096793000000 1


name: DummyNumber2
---------------------------
time            value
1451521097830000000 3.798

Should the check in InfluxDBPersistenceService.java also check against ALL or is my persistence setup faulty?

[theoweiss]

Hi @vsaw,

could you please post your item configuration for "All".

Regards,
Theo

[vsaw]

Hi @theoweiss it's like

Group All
Group gGF (All)

Group GF_Doors (gAll)

Group GF_Room "Room" <bedroom> (gGF)
Contact GF_Room_Door "Door [MAP(en.map):%s]" <door> (GF_Room, GF_Doors)

I removed additional rooms and items from this to not swamp you. However the original grouping structure is the same as in my full *.items file

[pravussum]

'ALL' is a keyword in InfluxDB, can you try renaming your group?
https://influxdb.com/docs/v0.9/query_language/spec.html

[vsaw]

@pravussum Yep that's it!

Looking at #2748 (comment) I noticed I'm using All and gAll by mistake. Switching to gAll works. Here's the renamed items query

08:42:34.837 [DEBUG] [i.i.InfluxDBPersistenceService:264  ] - got a query
08:42:34.838 [TRACE] [i.i.InfluxDBPersistenceService:292  ] - filter itemname: gAll
08:42:34.839 [TRACE] [i.i.InfluxDBPersistenceService:293  ] - filter ordering: DESCENDING
08:42:34.840 [TRACE] [i.i.InfluxDBPersistenceService:294  ] - filter state: null
08:42:34.841 [TRACE] [i.i.InfluxDBPersistenceService:295  ] - filter operator: EQ
08:42:34.842 [TRACE] [i.i.InfluxDBPersistenceService:296  ] - filter getBeginDate: null
08:42:34.843 [TRACE] [i.i.InfluxDBPersistenceService:297  ] - filter getEndDate: null
08:42:34.844 [TRACE] [i.i.InfluxDBPersistenceService:298  ] - filter getPageSize: 1
08:42:34.845 [TRACE] [i.i.InfluxDBPersistenceService:299  ] - filter getPageNumber: 0
08:42:34.846 [DEBUG] [i.i.InfluxDBPersistenceService:345  ] - descending ordering 
08:42:34.847 [TRACE] [i.i.InfluxDBPersistenceService:350  ] - appending limit 1
08:42:34.848 [TRACE] [i.i.InfluxDBPersistenceService:356  ] - startEntryNum 0
08:42:34.848 [DEBUG] [i.i.InfluxDBPersistenceService:358  ] - query string: select value from gAll ORDER BY time DESC limit 1
08:42:34.882 [DEBUG] [i.i.InfluxDBPersistenceService:370  ] - query returned no series

Using All produces the error I mentioned in #2748 (comment)

[vsaw]

Now that openHAB 1.8 has official support for InfluxDB 0.9 can this be closed? I'm using openHAB 1.8 and InfluxDB v0.9 for a couple of days now and it all seems to working as expected (except for the "All" keyword error)

[teichsta]

agreed!