Cumulus Support

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

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.

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

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.

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.

I'll implement this in the next release

That's great!