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/f65d817c-f242-464e-b26d-5d1b37733c55n%40googlegroups.com.
