You can check the integrity of the database using the pragma " integrity_check <https://www.sqlite.org/pragma.html#pragma_integrity_check> ".
*sqlite3 db3.sdb* sqlite> *pragma integrity_check;* On Sun, Feb 23, 2020 at 6:56 AM Günther Wrana <[email protected]> wrote: > daham@hp-pc:/tmp$ sudo sqlite3 /tmp/db3.sdb > [sudo] Passwort für daham: > sqlite> select datetime(dateTime,'unixepoch','localtime'), outTemp from > archive order by dateTime desc limit 10; > 2020-01-16 17:40:00|33.2514285714286 > 2020-01-16 17:35:00|33.3028571428572 > 2020-01-16 17:30:00|33.701 > 2020-01-16 17:25:00|33.7914285714286 > 2020-01-16 17:20:00|33.9371428571429 > 2020-01-16 17:15:00|33.9542857142857 > 2020-01-16 17:10:00|34.0914285714286 > 2020-01-16 17:05:00|34.34 > 2020-01-16 17:00:00|34.4685714285714 > 2020-01-16 16:55:00|34.5628571428571 > sqlite> > > This is the answer if i open db3.sdb in terminal on my linux computer. > I want to know, how the syntax looks like, if i want to look for the > mistake. > > Danke Günther > > Am Sonntag, 23. Februar 2020 13:06:01 UTC+1 schrieb Thomas Keffer: >> >> I doubt it, but it is easy enough to check. What happens if you use >> sqlite3? >> >> *sqlite3 db3.sdb* >> sqlite> *select datetime(dateTime,'unixepoch','localtime'), outTemp from >> archive order by dateTime desc limit 10;* >> >> >> >> On Sun, Feb 23, 2020 at 1:37 AM Günther Wrana <[email protected]> wrote: >> >>> Sorry i have it on Ubuntu with DB Browser for SQLite opend. >>> And with this sofware i can take a look on the database. >>> But i dont know were is the problem. >>> Is this the problem that i opned the database with this software and not >>> in terminal? >>> >>> Günther >>> >>> Am Samstag, 22. Februar 2020 22:54:32 UTC+1 schrieb Thomas Keffer: >>>> >>>> Günther, >>>> >>>> I get "database disk image is malformed" on both my Ubuntu system, and >>>> on a Raspberry Pi. >>>> >>>> -tk >>>> >>>> On Sat, Feb 22, 2020 at 11:14 AM Günther Wrana <[email protected]> >>>> wrote: >>>> >>>>> https://drive.google.com/open?id=1q_ILd6m6M_KT0HThnv6X7JHxbaq8zMfA >>>>> >>>>> If i open this merged database on Ubuntu with SQLite there is no >>>>> problem. >>>>> But when i send this datatbase on the raspberry where weewx is runing >>>>> weewx is out of order. >>>>> Where is the problem? >>>>> >>>>> I know that the database on the raspberry is named weewx.sdb >>>>> >>>>> I hope anybody can help me and understand my terrible english. >>>>> >>>>> Thanks >>>>> >>>>> Am Sonntag, 16. Februar 2020 09:42:29 UTC+1 schrieb Günther Wrana: >>>>>> >>>>>> Das wird gemeldet wenn ich die alte Datenbank mit den oben >>>>>> angeführten Befehlen abfrage. >>>>>> Ist da noch etwas zu machen oder ist die alte Datenbank defekt und >>>>>> ich kann sie löschen. >>>>>> >>>>>> Grüße Günther >>>>>> >>>>>> >>>>>> daham@hp-pc:/tmp$ sudo sqlite3 /tmp/db1.sdb >>>>>> SQLite version 3.22.0 2018-01-22 18:45:57 >>>>>> Enter ".help" for usage hints. >>>>>> sqlite> SELECT MIN(dateTime),MAX(dateTime),COUNT(dateTime) FROM >>>>>> archive; >>>>>> Error: database disk image is malformed >>>>>> sqlite> SELECT strftime('%d-%m-%Y %H:%M:%S', datetime(MIN(dateTime), >>>>>> 'unixepoch', 'localtime')),strftime('%d-%m-%Y %H:%M:%S', >>>>>> datetime(MAX(dateTime), 'unixepoch', 'localtime')),COUNT(dateTime) FROM >>>>>> archive; >>>>>> Error: database disk image is malformed >>>>>> sqlite> >>>>>> >>>>>> >>>>>> Am Montag, 6. Januar 2020 22:43:02 UTC+1 schrieb gjr80: >>>>>>> >>>>>>> Hallo >>>>>>> >>>>>>> Die angezeigten Fehlermeldungen werden erwartet und können ignoriert >>>>>>> werden. >>>>>>> >>>>>>> Die Dateigröße kann manchmal ein nützlicher Maßstab sein, liefert >>>>>>> jedoch nicht immer ein genaues Bild. Der wahre Test besteht darin, die >>>>>>> Daten in der neuen zusammengeführten Datenbank zu überprüfen oder zu >>>>>>> verwenden. Haben Sie die letzten Schritte befolgt und WeeWX tatsächlich >>>>>>> dazu gebracht, die neue zusammengeführte Datenbank zu verwenden? Wenn >>>>>>> ja, >>>>>>> was zeigen die Berichte jetzt? >>>>>>> >>>>>>> Sie können auch die neue Datenbank abfragen und die frühesten und >>>>>>> neuesten Zeitstempel finden. Wenn Ihre Datenbank >>>>>>> /home/weewx/archive/weewx.sdb lautet, kann die folgende Befehlsfolge >>>>>>> verwendet werden: >>>>>>> >>>>>>> $ sqlite3 /home/weewx/archive/weewx.sdb >>>>>>> sqlite> SELECT MIN(dateTime),MAX(dateTime),COUNT(dateTime) FROM >>>>>>> archive; >>>>>>> >>>>>>> Dies zeigt die frühesten und neuesten Zeitstempel, gefolgt von der >>>>>>> Anzahl der Archivdatensätze. >>>>>>> >>>>>>> 1486989000|1578345600|300759 >>>>>>> >>>>>>> oder um die Zeitstempel in lesbarer Form anzuzeigen: >>>>>>> >>>>>>> sqlite> SELECT strftime('%d-%m-%Y %H:%M:%S', datetime(MIN(dateTime), >>>>>>> 'unixepoch', 'localtime')),strftime('%d-%m-%Y %H:%M:%S', datetime( >>>>>>> MAX(dateTime), 'unixepoch', 'localtime')),COUNT(dateTime) FROM >>>>>>> archive; >>>>>>> >>>>>>> was so etwas zeigen wird: >>>>>>> >>>>>>> 13-02-2017 22:30:00|07-01-2020 07:25:00|300760 >>>>>>> >>>>>>> Sie können die obigen Überprüfungen auch für die beiden Datenbanken >>>>>>> vor dem Zusammenführen durchführen. Wenn der früheste und der späteste >>>>>>> Zeitstempel und Datensatz übereinstimmen, haben Sie ein gewisses Maß an >>>>>>> Sicherheit, dass die Zusammenführung wie erwartet erfolgt ist. Eine >>>>>>> umfassendere Überprüfung besteht darin, dass WeeWX die zusammengeführte >>>>>>> Datenbank verwendet und anschließend neu generierte NOAA-Berichte >>>>>>> überprüft. Dies sollte auf einen Blick zeigen, ob die Zusammenführung >>>>>>> funktioniert hat. >>>>>>> >>>>>>> Gary >>>>>>> >>>>>>> The error messages you see are expected, they can be ignored. >>>>>>> >>>>>>> File size can sometimes be a useful yardstick but does not always >>>>>>> give an accurate picture. The true test is to check or use the data in >>>>>>> the >>>>>>> new merged database. Did you follow the last few steps and actually get >>>>>>> WeeWX to use the new merged database? If so what do the reports show >>>>>>> now? >>>>>>> >>>>>>> Another check you can do is to query the new database and find the >>>>>>> earliest and latest timestamps. If your database is >>>>>>> /home/weewx/archive/weewx.sdb the following sequence of commands can be >>>>>>> used: >>>>>>> >>>>>>> $ sqlite3 /home/weewx/archive/weewx.sdb >>>>>>> sqlite> SELECT MIN(dateTime),MAX(dateTime),COUNT(dateTime) FROM >>>>>>> archive; >>>>>>> >>>>>>> this will show the earliest and latest timestamps followed by the >>>>>>> archive record count, something like: >>>>>>> >>>>>>> 1486989000|1578345600|300759 >>>>>>> >>>>>>> or to view the timstamps in human readable form: >>>>>>> >>>>>>> sqlite> SELECT strftime('%d-%m-%Y %H:%M:%S', datetime(MIN(dateTime), >>>>>>> 'unixepoch', 'localtime')),strftime('%d-%m-%Y %H:%M:%S', datetime( >>>>>>> MAX(dateTime), 'unixepoch', 'localtime')),COUNT(dateTime) FROM >>>>>>> archive; >>>>>>> >>>>>>> which will show something like: >>>>>>> >>>>>>> 13-02-2017 22:30:00|07-01-2020 07:25:00|300760 >>>>>>> >>>>>>> You can also perform the above checks on the two databases before >>>>>>> the merge, if the earliest and latest timestamps and record counts tally >>>>>>> then you have a degree of confidence that the merge occurred as >>>>>>> expected. A >>>>>>> more complete check is having WeeWX use the merged database and then >>>>>>> checking re-generated NOAA reports. This should show at a glance if the >>>>>>> merge worked. >>>>>>> >>>>>>> On Tuesday, 7 January 2020 05:56:41 UTC+10, Günther Wrana wrote: >>>>>>>> >>>>>>>> >>>>>>>> Hallo wenn ich folgende Befehle im Terminal eingebe wie oben >>>>>>>> beschrieben. >>>>>>>> Bekomme ich folgende Fehlermeldungen. >>>>>>>> >>>>>>>> Es wir irgend etwas in der Datenbank durchgeführt nur wird sie >>>>>>>> dadurch nicht grösser. >>>>>>>> Wo liegt das Problem, oder was mache ich falsch? >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> daham@daham-p7-1011de:~$ sqlite3 /var/tmp/db1.sdb >>>>>>>> SQLite version 3.22.0 2018-01-22 18:45:57 >>>>>>>> Enter ".help" for usage hints. >>>>>>>> sqlite> .output/var/tmp/db1.dump >>>>>>>> daham@daham-p7-1011de:~$ sqlite3 /media/daham/Neu1601/Eigene\ >>>>>>>> Dateien/Wetterstation/weewx/271220192133/weewx.sdb >>>>>>>> SQLite version 3.22.0 2018-01-22 18:45:57 >>>>>>>> Enter ".help" for usage hints. >>>>>>>> sqlite> .read /var/tmp/db1.dump >>>>>>>> Error: near line 3: table archive already exists >>>>>>>> Error: near line 40840: table archive_day_barometer already exists >>>>>>>> Error: near line 40984: table archive_day_pressure already exists >>>>>>>> Error: near line 41128: table archive_day_altimeter already exists >>>>>>>> Error: near line 41256: table archive_day_inTemp already exists >>>>>>>> Error: near line 41400: table archive_day_outTemp already exists >>>>>>>> Error: near line 41544: table archive_day_inHumidity already exists >>>>>>>> Error: near line 41688: table archive_day_outHumidity already exists >>>>>>>> Error: near line 41832: table archive_day_windSpeed already exists >>>>>>>> Error: near line 41976: table archive_day_windDir already exists >>>>>>>> Error: near line 42120: table archive_day_windGust already exists >>>>>>>> Error: near line 42264: table archive_day_windGustDir already exists >>>>>>>> Error: near line 42408: table archive_day_rainRate already exists >>>>>>>> Error: near line 42552: table archive_day_rain already exists >>>>>>>> Error: near line 42696: table archive_day_dewpoint already exists >>>>>>>> Error: near line 42840: table archive_day_windchill already exists >>>>>>>> Error: near line 42984: table archive_day_heatindex already exists >>>>>>>> Error: near line 43128: table archive_day_ET already exists >>>>>>>> Error: near line 43272: table archive_day_radiation already exists >>>>>>>> Error: near line 43416: table archive_day_UV already exists >>>>>>>> Error: near line 43560: table archive_day_extraTemp1 already exists >>>>>>>> Error: near line 43704: table archive_day_extraTemp2 already exists >>>>>>>> Error: near line 43848: table archive_day_extraTemp3 already exists >>>>>>>> Error: near line 43992: table archive_day_soilTemp1 already exists >>>>>>>> Error: near line 44136: table archive_day_soilTemp2 already exists >>>>>>>> Error: near line 44280: table archive_day_soilTemp3 already exists >>>>>>>> Error: near line 44424: table archive_day_soilTemp4 already exists >>>>>>>> Error: near line 44568: table archive_day_leafTemp1 already exists >>>>>>>> Error: near line 44712: table archive_day_leafTemp2 already exists >>>>>>>> Error: near line 44856: table archive_day_extraHumid1 already exists >>>>>>>> Error: near line 45000: table archive_day_extraHumid2 already exists >>>>>>>> Error: near line 45144: table archive_day_soilMoist1 already exists >>>>>>>> Error: near line 45288: table archive_day_soilMoist2 already exists >>>>>>>> Error: near line 45432: table archive_day_soilMoist3 already exists >>>>>>>> Error: near line 45576: table archive_day_soilMoist4 already exists >>>>>>>> Error: near line 45720: table archive_day_leafWet1 already exists >>>>>>>> Error: near line 45864: table archive_day_leafWet2 already exists >>>>>>>> Error: near line 46008: table archive_day_rxCheckPercent already >>>>>>>> exists >>>>>>>> Error: near line 46152: table archive_day_txBatteryStatus already >>>>>>>> exists >>>>>>>> Error: near line 46296: table archive_day_consBatteryVoltage >>>>>>>> already exists >>>>>>>> Error: near line 46440: table archive_day_hail already exists >>>>>>>> Error: near line 46584: table archive_day_hailRate already exists >>>>>>>> Error: near line 46728: table archive_day_heatingTemp already exists >>>>>>>> Error: near line 46872: table archive_day_heatingVoltage already >>>>>>>> exists >>>>>>>> Error: near line 47016: table archive_day_supplyVoltage already >>>>>>>> exists >>>>>>>> Error: near line 47160: table archive_day_referenceVoltage already >>>>>>>> exists >>>>>>>> Error: near line 47304: table archive_day_windBatteryStatus already >>>>>>>> exists >>>>>>>> Error: near line 47448: table archive_day_rainBatteryStatus already >>>>>>>> exists >>>>>>>> Error: near line 47592: table archive_day_outTempBatteryStatus >>>>>>>> already exists >>>>>>>> Error: near line 47736: table archive_day_inTempBatteryStatus >>>>>>>> already exists >>>>>>>> Error: near line 47880: table archive_day__metadata already exists >>>>>>>> Error: near line 47881: UNIQUE constraint failed: >>>>>>>> archive_day__metadata.name >>>>>>>> Error: near line 47882: UNIQUE constraint failed: >>>>>>>> archive_day__metadata.name >>>>>>>> Error: near line 47883: table archive_day_wind already exists >>>>>>>> sqlite> >>>>>>>> >>>>>>>> -- >>>>> 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/87c9cfc6-2b06-4924-8479-c996405e1d74%40googlegroups.com >>>>> <https://groups.google.com/d/msgid/weewx-user/87c9cfc6-2b06-4924-8479-c996405e1d74%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/ff73bff7-949e-4266-9964-62844b78556d%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/ff73bff7-949e-4266-9964-62844b78556d%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/c8e26b1c-5cf4-42ec-b1d7-dc40c07703ac%40googlegroups.com > <https://groups.google.com/d/msgid/weewx-user/c8e26b1c-5cf4-42ec-b1d7-dc40c07703ac%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/CAPq0zEC%2BE0dE21%3Do3XO46iX-EeRpgueUfSqJhnMSCm%3DNFZvbrw%40mail.gmail.com.
