influxdb icon indicating copy to clipboard operation
influxdb copied to clipboard

Published 20 hours ago • verified publisher icon influxdata

Missing data

Open CrAsH1101 opened this issue 7 years ago • 52 comments

We have 2 databases on same host. From time to time we notice that huge chunk of data is missing across all measurements. I'ts like someone hit delete from <all_measurements> where time > X and time < Y. First two times we tried to re-create the data in influx manually because it was in smaller DB ("only" few thousand records were missing). By accident i figured that if we backup our database and just restore it back, all the data is back. So data is not lost, but influx is not showing them. Few days ago we had identical situation with our larger database. Everything from July 12, 2018 to August 23, 2018 - gone! We did backup/restore (12 GB dump), everything is back! Any ideas? We use default RP, so none/infinite, no CQ, version is 1.5.2. It happened before with 1.2.4 also. Debian 8 x64

CrAsH1101 avatar Sep 15 '18 12:09 CrAsH1101

I guess I just hit the same issue on 1.5.2. Suddenly old data is missing altough we have infinite retention (default RP). Did you find a solution for this problem @CrAsH1101 ?

mattpoel avatar Dec 03 '18 13:12 mattpoel

I did, although it's not a solution. I dumped whole db to file, and just imported same dump back. Magically, all missing data were back!??? Hope this helps you too...

P.S. Try importing it first to different DB and see if missing data exists before you drop original DB ;)

CrAsH1101 avatar Dec 03 '18 13:12 CrAsH1101

Thanks for the fast response! I'm already in the preparations for this ;-) Read some posts about missing data and possible recovery by dumping and importing the data.

mattpoel avatar Dec 03 '18 13:12 mattpoel

This is how I "fixed" it with my standard installation, just in case somebody else is encountering the same problem.

I don't take responsibility for any of the following steps. I just want to document how I was able to restore all of the data. If you have custom retention policies, continious queries, etc. please keep in mind that you might have to re-create them manually.

Stop influxdb and take a cold backup

systemctl stop influxdb
cp -rp /var/lib/influxdb /BACKUP_DESTINATION

Extract data

influx_inspect export -datadir /var/lib/influxdb/data  -waldir /var/lib/influxdb/wal -out /DUMP/influxdb.dmp -compress

Without compress option, dumps can get huge.

Erase existing data

rm -rf /var/lib/influxdb/*

Start influxdb

systemctl start influxdb

Import data

influx -import -compressed -path /DUMP/influxdb.dmp

mattpoel avatar Dec 03 '18 20:12 mattpoel

Also seeing this similar issue, it seems to be related to a restart of the service. However my details on the cause, restart, and when exactly the issue came up are still limited. I will update when I get more information.

Version Info:

# curl -sL -I localhost:8086/ping
HTTP/1.1 204 No Content
Content-Type: application/json
Request-Id: 9ce66a5e-612f-11e9-947f-000000000000
X-Influxdb-Build: OSS
X-Influxdb-Version: 1.5.3
X-Request-Id: 9ce66a5e-612f-11e9-947f-000000000000
Date: Wed, 17 Apr 2019 16:41:13 GMT

Our Manual Solution:

  • Make a backup of the database
  • Restore backup to a new database
  • Change the retention policy on the original database
  • Write the data from the backup database to the original database
  • Change the retention policy back to what it was
  • Clean up

Explanation

  • We executed a restore from a backup to the original database so as not to have destructive operations and no downtime of the particular database we need to operate against.
  • We update the retention policy because attempting to write data outside the retention policy throws unhandleable errors and causes restore to fail

On the host

sudo -u influxdb bash
export DB_NAME="database_name"
mkdir /tmp/db_backup
influxd backup -portable -database ${DB_NAME} /tmp/db_backup
influxd restore -portable -db ${DB_NAME} -newdb backup_temp /tmp/db_backup
unset DB_NAME

On a terminal with influxdb cli

export DB_NAME="database_name"
  influx -host example.com -execute "show retention policies on ${DB_NAME}"
  export RETENTION="336h"
  export RETENTION_TEMP="6000h"
  export SHARD="168h"
  export SHARD_TEMP="168h"
  export POLICY_NAME="autogen"
  influx -host example.com -execute "alter retention policy ${POLICY_NAME} on ${DB_NAME} DURATION ${RETENTION_TEMP} shard duration ${SHARD_TEMP} default"
  influx -host example.com -execute "SELECT * INTO ${DB_NAME}..:MEASUREMENT FROM /.*/ GROUP BY *" -database="backup_temp"
  influx -host example.com -execute "alter retention policy ${POLICY_NAME} on ${DB_NAME} DURATION ${RETENTION} shard duration ${SHARD} default"
  influx -host example.com -execute "show retention policies on ${DB_NAME}"
  influx -host example.com -execute 'DROP DATABASE backup_temp'

