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).
Welcome to the Cumulus Support forum.
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4018) - 28 March 2024
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
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4018) - 28 March 2024
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
- HansR
- Posts: 5871
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bullseye
- Location: Wagenborgen (NL)
- Contact:
Making integrity of the SQL database equal to the logfiles
Hans
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
- mcrossley
- Posts: 12694
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Making integrity of the SQL database equal to the logfiles
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.
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.
- HansR
- Posts: 5871
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bullseye
- Location: Wagenborgen (NL)
- Contact:
Re: Making integrity of the SQL database equal to the logfiles
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.
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.
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.
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.
Not for me
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.
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.
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
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
- mcrossley
- Posts: 12694
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Making integrity of the SQL database equal to the logfiles
Yes, the monthly and daily updates are inserted into SQL on catch up, only the real time is not.
- HansR
- Posts: 5871
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bullseye
- Location: Wagenborgen (NL)
- Contact:
Re: Making integrity of the SQL database equal to the logfiles
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
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
- mcrossley
- Posts: 12694
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Making integrity of the SQL database equal to the logfiles
I'll implement this in the next release
- HansR
- Posts: 5871
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bullseye
- Location: Wagenborgen (NL)
- Contact:
Re: Making integrity of the SQL database equal to the logfiles
That's great!
Hans
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3