Unable to delete data

1

Hi all,

I’m fairly new to InfluxDB (have version 1.5.1). I use it with Grafana and Icinga2. I’m confused for managing the data, specifically deletion. I have entries “in the future” because my server time got offset and ended up writing data to the database during this period. So, after setting the time back to normal, I tried to execute the following command:

delete from grafana where time > now(), where grafana is the name of my database

The command runs without error, but when I query the HTTP API with the following command, I still see “future” entries:

curl -G 'http://localhost:8086/query?pretty=true' --data-urlencode "db=grafana" --data-urlencode "q=SELECT \"value\" FROM \"SOMECHECK\" WHERE \"hostname\"='SOMEHOSTNAME'"

I tried to edit the delete statement to:

delete from grafana where time > '2018-04-10T14:27:00Z

delete from grafana where time < now() + 24h (saw this in some old thread)

delete from SOMECHECK where time > now()

… but none of the above seemed to do anything.

Perhaps I have the delete functionality completely incorrect? Anyone have some insight?

Thanks in advance!

P.S.
I contacted InfluxDB support and they told me that the worst case scenario was to drop the database and restart. Preferably I wouldn’t like to do this.

Also, I checked these threads (1st thread, 2nd thread) that seemed to have a similar issue to mine but they don’t seem to help too much.

2

Hi,

I suspect (but have not tested) that you need to specify both a start time and an end time in your DELETE statement. For example,

DELETE FROM grafana WHERE time>'2017-04-10T14:27:00Z' AND time<'2019-01-01T00:00:00Z'

It’s also possible that you need to specify some tags in the DELETE for it to work properly.

Note that when I reported a similar problem the situation was slightly different. In my case the server time was correct, but the uploaded measurement data had future timestamps. If I read your query correctly, the server time was wrong, which would lead to timestamps on the database files (shards) being wrong also, but I don’t know if that would affect how InfluxDB manages its data.

1 Like
3

I have also seen problem with DELETE and DROP.
I deleted some series but when I “show series…” it was still there.

After I restarted InfluxDB is was not there, so I had deleted it correctly but maybe it was in the cache or something

1 Like
4

Hello!

Thanks for your replies.

@JeremySTX,
I tried your command and it didn’t seem to work. I also tried to specify tags like you say:

> delete from grafana where "hostname" = 'MYHOST' AND time > '2018-04-11T10:30:00Z'

which didn’t work. I realize that your problem was slightly different, but is in the scope of what I’m trying to do. Do you think I might have to delete the conflicting shard? I can see that these shards have weekly retention when I do show shards.

@flopp,
I tried restarting the influxdb service after trying the delete commands, but that didn’t work either.

By the way, I noticed that these “future” entries are not even showing up in my Grafana graphs. I guess I’m just worried about the trouble in having the future entries might make later on.

Thanks for the help so far!

5

Before I use delete command I show or select the data, to verify that the data really is there.

Have you tried to show with influx cli?

select * from grafana where time > ‘2018-04-11 09:00:00’ limit 10

If you see data then just change to

delete from grafana where time > ‘2018-04-11 09:00:00’

6

Interesting, when I execute the select command, I get no output. Which seemingly indicates that I have no “future” data.

However, if I use the curl command from my initial post to query the HTTP API, I get a ton of data and towards the end I get:


...

                        [
                            "2018-04-11T21:11:13Z",
                            2.38
                        ],
                        [
                            "2018-04-11T21:13:08Z",
                            0.73
                        ],
                        [
                            "2018-04-11T21:15:04Z",
                            0.69
                        ],
                        [
                            "2018-04-11T21:18:33Z",
                            0.72
                        ]
                    ],
                    "partial": true
                }
            ]
        }
    ]
}

At the time of this post, it’s only 11:24 AM but that last entry in the query output shows 9:18 PM.

Theoretically, shouldn’t the delete command be removing these entries?

7

I guess you live in GMT -10, correct?
If you last data in Influx is time stamp 21:18 I guess you can see same data value at 11:18 local time?

It is giving you data as UTC(GMT+0)

1 Like
8

you MUST specify both start and end times … just use an end time that is further in the future than your known errant data

9

Wow, is this actually the case? InfluxDB HTTP API will convert timestamps to UTC (GMT+0)? I would’ve never known.

This means that everything is functioning okay…

1 Like