Select * INTO failing with ALL records dropped

1

Using Influx 1.6 version:

This measurement has 3 TAG KEYS and around 8 FIELDS.
Retention policy we are using is 10 days.

Created a new retention policy for 7 days and want to migrate all the data into this retention policy.
We have a database with measurement --> “abc”

So, ran query:
select * INTO "7 Days(s)"."abc" FROM "10 Day(s)"."abc" where TAG_KEY='name' GROUP BY *

This is throwing a message saying “dropped records”.

Instead of using this database, I created new database and inserted 10 days of data into measurement, then SELECT INTO 7 days retention policy. THIS WORKED FINE.

So, we are thinking that something in the “database” definition (or) data in “measurement” causing the SELECT INTO failing in first case. Any suggestions on what might be causing this issue?

2

Hi , have you checked if there was data in the 10 days retention policy older than 7 days before you execute the select into ?

3

Yes, there is data.

I see message saying, XXXX records dropped.

As I debug more on this, I found that it seems one field is migrated into measurement then xxxxx records dropped message. So, it is not like complete SELECT INTO failure.

4

So in both cases records were dropped ? You could alter the 10days retention policy to 7days to achieve the same result without the select into side-effects or do you really need a new retention policy ?

5

I need new retention policy. One workaround at this point I used is, add TIME and TAG_KEY in WHERE clause.

select * INTO "7 Days(s)"."abc" FROM "10 Day(s)"."abc" where time >= now() - 7d AND TAG_KEY='name' GROUP BY *

This somehow working fine, but not sure what is the difference between these two query versions.

6

Glad you found a solution ,
the difference between the two query versions is that the last one only inserts data points from the last 7days into your 7d retention policy. Because there are no data points older than 7d inserted there are no dropped records because they all fit perfectly in the 7d retention policy.

7

I understand that part, but my assumption is, SELECT * INTO simply should continue migration of other FIELDs though it encounter “dropped record encounter”.

It seem “dropped records error” simply exiting the SELECT * INTO process based on following discussion.

Hope Influxdb team can pick this request and fix it.

1 Like
8

We’re using 1.5.x so may be a little different.

Three possible problems.

select * INTO “7 Days(s)”…“abc” FROM “10 Day(s)”…“abc” where TAG_KEY=‘name’ GROUP BY *

Note the “…” vs “.”
(Should be two “.” not the three this forum is showing for some reason)

The one “.” may be making Influx think “abc” is the Retention policy instead of the Measurement.

Second possibility may be you are trying to move too much data for your node configuration capacity.

Influx seems to pull the whole Measurement into Memory when doing a select into.
It may be exceeding the Memory capacity or your node and failing/restarting Influx.

We’ve had this problem quite a bit, best option in this case is to move the data in chunks with time >= ‘2018-11-27’ and time < ‘2018-11-28’ etc.

Also be aware that Influx some times has a tendency to convert Int’s to Floats - this will cause failures as well.
Hopefully this is fixed now, but we’ve had to use Kapacitor to work around this issue in cases.