Can I preserve NOAA history after database corruption & reinstall

[Dave, G1OGY]

I have suffered database corruption (on a RPi - Yes...)

I am by no means a `climate researcher` - I just like to know what the weather 

is doing/has been doing...

So. Two questions:

1) Given I upload all data to my website (where this morning's web pages remain 

- with all data accessible) will the NOAA files be overwritten if I were to:

 a)  reinstall the Pi with `buster`

 b)  install the new version of weewx

 c)  copy the NOAA .txt files to the appropriate place...

and carry on?

2) I have my weewx logs on a nfs mount (to reduce SD card writes, primarily; though 

I log only errors).  

Given the history of broad RPi database corruption; has anyone has success with 

hosting the database (or even the whole weewx installation) on an nfs mount?

This is a Pi3 with Gigabit (OK :  =< 150Mb/sec) Ethernet connectivity.

TIA for any feedback.

-D

[Thomas Keffer]

1. Depends on how you're doing the uploading. If using FTP, then WeeWX checks to see if there is a newer version available. If so, it uploads. If rsync, it synchronizes the two directories so they have the same content.  Either way, this is a tenuous way to maintain your NOAA reports. You're better off fixing the database, if possible. There's no substitute for good, primary data.

2. I know the RPi has a reputation for corrupting SD cards, but I haven't seen any hard data to that effect. In fact, just the opposite: I've been running an experiment on how long a card can survive in the WeeWX environment. It has now gone for nearly 5 years without a problem. If you have a good card on a good UPS, you can go for years without a problem. Just do sensible backups, and swap out the card every once in a while. Or, use a USB mounted SSD or hard drive. 

In any case, running a database over a NFS mount is unlikely to work very well. The problem is that many NFS implementations have subtle problems with their locking mechanisms. See the SQLite FAQ. 

-tk

--
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/a74e3bc2-7346-445f-b0f5-eee9e7ecdc2d%40googlegroups.com.

[vince]

On Thursday, October 31, 2019 at 6:41:04 AM UTC-7, Thomas Keffer wrote:

 Just do sensible backups

That's the key for new pi users.   The pi is just like any other computer.  If there's data you care about, you need to save that someplace else periodically.

There have been 'many' discussions here on how to minimize card writes on the pi to help make the card corruption risk lower, as well as to do periodic backups and how to check your backup is indeed restorable.  Do some searching in the list archives for those long detailed discussions and instructions.

I'm a bit surprised to hear a pi3 corrupted a SD card.  I've seen that a lot on model-B in years past but not for several years myself here.  I've never corrupted a card on a pi3 or pi3+ or pi4.

My recollection is you can host the database elsewhere if you switch off sqlite3 to mysql but that ups the ante quite a bit in terms of compexity for most folks.  Personally I just do a backup every week or so via scp to another system, and rely on the Davis logger to store its 8 days or so of records for the interim if the system crashed mid-week, hypothetically.

Again, the list archives have lots of great info in them.  Do some searching.

[Dave, G1OGY]

[ Didn't realise that `groups` defaults to top-posting - Oh well... ]

Thanks for the advice Tom

`integrity_check` reports 100 lines of scary stuff with `row out of order`, `multiple uses of byte`   and  `row missing from index`

the dump file exports a 14MB database to a tad under 30MB in 65K rows - which seems a bit small (?)

The `archive` table dump ends like this (I have truncated the row data):

INSERT INTO "archive" VALUES(1559748300,17,5,1.0067543065450712536e+03,1003.2,1.00648074943336762743e+03,

INSERT INTO "archive" VALUES(199647744053,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

/**** ERROR: (11) database disk image is malformed *****/

CREATE TABLE archive_day_barometer (dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, wsum REAL, sumtime INTEGER);

/**** ERROR: (11) database disk image is malformed *****/

INSERT INTO "archive_day_barometer" VALUES(1572480000,1.0192

INSERT INTO "archive_day_barometer" VALUES(1572393600,1.021

INSERT INTO "archive_day_barometer" VALUES(1572307200,1.022

the date of the final valid INSERT is 5th June (system started 1st March).  The dateTime of the last archive row is obviously corrupt but taking off the excess chars takes it forward to 2033!!

The two `ERROR` flags are the only ones in the dump - I cannot relate the 2nd `ERROR` though the _baro_ table appears to be in descending order while all others are ascending....

*****

In short: Is it worth persevering with this DB given it (essentially) truncates 5 months ago?

*****

Grateful for any further comments.

-D

To unsubscribe from this group and stop receiving emails from it, send an email to weewx...@googlegroups.com.

[Dave, G1OGY]

On Thursday, 31 October 2019 14:29:37 UTC, vince wrote:

On Thursday, October 31, 2019 at 6:41:04 AM UTC-7, Thomas Keffer wrote:

 Just do sensible backups

I'm a bit surprised to hear a pi3 corrupted a SD card.  I've seen that a lot on model-B in years past but not for several years myself here.  I've never corrupted a card on a pi3 or pi3+ or pi4.

The card is fine Vince.  Runs `stretch` and anything - including weewx !

this was the error print prior to stopping the system and examining the DB:

Oct 31 10:25:15 raspberrypi4 weewx[6183]: manager: Added record 2019-10-31 10:25:00 GMT (1572517500) to database 'weewx.sdb'

Oct 31 10:25:15 raspberrypi4 weewx[6183]: manager: Added record 2019-10-31 10:25:00 GMT (1572517500) to daily summary in 'weewx.sdb'

Oct 31 10:25:21 raspberrypi4 weewx[6183]: reportengine: Caught unrecoverable exception in generator 'weewx.imagegenerator.ImageGenerator'

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****  database disk image is malformed

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****  Traceback (most recent call last):

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 204, in run

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      obj.start()

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 300, in start

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      self.run()

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/imagegenerator.py", line 32, in run

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      self.genImages(self.gen_ts)

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/imagegenerator.py", line 166, in genImages

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      aggregate_interval=aggregate_interval)

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/manager.py", line 513, in getSqlVectors

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      aggregate_type, aggregate_interval)

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/manager.py", line 761, in _getSqlVectors

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      _cursor.execute(sql_str, stamp)

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weedb/sqlite.py", line 29, in guarded_fn

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      return fn(*args, **kwargs)

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weedb/sqlite.py", line 211, in execute

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****      return sqlite3.Cursor.execute(self, *args, **kwargs)

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****  DatabaseError: database disk image is malformed

Oct 31 10:25:21 raspberrypi4 weewx[6183]:         ****  Generator terminated

Oct 31 10:25:21 raspberrypi4 weewx[6183]: copygenerator: copied 5 files to /var/www/html/weewx

Oct 31 10:25:21 raspberrypi4 weewx[6183]: imagegenerator: Generated 6 images for SmartphoneReport in 0.44 seconds

Oct 31 10:25:21 raspberrypi4 weewx[6183]: copygenerator: copied 6 files to /var/www/html/weewx/smartphone

Oct 31 10:30:35 raspberrypi4 weewx[6183]: manager: Added record 2019-10-31 10:30:00 GMT (1572517800) to database 'weewx.sdb'

Oct 31 10:30:35 raspberrypi4 weewx[6183]: manager: Added record 2019-10-31 10:30:00 GMT (1572517800) to daily summary in 'weewx.sdb'

Oct 31 10:30:42 raspberrypi4 weewx[6183]: reportengine: Caught unrecoverable exception in generator 'weewx.imagegenerator.ImageGenerator'

Oct 31 10:30:42 raspberrypi4 weewx[6183]:         ****  database disk image is malformed

Oct 31 10:30:42 raspberrypi4 weewx[6183]:         ****  Traceback (most recent call last):

Oct 31 10:30:42 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 204, in run

Oct 31 10:30:42 raspberrypi4 weewx[6183]:         ****      obj.start()

Oct 31 10:30:42 raspberrypi4 weewx[6183]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 300, in start

Yet, all images/html files were generated and uploaded to my website.

 

Again, the list archives have lots of great info in them.  Do some searching.

Plenty of searching done.  Here and elsewhere.  My questions were specific.

Now: just for fun, I have restarted weewx the error print has changed:

Oct 31 15:03:13 raspberrypi4 weewx[2513]: forecast: ZambrettiThread: Zambretti: saved 1 forecast records

Oct 31 15:03:13 raspberrypi4 weewx[2513]: forecast: ZambrettiThread: Zambretti: deleted forecasts prior to 1571929393

Oct 31 15:03:13 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 10:43:59 GMT (1572518639) to database 'weewx.sdb'

Oct 31 15:03:13 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 10:43:59 GMT (1572518639) to daily summary in 'weewx.sdb'

Oct 31 15:03:16 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 10:48:59 GMT (1572518939) to database 'weewx.sdb'

Oct 31 15:03:16 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 10:48:59 GMT (1572518939) to daily summary in 'weewx.sdb'

.

.

Oct 31 15:03:42 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 15:02:59 GMT (1572534179) to database 'weewx.sdb'

Oct 31 15:03:42 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 15:02:59 GMT (1572534179) to daily summary in 'weewx.sdb'

Oct 31 15:03:42 raspberrypi4 weewx[2513]: engine: Starting main packet loop.

Oct 31 15:03:42 raspberrypi4 weewx[2513]: fousb: station status {'unknown': 0, 'lost_connection': 0, 'rain_overflow': 0} (0)

Oct 31 15:05:50 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 15:05:00 GMT (1572534300) to database 'weewx.sdb'

Oct 31 15:05:50 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 15:05:00 GMT (1572534300) to daily summary in 'weewx.sdb'

Oct 31 15:06:05 raspberrypi4 weewx[2513]: reportengine: Caught unrecoverable exception in generator 'weewx.imagegenerator.ImageGenerator'

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****  Unit type cannot change within a time interval (17 vs 17 vs 64).

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****  Traceback (most recent call last):

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 204, in run

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****      obj.start()

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 300, in start

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****      self.run()

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****    File "/home/weewx/bin/weewx/imagegenerator.py", line 32, in run

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****      self.genImages(self.gen_ts)

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****    File "/home/weewx/bin/weewx/imagegenerator.py", line 166, in genImages

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****      aggregate_interval=aggregate_interval)

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****    File "/home/weewx/bin/weewx/manager.py", line 513, in getSqlVectors

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****      aggregate_type, aggregate_interval)

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****    File "/home/weewx/bin/weewx/manager.py", line 770, in _getSqlVectors

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****      (std_unit_system, _rec[1], _rec[2]))

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****  UnsupportedFeature: Unit type cannot change within a time interval (17 vs 17 vs 64).

Oct 31 15:06:05 raspberrypi4 weewx[2513]:         ****  Generator terminated

Oct 31 15:06:05 raspberrypi4 weewx[2513]: copygenerator: copied 5 files to /var/www/html/weewx

Oct 31 15:06:08 raspberrypi4 weewx[2513]: imagegenerator: Generated 18 images for SmartphoneReport in 2.09 seconds

Oct 31 15:06:08 raspberrypi4 weewx[2513]: copygenerator: copied 6 files to /var/www/html/weewx/smartphone

Oct 31 15:11:14 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 15:10:00 GMT (1572534600) to database 'weewx.sdb'

Oct 31 15:11:14 raspberrypi4 weewx[2513]: manager: Added record 2019-10-31 15:10:00 GMT (1572534600) to daily summary in 'weewx.sdb'

Oct 31 15:11:23 raspberrypi4 weewx[2513]: reportengine: Caught unrecoverable exception in generator 'weewx.imagegenerator.ImageGenerator'

Oct 31 15:11:23 raspberrypi4 weewx[2513]:         ****  Unit type cannot change within a time interval (17 vs 17 vs 64).

The only (tenuous) link I can find to this error string points towards `numpy`.  `python --version` at a prompt reports `Python 2.7.13` though Python3 is also installed.  there is a `numpy` installed in each tree.

Any further clues? 

TIA

-D

[Thomas Keffer]

Every archive record in the database stores what unit system it's in. Unit system '17' is the METRICWX system. 

In the interest of efficiency, WeeWX does not allow you to change the unit system in the database. Whatever unit system you start with, you have to stick with. Unfortunately, some record in your database has unit system '64', which is different from 17, thus triggering the error. Furthermore, unit system 64 does not exist --- further evidence that your database has been corrupted.

Nothing to do with numpy or what version of Python you are using.

As I said, put your efforts into salvaging what you can from the database. I'd start with dumping the file (looks like you've already done that), then cleaning it up, then restoring to a new database on a new SD card. Don't worry about data gaps --- WeeWX can handle those.

-tk

--

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/6d65a535-786c-4936-80aa-e16cf7ef0546%40googlegroups.com.

[Dave, G1OGY]

On Thursday, 31 October 2019 15:54:33 UTC, Thomas Keffer wrote:

As I said, put your efforts into salvaging what you can from the database. I'd start with dumping the file (looks like you've already done that), then cleaning it up, then restoring to a new database on a new SD card. Don't worry about data gaps --- WeeWX can handle those.

Aye, aye Cap'n!

 

To unsubscribe from this group and stop receiving emails from it, send an email to weewx...@googlegroups.com.

[Dave, G1OGY]

On Thursday, 31 October 2019 16:15:43 UTC, Dave, G1OGY wrote:

On Thursday, 31 October 2019 15:54:33 UTC, Thomas Keffer wrote:

As I said, put your efforts into salvaging what you can from the database. I'd start with dumping the file (looks like you've already done that), then cleaning it up, then restoring to a new database on a new SD card. Don't worry about data gaps --- WeeWX can handle those.

Aye, aye Cap'n!

 

Calamity averted.

Kudos Tom & Vince! 

Thanks for the encouragement.

-D

<http://g1ogy.com/wx> 

[Thomas Keffer]

Peace, prosperity, and sanity break out!

--

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/ebf82f2a-595e-4c86-8582-6331eb5e6cf3%40googlegroups.com.

[p q]

I hear people talk about RPi SD card issues, but I've been running for 4+ years without a problem. I should count myself lucky I guess. 

Anyway, I have a chron job that momentarily stops weewx, makes a copy of the database, restarts it, and then copies the db to my google drive. Every month or so I go in and clean out the old ones, keeping the most recent and the one from the first of the month, just in case. If I ever lose the db, I'll have a backup.

The chron job runs a script based on montionuploader here: http://jeremyblythe.blogspot.com/2015/06/motion-google-drive-uploader-for-oauth.html

It's not what you're asking for, but it's another way to accomplish the same thing.

--

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/a74e3bc2-7346-445f-b0f5-eee9e7ecdc2d%40googlegroups.com.

--

Peter Quinn
(415)794-2264

[Dave (G1OGY)]

Thanks for your input, Peter.
I'll confess a somewhat `gung ho` attitude to backups but I've found
(since yesterday) I can copy the database (<>20MB) to my `weewxlogs`
nfs mount in much less than 5 seconds.
I'll setup a cron job to put this in place and run some tests with
`integrity_check` and see how it goes.
I've also freed up a Pi2 from another application so I'll also be
testing database (and whole weewx installation) on an nfs mount using
the simulator - just for the craic.

Dave, G1OGY

> You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/L5RIIG9EE_Y/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAA1SM23sjDyqwbpAz1npzjxzCT-%3DBaaNb7AF8S8f3y0OTcuVLQ%40mail.gmail.com.