Help: Continuous Query which is used to add another series to itself, log shows CQ works well but NO output of CQ execution in database

1

The formual of CQ is : “cms.sip.active.cps” = last(“cms.sip.active.cps”) + 2 * mean(“cms.session.sip.active”) ; increment the cms.sip.active.cps with last value of it and the value of cms.session.sip.active;
thanks in advance.

PART I Test Enviroment

Test Enviroment: CentOS 7
Influxdb version: version=1.5.4 branch=1.5 commit=4e4e00bc5ab85a3ff5e988c91020cf0399a87026
Startup logging:
ts=2019-05-09T15:44:21.943743Z lvl=info msg=“InfluxDB starting” log_id=0FJK6sil000 version=1.5.4 branch=1.5 commit=4e4e00bc5ab85a3ff5e988c91020cf0399a87026
ts=2019-05-09T15:44:21.943829Z lvl=info msg=“Go runtime” log_id=0FJK6sil000 version=go1.9.2 maxprocs=40
ts=2019-05-09T15:44:21.944736Z lvl=info msg=“Using data dir” log_id=0FJK6sil000 service=store path=/var/lib/xms/metrics/influx/data
ts=2019-05-09T15:44:21.944790Z lvl=info msg=“Open store (start)” log_id=0FJK6sil000 service=store trace_id=0FJK6sj0000 op_name=tsdb_open op_event=start
ts=2019-05-09T15:44:24.640535Z lvl=info msg=“Starting continuous query service” log_id=0FJK6sil000 service=continuous_querier

show continuous queries
name: meters
name query

cqd CREATE CONTINUOUS QUERY cqd ON meters BEGIN SELECT last(“cms.sip.active.cps”) + 2 * mean(“cms.session.sip.active”) AS “cms.sip.active.cps” INTO meters.cms FROM meters.cms GROUP BY time(1m) END

name: _internal
name query

PART II log about influxdb continuous query exection

ts=2019-05-09T15:45:00.652339Z lvl=info msg=“Continuous query execution (start)” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJK9Ew0000 op_name=continuous_querier_execute op_event=start
ts=2019-05-09T15:45:00.652395Z lvl=info msg=“Executing continuous query” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJK9Ew0000 op_name=continuous_querier_execute name=cqd db_instance=meters start=2019-05-09T15:44:00.000000Z end=2019-05-09T15:45:00.000000Z
ts=2019-05-09T15:45:00.652584Z lvl=info msg=“Executing query” log_id=0FJK6sil000 service=query query=“SELECT last("cms.sip.active.cps") + 2 * mean("cms.session.sip.active") AS "cms.sip.active.cps" INTO meters.cms FROM meters.cms WHERE time >= ‘2019-05-09T15:44:00Z’ AND time < ‘2019-05-09T15:45:00Z’ GROUP BY time(1m)”
ts=2019-05-09T15:45:00.653914Z lvl=info msg=“Finished continuous query” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJK9Ew0000 op_name=continuous_querier_execute name=cqd db_instance=meters written=0 start=2019-05-09T15:44:00.000000Z end=2019-05-09T15:45:00.000000Z duration=1ms
ts=2019-05-09T15:45:00.675613Z lvl=info msg=“Continuous query execution (end)” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJK9Ew0000 op_name=continuous_querier_execute op_event=end op_elapsed=23.284ms

