Database crash

[david murphy]

Hi all,

Weewx still running after 3 years. Currently running ver. 3.2.1 on a Pi / Davis Vantage Pro 2 (with memory added).

I have just noticed the error as outlined below. I have a backup of weewx.sdb and it exhibits the same fault. I have a script that makes a copy

every night, it would appear both databases are corrupt.

It appears that the Pi stopped collecting data from the Davis Pro on the 18/2/2016 and there is now an error when I try to start weewx.

I can view the database on my windows 8.1 system using the DB Browser for SQLite program.

Can someone kindly advise me what actions I should take...I'm aware of wee_database but have not used it. No real experience in the DB world.

Thanks in advance.

Feb 25 18:23:57 weewx weewx[2373]: engine: StdConvert target unit is 0x1

Feb 25 18:23:57 weewx weewx[2373]: engine: Archive will use data binding wx_binding

Feb 25 18:23:57 weewx weewx[2373]: engine: Record generation will be attempted in 'hardware'

Feb 25 18:23:57 weewx weewx[2373]: engine: Using archive interval of 300 seconds

Feb 25 18:23:57 weewx weewx[2373]: engine: Using binding 'wx_binding' to database 'weewx.sdb'

Feb 25 18:23:57 weewx weewx[2373]: engine: Starting backfill of daily summaries

Feb 25 18:23:57 weewx weewx[2373]: engine: Daily summaries up to date.

Feb 25 18:23:58 weewx weewx[2373]: restx: Wunderground-RF: Data for station INEWSOUT246 will be posted

Feb 25 18:23:58 weewx weewx[2373]: engine: Starting up weewx version 3.2.1

Feb 25 18:23:58 weewx weewx[2373]: engine: Clock error is -0.68 seconds (positive is fast)

Feb 25 18:24:01 weewx weewx[2373]: manager: added record 2016-02-18 20:40:00 AEDT (1455788400) to database 'weewx.sdb'

Feb 25 18:24:02 weewx weewx[2373]: engine: Caught unrecoverable exception in engine:

Feb 25 18:24:02 weewx weewx[2373]:     ****  database disk image is malformed

Feb 25 18:24:02 weewx weewx[2373]:     ****  Traceback (most recent call last):

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/engine.py", line 842, in main

Feb 25 18:24:02 weewx weewx[2373]:     ****      engine.run()

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/engine.py", line 154, in run

Feb 25 18:24:02 weewx weewx[2373]:     ****      self.dispatchEvent(weewx.Event(weewx.STARTUP))

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/engine.py", line 218, in dispatchEvent

Feb 25 18:24:02 weewx weewx[2373]:     ****      callback(event)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/engine.py", line 519, in startup

Feb 25 18:24:02 weewx weewx[2373]:     ****      self._catchup(self.engine.console.genStartupRecords)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/engine.py", line 634, in _catchup

Feb 25 18:24:02 weewx weewx[2373]:     ****      origin='hardware'))

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/engine.py", line 218, in dispatchEvent

Feb 25 18:24:02 weewx weewx[2373]:     ****      callback(event)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/engine.py", line 595, in new_archive_record

Feb 25 18:24:02 weewx weewx[2373]:     ****      dbmanager.addRecord(event.record)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/manager.py", line 234, in addRecord

Feb 25 18:24:02 weewx weewx[2373]:     ****      self._addSingleRecord(record, cursor, log_level)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/manager.py", line 1156, in _addSingleRecord

Feb 25 18:24:02 weewx weewx[2373]:     ****      self._set_day_summary(_day_summary, record['dateTime'], cursor)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weewx/manager.py", line 1429, in _set_day_summary

Feb 25 18:24:02 weewx weewx[2373]:     ****      cursor.execute(_sql_replace_str, _write_tuple)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weedb/sqlite.py", line 28, in guarded_fn

Feb 25 18:24:02 weewx weewx[2373]:     ****      return fn(*args, **kwargs)

Feb 25 18:24:02 weewx weewx[2373]:     ****    File "/home/weewx/bin/weedb/sqlite.py", line 198, in execute

Feb 25 18:24:02 weewx weewx[2373]:     ****      return sqlite3.Cursor.execute(self, *args, **kwargs)

Feb 25 18:24:02 weewx weewx[2373]:     ****  DatabaseError: database disk image is malformed

Feb 25 18:24:02 weewx weewx[2373]:     ****  Exiting.

[david murphy]

