[influxdb] FieldTypeConflictException: "..." is type integer, already exists as type float

[morph166955]

As part of migrating to OH3 I've added my influxdb persistence. For some reason, I'm now getting this error. I get this across multiple items, not just the one below. I could just purge these values from the database and let it create new as a workaround, but I'd rather not lose the historical data. Not sure why it's writing as an integer now and not a float as before.

2020-11-14 20:32:37.956 [ERROR] [org.influxdb.impl.BatchProcessor    ] - Batch could not be sent. Data will be lost
org.influxdb.InfluxDBException$FieldTypeConflictException: partial write: field type conflict: input field "value" on measurement "ZWave_DownstairsHallwayMotionSensor_BinarySensor" is type integer, already exists as type float dropped=1
        at org.influxdb.InfluxDBException.buildExceptionFromErrorMessage(InfluxDBException.java:144) ~[bundleFile:?]
        at org.influxdb.InfluxDBException.buildExceptionForErrorState(InfluxDBException.java:173) ~[bundleFile:?]
        at org.influxdb.impl.InfluxDBImpl.execute(InfluxDBImpl.java:827) ~[bundleFile:?]
        at org.influxdb.impl.InfluxDBImpl.write(InfluxDBImpl.java:460) ~[bundleFile:?]
        at org.influxdb.impl.OneShotBatchWriter.write(OneShotBatchWriter.java:22) ~[bundleFile:?]
        at org.influxdb.impl.BatchProcessor.write(BatchProcessor.java:340) [bundleFile:?]
        at org.influxdb.impl.BatchProcessor$1.run(BatchProcessor.java:287) [bundleFile:?]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) [?:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:834) [?:?]

[fwolter]

I must admit, I encountered the same problem and worked around it, instead of raising an issue. I changed the type of my historic data to integer by storing it to a temporary measurement:

select value::integer into Flur_Bell_Lock2 from Flur_Bell_Lock
drop measurement Flur_Bell_Lock
select value::integer into Flur_Bell_Lock from Flur_Bell_Lock2

[wborn]

Try updating to M2 or a recent snapshot build so you get the fix for #8798.

[morph166955]

I'm on M2 already. I'm avoiding going to the snapshots at the moment as I believe they had issues with the rules DSL not working properly.

[wborn]

OK then you should have the fix and there is no need to go to snapshots. The DSL issues are also fixed in build # 2010.

I don't know if you've used the initial version of the OH3 influxdb add-on? If so, it may have resulted in unrecoverable issues, see #8831:

If you already used the previous addon version you can still have problems. You need to start with a new database or from a copy of OH 2 database where the 3.0 persistence addon was never used.

[morph166955]

This was a fresh install of OH3 at 3.0.0M2. I opted to create a new VM for my OH3 instead of trying to upgrade from anything. I started at M2. The influxdb was migrated from OH2.

[matulekpl]

If you already used the previous addon version you can still have problems. You need to start with a new database or from a copy of OH 2 database where the 3.0 persistence addon was never used.

Hey, I today upgraded from OH2.5 to 3.0. And I'm getting this influxdb errors. Can You explain me how to repair this?
Step by step?:)

[thewiep]

I'm also experiencing this issue after upgrading from openHAB 2.5.10 to openHAB 3.0 M2.
I've also started converting from float to integer where needed:

select * into Hue_MotionSensor1_Presence_backup from Hue_MotionSensor1_Presence group by item
select time, item, value::integer into Hue_MotionSensor1_Presence_tmp from Hue_MotionSensor1_Presence group by item
drop measurement Hue_MotionSensor1_Presence
select * into Hue_MotionSensor1_Presence from Hue_MotionSensor1_Presence_tmp group by item
drop measurement Hue_MotionSensor1_Presence_tmp

and that seems to work fine.

[matulekpl]

I'm also experiencing this issue after upgrading from openHAB 2.5.10 to openHAB 3.0 M2.
I've also started converting from float to integer where needed:

select * into Hue_MotionSensor1_Presence_backup from Hue_MotionSensor1_Presence group by item
select time, item, value::integer into Hue_MotionSensor1_Presence_tmp from Hue_MotionSensor1_Presence group by item
drop measurement Hue_MotionSensor1_Presence
select * into Hue_MotionSensor1_Presence from Hue_MotionSensor1_Presence_tmp group by item
drop measurement Hue_MotionSensor1_Presence_tmp

and that seems to work fine.

