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/26f55692-3bf8-4409-b969-b2de0be549a9n%40googlegroups.com.
