howto fix weewx sqlite database

Fische Namenlos

unread,

Jul 10, 2022, 10:43:55 AM7/10/22

to weewx-user

Dear,

my station is using sqlite database (weewx.sdb) for nearly 5 years, and now - I suppose - is getting to big for my Raspi-server, it stops working/writting.

Howto fix or customize in weewx 4.8.0 to split this database, maybe yearly and howto "tell" weewx howto search historical data in "splitted" datafiles?

Sorry for my english, hope you understand what I'm meaning.

I try to find information in weewx docs session and/or wee_database, but ....(?)

thx

Graham Eddy

unread,

Jul 10, 2022, 11:00:58 AM7/10/22

to WeeWX User

15 years of data, running happily on RPi 4B:

graham@paperbark:/home/weewx/archive $ ls -l weewx.sdb
-rw-r--r-- 1 weewx weewx 175935488 Jul 11 00:55 weewx.sdb

p q

unread,

Jul 10, 2022, 11:45:42 AM7/10/22

to weewx...@googlegroups.com

What error messages are you getting?

--
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/8C3EB25A-F4A3-4D14-B43C-16556831AF91%40geddy.au.

--

Peter Quinn
(415)794-2264

Fische Namenlos

unread,

Jul 10, 2022, 1:27:31 PM7/10/22

to weewx-user

here it is:

Sun Jul 10 19:25:39 2022 user.debug weewx[2812] DEBUG weewx.restx: Shut down StationRegistry thread.
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: Caught unrecoverable exception:
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** database disk image is malformed
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** Traceback (most recent call last):
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 214, in run
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** self.dispatchEvent(weewx.Event(weewx.CHECK_LOOP, packet=packet))
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 245, in dispatchEvent
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** callback(event)
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 634, in check_loop
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** raise BreakLoop
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** weewx.engine.BreakLoop
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: ****
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** During handling of the above exception, another exception occurred:
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: ****
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** Traceback (most recent call last):
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewxd", line 153, in main
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** engine.run()
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 221, in run
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** self.dispatchEvent(weewx.Event(weewx.POST_LOOP))
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 245, in dispatchEvent
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** callback(event)
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 644, in post_loop
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** self._software_catchup()
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 715, in _software_catchup
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** self.engine.dispatchEvent(weewx.Event(weewx.NEW_ARCHIVE_RECORD,
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 245, in dispatchEvent
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** callback(event)
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/engine.py", line 675, in new_archive_record
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** dbmanager.addRecord(event.record,
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/manager.py", line 301, in addRecord
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** self._updateHiLo(accumulator, cursor)
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/manager.py", line 1031, in _updateHiLo
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** _stats_dict = self._get_day_summary(_sod_ts, cursor)
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weewx/manager.py", line 1412, in _get_day_summary
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** _cursor.execute(
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weedb/sqlite.py", line 30, in guarded_fn
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** return fn(*args, **kwargs)
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** File "/home/weewx/bin/weedb/sqlite.py", line 219, in execute
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** return sqlite3.Cursor.execute(self, *args, **kwargs)
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** sqlite3.DatabaseError: database disk image is malformed
Sun Jul 10 19:25:39 2022 user.crit weewx[2812] CRITICAL __main__: **** Exiting.

Tom Keffer

unread,

Jul 11, 2022, 9:02:52 AM7/11/22

to weewx-user

It is very unlikely that the size of the database is a problem. Like Graham, I have over 15 years of data on mine without a problem.

You did not say what kind of storage you are using, but if it is an old SD card, it is much more likely that the card is corrupt.

You may be able to recover the database by using the utility sqlite3.

sqlite3 /home/weewx/archive/weewx.sdb

sqlite> .recover

sqlite> .quit

However, usually this does not work, in which case you will have to recover from a backup copy. Then, replace the card.

-tk

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/47d68a21-a20c-4145-bfc0-2179b9e847e8n%40googlegroups.com.

Oscar

unread,

Jul 12, 2022, 11:47:09 AM7/12/22

to weewx-user

The database is corrupt. Could be caused by a bad SD card, losing power during a write, a number of reasons. I've been able to use this process to recover most of a corrupt data base. With 5 years of data, the recreate process will take a long time (days(?)) on a pi. I recommend coping the database to a faster machine to create the database, then copy it back to the pi.

# Dump the database as INSERT commands.

sqlite3 weewx.sdb

.mode insert

.output dump_all.sql

.dump

.exit

# remove unwanted records from the dump file

cat dump_all.sql | grep -v TRANSACTION | grep -v ROLLBACK | grep -v COMMIT >dump_all_notrans.sql

# recreate the database using the insert statements.

sqlite3 new-weewx.sdb ".read dump_all_notrans.sql"

make a copy of your existing weewx.sdb file and then copy new-weewx.sdb to weewx.sdb.

Fische Namenlos

unread,

Jul 24, 2022, 4:53:39 AM7/24/22

to weewx-user

great,

it worked. my linux-computer was working for hours for only 60MB of data, but database is readable by weewx.

a single gap is now that the NOAA-files are created from up 1988 and, of course, empty. The station has started in 2019.

Is there a way to fix it?

regards

Tom Keffer

unread,

Jul 24, 2022, 7:18:53 AM7/24/22

to weewx-user

See the wiki article Cleaning up old "bad" data, in particular, the section Deleting unwanted records.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/ed4161fb-8421-4f40-abcf-0f2323425010n%40googlegroups.com.

Fische Namenlos

unread,

Jul 25, 2022, 5:36:37 AM7/25/22

to weewx-user

done and working

thx

Reply all

Reply to author

Forward