InfluxDB Error "field type conflict: input field value on measurement is type integer, already exists as type float dropped=1"

Setup, Configuration and Use Migration
1

Hello Together,
first of all, great to see that OpenHab3 is now out and running. So far i am lucky and the upgrade with “openhabian-config” works so far so good. But:

After upgrading i get some InfluxDB error:

field type conflict: input field "value" on measurement "ShellyHT005_lastUpdate" is type integer, already exists as type float dropped=1

This is what my InfluxDB says:
show field keys from ShellyHT005_temperature
name: ShellyHT005_temperature
fieldKey fieldType
-------- ---------
value float

So far its clear whats the problem i think, OH3 is trying to put integer data into a float field. But whats not so clear is: Why?

Before the Update this works an the float value is created by the the Influx Binding itself on first Data-Input.

Do you know how i can fix this?

best regards

Flo

2
5 Likes
3

Sorry, i think i should have searched a little better. Thanks for the hint! :slight_smile:

1 Like
4

Thanks for this lifesaver!

Adding a little hint how to gather a list of items requiring fixing.
After all switch items have complained in the log, run:

grep conflict: /var/log/openhab/openhab.log | cut -d ' ' -f 12 | cut -d '"' -f 2 | sort | uniq

Paste list into @Wolfgang_S’s script.

To get subsequent items, run without sort | uniq

2 Likes
5

I had the same problem and was able to fix it. However, I had to look through a lot of documentation to find bits and pieces of information I needed. So, in order to help others, here’s a summary.

What’s the problem?

There was some change in how certain type of fields are presisted in InfluxDB. Due to that change, the definition of the field in InfluxDB, made by a previous version of OH, doesn’t match the data type that the newer version of OH is trying to put into the database. That results in the data getting lost and an error in the log file. This error typically looks like this:

2021-01-01 13:00:00.660 [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 "GF_Convectorput_Battery" is type integer, already exists as type float dropped=2

	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) [?:?]

Note that chances are that you have this problem for several measurements. Also note that this error message is written to the log everytime OH tries to persist a value into that measurement. So, even if you have the problem only for one value, you still might have multiple errors in your log files.

How to fix it?

Step 1: find all errors in the log

You can use the grep command to search to log files. To do so, log into the machine where OH is running via SSH, and perform the following command:

 grep "field type conflict:" /var/log/openhab/openhab.log*

This will give you all lines in the log files with “field type conflict:” in it. Note that log files are rotated if they get too long. You might have more than one log file, they will have names with numbers appended, like openhab.log.1. That’s why there is a * at the end of the file name; this way, also files names openhab.log.1, etc, will be searched.

Step 2: get a copy-pastable list of measurement names

To get a list of only the measurement names, we need some more bash magic. @OMR assembled a nice oneliner. I present a slightly improved version here:

grep "field type conflict:" /var/log/openhab/openhab.log* | cut -d ' ' -f 12 | cut -d '"' -f 2 | sort | uniq

The pipes | are used to pipe the output of one command to the other. In this case the log lines from the grep command are piped to two cut commands, that cut the start and end of the log line, keeping onlye the measurement name. Than the list is sorted by sort and double values are filtered out by uniq.

Note, as @Masssssy pointed out in a comment below, different operating systems come with different versions of grep. It might be that your output is different, in that case, you might have to fiddle with the parameters of the cut commands to get it right. The commands given here are tested on the latest openHABian.

Step 3: create a script

Now you have to create a script that issues an InfluxDB query for every measurement, that fixes the problem. Wouter Born posted a bash script in this Github issue. I copy-pasted it here, for easy reference:

#!/bin/bash

