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 Michael Waldor schrieb am Mittwoch, 20. März 2024 um 15:46:04 UTC+1: > 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. > [email protected] schrieb am Mittwoch, 20. März 2024 um 14:21:01 UTC+1: > >> 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 schrieb am Mittwoch, 20. März 2024 um 14:13:54 UTC+1: >> >>> 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:-( >>> >>> Tom Keffer schrieb am Mittwoch, 20. März 2024 um 14:04:37 UTC+1: >>> >>>> 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. >>>> >>>> On Wed, Mar 20, 2024 at 5:55 AM 'Michael Waldor' via weewx-user < >>>> [email protected]> wrote: >>>> >>>>> 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 schrieb am Mittwoch, 20. März 2024 um 13:28:57 UTC+1: >>>>> >>>>>> 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. >>>>>> >>>>>> On Wed, Mar 20, 2024 at 3:57 AM 'Michael Waldor' via weewx-user < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> 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 >>>>>>> Michael Waldor schrieb am Mittwoch, 20. März 2024 um 10:43:13 UTC+1: >>>>>>> >>>>>>>> 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 schrieb am Mittwoch, 20. März 2024 um 10:04:57 UTC+1: >>>>>>>> >>>>>>>>> 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 >>>>>>>>> >>>>>>>>> -- >>>>>>> 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 [email protected]. >>>>>>> To view this discussion on the web visit >>>>>>> https://groups.google.com/d/msgid/weewx-user/b00628cf-ebf4-4fc2-a8bb-a193447e9476n%40googlegroups.com >>>>>>> >>>>>>> <https://groups.google.com/d/msgid/weewx-user/b00628cf-ebf4-4fc2-a8bb-a193447e9476n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> >>>>>> -- >>>>> 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 [email protected]. >>>>> >>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/weewx-user/e2a4a04c-f05c-486c-9306-9d52690a3d28n%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/weewx-user/e2a4a04c-f05c-486c-9306-9d52690a3d28n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/70e1d88d-fd40-4c43-a06f-80800a87cff4n%40googlegroups.com.