On the host again:

 rm -rf /tmp/db_backup

gregops312 avatar Apr 17 '19 16:04 gregops312

We experienced a similar issue (like: above ) twice in the last month. However, after a restart the data was displayed (and fine). It seems data written to a new shard, wasn't returned in queries. InfluxDB version: 1.7.6.

tw-bert avatar Jul 03 '19 11:07 tw-bert

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] avatar Oct 01 '19 12:10 stale[bot]

This issue is not stale until someone responds to concerns, even if it is as simple as there's a fix, the fact is a resolution has not explicitly been found.

gregops312 avatar Oct 01 '19 13:10 gregops312

This issue is happening every time the server is restarted by force (power outage). I already have scripts in place which export/import database and after that everything is fine and all data is displayed correctly. But it's insane that I have to think about it. To be honest, I'm considering alternatives for my time series database :(

CrAsH1101 avatar Oct 01 '19 18:10 CrAsH1101

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] avatar Dec 30 '19 19:12 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.

stale[bot] avatar Jan 06 '20 20:01 stale[bot]

This stale bot is really annoying considering no one has ever responded.

gregops312 avatar Jan 16 '20 19:01 gregops312

It's amazing that this is not getting more attention. I can literally reproduce issue at will, just kill the server by cutting power off, start the server and points are missing in database. I'm still on version 1.5.2

Igor

On Thu, Jan 16, 2020, 20:09 Gregory Kman [email protected] wrote:

This stale bot is really annoying considering no one has ever responded.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/influxdata/influxdb/issues/10287?email_source=notifications&email_token=ADAGA72I5VO6NCS4OWMYULLQ6CWHVA5CNFSM4FVJKHMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJFF5XY#issuecomment-575299295, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADAGA7Z5WWZ7O5GD5B6CWNDQ6CWHVANCNFSM4FVJKHMA .

CrAsH1101 avatar Jan 16 '20 19:01 CrAsH1101

I completely agree, this issue should be looked at.

tw-bert avatar Jan 16 '20 19:01 tw-bert

To be honest, I'm not worried to much about it, as i have quick solution for when it happens, but we are currently in the project migrating everything from influx to TimeScale, and this issue is one of the primary reasons :(

On Thu, Jan 16, 2020, 20:37 tw-bert [email protected] wrote:

I completely agree, this issue should be looked at.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/influxdata/influxdb/issues/10287?email_source=notifications&email_token=ADAGA76PTJUXIPWBR74G65DQ6CZQRA5CNFSM4FVJKHMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJFIZCQ#issuecomment-575310986, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADAGA76OTQOG5NEY3P5WVWTQ6CZQRANCNFSM4FVJKHMA .

CrAsH1101 avatar Jan 16 '20 19:01 CrAsH1101

Apologies for the handling of this issue. @CrAsH1101 could you test this with 1.7.10 or 1.8?

dgnorton avatar May 19 '20 19:05 dgnorton

@dgnorton, I extensively tested Influxdb version 1.8.0-1 on machine A with data collected by machine B running Influxdb version 1.7.8, and I, unfortunately, have to confirm that the above-discussed problem still occurs. Although my data is still there (15+GB of data for some measure between feb 2020 and june 2020), I can only retrieve data back from april 2020 till june 2020 with help of for example Grafana or the standard Influx-CLI tool. Any updates on this issue? The above-discussed workarounds work temporary but are not an option in my situation as machine A has to reboot quite often..

alexl04 avatar Jul 24 '20 07:07 alexl04

Having the same problem, with v1.8.0. I'm surprised only a few people reporting the issue. This makes it really hard to rely on InfluxDB. I guess a reloading method of the existing shards would be nice, since the data itself is present, but the DB just can't see it.

hiksuman avatar Oct 13 '20 13:10 hiksuman

We're seeing the same thing. Looking for alternatives as our production system gets missing gaps of data every time our influx restarts.

ELymar avatar Dec 04 '20 12:12 ELymar

I am facing the same issue. I have two databases in influx lets say DB1 and DB2 Post service restart DB1 is fine and showing all data but DB2 shows only 3 measurements, rest all are missing

rohithbb avatar Mar 09 '21 11:03 rohithbb

Has anyone fixed this issue with a 2.0 upgrade?

We are also experiencing lost shards, and debugging is proving very difficult. We are attempting the export/import method mentioned above, but are not able to complete the import step. Retention policy errors are reported and the database does not grow in size on disk. Importing with various retention policy configurations has not solved the issue.

andybaumgar avatar Mar 26 '21 17:03 andybaumgar

@andybaumgar @rohithbb @ELymar can you share the output of SHOW SHARDS? If not here (in public), could you email it to david at-symbol influxdata.com with a link to this issue? I'm curious to see if there are shards with overlapping time ranges. Thanks.

