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

from air-quality-web.

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

from air-quality-web.

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:

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:

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.

from air-quality-web.

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.

from air-quality-web.

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.

from air-quality-web.

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).

from air-quality-web.

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.

from air-quality-web.

Cool, thanks @BNNorman 😺

from air-quality-web.