InfluxDB doesn't receive new valus after "DROP" this MEASUREMENT

1

I have InfluxDB on Home Assistant.
I needed to delete some not valid data from InfluxDB and I did it by copying the measurement :
SELECT * INTO metrics_clean FROM metrics WHERE value!=0
After that I deleted the old measurement:
DROP measurement metrics.
And than copied the new measurement to the old name:
SELECT * INTO metrics FROM metrics_clean

But after all this , I don’t see new values of this measurement saved in InfluxDB from Home assistant.
Other measurements continue to work properly.

Update : I think maybe because it was created by me , the Home Assistant can’t to write to the MEASURMENT.
I backed up the data by SELECT * INTO metrics FROM metrics_backup
And deleted the "metrics " by DROP measurement metrics.
And after that I saw that the MEASURMENT was created by itself and the data begun to be written in InfluxDB.
Than I copied the metrics_backup back into the exsisted metrics.
But after the copying I saw that the all date turned into mush, all ‘fields’ was duplicated and I paid attention , after several copies of the metrics , the MEASURMENT looks garbadge.

image
image2471×192 21.9 KB

How to copy without field’s duplications and can I repair the MEASUREMENT ?

2

you missed a crucial part in your query… and “lost” all the tags

--This will store everything as field
SELECT * INTO metrics_clean FROM metrics

--This will keep tags
SELECT * INTO metrics_clean FROM metrics GROUP BY *

About how to fix thew data… only manually, the only option I see is to download/fetch all the measurement data, edit them however you want and then insert them back

The easier way to clean the current structure is to drop the measurement, otherwise you will have to delete all the “messed up” data points.

I expect you to have duplicate “column” names, meaning that “col1” exists both as tag and field, or even worst you can have “field” and “field_1”, as you can imagine this will make querying the data harder… and if there was something built on top of those data I expect weird results

1 Like
3

I don’t find any information about “download/fetch all the measurement data” and " insert them back".

Yes , it duplicated many times some columns.
The defected “measurement” I saved with different name with archive only for use.
And the new one was created with correct fields for collect a new data.

THanks.