ts=2019-05-09T15:46:00.694844Z lvl=info msg=“Continuous query execution (start)” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKCuTW000 op_name=continuous_querier_execute op_event=start
ts=2019-05-09T15:46:00.694890Z lvl=info msg=“Executing continuous query” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKCuTW000 op_name=continuous_querier_execute name=cqd db_instance=meters start=2019-05-09T15:45:00.000000Z end=2019-05-09T15:46:00.000000Z
ts=2019-05-09T15:46:00.695079Z lvl=info msg=“Executing query” log_id=0FJK6sil000 service=query query=“SELECT last("cms.sip.active.cps") + 2 * mean("cms.session.sip.active") AS "cms.sip.active.cps" INTO meters.cxm FROM meters.cms WHERE time >= ‘2019-05-09T15:45:00Z’ AND time < ‘2019-05-09T15:46:00Z’ GROUP BY time(1m)”
ts=2019-05-09T15:46:00.696064Z lvl=info msg=“Finished continuous query” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKCuTW000 op_name=continuous_querier_execute name=cqd db_instance=meters written=0 start=2019-05-09T15:45:00.000000Z end=2019-05-09T15:46:00.000000Z duration=1ms
ts=2019-05-09T15:46:00.707184Z lvl=info msg=“Continuous query execution (end)” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKCuTW000 op_name=continuous_querier_execute op_event=end op_elapsed=12.372ms

ts=2019-05-09T15:47:00.726620Z lvl=info msg=“Continuous query execution (start)” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKGZyW000 op_name=continuous_querier_execute op_event=start
ts=2019-05-09T15:47:00.726665Z lvl=info msg=“Executing continuous query” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKGZyW000 op_name=continuous_querier_execute name=cqd db_instance=meters start=2019-05-09T15:46:00.000000Z end=2019-05-09T15:47:00.000000Z
ts=2019-05-09T15:47:00.726838Z lvl=info msg=“Executing query” log_id=0FJK6sil000 service=query query=“SELECT last("cms.sip.active.cps") + 2 * mean("cms.session.sip.active") AS "cms.sip.active.cps" INTO meters.cms FROM meters.cms WHERE time >= ‘2019-05-09T15:46:00Z’ AND time < ‘2019-05-09T15:47:00Z’ GROUP BY time(1m)”
ts=2019-05-09T15:47:00.727822Z lvl=info msg=“Finished continuous query” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKGZyW000 op_name=continuous_querier_execute name=cqd db_instance=meters written=0 start=2019-05-09T15:46:00.000000Z end=2019-05-09T15:47:00.000000Z duration=1ms
ts=2019-05-09T15:47:00.738694Z lvl=info msg=“Continuous query execution (end)” log_id=0FJK6sil000 service=continuous_querier trace_id=0FJKGZyW000 op_name=continuous_querier_execute op_event=end op_elapsed=12.078ms

PART III output in database – NO UPDATE AT ALL

select last(“cms.sip.active.cps”) from cms
name: cms
time last

1557354900000000000 148.83050847457628

select last(“cms.sip.active.cps”) from cms
name: cms
time last

1557354900000000000 148.83050847457628

select last(“cms.sip.active.cps”) from cms
name: cms
time last

1557354900000000000 148.83050847457628

select last(“cms.sip.active.cps”) from cms
name: cms
time last

1557354900000000000 148.83050847457628

select last(“cms.sip.active.cps”) from cms
name: cms
time last

1557354900000000000 148.83050847457628

2

Hi @gabriel.wang welcome,
What is the result of following query ?

SELECT last(“cms.sip.active.cps”) + 2 * mean(“cms.session.sip.active”) AS “cms.sip.active.cps” FROM meters.cms GROUP BY time(1m)

My wild guess is that you have to replace 2 * mean … by 2.0 * mean …

3

Hi MarcV,
Thanks for your response to my question.
According to my testing, it does NOT make difference : 2mean() or 2.0mean(),

If I replace the counter to another one in the SELECT clause(like below), it works and get the right result in Z.

SELECT last(“X”) + 2.0 * mean(“Y”) AS “Z” FROM meters.cms GROUP BY time(1m)

I suspect the CQ does NOT support add this kind of clause:
SELECT last(“X”) + 2.0 * mean(“Y”) AS “X” FROM meters.cms GROUP BY time(1m)

in above query statement, take the last value of X, add value of Y to it, and save it as point of X.

In short, the following two scenarios represent what I said above.
SELECT last(A) + 2 *mean(B) AS C INTO D works
SELECT last(A) + 2 *mean(B) AS A INTO D does not work, no output in the database.