I used this command for every thing that showed errors. there was many of them but now it is ok.

select value::integer into temp from Flur_Bell_Lock
drop measurement Flur_Bell_Lock
select value::integer into Flur_Bell_Lock from temp
dropmeasurement temp

[pailloM]

I am facing this issue too.
It seems the item switches and contacts are stored as float in OH2.5.x and integer in OH3.0.0Mx.
I have to convert the items back and forth every time I go from one version to the other of OH.

[bucofski]

same here

[querdenker2k]

have somebody wrote a script to do this automatic for all items?

[wborn]

On one of my OH instances I also ran into this issue with some Switch items. So I wrote a little Bash script to fix it for several items to prevent errors and tedious work 😉 :

#!/bin/bash

MEASUREMENTS=$(cat << EOF
Room1_Motion
Room2_Motion
Room3_Motion
EOF
)

command="SELECT value::integer INTO temp FROM measurement; DROP MEASUREMENT measurement; SELECT value::integer INTO measurement FROM temp; DROP MEASUREMENT temp;"

for m in $MEASUREMENTS; do
	echo "Updating $m..."
	influx -database 'openhab' -execute "$(sed "s#measurement#$m#g" <<< $command)"
done

Just replace Room1_Motion etc. with your own items that have this issue. Make sure you have a backup in case something goes wrong. 💾

[RBroks]

On one of my OH instances I also ran into this issue with some Switch items. So I wrote a little Bash script to fix it for several items to prevent errors and tedious work 😉 :

#!/bin/bash

MEASUREMENTS=$(cat << EOF
Room1_Motion
Room2_Motion
Room3_Motion
EOF
)

command="SELECT value::integer INTO temp FROM measurement; DROP MEASUREMENT measurement; SELECT value::integer INTO measurement FROM temp; DROP MEASUREMENT temp;"

for m in $MEASUREMENTS; do
	echo "Updating $m..."
	influx -database 'openhab' -execute "$(sed "s#measurement#$m#g" <<< $command)"
done

Just replace Room1_Motion etc. with your own items that have this issue. Make sure you have a backup in case something goes wrong. 💾

So far so good :) But tell please for beginner where you write this? In which file? influxdb.persist?

[matulekpl]

On one of my OH instances I also ran into this issue with some Switch items. So I wrote a little Bash script to fix it for several items to prevent errors and tedious work 😉 :

#!/bin/bash

MEASUREMENTS=$(cat << EOF
Room1_Motion
Room2_Motion
Room3_Motion
EOF
)

command="SELECT value::integer INTO temp FROM measurement; DROP MEASUREMENT measurement; SELECT value::integer INTO measurement FROM temp; DROP MEASUREMENT temp;"

for m in $MEASUREMENTS; do
	echo "Updating $m..."
	influx -database 'openhab' -execute "$(sed "s#measurement#$m#g" <<< $command)"
done

Just replace Room1_Motion etc. with your own items that have this issue. Make sure you have a backup in case something goes wrong. 💾

So far so good :) But tell please for beginner where you write this? In which file? influxdb.persist?

This is BASH script. Read on Google what is this.

[fuetti]

I had the exact same problem on my windows installation. In case anyone needs the powershell script - feel free.

$array = @(
"Item1",
"Item2",
"Item3"
)

Write-Host "Type 'x' to exit."

foreach ($i in $array) {
  $i2 = "$($i)_2"
  Write-Host "Processing: $i ($i2)"
  Write-Host "[SELECT value::integer INTO $i2 FROM $i; DROP measurement $i; SELECT value::integer INTO $i FROM $i2; DROP measurement $i2;]" -ForegroundColor Darkgray 
  ./influx.exe -database 'openhab' -username admin -password secret -execute "SELECT value::integer INTO $i2 FROM $i; DROP measurement $i; SELECT value::integer INTO $i FROM $i2; DROP measurement $i2;"
  Write-Host -Nonewline "Continue? "
  $x = Read-Host 
  if ($x -eq "x") { exit }
}

If you feel good with it - remove the readhost in the loop :-)

But it would really be nice, if we had a better upgrade path from 2.x to 3.x OH with influxdb here. Maybe integrate a kind of update routine in the installation/ update?

[olemr]

Here is a description on how to produce a copy-paste list of items needing fixing. (Linux)

[nsteinmetz]

For people in a InfluxDB2 context:

