Problem with retention policies in continuous queries

Welcome & Getting Started
1

Hello,

I’m a beginner with influxdb (Ver 1.8 – it has to run on a raspberry) and try to establish a database to collect data from fish tanks.
It drives me mad, because I’m not able to run continuous queries with RP’s other than “default”

The following CQ works perfectly well:

CREATE CONTINUOUS QUERY CQ_Test_mean_1m ON Aquadata BEGIN SELECT mean(tempA) AS mean_tempA INTO Test_mean_1m FROM HighRes_Data GROUP BY time(1m) END

As soon as I try to assign a RP other than “default” it does not work:

CREATE CONTINUOUS QUERY CQ_Test_mean_1mB ON Aquadata BEGIN SELECT mean(tempA) AS mean_tempA INTO Aquadata.RP_60m.Test_mean_1m FROM HighRes_Data GROUP BY time(1m) END

The RP exists and I get no error message, but data are not written into the measurement “Test_mean_1m”

I would be more than happy if someone has advice.

Frank

2

Hello @frenki-d,
Welcome! Monitoring your fishtank! That’s cool.

What do you get when you run

SHOW RETENTION POLICIES ON "Aquadata"

?

Thank you.

3

Hi Anais,

with SHOW RETENTION POLICIES ON “Aquadata” I get:

name duration shardGroupDuration replicaN default

RP_all_in 168h0m0s 24h0m0s 1 true

RP_60m 1h0m0s 1h0m0s 1 false

With “show continuous queries” I get this:

name: _internal

name query

name: Aquadata

name query

CQ_Test_mean_1m CREATE CONTINUOUS QUERY CQ_Test_mean_1m ON Aquadata BEGIN SELECT mean(tempA) AS mean_tempA INTO Aquadata.RP_all_in.Test_mean_1m FROM Aquadata.RP_all_in.HighRes_Data GROUP BY time(1m) END

CQ_Test_mean_1mB CREATE CONTINUOUS QUERY CQ_Test_mean_1mB ON Aquadata BEGIN SELECT mean(tempA) AS mean_tempA INTO Aquadata.RP_60m.Test_mean_1mB FROM Aquadata.RP_all_in.HighRes_Data GROUP BY time(1m) END

“CQ_Test_mean_1m” is working, “CQ_Test_mean_1mB” doesn’t work (no Error message, no data are written)

4

Hello @frenki-d,
That feels like a bug. I don’t know why that wouldn’t work. Can you please submit an issue? Also can you try recreating the 60m retention policy and share the command and output when you do please?

5

Hi Anais,

I just started from scratch. I completely reinstalled Influxdb (1.8.3) and rebuilt the database:

The problem remains (on my Windows system as well as on my Raspberry!!).

Here is all I did with all system output (Raspberry only)

create database Aquadata

use Aquadata

create retention policy “60_min” on “Aquadata” duration 1h replication 1

show retention policies

name duration shardGroupDuration replicaN default

autogen 0s 168h0m0s 1 true

RP_12h 12h0m0s 1h0m0s 1 false

###### Now feeding in example data with a Python program into measurement “HighRes_Data”

show measurements
name: measurements
name

HighRes_Data

CREATE CONTINUOUS QUERY “CQ_mean_1m” ON “Aquadata” BEGIN SELECT mean(“tempA”) INTO “RP_12h”.“Test_mean_1m” FROM “HighRes_Data” GROUP BY time(1m) END

CREATE CONTINUOUS QUERY “CQ_mean_1h” ON “Aquadata” BEGIN SELECT mean(“tempA”) INTO “RP_12h”.“Test_mean_1h” FROM “HighRes_Data” GROUP BY time(1h) END

show continuous queries
name: _internal
name query

name: Aquadata
name query

CQ_mean_1m CREATE CONTINUOUS QUERY CQ_mean_1m ON Aquadata BEGIN SELECT mean(tempA) INTO Aquadata.RP_12h.Test_mean_1m FROM Aquadata.autogen.HighRes_Data GROUP BY time(1m) END
CQ_mean_1h CREATE CONTINUOUS QUERY CQ_mean_1h ON Aquadata BEGIN SELECT mean(tempA) INTO Aquadata.RP_12h.Test_mean_1h FROM Aquadata.autogen.HighRes_Data GROUP BY time(1h) END

select * from “HighRes_Data”
name: HighRes_Data
time kanal1 kanal2 tempA tempW

1609951429451341983 256 128 13 87
1609951434519897845 256 128 14 86
1609951439569317183 256 128 15 85
1609951444610211031 256 128 16 84
1609951449645838565 256 128 17 83
1609951454681407054 256 128 18 82
1609951459719653998 256 128 19 81

this is just part of the data as an example for the fed in data
after one hour of data feeding:

show measurements
name: measurements
name

HighRes_Data
Test_mean_1h
Test_mean_1m

select * from “Test_mean_1h”
select * from “Test_mean_1m”

Both measurements were built by the CQ’s but they both contain no data

6

I use InfluxDB 1.8.3 on Windows with several CQs without any issue so far.

Something I’ve noticed is that you are not specifing the retention policy in your select query, therefore you might just be querying the wrong “table”.
By default, if you don’t specify the retention policy you will query the default one (“autogen” in your case)

Therefore:

Those are quering the default RP (autogen)
select * from “Test_mean_1h”
select * from “Test_mean_1m”

To query your new RP use
select * from "RP_12h".“Test_mean_1h”
select * from "RP_12h".“Test_mean_1m”

I’ll try so summarize what you have so far, correct me if there is something wrong.
Database:
-AcquaData
Retention Policies:
-autogen (default RP)
-RP_12h
-60_min (is mentioned but I don’t see around, I assume the correct one is “RP_12h”)
Measurements:
-HighRes_Data
-Test_mean_1m
-Test_mean_1h

You are downsampling data from the “autogen” to “RP_12h” RP using CQs
Now you just have to query the right RP by specifying it in the query.

Just an additional concept about the DB structure

It took me a while to understand this, in an InfluxDB database, you have RPs and measurements, but they do not behave like schemas and tables in a relational database (in which you explicitly create objects inside a schema).
In InfluxDB you can just have a “matrix” with Measurements and RPs, therefore a measurement exists in all RPs, even if it has no data, and if it gets queried it will return nothing. (I think this is your case)

here is an image to clarify the structure
image

7

Hi,
many thanks!! Now it work’s. I just had a severe misconception of the DB structure. Your comment about the DB structrure helped a lot!!

Thanks again

Frank