table archive_day_barometer already exists

[Dave Harper]

Over the past year I've had numerous cases of a corrupted database.  I learned early on to have a good backup scheme in place and it has served me well - up until today.  After I noticed that the web page was no longer updating I checked the /var/sys/logfile and found the message "DatabaseError: database disk image is malformed".  Shortly after that I discovered my backups for the past month were not working, so I've been faced with trying to repair the weewx.sdb file.  I've found several posts describing how to recover from problems like this and ended up using the repairsdb.sh script that I found in one of them.  That fixed the "malformed" error and I am now able to load the database but when weewx starts it now aborts with the error that "table archive_day_barometer already exists".  I had never used sqlite3 prior to weewx and am not really familiar with what to try now.  Anybody have any ideas I can try?

Thanks

Dave

[gjr80]

Hi,

Try the following:

1. Stop weeWX
2. Use the wee_database utility to drop the daily summaries
3. Use the wee_database utuilit to rebuild the daily summaries
4. Restart WeeWX

wee_database usage is documented in the Utilities Guide (http://weewx.com/docs/utilities.htm#wee_database_utility)

Gary

[kutz...@gmail.com]

I'm not a database nor a weewx expert. But I'd try using the wee_database utility trying first the --rebuild-daily option first (you could limit it to only today). If that fails, I'd then do a wee_database --drop-daily followed by a wee_database --rebuild-daily. See the Utlilities Guide on this: http://weewx.com/docs/utilities.htm#wee_database_utility. Make sure to stop weewx before using these commands and keep a backup of the database if my advice hoses it :-).

phil

[kutz...@gmail.com]

Whoops. Someone who really knows crossposted at the same time I did. Happy to see my advice was good!

phil

[Colin Larsen]

It's always good when 2 people come back with the same answer :)

--
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.
For more options, visit https://groups.google.com/d/optout.

[Dave Harper]

Thanks to you both for your suggestions.  Gary, I followed your instructions with the following results:

1) Stopped weeWX and verified in /var/log/syslog that it was stopped

2) Used the wee_database utility to drop the daily summaries.  The system responded with:

Dropping daily summary tables from 'weewx.sdb' ...  

No daily summaries found in database 'weewx.sdb'. Nothing done.

3) Tried the --rebuild-daily.  That produced the following list of errors:

Traceback (most recent call last):

  File "/usr/bin/wee_database", line 793, in <module>

    main()

  File "/usr/bin/wee_database", line 153, in main

    rebuildDaily(config_dict, db_binding, options)

  File "/usr/bin/wee_database", line 251, in rebuildDaily

    with weewx.manager.open_manager_with_config(config_dict, db_binding, initialize=True) as dbmanager:

  File "/usr/share/weewx/weewx/manager.py", line 1017, in open_manager_with_config

    return open_manager(manager_dict, initialize)

  File "/usr/share/weewx/weewx/manager.py", line 1006, in open_manager

    manager_dict['schema'])

  File "/usr/share/weewx/weewx/manager.py", line 140, in open_with_create

    dbmanager = cls(connection, table_name=table_name, schema=schema)

  File "/usr/share/weewx/weewx/manager.py", line 1152, in __init__

    self._initialize_day_tables(schema, _cursor)

  File "/usr/share/weewx/weewx/wxmanager.py", line 35, in _initialize_day_tables

    weewx.manager.DaySummaryManager._initialize_day_tables(self, archiveSchema, cursor)

  File "/usr/share/weewx/weewx/manager.py", line 1179, in _initialize_day_tables

    cursor.execute(DaySummaryManager.sql_create_str % (self.table_name, _obs_type))

  File "/usr/share/weewx/weedb/sqlite.py", line 39, in guarded_fn

    raise weedb.TableExistsError(e)

weedb.TableExistsError: table archive_day_barometer already exists

I also played with some of the other options to see what they would do (using the --dry-run option just in case) but they all ended up printing a list of errors that terminated in:

weedb.OperationalError: No day summary schema for table 'archive' in database 'weewx.sdb'

Still digging...

[gjr80]

OK, a bit more serious than first looks. Make sure you have a backup of your current db then try using wee_database with the --reconfigure option. This will give you a new copy of your database with _new appended to the file name. Move your original db aside, rename the _new version and see how that goes.

If that still fails we may have to get our hands dirty.

Gary
Sent from my Samsung mobile

[Dave Harper]

Gary - I am in your debt.  The --reconfigure option seems to have done the trick.  It took a while to generate the new file but, once it was done, weeWX was able to accept the database.  It then spend another large chunk of time rebuilding the daily summaries and, once that was done, it started downloading the updates from my console.  It has loaded almost 7 hours worth and has about 3 more to go but everything seems to be going smoothly and there is no reason to think it won't finish normally at this point.  I've been running on a dedicated Raspberry Pi and a while back, in anticipation of this crash, I built another Pi that boots from a 250GB hard drive and installed everything on that.  Now that I have a working database again, I will be switching over to that one tomorrow and implementing another level of backups in addition to the NAS I have now.  I really appreciate the time you've spent helping me get up and going again.

