[0.9.5-rc2] data not returned by SELECT * query

[rackunits]

Using influxdb_0.9.5-rc2

After a while of successful queries my databases no longer respond to select queries.

> use metrics_888
> show series
> name: network_tcpconn_established
> _key                                  archive     host
> network_tcpconn_established,archive=20151117_14_48,host=column1   20151117_14_48  column1
> network_tcpconn_established,archive=20151117_14_48,host=column2   20151117_14_48  column2
> network_tcpconn_established,archive=20151117_14_48,host=column3   20151117_14_48  column3
> network_tcpconn_established,archive=20151117_14_48,host=column4   20151117_14_48  column4
> network_tcpconn_established,archive=20151117_14_48,host=column5   20151117_14_48  column5
> network_tcpconn_established,archive=20151117_14_48,host=column6   20151117_14_48  column6

> select * from network_tcpconn_established
\> 

No response from the InfluxDBShell..

While SHOW queries do show results

>  SHOW TAG VALUES WITH KEY = "host"
name: hostTagValues
host
column1
column2
column3
column4
column5
column6

Here is a part from the influxdb log:

[wal] 2015/11/18 09:07:36 Flush due to idle. Flushing 12 series with 12 points and 573 bytes from partition 1
[wal] 2015/11/18 09:07:36 write to index of partition 1 took 9.789027ms
[wal] 2015/11/18 09:07:46 Flush due to idle. Flushing 12 series with 12 points and 573 bytes from partition 1
[wal] 2015/11/18 09:07:46 write to index of partition 1 took 6.871001ms
[query] 2015/11/18 09:07:48 SHOW SERIES
[http] 2015/11/18 09:07:48 127.0.0.1 - - [18/Nov/2015:09:07:48 +0100] GET /query?db=metrics_888&epoch=ns&q=show+series HTTP/1.1 200 964 - InfluxDBShell/0.9.5-rc2 74c40fe0-8dcb-11e5-8017-000000000000 2.376884ms
[wal] 2015/11/18 09:07:56 Flush due to idle. Flushing 12 series with 12 points and 573 bytes from partition 1
[wal] 2015/11/18 09:07:56 write to index of partition 1 took 4.151501ms
[query] 2015/11/18 09:07:57 SELECT * FROM "metrics_888"."default".network_tcpconn_established
[http] 2015/11/18 09:07:57 127.0.0.1 - - [18/Nov/2015:09:07:57 +0100] GET /query?db=metrics_888&epoch=ns&q=select+%2A+from+network_tcpconn_established HTTP/1.1 200 40 - InfluxDBShell/0.9.5-rc2 7a88eb73-8dcb-11e5-8018-000000000000 3.199066ms
[query] 2015/11/18 09:08:06 SHOW TAG VALUES
[http] 2015/11/18 09:08:06 127.0.0.1 - - [18/Nov/2015:09:08:06 +0100] GET /query?db=metrics_888&epoch=ns&q=+SHOW+TAG+VALUES+WITH+KEY+%3D+%22host%22 HTTP/1.1 200 114 - InfluxDBShell/0.9.5-rc2 7fc09e73-8dcb-11e5-8019-000000000000 807.283µs
[wal] 2015/11/18 09:08:06 Flush due to idle. Flushing 12 series with 12 points and 573 bytes from partition 1
[wal] 2015/11/18 09:08:06 write to index of partition 1 took 4.996032ms

[beckettsean]

@rackunits the system is responding to the query, as evidenced by this line in the logs:

[http] 2015/11/18 09:07:57 127.0.0.1 - - [18/Nov/2015:09:07:57 +0100] GET /query?db=metrics_888&epoch=ns&q=select+%2A+from+network_tcpconn_established HTTP/1.1 200 40 - InfluxDBShell/0.9.5-rc2 7a88eb73-8dcb-11e5-8018-000000000000 3.199066ms

That shows that the query was returned, so nothing is hanging. The question becomes why are there no points found for SELECT * FROM "metrics_888"."default".network_tcpconn_established.

Can you please give the output from SHOW RETENTION POLICIES ON metrics_888?

[beckettsean]

To be clear, an empty response with a 200 HTTP code is the expected result for a query with no matching points.

[benoit-stratoscale]

@beckettsean I experience the exact same behavior with RC3 . select * return an empty set.

A single point (with tags and field and retention policy applied) is pushed to the data base the write succeed, series is create , etc.. but the select * fail. Note that if i revert to 9.4 stable the issue disappear.

Here some of the information :

