Influxdb: Influx >=1.4: non_negative_derivative and difference broken, CQ's stop working

[neographikal]

Bug report

__System info:__ InfluxDB 1.4.2 on both 16.04.3 LTS and latest Docker image

My continuous queries stopped worked after upgrading to 1.4.2, there seems to be some problem with a few aggregate functions: non_negative_derivative and difference tested

InfluxDB shell version: 1.4.2
> select topic, Total from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' LIMIT 10
name: mqtt_consumer
time                topic                      Total
----                -----                      -----
1514560525743803802 tele/sonoffpowsplit/ENERGY 457.902
1514560555772347740 tele/sonoffpowsplit/ENERGY 457.91
1514560585849035726 tele/sonoffpowsplit/ENERGY 457.917
1514560615896216743 tele/sonoffpowsplit/ENERGY 457.925
1514560646493127648 tele/sonoffpowsplit/ENERGY 457.932
1514560677089769307 tele/sonoffpowsplit/ENERGY 457.939
1514560708040578480 tele/sonoffpowsplit/ENERGY 457.946
1514560783541412296 tele/sonoffpowsplit/ENERGY 457.963
1514560814705513205 tele/sonoffpowsplit/ENERGY 457.97
1514560848327978540 tele/sonoffpowsplit/ENERGY 457.977

> select difference(max("Total")) from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(5m)

No results

> select non_negative_derivative(max(Total)) from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(1m)

No results

> select sum(Total) from telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(1m) LIMIT 10
name: mqtt_consumer
time                sum
----                ---
1514560500000000000 915.812
1514560560000000000 915.842
1514560620000000000 915.8710000000001
1514560680000000000 457.946
1514560740000000000 457.963
1514560800000000000 915.947
1514560860000000000 915.9770000000001
1514560920000000000 916.011
1514560980000000000 458.019
1514561040000000000 916.067

Summing the values still works

Continuous query that worked fine for a year and stopped working:

cq_splitheatpump CREATE CONTINUOUS QUERY cq_splitheatpump ON home BEGIN SELECT non_negative_derivative(max(Total), 1m) AS value INTO home."1day".splitheatpump FROM telegraf.autogen.mqtt_consumer WHERE topic = 'tele/sonoffpowsplit/ENERGY' GROUP BY time(1m) END

Which filled this series:

> select * from home."1day".splitheatpump LIMIT 10
name: splitheatpump
time                value
----                -----
1485905760000000000 0.0005
1485905820000000000 0.0005
1485905880000000000 0.0005
1485905940000000000 0.0005
1485906000000000000 0.0005
1485906060000000000 0.0005
1485906120000000000 0
1485906180000000000 0.0005
1485906240000000000 0.0005
1485906300000000000 0.0005

[tomjonandy]

I have seen a similar problem I think.
My query:

SELECT SUM(diffs) FROM (SELECT NON_NEGATIVE_DERIVATIVE(inverse, 5m) as diffs FROM (SELECT "value"/-100 AS inverse FROM balance)) WHERE time >= now() - 30d GROUP BY time(1d, 3h)

which used to work completely as expected (in a version before 1.4.2, unfortunately I can't be sure which but it was recent, if not the previous version) now just returns the error ERR: aggregate function required inside the call to non_negative_derivative.

This seems to be an issue with NON_NEGATIVE_DERIVATIVE and GROUP BY as by removing the GROUP BY the query returns the single result that I would expect.

[krisp]

I have a similar problem with 1.4.2, the following continuous query used to produce data but stopped after upgrading. I just reverted back to 1.3.5 which was what I was previously running and it is working again.

CREATE CONTINUOUS QUERY "quotas.delta" ON isi_data_insights BEGIN SELECT derivative(last(logical), 1s) INTO isi_data_insights.autogen."quotas.delta" FROM isi_data_insights.autogen.quotas GROUP BY time(1m), path, cluster, basename END

[neographikal]

Can you just downgrade? I was afraid the upgrades maybe upgraded the storage schemas.

[Ultimation]

I am also now having this same issue
CREATE CONTINUOUS QUERY name ON database BEGIN SELECT non_negative_difference(last(field)) AS non_negative_difference_field INTO database.retention_policy.measurement FROM database.retention_policy.measurement GROUP BY time(29s, 15s), * END

On 1.4.1 this was not writing any datapoints, downgrading to 1.3.8 started to create points. Centos 7.2.

[neographikal]

Downgraded to 1.3.8 and it's working again. This is one hell of a regression to go unnoticed for 2-3 versions.

[neographikal]

Since it is not attracting much attention I edited the title.

[neographikal]

Is there any way to get some attention to this? 1.4.3 apparently didn't fix this bug if the release notes are to be believed.

[Naumis1]

Hi, I just had the same problem. Updated from 1.3.5 to 1.5.1. So it seems, that problem still exists. Had to downgrade. Now ok. So yes, it would be great to have attention to this issue.

[Naumis1]

Is there any possibility to include this bug fix to 1.6 version? I can't do update, because we are using a lot of CQ. Mostly calculating bit rates from counters.

[neographikal]

Well I'm already looking towards alternatives (experimenting with TimescaleDB), this kind of regression unfixed for half a year, doesn't give a lot of confidence.

[Naumis1]

Yes, that's true. But still, I will try to wait for fixed bug. :)

[Naumis1]

...for some more time...

[hahnjo]

I just ran into a similar problem: I think non_negative_derivative doesn't work with GROUP BY time if there is no WHERE time.

Reproduced with current master:

1. CREATE DATABASE test
2. USE test
3. INSERT test val=1i
4. Wait some seconds, then run INSERT test val=2i

• SELECT non_negative_derivative("val", 1s) FROM test works
• SELECT non_negative_derivative(first("val"), 1s) FROM test GROUP BY time(1s) doesn't, but
• SELECT non_negative_derivative(first("val"), 1s) FROM test WHERE time > NOW() - 1h GROUP BY time(1s) is fine.

We also have some CQs running which use non_negative_derivative and still produce the expected results. They use the Advanced Syntax, maybe that's a workaround you can try?

[Ultimation]

Doesn't that get invalidated? From the docs:

Note: Notice that the cq_query does not require a time range in a WHERE clause. InfluxDB automatically generates a time range for the cq_query when it executes the CQ. Any user-specified time ranges in the cq_query’s WHERE clause will be ignored by the system.

[hahnjo]

@Ultimation yes, probably. I meant to say that you should give the Advanced Syntax a try because such CQs work for me.

[zngzlg]

same question

[neographikal]

Still no attention for this?

[robertsLando]

Seems working in 1.7 version

[dimiii]

@robertsLando after migration, or a new instance?

[robertsLando]

After migration. Previously was using 1.6 version

On 21 Mar 2019, at 16:39, DM [e-mail] wrote:

@robertsLando after migration, or a new instance?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.