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/c3e61036-aada-4c70-9906-047fed1066e7n%40googlegroups.com.
