Scrape Util not inserting data

[eileenpeppard]

Hi, Gang.

The Frog Webctrl data stopped in mid-March. A few sensors have data on 4/9. I don't know what is wrong. Justin Delp realized our vitual machine was full and added space around 4/9. Data is in the Webctrl, just not getting to our database.

Regards, Eileen

[carlosparadis]

@ryantanaka May want to check the timestamps on scrape-util so it doesn't skip any data. It's strange some sensors have data and others don't, considering scrape-util is a single cron-job. I would imagine something went wrong on these for this weird behavior.

[ryantanaka]

@carlosparadis, right i will take a look at those. ATM i have scrape-util off (cronjob won't be running), and I will be manually running it and looking at its output so I can see where the database inserts fail (or if there is problems pulling from sensors).

[eileenpeppard]

Thanks, Ryan.

[ryantanaka]

@carlosparadis, some things that I have so far.

There was one error that came up that I don't understand. This happened once, and I was able to run scrape-util without doing anything. Just as a note, this is the output:

When the program tried to query webctrl for that sensor, the server responded with error code 400.

Anyway

Currently, it appears that scrape-util is not able to advance its state time stamps for webctrl because the state-timestamps are stuck at times prior to 3/16, 3/17, or 3/18. For example, in /var/local/csbcd-configs/scrape-util-projects/uhm-frog/state-files/webctrl.toml, we have frog-1-service-lights-avg-kw = 1552162500.0 (Saturday, March 9, 2019 10:15:00 AM GMT-10:00).

I believe this corresponds topurpose_id 93.

 purpose_id | building | variable_name  | unit |      type      |      appliance      | room | surface | sample_resolution |                  query_string                   | note
------------+----------+----------------+------+----------------+---------------------+------+---------+-------------------+-------------------------------------------------+------
         93 | frog-1   | service-lights | kW   | Power-avg (kW) | Indoor lights power |      |         | 5 min             | ABSPATH:1:#powerscout_frog_1e/kw_avg_phase_b_tn |
(1 row)

Now, the latest data inserted for this sensor is listed here:

frog_uhm=# select * from reading where purpose_id=93 order by datetime desc limit 10;
      datetime       | purpose_id | units | reading |    upload_timestamp
---------------------+------------+-------+---------+------------------------
 2019-03-16 10:20:00 |         93 | kW    |    0.24 | 2019-03-16 10:31:32.77
 2019-03-16 10:15:00 |         93 | kW    |    0.24 | 2019-03-16 10:31:32.77
 2019-03-16 10:10:00 |         93 | kW    |    0.24 | 2019-03-16 10:11:39.69
 2019-03-16 10:05:00 |         93 | kW    |    0.34 | 2019-03-16 10:11:39.69
 2019-03-16 10:00:00 |         93 | kW    |    0.38 | 2019-03-16 10:01:35.38
 2019-03-16 09:55:00 |         93 | kW    |    0.24 | 2019-03-16 10:01:35.38
 2019-03-16 09:50:00 |         93 | kW    |    0.24 | 2019-03-16 09:51:32.14
 2019-03-16 09:45:00 |         93 | kW    |    0.24 | 2019-03-16 09:51:32.14
 2019-03-16 09:40:00 |         93 | kW    |    0.24 | 2019-03-16 09:41:30.74
 2019-03-16 09:35:00 |         93 | kW    |    0.24 | 2019-03-16 09:41:30.74
(10 rows)

So right now, data is pulled for this sensor from March 9th, but then is unable to be inserted because data already exists for that datetime and purpose_id. To verify this, I added some print statements and we can see that duplicates are trying to be inserted (for about 230000 rows).

Since the insertion fails, the timestamp can't be updated, and this process was just repeating over and over.

Right now, I've advanced frog-2-lutron-lights-kwh from 1552234800.0(Sunday, March 10, 2019 6:20:00 AM GMT-10:00) to 1554717600.0 (Monday, April 8, 2019 12:00:00 AM GMT-10:00) to see if we can start getting data from 4/8 when webctrl came back on. If that works, then I'll need to go ahead and advance all the timestamps for webctrl to 4/8.

[ryantanaka]

Notes:

frog-1-ceiling-fans-avg-kw = 1552162500.0 (Saturday, March 9, 2019 10:15:00 AM GMT-10:00) fast forwarding to 1554717600 (Monday, April 8, 2019 12:00:00 AM GMT-10:00)

[carlosparadis]

@ryantanaka hey ryan, we are having a follow-up problem of this on #33. Any chance you can post what you spent time on your diagnosis? It's been almost a month now :') I worry you will forget the details, if not already.

[carlosparadis]

Based on what I diagnosed from #33, and what was written on this issue log, my current best assumption is that because the state timestamp ended up before the most recent reading timestamp in the database, it assumes, wrongly, "no new data is available". And there stays forever assuming that, since the state timestamp will never be updated.

The suggestion by Ryan above, to force the timestamp to after webctrl is back, likely reads a timestamp after the most recent one of the database. I am adding more details to #23 that may verify this fact.

Update: Adding a timestamp just one minute ahead from the latest reading timestamp on #33 did not update the data either.

My last guess at this point is to believe the problem can be anywhere. Good thing we are moving away from this code.