> SHOW RETENTION POLICIES on stratoscale
name                    duration        replicaN        default
default                 0               1               true
metrics                 0               1               false
sysevents               0               1               false
control_alarms          0               1               false
processed_alarms        0               1               false
processed               0               1               false

> show series
name: vm.utilization.cpu
------------------------
_key                                                                                                    channel hostname        vm-strato
vm.utilization.cpu,channel=metrics,hostname=first,vm-strato=cf086614-802d-4f24-bf0b-9c8429e4d48b        metrics first           cf086614-802d-4f24-bf0b-9c8429e4d48b

> select * from /.*/
>
> select * from "vm.utilization.cpu"
> 

Note that if I do not add any retention policies and do not use a retention policy during the write operation the select * query succeed ...

It seems that the issue is with the retention policy but in my case i force them to be Infinite ("INF") for test purpose. Is there a bug that interpret the 0 duration to drop immediately the datapoints ?

[beckettsean]

@benoit-stratoscale

if I do not add any retention policies and do not use a retention policy during the write operation the select * query succeed ...

That implies that it fails when you do explicitly supply a retention policy on writes. Are you aware that you must also provide that retention policy when querying?

For example, if you write points to the metrics retention policy, select * from "vm.utilization.cpu" will return nothing, because the metrics RP does not have default=true. The proper query would be select * from metrics."vm.utilization.cpu"

Does that clarification make sense? Knowing that, are you still seeing select queries incorrectly returning no results?

[pkittenis]

Having the same issue with default retention policy and no policy changes what so ever.

It looks like that, intermittently, only the last point written is returned, and sometimes no values at all.

Subsequent queries respond with all data.

This seems to be similar to previous issue reported on nightly builds - #4354

So yes, am seeing select queries incorrectly returning only one or no results on latest 0.9.5-rc3 with tsm1 engine.

Intermittently a response looks like this:

{"results":[{"series":[{"name":"carbon.relays.c1ba46da-cd60-4a99-a67b-24b04accfcf4.metricsReceived","columns":["time","value"],"
values":[[1448033450000,0]]}]}]}

That data point is for the last point written and, interestingly, has an (incorrect) 0 value.

Subsequent queries look like this:

{"results":[{"series":[{"name":"carbon.relays.c1ba46da-cd60-4a99-a67b-24b04accfcf4.metricsReceived","columns":["time","value"],"
values":[[1448029970000,59],[1448030030000,59],[1448030090000,59],[1448030150000,59],[1448030210000,59],[1448030270000,59],[1448030330000,59],[1448030390000,59],[1448030450000,59],[1448030510000,59],[1448030570000,59],[1448030630000,59],[1448030690000,59],[1448030750000,59],[1448030810000,59],[1448030870000,59],[1448030930000,59],[1448030990000,59],[1448031050000,59],[1448031110000,59],[1448031170000,59],[1448031230000,59],[1448031290000,59],[1448031350000,59],[1448031410000,59],[1448031470000,59],[1448031530000,59],[1448031590000,59],[1448031650000,59],[1448031710000,59],[1448031770000,59],[1448031830000,59],[1448031890000,59],[1448031950000,59],[1448032010000,59],[1448032070000,59],[1448032130000,59],[1448032190000,59],[1448032250000,59],[1448032310000,59],[1448032370000,59],[1448032430000,59],[1448032490000,59],[1448032550000,59],[1448032610000,59],[1448032670000,59],[1448032730000,59],[1448032790000,59],[1448032850000,59],[1448032910000,59],[1448032970000,59],[1448033030000,59],[1448033090000,59],[1448033150000,59],[1448033210000,59],[1448033270000,59],[1448033330000,59],[1448033390000,59],[1448033450000,59]]}]}]}

Notice that the same data point as the previous query now has the correct non-zero value.

Version info:

2015/11/18 15:51:51 InfluxDB starting, version 0.9.5-rc3, branch 0.9.5, commit a589f941e93078a4a093806d7336ee903d32f23a, built 2015-11-17T0

Retention policies:

SHOW RETENTION POLICIES ON "graphite"
name    duration    replicaN    default
default "0" 1   true

[rackunits]

Apologies, I have found the reason for this issue.
I was injecting values with timestamps somewhere in the year 8000.
Obviously I had an error in my script calculating the timestamps, so after correcting this, the was no longer an issue.
When adding the wrong timestamps the meta data was stored successfully in the database (show series worked) but the metrics were not available. No error was thrown during the inserts.

[beckettsean]

Thanks for the followup, @rackunits.

@pkittenis I'm closing this issue, but I will re-open if your issues are not adequately covered by #4354.

@benoit-stratoscale I assume your data is query-able when using the proper retention policy.