MAP displays are incorrect

[BNNorman]

If you look at brian02 its is a dust sensor giving just PM10 and PM25 values - yet the map shows Humidity, Temperature and pressure (HT&P). This has solved the problem of 'no data' but it's telling porkies. In fact the three sensors, 02,03 and 04, which don't report HT&P all have the same HT&P readings. I suspect your SQL has gone wonky.

[BNNorman]

Also, brian01 which is just a HT&P sensor is reporting PM values which it doesn't send.

[sbrl]

Thanks for the report, @BNNorman! I've investigated, and I've found the following extra data in the database:

brian01 - device id 22

Reading counts:

Measurement type	Count
humidity	1043
PM10	62
PM25	69
pressure	1044
temperature	1043

Hrm. Looks like there are ~69 extra reading rows in there that shouldn't be. I've extracted those from the database like so:

SELECT readings.id,
       readings.storedon,
       readings.raw_json
FROM reading_values
JOIN readings ON reading_values.reading_id = readings.id
WHERE readings.device_id = 22
    AND reading_values.reading_value_types_id = "PM25"
LIMIT 100;

Result: https://hastebin.com/opigeqihev.json

These will need to be cleared out from the database in order to fix the issue. @robinharris is the person to talk to about that I think.

brian02 - device id 24

Reading counts:

Measurement type	Count
humidity	1588
PM10	2400
PM25	2558
pressure	1588
temperature	1588

This is very odd. Here's the query I used to obtain those reading counts:

SELECT reading_value_types.*,
       COUNT(reading_value_types.id) AS count
FROM reading_values
JOIN readings ON
    readings.id = reading_values.reading_id
JOIN reading_value_types ON
    reading_value_types.id = reading_values.reading_value_types_id
WHERE readings.device_id = :device_id
GROUP BY reading_value_types.id;

Again, I suspect some rogue data in the system. Using the following query, I can obtain a sample of the data causing the issue:

SELECT readings.id,
       readings.storedon,
       readings.raw_json,
       reading_values.*
FROM reading_values
JOIN readings ON reading_values.reading_id = readings.id
WHERE readings.device_id = 24
 AND reading_values.reading_value_types_id = "humidity"
LIMIT 100;

Query result download: https://hastebin.com/godihotaca.json

In this case, I've joined the readings table with the reading_values table, and it looks like there are some entries in the reading_values table that are assigned to the wrong reading_id. Let's pick an offending reading_id (e.g. 63457) and take a look at everything that's assigned to it:

SELECT * FROM reading_values WHERE reading_values.reading_id = 63457;

Result: https://hastebin.com/hiduwonoyi.json

...that can't be right?! According to the database, there are 56(!) items in the reading_values table attached to the reading in the readings table with the id 63457. If I use this query:

SELECT raw_json FROM readings WHERE id = 63457;

...I can get the raw JSON that was originally reported:

{'dev': 'brian02', 'PM25': 3, 'PM10': 6}

(Note: JSON uses double quotes. The above is exactly what appears in the database. Again, I'm unsure where the problem lies there.)

This proves that it's not an issue on your end or mine I think. I'm unsure as to how the mechanism by which the data is inserted into the database works, but I suspect that it's gotten confused.

The best course of action here I think is to contact @robinharris to see if we can't work out what's gone wrong.

[BNNorman]

brian01 - I would just remove the PM entries - they are eroneous. brian02 - ordinarily I would suggest it's possibly an SQL inner/outer join returning repeated values OR the data in the table is incorrect. Always had problems with multiple joins myself.

If you let me know the table structures I could recreate it here and play around. It should be possible to export it from mysql if I remember correctly.

To be honest, the data coming from the brian camp has only really been stable for the past week - I would be inclined to delete any earlier data and see where we stand. I'll PM Robin.

[BNNorman]

Actually, I tell a lie. brian01 did originally have a dust sensor - which I removed as it was an old DSM501 which is non-serial and was way off kilter with the others. I'll ask Robin to remove any entries over a week old and see where we stand

[BNNorman]

Installed the database at home so I can check stuff out. Your query on brian02 is flawed, you are missing 'and devices.device_id=24' to restrict the returned values to brian02. Now I can look more carefully at the database entries.

Searching for brian02 (device 24) using this SQL:-

select * from readings where device_id=24 and raw_json like "%humidity%";

returns an empty set indicating no humidity was uploaded for brian02 . Same result for temp and pressure which suggests your SQL for counting readings per device is flawed.

Furthermore it is suspicious that your (corrected) SQL returns the same count for humidity,temperatue and pressure. I'll ponder the SQL a while.

[sbrl]

Hey, thanks for the info! I haven't tested it, but I don't think I've got write access to the database, so it would def. have to be @robinharris who would have to remove the rogue entries, I think.

The first two queries for brian02 do restrict by device id 24. The readings.device_id = :device_id in the first one is just because I took the query straight from the debug logs. The :device_id gets replaced with the device id just before it's sent off to the database.

In the second I do this: .....WHERE readings.device_id = 24....

The third query:

SELECT * FROM reading_values WHERE reading_values.reading_id = 63457;

...doesn't require a filter by device id because it's a single reading I'm extracting from. Each reading is assigned to a single device by it's id.

I think the problem lies in the fact that the data uploaded and present in readings.raw_json is not the same as the data we've found in the reading_values table, leadaing me to think there's a problem in the data insertion code (which I didn't writ and don't have access to as far as I can tell).

[BNNorman]

I'm closing this becasue Robin and I are working on a database change and a new version of the MQTT subscriber which populates the database.

[sbrl]

Cool, thanks @BNNorman :smiley_cat: