Welcome to the Cumulus Support forum.

Latest Cumulus MX release 3.11.4 (build 3133) - 25 May 2021 (please see announcement regarding releases since 3.5.0)
Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

Making integrity of the SQL database equal to the logfiles

Please DO NOT use this to publish your entire wish. This Forum is for specific suggestions to enhance the usability of Cumulus MX for all users, NOT your personal requirements. Please check this forum and the rejected forum to make sure you are NOT posting a DUPLICATE suggestion. It will be heavily monitored by Admin and Mark Crossley to determine the feasibility and the difficulty of the suggestion. Those Topics that are deemed inadmissible will moved to the rejected Forum. The remaining Topics will be the Accepted list of future developments, and when our voluntary development group adds it to a build, the build number will be added to the Topic title.
Post Reply
User avatar
HansR
Posts: 2159
Joined: Sat 20 Oct 2012 6:53 am
Weather Station: Davis Vantage Pro 2+
Operating System: Raspbian GNU/Linux 10 (Buster)
Location: Wagenborgen (NL)
Contact:

Making integrity of the SQL database equal to the logfiles

Post by HansR »

Question:
Is it possible to make the SQL database as a data source equal to the logfiles?
Currently, if the database is out for whatever reason, the missed data records are not caught up by CMX and the user has to take manual action, the logfiles however are always complete. I can never be sure of the data integrity of the SQL database.

Some remarks:
With the above point of view of data integrity, will de other sensor data, AirLink etc.... ever be part of the SQL database system?

And to continue: currently the SQL subsystem is there and it isn't. It is fine that some handy users can make full use of it (I could, don't worry) but that is not how it should be if the SQL database were a true data source. Currently it is a toy for techies (yes I am, but the tool I make is for users and only wants to use configurations).
Hans

https://meteo-wagenborgen.nl
Cumulus build 3132 ● Davis Vantage Pro 2+ ● RPi 3B+ ● Raspbian 5.10.17-v7+ ● Mono 5.18.0.240

User avatar
mcrossley
Posts: 8473
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Buster Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Making integrity of the SQL database equal to the logfiles

Post by mcrossley »

Tricky, what does MX do if the database is down? If it cannot write to the log file it is going to fail.

Should it stop the program if the database is not available because say you are rebooting your router for instance?
Should it buffer values until it comes back, what if it never comes back before MX is shutdown? Or never at all!

Currently SQL inserts are asynchronous the prevent them stalling the process, should they be made synchronous.

Lots to think about.

Other data sources such as the online repos behave the same, if they are not contactable during live running then they just miss data for that period. Are they less important data stores than SQL? Maybe to most, but maybe not to others.

Catch-up does work for the period MX is stopped.

User avatar
HansR
Posts: 2159
Joined: Sat 20 Oct 2012 6:53 am
Weather Station: Davis Vantage Pro 2+
Operating System: Raspbian GNU/Linux 10 (Buster)
Location: Wagenborgen (NL)
Contact:

Re: Making integrity of the SQL database equal to the logfiles

Post by HansR »

mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Tricky, what does MX do if the database is down? If it cannot write to the log file it is going to fail.
The assumption is that the log files (always local) are always accessible. If CMX can't write txt files there is a serious system fail.
So the assumption that the logfiles have the highest data integrity should hold (and I think is true). I mean, if the station fails you're gonna miss data as well. There is always a situation thinkable where data will be missed. But that is not the point.

Point is that SQL is kept up to date in realtime. Of course that is done async, but it is still realtime.

mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Should it stop the program if the database is not available because say you are rebooting your router for instance?
No, automatically stopping CMX is never an option, that must be a user choice havving the logger as a bckup (or not). But if the database gets back online CMX can fill the gap automatically.
mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Should it buffer values until it comes back, what if it never comes back before MX is shutdown? Or never at all!
I agree that a long period of database down should require user intervention. So e.g. a logger has two days of data, then if the database is more than two days down will not catch up automatically, any downtime within those two days would trigger the database catchup.

It is thinking about the technical solution. In essence it should only have a guard signalling when the database goes down/up. When it comes up while having missed data it should fill them in as it also fills the month/realtime and dayfile tables if everything goes well. So as said above, with a limit.

That data can be fetched from the the local sources of CMX : memory or logfiles whichever is easiest.
mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Currently SQL inserts are asynchronous the prevent them stalling the process, should they be made synchronous.
Why? I don't think so, all database actions should be async: just dump the data in an array to the database, the system will handle it and you can send the next batch (if any) when the first is ready.
mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Lots to think about.
Not for me 8-)
Seriously: yes there are some things to consider, but I really think it should be a topic if you take the SQL database as a serious data sink/source for CMX itself derivative software.
And on a whole it might make it more secure and data easier to safeguard.
mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Other data sources such as the online repos behave the same, if they are not contactable during live running then they just miss data for that period. Are they less important data stores than SQL? Maybe to most, but maybe not to others.
Yes they are less important because they never completely mirror the logs, the database in principle does.
They are also not so easy to fetch and analysed by the user, they do not contain a complete dataset of CMX. The database does.
mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Catch-up does work for the period MX is stopped.
OK.... so the database is filled up on a normal catch up?

So it does as long as the logger has data and only to the logfiles.
If a user has no logger than of course there is no data (see above) and catch up can't take place.
There is a user responsibility: if he goes on holiday and switches of electricity than bad luck. But that is not what we're talking about.
Hans

https://meteo-wagenborgen.nl
Cumulus build 3132 ● Davis Vantage Pro 2+ ● RPi 3B+ ● Raspbian 5.10.17-v7+ ● Mono 5.18.0.240

User avatar
mcrossley
Posts: 8473
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Buster Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Making integrity of the SQL database equal to the logfiles

Post by mcrossley »

HansR wrote:
Fri 19 Feb 2021 3:14 pm
mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Catch-up does work for the period MX is stopped.
OK.... so the database is filled up on a normal catch up?
Yes, the monthly and daily updates are inserted into SQL on catch up, only the real time is not.

User avatar
HansR
Posts: 2159
Joined: Sat 20 Oct 2012 6:53 am
Weather Station: Davis Vantage Pro 2+
Operating System: Raspbian GNU/Linux 10 (Buster)
Location: Wagenborgen (NL)
Contact:

Re: Making integrity of the SQL database equal to the logfiles

Post by HansR »

mcrossley wrote:
Fri 19 Feb 2021 4:30 pm
HansR wrote:
Fri 19 Feb 2021 3:14 pm
mcrossley wrote:
Fri 19 Feb 2021 2:48 pm
Catch-up does work for the period MX is stopped.
OK.... so the database is filled up on a normal catch up?
Yes, the monthly and daily updates are inserted into SQL on catch up, only the real time is not.
Realtime is not really necessary to have in the database table I think, anyway, so it is already halfway there ;) All that is needed is the database down detection, measurement of the period and fill up over that period (if not too long) when it is up again.
Hans

https://meteo-wagenborgen.nl
Cumulus build 3132 ● Davis Vantage Pro 2+ ● RPi 3B+ ● Raspbian 5.10.17-v7+ ● Mono 5.18.0.240

User avatar
mcrossley
Posts: 8473
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Buster Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Making integrity of the SQL database equal to the logfiles

Post by mcrossley »

I'll implement this in the next release

User avatar
HansR
Posts: 2159
Joined: Sat 20 Oct 2012 6:53 am
Weather Station: Davis Vantage Pro 2+
Operating System: Raspbian GNU/Linux 10 (Buster)
Location: Wagenborgen (NL)
Contact:

Re: Making integrity of the SQL database equal to the logfiles

Post by HansR »

That's great!
Hans

https://meteo-wagenborgen.nl
Cumulus build 3132 ● Davis Vantage Pro 2+ ● RPi 3B+ ● Raspbian 5.10.17-v7+ ● Mono 5.18.0.240

Post Reply