from(bucket: "<bucket>")
  |> range(start: 2020-01-01T00:00:00Z, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "<measurement>")
  |> filter(fn: (r) => r["_field"] == "value")
  |> toInt()
  |> to(bucket: "temp", organisation: "myOrg"

Drop the bucket <bucket> from the UI or CLI

from(bucket: "temp")
  |> range(start: 2020-01-01T00:00:00Z, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "<measurement>")
  |> filter(fn: (r) => r["_field"] == "value")
  |> to(bucket: "<bucket>", organisation: "myOrg"

Drop the bucket temp from the UI or CLI

[FrankBrandstetter]

I tried to fix influxDB using the bash script from wborn above. But it didn't work well. I have about 100 affected items. I had the following issues:

• The script run multiple hours!
• Migration of some items failed with timout errors. I was able to mitigate this by executing all SQL statements individually instead of in one long command.
• influxdb consumed more and more memory and finally the system (Raspi 4b) broke down due lack of free memoy. A restart of the influxdb service did not help: influxdb immediately consumed up to 80% of the total memory again. I don't know why. The system became unusable.

I've no idea why influxdb consumes so much memory. So my only option seems to be to drop my database and recreate it. :-(

I assume, that I'm not the only one affected by this problem. I'd appreciate if there were be some kind of workaround implemented in openhab, enabling openhab to work with both: floats and integers

[m0rgano]

On one of my OH instances I also ran into this issue with some Switch items. So I wrote a little Bash script to fix it for several items to prevent errors and tedious work 😉 :

Note for Ubuntu users. When you run wborn's script use:

# bash script-name.sh

Otherwise Ubuntu will run dash shell by default and you will get a redirection error. See https://stackoverflow.com/questions/2462317/bash-syntax-error-redirection-unexpected

Also, in addition to adding your specific measurement names at the top of the script, check that the DB name is correct in the 'influx' line. Mine was 'openhab_db' rather than 'openhab'.

To find the Influx DB name for your openHAB instance, start a influx shell and run a query as below:

# influx
> show databases

[openhab-bot]

This issue has been mentioned on openHAB Community. There might be relevant details there:

https://community.openhab.org/t/rules-and-persistence-error/117060/4

[openhab-bot]

This issue has been mentioned on openHAB Community. There might be relevant details there:

https://community.openhab.org/t/speedtest-cli-by-ookla-internet-up-downlink-measurement-integration/94447/100

[alexf2015]

The issue still exists in openhab 3.1.
I just tried to migrate from openhab 2.5.12 to openhab 3.1. At least all my switch items now have that issue.

[Wikibear]

This issue happens if you create a blank complete new database.

[stefan-muc]

Same here, migrating from openHAB 2.5 to 3.1 and discovering this bug.

For people in a InfluxDB2 context:

Thanks, but this doesn't yet work for me on InfluxDB 2.0.8

My query:

from(bucket: "<bucket>")
  |> range(start: 2010-01-01T00:00:00Z, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "<measurement>")
  |> filter(fn: (r) => r["_field"] == "value")
  |> toInt()
  |> to(bucket: "temp", org: "<org>")

Result:

 failed to initialize execute state: failed to look up bucket "temp" in org "<org>"

[nsteinmetz]

@stefan-muc you have to replace the values of <bucket>, <org>, <measurement> by the real names of your bucket, organisation and measurement in your context.

"temp" bucket must be created before the query is executed.

[stefan-muc]

@stefan-muc you have to replace the values

Yeah sure!

"temp" bucket must be created before the query is executed.

Yes, I figured that out. So the steps are:

1. create temp bucket in UI: /load-data/buckets
2. run <measurement> to temp query
3. drop <measurement> in <bucket> (do not drop <bucket> as written above - you will lose all your data)
4. run temp to <measurement> query
5. repeat from 2. for all other <measurement>
6. drop temp

So the only question left for me is: How do I do 3. drop a measurement? Didn't yet find that in UI.

[stefan-muc]

So the only question left for me is: How do I do 3. drop a measurement? Didn't yet find that in UI.

Still didn't find it in UI, but found a CLI line for that:

influx delete --bucket <bucket> --start '2010-01-01T00:00:00Z' --stop $(date +"%Y-%m-%dT%H:%M:%SZ") --predicate '_measurement="<measurement>"' --org <org> --token <token>

[nsteinmetz]

Indeed deleting is only from the CLI

[shutterfreak]

Would it make sense to check persistence settings in such cases to avoid this type of regressions in the future?

In this case, it's about Contact and Switch.