MEASUREMENTS=$(cat << EOF
****insert list of measurements here****
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

Replace ****insert list of measurements here**** with the outcome of step 2.

Step 4: Make sure you can connect to InfluxDB

The script from step 3 uses the influx command to connect to the InfluxDB database. This should work directly if you run the bash script on the machine where InfluxDB is running. This might be the same machine where OH is running, but it might also be another machine. To try if it works, execute the following command:

influx -database 'openhab' -execute "show measurements"

This should give you a list of all measurements in the InfluxDB. If you don’t get a list, refer to the documentation of InfluxDB to see if you can get it to work.

In case you run InfluxDB on another machine, you can also install InfluxDB on your local machine and make a remote connection, in that case, the previous command should look like this:

influx -host <host> -port <port> -username <user> -password <pass> -database 'openhab' -execute "show measurements"

(Replace <host> with the hostname or IP-address of the machine, and also replace <port>, <user> and <pass> with the appropriate values.)

If you figured out the right way to connect to InfluxDB, you might need to adapt the script from step 3 accordingly.

Step 5: Run the script

Save the script in a file and give it a name, e.g. fix-influx.sh. Most of the time you need to give yourself execute rights on this script before you can run it:

chmod ug+x fix-influx.sh

Then, in the same directory, run the script:

./fix-influx.sh

Depending on the number of measurements you have to fix, the script might need some time to run. For each measurement it fixes, it will show something like this on the output:

Updating measurement_xyz...
name: result
time written
---- -------
0    17244

0 17244

That’s it!

18 Likes
6

It’s probably due to different output of grep on different distros or something but the oneliner does not work. It also only searches the latest log file.

On Ubuntu 18.04 this does the trick.

grep conflict: /var/log/openhab/openhab.log* | cut -d ' ' -f 10 | tr -d '"' | sort | uniq

Also note that the item may need to have been switched on/off once or it might not be in any of the logs. Which kind of makes this a big pain if you have a lot of switches, some who might just trigger rarely and are hard to trigger manually.

7

@bartkummel

I, as a newbie, was able to customize my influxdb with this top tutorial.

thank you very much!

1 Like
8

Thanks for pointing that out! I’ve adapted my post slightly.

1 Like
9

Yes, Thanks for that post. It would have been impossible for me to get this done without it.

10

Is your switch logged as on/off or as 1/0?

11

Me???
The switches are defined as items e.g.:

Switch PIR_Out_SudOst				"PIR Out SüdOst"			{ channel="knx:device:bridge:generic:PIR_Out_SudOst" }

and go to influxdb:

	PIR_Out_SudOst						: strategy =  everyUpdate, everyDay

So, I think they are On/Off, but in Grafana they appear as 0/1

12

Hi,

I have the same issue. I‘m already on influxdb 2.0.3. Does anybody know how to change the data types with this release?

Best Regards,
Marcel

13

Dear all,

I stumbled over the same problem after updating my 2.5 installation to OH3, and was able to solve the problem by the information given in this thread. As the measurements affected by the problem show up in the logs only step by step, I now use the following script to fix problems when they occur. It searches all logs but checks the datatype of the measurements it finds and only takes care of those which are still of type “float”, so you can run it multiple times:

#!/bin/bash
cat /var/log/openhab/openhab.log* | grep "field type conflict:" | cut -d ' ' -f 12 | cut -d '"' -f 2 | sort -u | while read a
do 
	echo -n $a:
	influx -database openhab -execute "show field keys from $a" | grep value | awk '{ print $2 }' 
	influx -database openhab -execute "show field keys from $a" | grep -q float
	[ "$?" -eq 0 ] && influx -database openhab -execute "SELECT value::integer INTO temp FROM $a;DROP MEASUREMENT $a; SELECT value::integer INTO $a FROM temp; DROP MEASUREMENT temp;"
done
8 Likes
14

This script works great. I only needed to add connection options (username, password etc.) to the influx call and now it runs like a charm. Thanks for sharing this script.
:grinning:

15

Very well written tutorial!

16

Spectacular post. Thank you. Worked like a charm

17

Thanks for the tutorial!

Some things to mention:
1. greping the logs
This can be a way to go in first step - but this won’t solve all your problems. This will only solve the problems for items, that already triggered the error. This will not solve the problems for items, that will trigger the error in future (like some not so often changing items).
It would be better do grep your *.items files for Switch and Contact items that get persisted.

2. InfluxDB in Docker
If you run your InfluxDB in a docker container, the influxdb command line will not work. You will have to adjust it to:
docker exec -it <name of your infuxdb container> influx <parameters>

3. InfluxDB 2
If you already upgraded to InfluxDB 2, the influxdb command will not work as expected, as syntax has changed massively.
e.g. the show measurements query will have to look like this:

import "influxdata/influxdb/schema"
schema.measurements(bucket: "openhab2/autogen")

Don’t yet know how to rewrite the other queries to get this solved.

18

So finally for InfluxDB 2 I found a way to do it, basically use this script:

#!/bin/bash

DATABASE=openhab2/autogen
TEMPDB=openhabinfluxdbmigratetemp
ORG=yourOrg
TOKEN="PleaseInsertTokenHere"

INFLUX=influx                                            # use this for regular influx installation
#INFLUX="docker exec -it <nameOfInfluxContainer> influx" # use this for docker installation, adapt to your influx docker container name

MEASUREMENTS=$(cat << EOF
****insert list of measurements here****
EOF
)

$INFLUX bucket create --name $TEMPDB --org $ORG --token $TOKEN

for measurement in $MEASUREMENTS; do
        echo "Updating $measurement..."

        $INFLUX query 'from(bucket: "'$DATABASE'") |> range(start: 1970-01-01T00:00:00Z, stop: now()) |> filter(fn: (r) => r["_measurement"] == "'$measurement'") |> filter(fn: (r) => r["_field"] == "value") |> toInt() |> to(bucket: "'$TEMPDB'", org: "'$ORG'")' --org $ORG --token $TOKEN

        $INFLUX delete --bucket $DATABASE --start '1970-01-01T00:00:00Z' --stop $(date +"%Y-%m-%dT%H:%M:%SZ") --predicate '_measurement="'$measurement'"' --org $ORG --token $TOKEN

        $INFLUX query 'from(bucket: "'$TEMPDB'") |> range(start: 1970-01-01T00:00:00Z, stop: now()) |> filter(fn: (r) => r["_measurement"] == "'$measurement'") |> filter(fn: (r) => r["_field"] == "value") |> to(bucket: "'$DATABASE'", org: "'$ORG'")' --org $ORG --token $TOKEN

        # could be dropped for performance reasons
        $INFLUX delete --bucket $TEMPDB --start '1970-01-01T00:00:00Z' --stop $(date +"%Y-%m-%dT%H:%M:%SZ") --predicate '_measurement="'$measurement'"' --org $ORG --token $TOKEN
done

$INFLUX bucket delete --name $TEMPDB --org $ORG --token $TOKEN

Configure your database name and insert a token with access (InfluxDB WebGUI → Data → Tokens → click token → Copy to Clipboard)

If you run InfluxDB in a Docker container, uncomment line 9 and insert your InfluxDB Docker container name.

There’s only one downside: This script is very chatty, as it outputs every database entry twice - thus you won’t see errors that (hopefully don’t) happen.

1 Like
19

Does this DROP MEASUREMENT mean that all the data is being lost?

I can see that this is a clean way of fixing the problem - but I am rather attached to the past 4 years of data and would like to keep it if possible.

I see that there is an influxdb command toInt
( toInt() function | Flux 0.x Documentation )
I am not sure if this would exponentially complicate the matter…
I have non-existent influxdb experience, and minimal MariaDB/mysql experience. I imagine what would be needed would be duplicating the database, dropping the given measurement, then moving over the floats with a toInt command to the new database… Likley very cumbersome - thus I had been hoping someone would have done this already out there!

Cheers!

20

@Wolfgang1966
Looking closer at this script, it appears to be doing something similar - copying the value to a temp value, dropping the measurement, recreating the measurement, and pasting it from temp into the new measurement - however I don’t see any convert / toInt-type command. Perhaps this is implied in the value::integer.

I suppose I’m just nervous with my data.