dgnorton avatar Apr 01 '21 13:04 dgnorton

@dgnorton Thanks David! Sure I'll send it over.

andybaumgar avatar Apr 09 '21 17:04 andybaumgar

To be honest, I'm not worried to much about it, as i have quick solution for when it happens, but we are currently in the project migrating everything from influx to TimeScale, and this issue is one of the primary reasons :( … On Thu, Jan 16, 2020, 20:37 tw-bert @.***> wrote: I completely agree, this issue should be looked at. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#10287?email_source=notifications&email_token=ADAGA76PTJUXIPWBR74G65DQ6CZQRA5CNFSM4FVJKHMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJFIZCQ#issuecomment-575310986>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADAGA76OTQOG5NEY3P5WVWTQ6CZQRANCNFSM4FVJKHMA .

Same here. Was a big fan of InfluxDB. We used it since the early beginnings, but this issue was a show stopper. After facing this problem for more than two years (even with the latest versions) and the wontfix label has been added, we migrated everything to Timescale. No more missing data, lower memory usage, higher throughput and full SQL capabilities.

mattpoel avatar Apr 12 '21 07:04 mattpoel

To be honest, I'm not worried to much about it, as i have quick solution for when it happens, but we are currently in the project migrating everything from influx to TimeScale, and this issue is one of the primary reasons :( Same here. Was a big fan of InfluxDB. We used it since the early beginnings, but this issue was a show stopper. After facing this problem for more than two years (even with the latest versions) and the wontfix label has been added, we migrated everything to Timescale. No more missing data, lower memory usage, higher throughput and full SQL capabilities.

Yes, it's time (pun) for us to move to Timescale as well. A db with unreliable persistency is not worth investing into.

tw-bert avatar Apr 12 '21 07:04 tw-bert

@andybaumgar ~~didn't receive the email Friday (checked spam folder as well). Did it bounce back?~~ [edit: I found it. Thanks.]

@mattpoel sorry we didn't get to this sooner and hope you'll give InfluxDB another try. @tw-bert, sounds like maybe you haven't migrated yet. If that's the case, can you share the output of SHOW SHARDS either here or email it to david at-symbol influxdata.com? Thanks.

dgnorton avatar Apr 12 '21 15:04 dgnorton

@dgnorton Sure, here you go:

telegraf
id   database   retention_policy   shard_group   start_time   end_time   expiry_time   owners  
1997   telegraf   A   1997   2021-04-05T00:00:00Z   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z  
2006   telegraf   A   2006   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z   2021-04-26T00:00:00Z  
1888   telegraf   B   1888   2021-01-11T00:00:00Z   2021-03-08T00:00:00Z   2021-05-03T00:00:00Z  
1961   telegraf   B   1961   2021-03-08T00:00:00Z   2021-05-03T00:00:00Z   2021-06-28T00:00:00Z  
1074   telegraf   C   1074   2019-04-29T00:00:00Z   2020-05-04T00:00:00Z   2021-05-10T00:00:00Z  
1559   telegraf   C   1559   2020-05-04T00:00:00Z   2021-05-10T00:00:00Z   2022-05-16T00:00:00Z  
6   telegraf   D   6   2014-02-17T00:00:00Z   2024-02-05T00:00:00Z   2024-02-05T00:00:00Z  
_internal
id   database   retention_policy   shard_group   start_time   end_time   expiry_time   owners  
1998   _internal   monitor   1998   2021-04-05T00:00:00Z   2021-04-06T00:00:00Z   2021-04-13T00:00:00Z  
2000   _internal   monitor   2000   2021-04-06T00:00:00Z   2021-04-07T00:00:00Z   2021-04-14T00:00:00Z  
2001   _internal   monitor   2001   2021-04-07T00:00:00Z   2021-04-08T00:00:00Z   2021-04-15T00:00:00Z  
2002   _internal   monitor   2002   2021-04-08T00:00:00Z   2021-04-09T00:00:00Z   2021-04-16T00:00:00Z  
2003   _internal   monitor   2003   2021-04-09T00:00:00Z   2021-04-10T00:00:00Z   2021-04-17T00:00:00Z  
2004   _internal   monitor   2004   2021-04-10T00:00:00Z   2021-04-11T00:00:00Z   2021-04-18T00:00:00Z  
2005   _internal   monitor   2005   2021-04-11T00:00:00Z   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z  
2007   _internal   monitor   2007   2021-04-12T00:00:00Z   2021-04-13T00:00:00Z   2021-04-20T00:00:00Z  
nmonmetrics
id   database   retention_policy   shard_group   start_time   end_time   expiry_time   owners  
899   nmonmetrics   autogen   899   2018-12-31T00:00:00Z   2019-01-07T00:00:00Z   2019-01-07T00:00:00Z  
907   nmonmetrics   autogen   907   2019-01-07T00:00:00Z   2019-01-14T00:00:00Z   2019-01-14T00:00:00Z  
921   nmonmetrics   autogen   921   2019-01-14T00:00:00Z   2019-01-21T00:00:00Z   2019-01-21T00:00:00Z  
932   nmonmetrics   autogen   932   2019-01-21T00:00:00Z   2019-01-28T00:00:00Z   2019-01-28T00:00:00Z  
943   nmonmetrics   autogen   943   2019-01-28T00:00:00Z   2019-02-04T00:00:00Z   2019-02-04T00:00:00Z  
954   nmonmetrics   autogen   954   2019-02-04T00:00:00Z   2019-02-11T00:00:00Z   2019-02-11T00:00:00Z  
964   nmonmetrics   autogen   964   2019-02-11T00:00:00Z   2019-02-18T00:00:00Z   2019-02-18T00:00:00Z  
974   nmonmetrics   autogen   974   2019-02-18T00:00:00Z   2019-02-25T00:00:00Z   2019-02-25T00:00:00Z  
984   nmonmetrics   autogen   984   2019-02-25T00:00:00Z   2019-03-04T00:00:00Z   2019-03-04T00:00:00Z  
994   nmonmetrics   autogen   994   2019-03-04T00:00:00Z   2019-03-11T00:00:00Z   2019-03-11T00:00:00Z  
1007   nmonmetrics   autogen   1007   2019-03-11T00:00:00Z   2019-03-18T00:00:00Z   2019-03-18T00:00:00Z  
1017   nmonmetrics   autogen   1017   2019-03-18T00:00:00Z   2019-03-25T00:00:00Z   2019-03-25T00:00:00Z  
1027   nmonmetrics   autogen   1027   2019-03-25T00:00:00Z   2019-04-01T00:00:00Z   2019-04-01T00:00:00Z  
1037   nmonmetrics   autogen   1037   2019-04-01T00:00:00Z   2019-04-08T00:00:00Z   2019-04-08T00:00:00Z  
1047   nmonmetrics   autogen   1047   2019-04-08T00:00:00Z   2019-04-15T00:00:00Z   2019-04-15T00:00:00Z  
1057   nmonmetrics   autogen   1057   2019-04-15T00:00:00Z   2019-04-22T00:00:00Z   2019-04-22T00:00:00Z  
1066   nmonmetrics   autogen   1066   2019-04-22T00:00:00Z   2019-04-29T00:00:00Z   2019-04-29T00:00:00Z  
1076   nmonmetrics   autogen   1076   2019-04-29T00:00:00Z   2019-05-06T00:00:00Z   2019-05-06T00:00:00Z  
1086   nmonmetrics   autogen   1086   2019-05-06T00:00:00Z   2019-05-13T00:00:00Z   2019-05-13T00:00:00Z  
1095   nmonmetrics   autogen   1095   2019-05-13T00:00:00Z   2019-05-20T00:00:00Z   2019-05-20T00:00:00Z  
1104   nmonmetrics   autogen   1104   2019-05-20T00:00:00Z   2019-05-27T00:00:00Z   2019-05-27T00:00:00Z  
1113   nmonmetrics   autogen   1113   2019-05-27T00:00:00Z   2019-06-03T00:00:00Z   2019-06-03T00:00:00Z  
1122   nmonmetrics   autogen   1122   2019-06-03T00:00:00Z   2019-06-10T00:00:00Z   2019-06-10T00:00:00Z  
1131   nmonmetrics   autogen   1131   2019-06-10T00:00:00Z   2019-06-17T00:00:00Z   2019-06-17T00:00:00Z  
1140   nmonmetrics   autogen   1140   2019-06-17T00:00:00Z   2019-06-24T00:00:00Z   2019-06-24T00:00:00Z  
1149   nmonmetrics   autogen   1149   2019-06-24T00:00:00Z   2019-07-01T00:00:00Z   2019-07-01T00:00:00Z  
1159   nmonmetrics   autogen   1159   2019-07-01T00:00:00Z   2019-07-08T00:00:00Z   2019-07-08T00:00:00Z  
1168   nmonmetrics   autogen   1168   2019-07-08T00:00:00Z   2019-07-15T00:00:00Z   2019-07-15T00:00:00Z  
1177   nmonmetrics   autogen   1177   2019-07-15T00:00:00Z   2019-07-22T00:00:00Z   2019-07-22T00:00:00Z  
1186   nmonmetrics   autogen   1186   2019-07-22T00:00:00Z   2019-07-29T00:00:00Z   2019-07-29T00:00:00Z  
1195   nmonmetrics   autogen   1195   2019-07-29T00:00:00Z   2019-08-05T00:00:00Z   2019-08-05T00:00:00Z  
1204   nmonmetrics   autogen   1204   2019-08-05T00:00:00Z   2019-08-12T00:00:00Z   2019-08-12T00:00:00Z  
1213   nmonmetrics   autogen   1213   2019-08-12T00:00:00Z   2019-08-19T00:00:00Z   2019-08-19T00:00:00Z  
1222   nmonmetrics   autogen   1222   2019-08-19T00:00:00Z   2019-08-26T00:00:00Z   2019-08-26T00:00:00Z  
1232   nmonmetrics   autogen   1232   2019-08-26T00:00:00Z   2019-09-02T00:00:00Z   2019-09-02T00:00:00Z  
1241   nmonmetrics   autogen   1241   2019-09-02T00:00:00Z   2019-09-09T00:00:00Z   2019-09-09T00:00:00Z  
1250   nmonmetrics   autogen   1250   2019-09-09T00:00:00Z   2019-09-16T00:00:00Z   2019-09-16T00:00:00Z  
1259   nmonmetrics   autogen   1259   2019-09-16T00:00:00Z   2019-09-23T00:00:00Z   2019-09-23T00:00:00Z  
1268   nmonmetrics   autogen   1268   2019-09-23T00:00:00Z   2019-09-30T00:00:00Z   2019-09-30T00:00:00Z  
1277   nmonmetrics   autogen   1277   2019-09-30T00:00:00Z   2019-10-07T00:00:00Z   2019-10-07T00:00:00Z  
1286   nmonmetrics   autogen   1286   2019-10-07T00:00:00Z   2019-10-14T00:00:00Z   2019-10-14T00:00:00Z  
1295   nmonmetrics   autogen   1295   2019-10-14T00:00:00Z   2019-10-21T00:00:00Z   2019-10-21T00:00:00Z  
1305   nmonmetrics   autogen   1305   2019-10-21T00:00:00Z   2019-10-28T00:00:00Z   2019-10-28T00:00:00Z  
1314   nmonmetrics   autogen   1314   2019-10-28T00:00:00Z   2019-11-04T00:00:00Z   2019-11-04T00:00:00Z  
1323   nmonmetrics   autogen   1323   2019-11-04T00:00:00Z   2019-11-11T00:00:00Z   2019-11-11T00:00:00Z  
1332   nmonmetrics   autogen   1332   2019-11-11T00:00:00Z   2019-11-18T00:00:00Z   2019-11-18T00:00:00Z  
1341   nmonmetrics   autogen   1341   2019-11-18T00:00:00Z   2019-11-25T00:00:00Z   2019-11-25T00:00:00Z  
1350   nmonmetrics   autogen   1350   2019-11-25T00:00:00Z   2019-12-02T00:00:00Z   2019-12-02T00:00:00Z  
1359   nmonmetrics   autogen   1359   2019-12-02T00:00:00Z   2019-12-09T00:00:00Z   2019-12-09T00:00:00Z  
1368   nmonmetrics   autogen   1368   2019-12-09T00:00:00Z   2019-12-16T00:00:00Z   2019-12-16T00:00:00Z  
1378   nmonmetrics   autogen   1378   2019-12-16T00:00:00Z   2019-12-23T00:00:00Z   2019-12-23T00:00:00Z  
1387   nmonmetrics   autogen   1387   2019-12-23T00:00:00Z   2019-12-30T00:00:00Z   2019-12-30T00:00:00Z  
1396   nmonmetrics   autogen   1396   2019-12-30T00:00:00Z   2020-01-06T00:00:00Z   2020-01-06T00:00:00Z  
1405   nmonmetrics   autogen   1405   2020-01-06T00:00:00Z   2020-01-13T00:00:00Z   2020-01-13T00:00:00Z  
1414   nmonmetrics   autogen   1414   2020-01-13T00:00:00Z   2020-01-20T00:00:00Z   2020-01-20T00:00:00Z  
1423   nmonmetrics   autogen   1423   2020-01-20T00:00:00Z   2020-01-27T00:00:00Z   2020-01-27T00:00:00Z  
1432   nmonmetrics   autogen   1432   2020-01-27T00:00:00Z   2020-02-03T00:00:00Z   2020-02-03T00:00:00Z  
1441   nmonmetrics   autogen   1441   2020-02-03T00:00:00Z   2020-02-10T00:00:00Z   2020-02-10T00:00:00Z  
1451   nmonmetrics   autogen   1451   2020-02-10T00:00:00Z   2020-02-17T00:00:00Z   2020-02-17T00:00:00Z  
1460   nmonmetrics   autogen   1460   2020-02-17T00:00:00Z   2020-02-24T00:00:00Z   2020-02-24T00:00:00Z  
1469   nmonmetrics   autogen   1469   2020-02-24T00:00:00Z   2020-03-02T00:00:00Z   2020-03-02T00:00:00Z  
1478   nmonmetrics   autogen   1478   2020-03-02T00:00:00Z   2020-03-09T00:00:00Z   2020-03-09T00:00:00Z  
1487   nmonmetrics   autogen   1487   2020-03-09T00:00:00Z   2020-03-16T00:00:00Z   2020-03-16T00:00:00Z  
1496   nmonmetrics   autogen   1496   2020-03-16T00:00:00Z   2020-03-23T00:00:00Z   2020-03-23T00:00:00Z  
1505   nmonmetrics   autogen   1505   2020-03-23T00:00:00Z   2020-03-30T00:00:00Z   2020-03-30T00:00:00Z  
1514   nmonmetrics   autogen   1514   2020-03-30T00:00:00Z   2020-04-06T00:00:00Z   2020-04-06T00:00:00Z  
1524   nmonmetrics   autogen   1524   2020-04-06T00:00:00Z   2020-04-13T00:00:00Z   2020-04-13T00:00:00Z  
1533   nmonmetrics   autogen   1533   2020-04-13T00:00:00Z   2020-04-20T00:00:00Z   2020-04-20T00:00:00Z  
1542   nmonmetrics   autogen   1542   2020-04-20T00:00:00Z   2020-04-27T00:00:00Z   2020-04-27T00:00:00Z  
1551   nmonmetrics   autogen   1551   2020-04-27T00:00:00Z   2020-05-04T00:00:00Z   2020-05-04T00:00:00Z  
1561   nmonmetrics   autogen   1561   2020-05-04T00:00:00Z   2020-05-11T00:00:00Z   2020-05-11T00:00:00Z  
1570   nmonmetrics   autogen   1570   2020-05-11T00:00:00Z   2020-05-18T00:00:00Z   2020-05-18T00:00:00Z  
1579   nmonmetrics   autogen   1579   2020-05-18T00:00:00Z   2020-05-25T00:00:00Z   2020-05-25T00:00:00Z  
1588   nmonmetrics   autogen   1588   2020-05-25T00:00:00Z   2020-06-01T00:00:00Z   2020-06-01T00:00:00Z  
1598   nmonmetrics   autogen   1598   2020-06-01T00:00:00Z   2020-06-08T00:00:00Z   2020-06-08T00:00:00Z  
1607   nmonmetrics   autogen   1607   2020-06-08T00:00:00Z   2020-06-15T00:00:00Z   2020-06-15T00:00:00Z  
1616   nmonmetrics   autogen   1616   2020-06-15T00:00:00Z   2020-06-22T00:00:00Z   2020-06-22T00:00:00Z  
1625   nmonmetrics   autogen   1625   2020-06-22T00:00:00Z   2020-06-29T00:00:00Z   2020-06-29T00:00:00Z  
1634   nmonmetrics   autogen   1634   2020-06-29T00:00:00Z   2020-07-06T00:00:00Z   2020-07-06T00:00:00Z  
1643   nmonmetrics   autogen   1643   2020-07-06T00:00:00Z   2020-07-13T00:00:00Z   2020-07-13T00:00:00Z  
1652   nmonmetrics   autogen   1652   2020-07-13T00:00:00Z   2020-07-20T00:00:00Z   2020-07-20T00:00:00Z  
1661   nmonmetrics   autogen   1661   2020-07-20T00:00:00Z   2020-07-27T00:00:00Z   2020-07-27T00:00:00Z  
1671   nmonmetrics   autogen   1671   2020-07-27T00:00:00Z   2020-08-03T00:00:00Z   2020-08-03T00:00:00Z  
1680   nmonmetrics   autogen   1680   2020-08-03T00:00:00Z   2020-08-10T00:00:00Z   2020-08-10T00:00:00Z  
1689   nmonmetrics   autogen   1689   2020-08-10T00:00:00Z   2020-08-17T00:00:00Z   2020-08-17T00:00:00Z  
1698   nmonmetrics   autogen   1698   2020-08-17T00:00:00Z   2020-08-24T00:00:00Z   2020-08-24T00:00:00Z  
1707   nmonmetrics   autogen   1707   2020-08-24T00:00:00Z   2020-08-31T00:00:00Z   2020-08-31T00:00:00Z  
1716   nmonmetrics   autogen   1716   2020-08-31T00:00:00Z   2020-09-07T00:00:00Z   2020-09-07T00:00:00Z  
1725   nmonmetrics   autogen   1725   2020-09-07T00:00:00Z   2020-09-14T00:00:00Z   2020-09-14T00:00:00Z  
1734   nmonmetrics   autogen   1734   2020-09-14T00:00:00Z   2020-09-21T00:00:00Z   2020-09-21T00:00:00Z  
1744   nmonmetrics   autogen   1744   2020-09-21T00:00:00Z   2020-09-28T00:00:00Z   2020-09-28T00:00:00Z  
1753   nmonmetrics   autogen   1753   2020-09-28T00:00:00Z   2020-10-05T00:00:00Z   2020-10-05T00:00:00Z  
1762   nmonmetrics   autogen   1762   2020-10-05T00:00:00Z   2020-10-12T00:00:00Z   2020-10-12T00:00:00Z  
1771   nmonmetrics   autogen   1771   2020-10-12T00:00:00Z   2020-10-19T00:00:00Z   2020-10-19T00:00:00Z  
1780   nmonmetrics   autogen   1780   2020-10-19T00:00:00Z   2020-10-26T00:00:00Z   2020-10-26T00:00:00Z  
1789   nmonmetrics   autogen   1789   2020-10-26T00:00:00Z   2020-11-02T00:00:00Z   2020-11-02T00:00:00Z  
1798   nmonmetrics   autogen   1798   2020-11-02T00:00:00Z   2020-11-09T00:00:00Z   2020-11-09T00:00:00Z  
1807   nmonmetrics   autogen   1807   2020-11-09T00:00:00Z   2020-11-16T00:00:00Z   2020-11-16T00:00:00Z  
1817   nmonmetrics   autogen   1817   2020-11-16T00:00:00Z   2020-11-23T00:00:00Z   2020-11-23T00:00:00Z  
1826   nmonmetrics   autogen   1826   2020-11-23T00:00:00Z   2020-11-30T00:00:00Z   2020-11-30T00:00:00Z  
1835   nmonmetrics   autogen   1835   2020-11-30T00:00:00Z   2020-12-07T00:00:00Z   2020-12-07T00:00:00Z  
1844   nmonmetrics   autogen   1844   2020-12-07T00:00:00Z   2020-12-14T00:00:00Z   2020-12-14T00:00:00Z  
1853   nmonmetrics   autogen   1853   2020-12-14T00:00:00Z   2020-12-21T00:00:00Z   2020-12-21T00:00:00Z  
1862   nmonmetrics   autogen   1862   2020-12-21T00:00:00Z   2020-12-28T00:00:00Z   2020-12-28T00:00:00Z  
1871   nmonmetrics   autogen   1871   2020-12-28T00:00:00Z   2021-01-04T00:00:00Z   2021-01-04T00:00:00Z  
1880   nmonmetrics   autogen   1880   2021-01-04T00:00:00Z   2021-01-11T00:00:00Z   2021-01-11T00:00:00Z  
1890   nmonmetrics   autogen   1890   2021-01-11T00:00:00Z   2021-01-18T00:00:00Z   2021-01-18T00:00:00Z  
1899   nmonmetrics   autogen   1899   2021-01-18T00:00:00Z   2021-01-25T00:00:00Z   2021-01-25T00:00:00Z  
1908   nmonmetrics   autogen   1908   2021-01-25T00:00:00Z   2021-02-01T00:00:00Z   2021-02-01T00:00:00Z  
1917   nmonmetrics   autogen   1917   2021-02-01T00:00:00Z   2021-02-08T00:00:00Z   2021-02-08T00:00:00Z  
1926   nmonmetrics   autogen   1926   2021-02-08T00:00:00Z   2021-02-15T00:00:00Z   2021-02-15T00:00:00Z  
1935   nmonmetrics   autogen   1935   2021-02-15T00:00:00Z   2021-02-22T00:00:00Z   2021-02-22T00:00:00Z  
1944   nmonmetrics   autogen   1944   2021-02-22T00:00:00Z   2021-03-01T00:00:00Z   2021-03-01T00:00:00Z  
1953   nmonmetrics   autogen   1953   2021-03-01T00:00:00Z   2021-03-08T00:00:00Z   2021-03-08T00:00:00Z  
1963   nmonmetrics   autogen   1963   2021-03-08T00:00:00Z   2021-03-15T00:00:00Z   2021-03-15T00:00:00Z  
1972   nmonmetrics   autogen   1972   2021-03-15T00:00:00Z   2021-03-22T00:00:00Z   2021-03-22T00:00:00Z  
1981   nmonmetrics   autogen   1981   2021-03-22T00:00:00Z   2021-03-29T00:00:00Z   2021-03-29T00:00:00Z  
1990   nmonmetrics   autogen   1990   2021-03-29T00:00:00Z   2021-04-05T00:00:00Z   2021-04-05T00:00:00Z  
1999   nmonmetrics   autogen   1999   2021-04-05T00:00:00Z   2021-04-12T00:00:00Z   2021-04-12T00:00:00Z  
2008   nmonmetrics   autogen   2008   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z   2021-04-19T00:00:00Z  

