DROP SERIES not working for some series

JeremySTX · March 31, 2017, 8:03am

I am reworking some unit tests that operate on data in my InfluxDB database.
Some of the unit tests need to delete existing measurements, so the test does a
DROP SERIES FROM <measurement> WHERE <tag>=<value>
before continuing. Mostly that works nicely but not for the ‘battery’ measurement (see below).
The only way I can drop the measurement values for a ‘battery’ series is to drop the measurement ‘battery’ (which deletes all ‘battery’ series, not just the one I want.)

The DROP SERIES command seems to behave properly for all other measurements.

For example,

> show series
key
---
battery,__devhash=7522A1BAB0EA7FDD9870136C23CCA139
batteryinverter,__devhash=f9dd62182e56ddbd7413fadc6178eb17
hybridinverter,__devhash=1028e4ba6cd9673387fc21b342b01e0e
hybridinverter,__devhash=44ab508a0b3b6a4544f6418e4b6f5b21
inverter,__devhash=4785c000644d9b915000a875ba427d4d
inverter,__devhash=FaultTest_1
inverter,__devhash=FaultTest_2
inverter,__devhash=FaultTest_3
inverter,__devhash=I_American_35
inverter,__devhash=I_Australian_34
inverter,__devhash=I_European_33
inverter,__devhash=I_Super_1902
inverter,__devhash=inverter_09e85c9825becdef24ea77f
inverter,__devhash=inverter_1c12f607ad2594cb84d86e9
inverter,__devhash=inverter_b2ba7986052360fec0e0c6d
inverter,__devhash=inverter_d7cd1c9d44a4441b19340bb
inverter,__devhash=inverter_f1f71b2277f74927be497ca
usagemeter,__devhash=71d28883ce1ccd98718fe306c4d68c97
usagemeter,__devhash=USAGE_METER_TEST_1
usagemeter,__devhash=a4deb211596d01b46be4663b6a62d649
usagemeter,__devhash=usagemeter_d76e69c9ecfc4b1a43b7b

> drop series from battery where __devhash='7522A1BAB0EA7FDD9870136C23CCA139'
> 
> show series
key
---
battery,__devhash=7522A1BAB0EA7FDD9870136C23CCA139
batteryinverter,__devhash=f9dd62182e56ddbd7413fadc6178eb17
hybridinverter,__devhash=1028e4ba6cd9673387fc21b342b01e0e
hybridinverter,__devhash=44ab508a0b3b6a4544f6418e4b6f5b21
inverter,__devhash=4785c000644d9b915000a875ba427d4d
inverter,__devhash=FaultTest_1
inverter,__devhash=FaultTest_2
inverter,__devhash=FaultTest_3
inverter,__devhash=I_American_35
inverter,__devhash=I_Australian_34
inverter,__devhash=I_European_33
inverter,__devhash=I_Super_1902
inverter,__devhash=inverter_09e85c9825becdef24ea77f
inverter,__devhash=inverter_1c12f607ad2594cb84d86e9
inverter,__devhash=inverter_b2ba7986052360fec0e0c6d
inverter,__devhash=inverter_d7cd1c9d44a4441b19340bb
inverter,__devhash=inverter_f1f71b2277f74927be497ca
usagemeter,__devhash=71d28883ce1ccd98718fe306c4d68c97
usagemeter,__devhash=USAGE_METER_TEST_1
usagemeter,__devhash=a4deb211596d01b46be4663b6a62d649
usagemeter,__devhash=usagemeter_d76e69c9ecfc4b1a43b7b

> select count(*) from battery
name: battery
time count_Fac count_Pac count_Vac count___saved count_chargeKWh count_eExport count_eImport count_eToday
---- --------- --------- --------- ------------- --------------- ------------- ------------- ------------
0    334       334       334       334           334             334           334           334

> show measurements
name: measurements
name
----
battery
batteryinverter
hybridinverter
inverter
usagemeter

> drop measurement battery
> show measurements
name: measurements
name
----
batteryinverter
hybridinverter
inverter
usagemeter