I should mention I have a USB data logger by Travis Cook hooked up to
the Vantage 2 (http://www.wxforum.net/index.php?action=profile;area=showposts;u=11045).

> --
> 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/dPPz2Ngm2Sw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> weewx-user+...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Regards,

Dave Murphy

[mwall]

On Thursday, February 25, 2016 at 2:33:09 AM UTC-5, david murphy wrote:

Feb 25 18:24:02 weewx weewx[2373]:     ****  DatabaseError: database disk image is malformed

Feb 25 18:24:02 weewx weewx[2373]:     ****  Exiting.

your sqlite database is corrupt.  you'll have to replace it with a backup (do you have redundant backups?), or copy it then try to recover data from the corrupt sdb file.

m

[david murphy]

M,

Yes already tried the backup and it also seems to be corrupt. My backup script runs every night, alas I did not become aware of error for 4 days after the event. At which stage my live copy had been copied over the backup.

Are there any weewx programs I can use to check or repair the DB?

Thanks in advance

[mwall]

weewx has no special utilities to check/repair the db

the db is a standard sqlite database file.  do a little searching to see how/if anyone has recovered data from a corrupt file.

when i've had corrupt sdb files, it has been due to sd card failure, so i was not able to recover anything.  but you might get lucky.

now i do 30 day (sometimes more) rolling backups, with periodic (weekly) checks to verify the integrity of the backup.  i'm planning to move critical systems to zfs soon so i don't have to run so many custom backup/check scripts.

m

[Thomas Keffer]

You can try dumping the database using the command line tool sqlite3. You may get lucky and be able to recover some of it

Install using apt-get

sudo apt-get install sqlite3

echo '.dump' | sqlite3 /home/weewx/archive/weewx.sdb

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

[david murphy]

Thanks to you both. Will try your suggestions on the weekend.

[david murphy]

Tom & mwall,

Had another spare Pi and loaded sqlite3 and a copy of suspect weewx.sdb file and managed to rebuild a good weewx.sdb file using information from the following txt info as a guide.

Thanks again for your time, energy and a fantastic product.

Converting An Entire Database To An ASCII Text File

Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.

A good way to make an archival copy of a database is this:

$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

$ zcat ex1.dump.gz | sqlite3 ex2

The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:

$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2

[Nicolas JULIEN]

That just saved my day ! Thanks !

Just an easy script to automate the proces, those Pi are sometimes unresponsive and the only way is to sadly cut the power :o

#!/bin/sh
#
# repairsdb.sh
#
# requires sqlite3 (apt install sqlite3)
# adjust following line with path/file.sdb
DBFILE="../database/weewx.sdb"
# dump
echo "Dump corrupted sqlite file $DBFILE to $DBFILE.dump.gz"
echo '.dump' | sqlite3 $DBFILE | gzip -c >$DBFILE.dump.gz
# backup sdb
echo "Rename corrupted sqlite file $DBFILE to $DBFILE.backup"
mv $DBFILE $DBFILE.backup
# rebuild sdb from dump
echo "Building proper sqlite file $DBFILE from dump $DBFILE.dump.gz"
zcat $DBFILE.dump.gz | sqlite3 $DBFILE
echo "Done !"

[Greg Troxel]

Nicolas JULIEN <nicolas....@gmail.com> writes:

> Just an easy script to automate the proces, those Pi are sometimes
> unresponsive and the only way is to sadly cut the power :o
>
> #!/bin/sh
> #
> # repairsdb.sh
> #
> # requires sqlite3 (apt install sqlite3)
> # adjust following line with path/file.sdb
> DBFILE="../database/weewx.sdb"

Is there any equivalent to fsck where a database can be checked for errors?

> # dump
> echo "Dump corrupted sqlite file $DBFILE to $DBFILE.dump.gz"
> echo '.dump' | sqlite3 $DBFILE | gzip -c >$DBFILE.dump.gz
> # backup sdb
> echo "Rename corrupted sqlite file $DBFILE to $DBFILE.backup"
> mv $DBFILE $DBFILE.backup
> # rebuild sdb from dump
> echo "Building proper sqlite file $DBFILE from dump $DBFILE.dump.gz"
> zcat $DBFILE.dump.gz | sqlite3 $DBFILE
> echo "Done !"

It might be good to cp -p the file to the backup, and then load to a new
name, and then mv, to get atomicity.

Is this dump/restore guaranteed to work, with some sort of transaction
semantics?

[vince]

On Wednesday, October 24, 2018 at 5:51:02 PM UTC-7, Greg Troxel wrote:

Is there any equivalent to fsck where a database can be checked for errors?

yes - there was a long discussion here a couple years ago about that with how to check your database for internal errors, but a google for "sqlite3 check database' will find a bunch of hits as well for how to do it.

 

Is this dump/restore guaranteed to work, with some sort of transaction
semantics?

For the data that it could dump successfully, I'd think yes as the unique key (timestamp) should ensure that only unique records are restored.

That said, I'm not positive that the script posted above is quite smart enough to do the right thing completely hands-off.  For example, what if you had 1,000,000 records hypothetically and it was corrupted in the middle of the file....what would the .dump actually dump ?  Would a simple dump/restore just restore the 'good' records using the script as-is with no manual intervention ?  I'm not 100% certain.

And for weewx, you 'probably' just want to restore the archive table, clear your NOAA files out, and let weewx on the new/clean system rebuild the other summary tables and NOAA files anyway.

My script for backing up is on github and has been in use for at least 4 years here.  I've checked hundreds of backups and they've all verified ok so far, FWIW.   Hope I never have to use it for really restoring a backup in a corrupted db scenario.