tw-bert avatar Apr 12 '21 20:04 tw-bert

@dgnorton Two other influx db's on our LAN:

_internal
id   database   retention_policy   shard_group   start_time   end_time   expiry_time   owners  
1891   _internal   monitor   1891   2021-04-05T00:00:00Z   2021-04-06T00:00:00Z   2021-04-13T00:00:00Z  
1893   _internal   monitor   1893   2021-04-06T00:00:00Z   2021-04-07T00:00:00Z   2021-04-14T00:00:00Z  
1894   _internal   monitor   1894   2021-04-07T00:00:00Z   2021-04-08T00:00:00Z   2021-04-15T00:00:00Z  
1895   _internal   monitor   1895   2021-04-08T00:00:00Z   2021-04-09T00:00:00Z   2021-04-16T00:00:00Z  
1896   _internal   monitor   1896   2021-04-09T00:00:00Z   2021-04-10T00:00:00Z   2021-04-17T00:00:00Z  
1897   _internal   monitor   1897   2021-04-10T00:00:00Z   2021-04-11T00:00:00Z   2021-04-18T00:00:00Z  
1898   _internal   monitor   1898   2021-04-11T00:00:00Z   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z  
1899   _internal   monitor   1899   2021-04-12T00:00:00Z   2021-04-13T00:00:00Z   2021-04-20T00:00:00Z  
basmetrics
id   database   retention_policy   shard_group   start_time   end_time   expiry_time   owners  
948   basmetrics   A   948   2014-02-17T00:00:00Z   2024-02-05T00:00:00Z   2024-02-12T00:00:00Z  
1794   basmetrics   B   1794   2021-01-11T00:00:00Z   2021-03-08T00:00:00Z   2021-05-03T00:00:00Z  
1859   basmetrics   B   1859   2021-03-08T00:00:00Z   2021-05-03T00:00:00Z   2021-06-28T00:00:00Z  
1072   basmetrics   C   1072   2019-04-29T00:00:00Z   2020-05-04T00:00:00Z   2021-05-10T00:00:00Z  
1501   basmetrics   C   1501   2020-05-04T00:00:00Z   2021-05-10T00:00:00Z   2022-05-16T00:00:00Z  
879   basmetrics   D   879   2004-03-01T00:00:00Z   2014-02-17T00:00:00Z   2014-02-17T00:00:00Z  
324   basmetrics   D   324   2014-02-17T00:00:00Z   2024-02-05T00:00:00Z   2024-02-05T00:00:00Z  

