weewx sql database repair tools

[VE4PER Andy]

I had a major commercial power failure about 10 days ago. In the process
it ran the UPS down almost completely such that when the last few power
surge/bumps/fails occured something in the date time pointer system , I
believe in the SQL databse became disconnected and immediate restart of
the program on power restoral Had the local /var/www/html page was a
wrong date and the FTP'd  page sent to my web site also had a corrupt
date/Time string and neither agreed nor were either  at all real cuurent
time accurate.

I shut down the PC with the intent of looking for a mySQL dB
troubleshooting/repair software application and attempting to correct
the corrupted data when I had time.  About a week later I found time to
take a look at the problem and when I restated the PC , with the intent
of halting weewx to troubleshooting the dB. It all came back working
normally but with no back archived data available but it appears the
delay from it being down so long allowed it to restart and regenerate
the consistent web pages and messages.

So although it is back working now, I would like to know if their is a
recommended dB software /data repair/maintenance package available that
is open source freeware either standalone program or possibly something
like a plug option/package to use with a browser similar to phpmyadmin.

My 2nd takeaway is when urgent commercial power fails get to the weewx
PC and shut it down immediately in an orderly fashion before the 8-10
min UPS backup support runs out.

Any pointers are welcome, considering so many outside agancies use the
data (eg AWEKAS, CWOP/NOAA/aprs/ WeatherUnderground in addition to my on
local and www page.

Thanks 73

Andy

[vince]

You completely lost me there in all those words.  You said it's working now, so what exactly is the question you're asking ?

[Doug Jenkins]

Andy:

I use dbeaver (dbeaver.io) to manage all my databases. It is a client tool, but it connects to MariaDB, MySQL, and SQLite databases. You can manage the database here.

For your power problem, I would suggest NUT (Network UPS Tool). This can run as a service on your linux server (Including RPi) and you can configure it to talk directly to your UPS (they have many drivers). Here is a good video walkthrough in setting up the tool and creating a quick interface for it.

--
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/31c1e793-4db7-672c-ffc0-01361e7aec82%40gmail.com.

[Tom Keffer]

Not clear what you're asking, but I'd like to know more about the corruption of the database. I spent a lot of time making sure that commits were atomic. Did I miss something? Could you be more specific about what's wrong with the database?

[Greg Troxel]

Tom Keffer <tke...@gmail.com> writes:

> Not clear what you're asking, but I'd like to know more about the
> corruption of the database. I spent a lot of time making sure that commits
> were atomic. Did I miss something? Could you be more specific about what's
> wrong with the database?

I am assuming you mean that you are using database transactions, with
autocommit off and begin/end transaction.

I am curious if people believe:

sqlite properly turns transactions into filesystem writes and fsync
calls (am guessing yes)

if various OS kernels on various platforms properly turn filesystem
write and fsync into hardware write operations that reflect completion
status all the way back to the fsync call

if the actual uSD cards have internal transactions and accurate
completion status reporting, so that if a block reports as complete
and no new write is started to that block, it's guaranteed present
after power loss

It feels difficult to get the entire stack correct, and I admit to being
a bit skeptical.


And +1 for Doug's recommendation of ups-nut.

Also, if your UPS lasts only 8 minutes, it isn't big enough. While
there are a fair number of short glitches up to maybe 15 second outages
(for me, 3x 2s with periods back on at most), my experience then jumps
to 25 minutes, if you are exceeding lucky (lineman and truck are on
duty, in town, and not committed, and your problem is the only one), to
maybe an hour for not quite so lucky. After that all bets are off.
That's a long way of saying that it's best if your UPS can run for
somewhat over an hour.

But whatever it can do, it's good to have automatic shutdown. When UPS
shopping, proper automated shutdown with guaranteed restart when power
returns (or better yet, after the battery is recharged) is one of the
trickiest things to get right. But that's a topic for the NUT
lists/docs.

[VE4PER Andy]

I am not exactly sure what occurred which is why I was looking for a tool to inspect it deeper. Only thing I know for sure was that UPS backup was so run down and deteriorated that the commercial power slamming off and on in rapid succession was passed through to the PC and if it occurred right during a data update cycle from the sensors somehow the whole transaction became so confused that the local page in the /var/www/html folder did not have a correct date time string on it; and a simmilar problem happened to the report generated for transfer via ftp to my personal web page. and neither of these agreed with each other. and neither was the actual time of the data update. Successive updates or page reloads/refreshes displayed these same frozen data pages. Once the system shut down for a week and restarted it began with no existing archive data showing on any graphs but current display data from sensors properly processed and updated properly every 5 mins approx.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEDqJCB-hn39gPTFqVUg%3DRmerrsqAFWqDLx6%2Bxxztq%3DYQg%40mail.gmail.com.

[VE4PER Andy]

Thanks Doug I will check into this further; I had always used APC UPS's but of late units don't come with acceess to detailed logging details wrt short surge type failures any more. I used to be able to get timing messages from that log that helped in troubleshooting.

I have slowly changed to higher capacity CyberPower units with a bit better monitoring and logging but still not a lot of of tech dails in that even to satisfy my Retire Eng Etech background.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CACC0i0yma27VUTjTdoW-KedaMBZrrUYWhfBKj6tk4-krtyLtSA%40mail.gmail.com.