Database vs MQTT published data

[Zsolt Máté]

What could be the reason of data (in 30 minute groups) missing from my sql database?

I started publishing weewx data via MQTT, logged with infludb and displayed with grafana. While there's nothing missing in influx, my msql is dropping lots of data.

I am using interceptor as driver.

[vince]

Are you sure you're not just seeing gaps in the 'graphs' of the data ?

See if you're really missing intervals in the db by querying your db directly.

[Zsolt Máté]

I am missing the data in mysql.

See the two attachments. The last hour in phpmysql vs grafana.

There are just 7 records in the last hour, but AFAIK my interceptor gets data every ~16 seconds - see the graph drawn by grafana using influxdb data.

[Tom Keffer]

Hard to say without seeing the log. Is the MySQL server located on an unreliable network link? Unfortunately, WeeWX cannot recover from a MySQL write error (see issue #259).

Take a look in your system log for any WeeWX errors, and in the MySQL log for any database errors.

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/fd9dab82-7f31-450e-9f0c-44e1d136e6a4n%40googlegroups.com.

[Zsolt Máté]

Since 04:30AM the data stream is continuous again, see attachment. For the record, the dropouts were 30 minutes long, followed by a 30 minutes good data logs.

The mysql server is running on the same raspberry pi as weewx. I think this rules out the possibility of having an unreliable network link.

I've restarted weewex after setting the debug flag to 1. I'll keep an eye on the server and check the log when it happens again.

/var/log/mysql/error.log is empty

[vince]

On Tuesday, January 26, 2021 at 1:38:20 PM UTC-8 lop...@gmail.com wrote:

Since 04:30AM the data stream is continuous again, see attachment. For the record, the dropouts were 30 minutes long, followed by a 30 minutes good data logs.

The mysql server is running on the same raspberry pi as weewx. I think this rules out the possibility of having an unreliable network link.

Can you provide more info on your exact setup ?

You have weewx and mysqld and influxdb and the interceptor driver all on the same raspi ?

What model pi ?   How much memory ?

What wifi band are you on (2.4 or 5 GHz) ?

I used to run a reasonably complicated pi3 or pi3+ setup with weewx, influxdb and MQTT and had lots of periodic issues with data loss.  Switching the pi to wired ethernet helped a lot.   Moving influxdb from the pi to a nice fast  i3 NUC with SSD that was also wired ethernet made the issue go away completely.

[Remy LAVABRE]

Hello Zsolt Mate. I unfortunately do not have the solution to your problem, but on the other hand, I would have a question for you:
Would it be possible to know how you did in the curves of speed and direction of the wind to indicate in the scale of Y the values "N", "W", "S" and "E" instead of "000 "," 90 "," 180 "and" 270 "?
thank you very much

Rémy LAVABRE

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/08529f65-a866-430f-b05d-a8cda42de4e9n%40googlegroups.com.

[Michael]

Hi,

Are you really sure that the data in the Influxdb is complete?
Maybe you have the following in your Grafana query in the grouping: GROUP BY time($__interval) fill(none)
This simply fills possible gaps.

Michael

[Zsolt Máté]

As an extra info, weewx is failing to generate the html during these periods, but interceptor and mqtt are able to function.

Vince,

I'll describe my configuration, but I'm going to attach a graphical representation as well:

wx station is connected via 2.5GHz to an AP hosted on the pi (model 3) using hostapd. The pi is hosting weewx nginx and mysql, and is connected to the LAN via cable.

grafana and influxdb are running in Home Assistant, hosted on a pretty powerful IBM server.

Rémy,

I'm running Belchertown weewx skin, developed by Pat.

Michael,

I know about filling the gaps. I have much more data in Grafana, even if I sent fill(none). See attached image. It is the same interval as in the opening post.

[vince]

On Wednesday, January 27, 2021 at 4:33:25 PM UTC-8 lop...@gmail.com wrote:

As an extra info, weewx is failing to generate the html during these periods, but interceptor and mqtt are able to function.

As Tom asked, we're going to need to see your syslogs with weewx debug=1 set in order to work this one.

You have a lot of things running on that pi.  Pat's skin is pretty heavyweight.  Do you have other skins enabled too ?  You have the box acting as an access point.  You're running interceptor.  You're running mysql.   You're feeding influxdb in your remote vm.   That's a lot of things expecting the pi network stack to work with multiple NICs and the processor to be able to keep up with.  And it's a pi3 so it's 2.4 GHz which can be bouncy as well.  That's a lot of things to have to be perfect.

So we need to see some syslogs for the weewx stuff, but I'd suggest taking grafana out of the picture completely and just doing influxdb queries and weewx db queries for the same time periods so we can verify that there really is data missing in one db or the other.   Find a one-hour period and just query each directly.

[Zsolt Máté]

The dropouts have no pattern. I have a few days without any issues, and then for 24hrs mysql starts choking.

At the beginning I didn't have the influx/grafana part. I've set it up for testing purposes, to see if mqtt is pushing out data even if the mysql is behaving.

Previously I tried setting up a vm/ct just for weewx, but I didn't manage to get interceptor working on virtual interfaces. 

For now I've set the debug flag to 1, so as soon as I see any issue I'll mark the time and start looking and posting the syslog.

[vince]

If you continue to not do what we ask, we're eventually going to give up trying to help.

Find a time period where you're getting data, then it stops, then it restarts.

Query your weewx database and get us the archive table records for that period (sorry, I don't recall mysqld syntax for that, I use sqlite3)

Query your influxdb for the same time period and get us the data for that same time period

Compare the timestamps on both.   During your 'lost data' time period did both databases miss the data ?

Do not use grafana.  Use direct db queries for both databases please.