> show series
key
---
batteryinverter,__devhash=f9dd62182e56ddbd7413fadc6178eb17
hybridinverter,__devhash=1028e4ba6cd9673387fc21b342b01e0e
hybridinverter,__devhash=44ab508a0b3b6a4544f6418e4b6f5b21
inverter,__devhash=4785c000644d9b915000a875ba427d4d
inverter,__devhash=FaultTest_1
inverter,__devhash=FaultTest_2
inverter,__devhash=FaultTest_3
inverter,__devhash=I_American_35
inverter,__devhash=I_Australian_34
inverter,__devhash=I_European_33
inverter,__devhash=I_Super_1902
inverter,__devhash=inverter_09e85c9825becdef24ea77f
inverter,__devhash=inverter_1c12f607ad2594cb84d86e9
inverter,__devhash=inverter_b2ba7986052360fec0e0c6d
inverter,__devhash=inverter_d7cd1c9d44a4441b19340bb
inverter,__devhash=inverter_f1f71b2277f74927be497ca
usagemeter,__devhash=71d28883ce1ccd98718fe306c4d68c97
usagemeter,__devhash=USAGE_METER_TEST_1
usagemeter,__devhash=a4deb211596d01b46be4663b6a62d649
usagemeter,__devhash=usagemeter_d76e69c9ecfc4b1a43b7b

>

jackzampolin · March 31, 2017, 4:14pm

@JeremySTX Is the battery measurement in the same retention policy as all of this other data? Might want to try using all proper quotes:

DROP SERIES FROM "db"."rp"."battery" WHERE "__devhash" = '7522A1BAB0EA7FDD9870136C23CCA139'

Does that work?

JeremySTX · April 3, 2017, 1:08am

Hi Jack,

The DROP SERIES statement objects to including the database name or retention policy:

> drop series from "solarmon"."autogen"."battery" where "__devhash" = 'E28667AC460736D043B91BE57286F23B'
ERR: error parsing query: retention policy not supported at line 1, char 1
> drop series from "solarmon".."battery" where "__devhash" = 'E28667AC460736D043B91BE57286F23B'
ERR: error parsing query: database not supported at line 1, char 1

Using double quotes around the series name and tag name made no difference.

I have noticed something curious - the DROP SERIES seems to be working, but dropping only a few measurement points at a time. For example:

> show series
key
---
battery,__devhash=8F75DE1AA3E3090DD54EFD44484C1B7C

> delete from "battery" where "__devhash" = '8F75DE1AA3E3090DD54EFD44484C1B7C'
> show series
key
---
battery,__devhash=8F75DE1AA3E3090DD54EFD44484C1B7C

> select count(*) from battery
name: battery
time                 count_Fac count_Pac count_Vac count___saved count_chargeKWh count_eExport count_eImport count_eToday
----                 --------- --------- --------- ------------- --------------- ------------- ------------- ------------
1970-01-01T00:00:00Z 816       816       816       816           816             816           816           816

> show series
key
---
battery,__devhash=8F75DE1AA3E3090DD54EFD44484C1B7C

> select count(*) from battery
name: battery
time                 count_Fac count_Pac count_Vac count___saved count_chargeKWh count_eExport count_eImport count_eToday
----                 --------- --------- --------- ------------- --------------- ------------- ------------- ------------
1970-01-01T00:00:00Z 816       816       816       816           816             816           816           816

> drop series where __devhash = '8F75DE1AA3E3090DD54EFD44484C1B7C'
> show series
key
---
battery,__devhash=8F75DE1AA3E3090DD54EFD44484C1B7C

> select count(*) from battery
name: battery
time                 count_Fac count_Pac count_Vac count___saved count_chargeKWh count_eExport count_eImport count_eToday
----                 --------- --------- --------- ------------- --------------- ------------- ------------- ------------
1970-01-01T00:00:00Z 814       814       814       814           814             814           814           814

> select count(*) from battery
name: battery
time                 count_Fac count_Pac count_Vac count___saved count_chargeKWh count_eExport count_eImport count_eToday
----                 --------- --------- --------- ------------- --------------- ------------- ------------- ------------
1970-01-01T00:00:00Z 814       814       814       814           814             814           814           814

> drop series where __devhash = '8F75DE1AA3E3090DD54EFD44484C1B7C'
> select count(*) from battery
name: battery
time                 count_Fac count_Pac count_Vac count___saved count_chargeKWh count_eExport count_eImport count_eToday
----                 --------- --------- --------- ------------- --------------- ------------- ------------- ------------
1970-01-01T00:00:00Z 813       813       813       813           813             813           813           813

>

Each time I run DROP SERIES or DELETE SERIES it deletes a small number of measurement points. That number can sometimes be 0.

The ‘battery’ measurement is the only one of my 6 measurements which exhibits this behaviour. I don’t understand why that should be the case; all measurements are created using the same code (although some are loaded using UDP and some are loaded using HTTP).

jackzampolin · April 3, 2017, 6:20pm

@JeremySTX That is really weird! Sound like it might be an issue. Can you open an issue on InfluxDB with the information here?

JeremySTX · April 4, 2017, 12:33am

I had a closer look at what is going on with my test script and InfluxDB.

The test script writes two ‘battery’ measurements at the current time T (precision = “s”) and then three more at T+1, resulting in two measurements in the database. It then initiates some backend processing which results in the measurement at T+1 being updated twice (i.e. one of the fields is rewritten with a new value). All of this seems to work and the DROP SERIES command has the desired effect afterwards.

The script then goes on to generate a full day’s worth of measurements with timestamps at one minute intervals.
Crucially, the timestamp of the first of these one-minute interval measurements will be somewhat earlier than the measurements written at timestamp T, and the last of those one-minute intervals will be somewhat later than the measurement at timestamp T. In fact they will very likely be several hours in the future (compared to the current time).

It is after the test script has generated the full day’s worth of measurements that the DROP SERIES command fails. it looks very much like DROP SERIES (and DELETE FROM WHERE =) fail when the target series contains timestamps in the future.

Do you still want me to raise an issue in Github, or is the script misbehaving so badly that it’s no surprise InfluxDB breaks in the way I have described?

Regards,
Jeremy Begg

jackzampolin · April 4, 2017, 1:03am

@JeremySTX It looks like you can do this, but the syntax is a little picky. Does this work for you?

JeremySTX · April 4, 2017, 1:25am

Hmm. Yes, DELETE FROM battery WHERE __devhash=.... AND time < '2018-01-01' does delete the data points and I can work with that in the test script.

But there’s no mention of a time restriction in the documentation for the DROP SERIES command so either the documentation needs to be updated or the behaviour needs to be fixed.

jackzampolin · April 4, 2017, 4:41pm

@JeremySTX Thank you for pointing that. I have updated the docs to note that.

JeremySTX · April 5, 2017, 1:10am

I think I’d prefer you fixed the behaviour

Regarding the documentation, I see you’ve updated DELETE but not DROP SERIES. I think both need to mention this limitation. Can I suggest the following wording for DROP SERIES:

DROP SERIES cannot be used to delete a measurement series which contains data points with timestamps in the future. Use DELETE for deleting such measurements.

And for DELETE I think you’ve got your wires crossed - there is no command “DELETE SERIES”. So maybe that note should read,

If you need to delete points with timestamps in the future, you must specify a time period in the FROM clause because DELETE runs for time < now() by default. For example,

DELETE FROM "h20_feet" WHERE "mytag"='tank 1' AND time < '2100-12-31'

And don’t forget to publish this to the web site at https://docs.influxdata.com/influxdb/v1/query_language/manage-database/ when you’re done

jackzampolin · April 5, 2017, 6:01pm

@JeremySTX Our docs are open source. You can open a PR and suggest changes as well.

I’ll try and open this one today.

Topic		Replies	Views
Drop series doesn't work	8	857	December 15, 2020
Drop series by tag not working Store	0	543	September 14, 2018
DROP MEASUREMENT for InfluxDB 2.x? InfluxDB 2 schema	14	17992	July 18, 2023
Writing in batches result in no field values? Drop Measurements? InfluxDB 2	1	461	July 6, 2021
InfluxDB Cloud - Drop Measurement / Column	0	795	February 4, 2021

DROP SERIES not working for some series

Related Topics