and

_internal
id   database   retention_policy   shard_group   start_time   end_time   expiry_time   owners  
786   _internal   monitor   786   2021-04-05T00:00:00Z   2021-04-06T00:00:00Z   2021-04-13T00:00:00Z  
788   _internal   monitor   788   2021-04-06T00:00:00Z   2021-04-07T00:00:00Z   2021-04-14T00:00:00Z  
789   _internal   monitor   789   2021-04-07T00:00:00Z   2021-04-08T00:00:00Z   2021-04-15T00:00:00Z  
790   _internal   monitor   790   2021-04-08T00:00:00Z   2021-04-09T00:00:00Z   2021-04-16T00:00:00Z  
791   _internal   monitor   791   2021-04-09T00:00:00Z   2021-04-10T00:00:00Z   2021-04-17T00:00:00Z  
792   _internal   monitor   792   2021-04-10T00:00:00Z   2021-04-11T00:00:00Z   2021-04-18T00:00:00Z  
793   _internal   monitor   793   2021-04-11T00:00:00Z   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z  
794   _internal   monitor   794   2021-04-12T00:00:00Z   2021-04-13T00:00:00Z   2021-04-20T00:00:00Z  
telegraf
id   database   retention_policy   shard_group   start_time   end_time   expiry_time   owners  
787   telegraf   A   787   2021-04-05T00:00:00Z   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z  
795   telegraf   A   795   2021-04-12T00:00:00Z   2021-04-19T00:00:00Z   2021-04-26T00:00:00Z  

tw-bert avatar Apr 12 '21 20:04 tw-bert

@tw-bert thanks! Could you also provide EXPLAIN ANALYZE output for a sample query that should return a few points from a single shard but isn't returning any data? Narrowing it down to a single shard that exists but isn't returning data may help. Andy provided his data via email and I've looked through that today. Will look through yours first thing in the morning. Thanks again

dgnorton avatar Apr 12 '21 22:04 dgnorton

@dgnorton Thank you, for looking into this.

Atm we don't have faulty queries. It is something that happens now and then. Hard to reproduce... so also hard to fix.

Maybe the post at the start of this issue provides a reproducible scenario?

tw-bert avatar Apr 12 '21 22:04 tw-bert