Regards,

Dave

[gjr80]

Dave,

Good that it was fixed, given the database was somehow corrupted there was probably something in there to do with the daily summaries was upsetting weeWX. Fortunately --reconfigure did the trick otherwise it was going to get messy.

You are also fortunate that you have a station that logs records and allows downloading later, that helps prevent data loss. Of course having an effective database backup strategy in place is important and as you have just found out knowing that you can recover from your backups is equally as important. Not sure what approach you have taken, but some folks in the past have had some issues when they were copying their .sdb file whilst weeWX was still running. One of the strengths of SQLite is having everything in a single file, it makes backups very easy but that is also its Achilles heel; SQLite doesn't like sharing access to the file. You might want to do a bit of googling of the forums regards backups to see some of the approaches used. I used to take a precisely scheduled copy of the .sdb when weeWX was unlikely to be using it and never had a bad backup. I have since moved on to using MySQL/MariaDB and now just dump the database to file. Backups are really horses for courses.

Gary

[Dave Harper]

Gary,

The backup strategy I use is a Python script I wrote that is started by cron every night.  I had read that there could be a problem if the database is copied while it's being updated.  The database is updated once a minute by weeWX so the script monitors the mtime of the weewx.sdb file, waits for the timestamp to change then waits about 15 seconds more before copying it to a subdirectory off my home directory.  I also copy the /etc/weewx directory into the same backup subdirectory and then the script calls Duplicity to do the actual backup.  Duplicity works by starting with a full backup the first time around and then doing incrementals from then on.  A restore starts with the original full backup and then applies all incremental changes to get to the target restore date.  To keep a restore process within reason, I only do a month of incrementals and on the first of each month the script creates a new monthly directory on the NAS.  This strategy has seemed to work well over the past year or so and looking into why it failed this time around will be one of the top priorities for tomorrow.

Dave

[gjr80]

Good stuff, seems well thought out. Hope you find the issue.

Gary

[kutz...@gmail.com]

What I do is outlined in the weewx wiki here: https://github.com/weewx/weewx/wiki/Using-the-RSYNC-skin-as-a-backup-solution

I added the following to weewx.conf:

[[RSYNCSQL]]
        # This backs up the sqlite databases to Diskstation
       
        skin = Rsync
        HTML_ROOT = /home/weewx/archive
        server = 10.0.10.225
        user = weewx
        path = /volume1/homes/weewx/archive_backup
       
        # every night at midnight, run this report
        report_timing = @daily
       
        delete = 0

The nice thing is that the backup is run synchronously within weewx ensuring the database is not being written to during the copy. From here - on my Synology NAS - I can set up a cron-driven backup script as you do, or utilize a purpose-written backup program like Duplicity.

I haven't done that last part yet, so I need to catch any corruption within 24 hours or my copy is overwritten by the corrupted database file. But, I plan to implement a backup that gives me multiple snapshots to choose from if I need to do a restore. I'm pretty sure Synology has a couple of backup packages that I could use.

In the meantime, I can increase the time between rsync copies, as my Davis console should buffer about  a week's worth of archive data. That would give me more time to react to any database corruption.

phil

[Dave Harper]

Phil,

One of the things I will be implementing in my new backup strategy is a check to make sure the database isn't corrupted.  This can be done using command line parameters to sqlite3.  When run manually, it looks something like:

pi@Weather:~ $ sqlite3 weewx.sdb 'pragma integrity_check'

ok

pi@Weather:~ $

I will be including this in a new Python script which will send me an email if it detects corruption.  Otherwise, it will archive that database as "known good".  This should give me an early warning if something goes wrong.  

Dave

[kutz...@gmail.com]

Dave,

That sounds like a great feature addition. I'd appreciate you sharing a copy of the script with me after you write it. In my implementation, the rsync "skin" gives me an offline copy of the database each night (or more or less often) and I can set a scheduled job to run your script against the offline copy automatically. If if finds that the database is corrupted, I can go back to an earlier database snapshot created by the Synology software. I like it!

phil

[Dave Harper]

Phil,

No problem.  Just starting it now and as soon as it's done and tested, I'l post a copy.

Dave

[Dave Harper]

Phil (and anyone else interested),

The script is now written and I have spent most of the day testing it.  It has currently been running for about three hours now and seems to be running correctly.  I'm attaching the script and a set of instructions on how to configure and run it.  The script creates and maintains a "last known good" copy of the weewx.sdb database at whatever interval the user specifies.  It also attempts to be as robust as possible, notifying the user (via email) of any exception conditions rather than simply "failing silently".  If you (or anyone else) runs it, I'd be interested in getting feedback as to any issues discovered.

Dave

[kutz...@gmail.com]

Dave,

Thanks for doing this and sharing it. I plan to try it out over the weekend. I'll let you know how it works in my setup!

phil

[Colin Larsen]

Thanks Dave, appreciate the share, I'll give it a try as well.

Cheers

Colin

--