# Drop corrupted measurements

**URL:** <https://community.influxdata.com/t/drop-corrupted-measurements/7829>\
**Category:** Systems\
**Tags:** influxdb\
**Created:** [December 13, 2018, 11:18am UTC](https://community.influxdata.com/t/drop-corrupted-measurements/7829 "2018-12-13T11:18:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ruudwame](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ruudwame/32/2372_2.png) [@ruudwame](https://community.influxdata.com/u/ruudwame)\
**Post date:** [December 13, 2018, 11:18am UTC](https://community.influxdata.com/t/drop-corrupted-measurements/7829/1 "2018-12-13T11:18:50Z")

</div>

We use Influxdb to store system metrics collected with Telegraf. Unfortunately some systems cause corrupted data to be send/inserted, which causes corrupted measurements to be created. (Similar to [Influxdb corrupted measurements created](https://community.influxdata.com/t/influxdb-corrupted-measurements-created/459) and [Metric corruption when using http2 nginx reverse proxy with influxdb output · Issue #2854 · influxdata/telegraf · GitHub](https://github.com/influxdata/telegraf/issues/2854))  
We are still trying to pinpoint the exact cause, but that’s not the point of this topic.

I’m trying to find a way to drop these corrupted measurements. Using the influx cli client I get errors like `ERR: shard 6: proto: invalid UTF-8 string` or `ERR: error parsing query: found \u, expected identifier at line 1, char 21`. I tried escaping the slashes, (i.e. `DROP MEASUREMENT "�\\u0000\u0000\u0000\u0000\u0006;diskio"`), but resulted in the same “invalid UTF-8 string” error.

some examples of these measurement names:

```
"1"
"d\u0000\u0000\u0000\u0000\u0000\u0001system"
"G\u0000\u0000\u0000\u0000\u0000\u0001system"
"M\u0000\u0000\u0000\u0000\u0000\u0001swap"
"name=xvda2"
"������\u0000\bL\u0000\u0001\u0000\u0000\u0000Cdiskio"
"������\u0000\bx\u0000\u0001\u0000\u0000\u00005diskio"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000gprocesses"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000mprocesses"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000Oprocesses"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000{processes"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000}processes"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000�processes"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000\u001fprocesses"
"������\u0000\t�\u0000\u0001\u0000\u0000\u0000Wprocesses"
"�\u0000\u0000\u0000\u0000\u0000\u0001nginx"
"�\u0000\u0000\u0000\u0000\u0000\u0001processes"
"�\u0000\u0000\u0000\u0000\u0000\u0001swap"
"�\u0000\u0000\u0000\u0000\u0006;cpu"
"�\u0000\u0000\u0000\u0000\u0006;diskio"
"�\u0000\u0000\u0000\u0000\u0006;processes"
"\"\u0000\u0001\u0000\u0000\u0000\u0011apt"
"\u0001\b\u0000\u0000\u0000\u0000\u0006;cpu"
"\u0001%\u0000\u0000\u0000\u0000\u0000\u0001mem"
"\u0001\u0019\u0000\u0000\u0000\u0000\u0006;cpu"
"\u0001\u001f\u0000\u0000\u0000\u0000\u0006;cpu"

```

Is there a way to deleted these measurements/series while keeping the rest?

---

<div class="post-metadata">

**Author:** ![LeJav](https://avatars.discourse-cdn.com/v4/letter/l/91b2a8/32.png) [@LeJav](https://community.influxdata.com/u/LeJav)\
**Post date:** [December 15, 2018, 10:43am UTC](https://community.influxdata.com/t/drop-corrupted-measurements/7829/2 "2018-12-15T10:43:28Z")

</div>

Hello,

Here here is what I did to remove the unwanted series.

First, delete all series whose name does not start with a char:

```bash
# echo "show series from /^[^a-z]/" | influx -username xxx -password xxxx -database telegraf

```

check and delete them:

```bash
# echo "drop series from /^[^a-z]/" | influx -username xxx -password xxxx -database telegraf

```

next, I have removed all series with less than 100 entries:

```bash
# echo "show measurements" | influx -username xxx -password xxxx -database telegraf >/tmp/lej1

```

edit file, remove header line and last blank line

```bash
# while read m; do echo -n "$m "; echo "select * from \"$m\" limit 100" | influx -username xxx -password xxxx -database telegraf | wc -l; done </tmp/lej1 >/tmp/lej2

```

check:

```bash
# diff /tmp/lej1 <(awk '{print $1}' /tmp/lej2)
...
# grep "[0-9][0-9][0-9]$" /tmp/lej2

```

you must see all your good measurements

```bash
# grep "[0-9][0-9][0-9]$" /tmp/lej2 >/tmp/lej3
# while read m; do if (! grep -q "^$m " /tmp/lej3); then echo $m; fi; done </tmp/lej1 > /tmp/lej4

# wc -l /tmp/lej1 /tmp/lej3 /tmp/lej4

```

and check that number of lines of lej1 = lej3 + lej4

now, you can delete all measurements in lej4:

```bash
# while read m; do echo "drop measurement \"$m\"" | influx -username xxx -password xxxx -database telegraf; done </tmp/lej4

```

now, it must be good!

```bash
# echo "show measurements" | influx -username xxx -password xxxx -database telegraf

```

---

<div class="post-metadata">

**Author:** ![ruudwame](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/ruudwame/32/2372_2.png) [@ruudwame](https://community.influxdata.com/u/ruudwame)\
**Post date:** [December 18, 2018, 10:12am UTC](https://community.influxdata.com/t/drop-corrupted-measurements/7829/3 "2018-12-18T10:12:13Z")

</div>

Thanks for the extensive guide. I ended up using the the _drop series_ with regex feature to remove the unwanted measurements/series.

```auto
drop series from /[^a-z_]/

```

This cleaned up everything nicely. (Including some measurements that started with a character, but had some unicode stuff in the middle)