Several devices are reporting no data in the 1 day window

[robinharris]

Data exists in the database but some devices are reporting no data in the 1 day window when it definitely exists. Devices 10, 18 and 39 are all incorrectly showing no data. One of these is a paid for device and the owner regularly reviews his data on the map.

[BNNorman]

Checked my database copy for device 10 - agree with Robin. Reading_values exist right up to today (25/6/2019 8:22 a.m.)

However, there appears to be a problem with the persistent column - it was showing 000-00-00 00:00:00 - and is only affecting 10,17 and 18. I have fixed those readings.

update readings set recordedon=null where s_or_r ="000-00-00 00:00:00";

39 has no data after 22/6/2019 12:25 so it is correct. If you select 1 week then the data appears.

We need to keep an eye on this. I have no idea why only those devices were affected.

[BNNorman]

The same thing had happened on my DB copy at home - mariaDb bug? Or race condition when the s_or_r persistent field was added.

[BNNorman]

Just checked. It continues to happen. The s_or_r column is set correctly by SQL UPDATE but doesn't appear to be set for some devices during SQL INSERT. Some more research needed for this.

[BNNorman]

Testing a theory on my database at home - I think the persistent column should be flagged as 'generated always'

[BNNorman]

It turns out that when the record is being inserted the value of the 'storedon' field is null until the insert is done. The persistent field calculation appears to be done before that time. Hence, since the 'recordedon' field was also null the persistent column took a null value. With none null recordedon values the persistent column was being set correctly.

The fix is a small change in the dbLoader.py SQL to include the storedon value as Now() when a record is inserted into the readings table.

This change is running on my home machine and appears to be working just fine at the moment. I have updated the dbLoader on github (now V1.32) in anticipation.

If the fix works for half an hour or so on my machine, I'll update the dbLoader on the live server.

[BNNorman]

The change has worked on my Pi so I've implemented it on the server and restarted dbLoader. In half an hour, I'll recheck and if no more problems we can close this issue.

[BNNorman]

Yep, seems to be fixed - no more null s_or_r field values

[sbrl]

Excellent work, @BNNorman! Thanks so much for squashing that one.

@robinharris - I was not aware that people have purchased a device and check the web interface. That's really cool! That's quite motivating actually.