Problem during fixing of potential corrupt database

[Michael Waldor]

Some days ago I've stopped weewx for roughly one day (I did change the GPIO connections of my raspberry pi4). Now I wanted to insert some data into my weewx.sdb from that timespan.

When trying some sqlite3 commands (on a local copy of weewx.sdb - as an exercise a simple count) sqlite3 failed with corrupt database. OK, can imagine that that might have happened sometimes during the last 5 years. Thus I tried to rebuild my database by

mv weewx.sdb weewx_corrupt.sdb

sqlite3 weewx_corrupt.sdb .recover > weewx.sql

sqlite3 weewx.sdb < weewx.sql

Now my sqlite3 commands worked as expected, i.e. the newly created weewx.sdb seemed to be fixed. Keep in mind that I did not modify weewx.sql. Thus a potential error might still have "survived" within the newly created weewx.sdb.

I then checked the modified weewx.sdb copied into /var/lib/weewx (weewx itself was NOT running) with

weectl database check

and got no error messages. But weewxd did not like that modified weewx.sdb. It reports "OverflowError: timestamp out of range for ..." when starting weewx.

To me it's difficult to tell

1. whether my original weewx.sdb is corrupt at all (weewxd works fine)

2. if it's corrupt howto fix it.

Hope somebody might have a suggestion, Michael

[Michael Waldor]

I do have a first idea: There are entries within weewx.sdb where datetime is 0 or 7. Those values clearly are no valid timestamps.

I'll proceed in that direction ...

[Michael Waldor]

Sadly those mysterious rows only appear AFTER .recover,  they appear within weewx.sql.

The failing command is

SELECT count(*)
FROM archive
WHERE
  date(datetime, 'unixepoch', 'localtime')
  between '2024-03-15' and '2024-03-15';

But that command works fine AFTER .recover.

The error message from weewxd clearly indicates wrong datetime:

Mär 20 11:34:37 imurr9 weewxd[15172]: CRITICAL __main__:     ****      last_d = datetime.date.fromtimestamp(weeutil.weeutil.startOfArchiveDay(
Mär 20 11:34:37 imurr9 weewxd[15172]:     time_dt = datetime.datetime.fromtimestamp(time_ts)
Mär 20 11:34:37 imurr9 weewxd[15172]: OverflowError: timestamp out of range for platform time_t
Mär 20 11:34:37 imurr9 weewxd[15172]: CRITICAL __main__:     ****    File "/usr/share/weewx/weeutil/weeutil.py", line 1196, in startOfArchiveDay
Mär 20 11:34:37 imurr9 weewxd[15172]: CRITICAL __main__:     ****      time_dt = datetime.datetime.fromtimestamp(time_ts)

Mär 20 11:34:37 imurr9 weewxd[15172]: CRITICAL __main__:     ****  OverflowError: timestamp out of range for platform time_t

Mär 20 11:34:37 imurr9 weewxd[15172]: CRITICAL __main__:     ****  Exiting

[Tom Keffer]

This could be anything. We will need to see more of the log to tell. For example, you could be asking for a date one month after January 30.

Set debug=1, restart weewxd, post the log from startup through the error.

--
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/b00628cf-ebf4-4fc2-a8bb-a193447e9476n%40googlegroups.com.

[Michael Waldor]

weewx itself works without problems - it can add new entries into weewx.sdb, and it'S running  right now.

But when trying to manually tweak weewx.sdb using sqlite3 commands, sqlite3 complains. Using .recover seems to make it worse - its sql output contains some completely broken lines with datetime being 7 or 74 or unprintable bnary data. Clearly weewxd rejects to use that "recovered" weewx.sdb.

Currently I try to follow https://groups.google.com/g/weewx-user/c/PJuWj35o8TM

Luckily the transformation process should run for ~30min (data is stored on a SSD).

But howto identify the location of a broken weewx.sdb? E.g. if I try to run the sqlite3 command

select date(datetime, 'unixepoch', 'localtime') from archive;

It outputs data till 2018-03-31. And then there is an sqlite3 error message. Only the date conversion seems to fail. If I stay with integer datetime all data seems to be accessible.

[Tom Keffer]

If you say the problem is in the database and the database cannot be recovered, I'm not sure there is anything we can offer. What are you looking for from the group?

Hopefully you have a backup database.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/e2a4a04c-f05c-486c-9306-9d52690a3d28n%40googlegroups.com.

[Michael Waldor]

Yes, I do have backups. But which one to use? I do not know WHEN the potential error might have occured. I even don't know IF there is an error. As I wrote, weewxd can happily append data.

I "only" encounter problems when I try to manually run sqlite3 commands. And those indicate that the error might have been introduced in 2018 just after 2months of runtime. If that's true I might safely start a fresh DB:-(

[michael.k...@gmx.at]

How many obvious bad timestamp values do you have? If there are only few, remove the lines from the database and see if you get along with it.

[Michael Waldor]

That's my mystery: There are no bad datetime entries at all.

But after .recover I get many bad entries with completely wrong datetimes (only a handfull) and with binary content. Thus .recover seems to have failed.

Currently I'm trying to rebuild the database using the description from Oscar (see previous post above). It runs for ~1h and creates an sdb twice the size of the original one, but without defect data. But I wonder, why its size has increased by ~2. I could fix that by the sqlite3 command VACUUM;

Next step will be to merge some data (my original plan), and after ~1h or so I'll know more.

[Michael Waldor]

Finally I could resolve my problem: Following Oscar's hint from https://groups.google.com/g/weewx-user/c/PJuWj35o8TM I could rebuild my database. Since I did the sqlite3 runs on my linux laptop with a local SSD, the runtime for 5years of data was ~1h. Additionally I did optimize the DB by applying VACUUM;. Even my merge with some data from one week ago was successful.

Anotherr hint: I also checked the versions of sqlite3 @raspberry and @linux laptop/tumbleweed. There are differences, but the different versions are not related with my problem.

And now I (hopefully) have again a clean weewx.sdb

Thanks for your quick help, Michael