Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)

[DS_Starter]

DbRep ist ganz allgemein so aufgebaut, dass man für jede Aufgabe ein Device erstellt, es parametrisiert und dann damit (regelmäßig) arbeitet. Also nicht ständig umkonfiguriert.

In deinem speziellen Fall wirst du also nicht drumherum kommen mehrere Devices zu erstellen und sie so einzustellen dass nur die in einem Lauf zu berücksichtigenden Device/Reading-Kombinationen berücksichtigt werden.

Das klingt jetzt aufwändig, ist es aber nicht wirklich. Ich kopiere dazu immer ein bereits erstelltes (und schon ähnlich eingestelltes Device) und passe es dann nur noch an.

Wenn es z.B. das Reading HM_4D1493_IEC_01_energyCounterValue_EnergyMonth  in allen Devices A,B,C gibt und über alle drei Devices (zusammen !) der Max-Wert dieses Readings ermittelt werden soll, reicht ein DbRep-Device mit devices = A,B,C und reading = HM_4D1493_IEC_01_energyCounterValue_EnergyMonth.

Sonst müsstest du weitere Devices erstellen und auch das Attr devices weiter aufdröseln.

Eine weitere Variante wäre ein eigenes, spezifisches  SQL zu entwerfen welches alle relevanten Devices und Readings getrennt voneinander ermittelt und als Reading ausgibt. Das geht mit "set ... sqlCmd". Dazu braucht man ein paar SQL-Kenntnisse oder Hilfe im Forum.
Dann kann man delEntries in einem zweiten DbRep anwenden mit dem Attr valueFilter welches so engestellt wird, dass Datensätze mit diesem Wert nicht gelöscht werden. Das ganze Verfahren ließe sich mit der userExitFn noch verknüpfen und automatisieren.

Also die zweite Alternative ist schon recht anspruchsvoll und mit Eigeninitiative verbunden, aber dafür absolut flexibel und elegant individeull angepasst lösbar.

LG,
Heiko

[MarcusR]

Guten Morgen Marcus,

das hast du nicht ganz richtig interpretiert.
Nein, es werden die Datensätze , die durch die Zeit- ,Reading- ,Device- und auch valueFilter aus der Quelldatenbank in die Standby-Datenbank übertragen. Das passiert bei jedem Lauf.

Der Nutzer muss also selbst darauf achten nichts doppelt zu schreiben. Aber das ist reativ einfach.
Eine nicht ganz so elegante Methode ist, das at immer auf den Beginn eines Tages also 00:00:00 zu setzen und im DbRep Device timeDiffToNow d:1 zu setzen. Das würde ein doppeltes Schreiben verhindern.

Aber diese Methode hat Nachteile, weil diese Nahtlosigkeit nicht garantiert werden kann wenn mal ein System nicht da ist oder andere Umstände.

Deswegen ist es am effektivsten, in der Standby-Datenbank die history-Tabelle mit einem primary Key aufzubauen. Dann verhindert die DB von sich aus doppelte Einträge und du kannst dein Verfahren so lassen wie du es engstellt hast.

Du hast doch eine MySQL als Archiv wenn ich mich nicht irre. Dann dropst du am Besten deine history Tabelle in der Standby nochmal und erstellst sie neu mit einem primary Key so:

Code Auswählen Erweitern


drop table history;

CREATE TABLE `fhemtest`.`history` (TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, DEVICE varchar(64), TYPE varchar(64), EVENT varchar(512), READING varchar(64), VALUE varchar(128), UNIT varchar(32));
ALTER TABLE `fhemtest`.`history` ADD PRIMARY KEY(TIMESTAMP, DEVICE, READING);


Dann kannst du neu Syncen und den Lauf so oft wiederholen wie du willst, die Zeiträume können sich dabei überlappen. Alles kein Problem. Die DB verhindert dass doppelte Einträge entstehen.

Wenn ich Zeit finde, erstelle ich auch mal einen Wiki-Beitrag dazu.
Probiers mal aus ...

LG,
Heiko

Hi Heiko,

ich habe mir nun mit der (periodisch aufgerufenen) Methode

Code Auswählen Erweitern

delDoublets beholfen, das funktioniert ganz gut. Bei einem zusammengesetzten Schlüssel hatte ich Bedenken, dass bei iner Schlüsselverletzung die ganze Transaktion fehlschlägt, und ich das irgendwie überwachen muss 

Aber das rauslöschen der Dubletten klappt gut, vielen Dank !

Viele Grüße
Marcus

[DS_Starter]

Hi Marcus,

Bei einem zusammengesetzten Schlüssel hatte ich Bedenken, dass bei iner Schlüsselverletzung die ganze Transaktion fehlschlägt, und ich das irgendwie überwachen muss

Brauchst keine Bedenken haben. Genau diese Möglichkeit habe ich im Modul berücksichtigt.

Aber schön dass du eine Lösung für dich gefunden hast. 

Grüße,
Heiko

[kadettilac89]

Hallo Heiko,

im Modul gibt es die Dump-Option und auch einen Restore dieser Files.

Ist es igendwie möglich, dem Dump einen Zeitraum mitzugeben?

Mir ist heute beim automatisierten Backup meine Installation abgeraucht. Fhem-Container lief noch halbwegs (unhealthy) aber auf die DB kam ich nicht mehr. Habe nicht näher analysiert sondern das Backup von gestern zurückgespielt. Keine Zeit für Analyse ... dadurch fehlen mir 24 Stunden (etwas weniger da ich den Cache der DBLog noch gesichert hatte).

Ich würde gerne einen stündlichen Dump machen. Ein voller Dump stündlich ist mir zu groß. Eine Art Incremential Backup. Irgend eine Idee? Es soll kein Feature-Request sein da zu speziell, dachte nur wenn es irgendwie so geht wäre das schön. Wenn es nicht geht dann mache ich das per cron ..

Das SQL ist relativ einfach ... select * from history where timestamp > "jetzt -1 Stunde" ... und das in ein SQL-Dumpfile damit auch Restore funktioniert.

Danke dir!

[DS_Starter]

Es gibt aktuell noch die Funktion exportToFile  bzw. importFromFile.
Dort kannst du Zeitgrenzen eingeben und theoretisch auch Devices/Readings abgrenzen.

Es entstehen dabei CSV-Files die du wieder einspielen kannst bei Bedarf.

Zum Beispiel könntest du ein Full-Dump machen und jeden Tag / Stunde (what ever) danach mit  exportToFile  Daten aus der DB schreiben.
Wenn du die Zeitattribute z.B. auf previous_day_begin und current_day_end setzt, hast du immer genügend Überschneidung der Zeitscheiben.
Beim Import stört das nicht wenn du in der DB history einen primary key gesetzt hast, sodass keine doppelten Datensätze entstehen. (Ich glaube den hast du wenn ich mich nicht irre.)

Würde dir das reichen ?

[kadettilac89]

Würde dir das reichen ?

Hi, das reicht. Habe ich in der Commandref übersehen. Das suche ich, teste ich mal. Vielen DAnk!

[Omega]

Hallo Heiko,

ich habe FHEM neu eingerichtet und möchte die Verbindung zu Synology / MariaDB testen (bisher mySQL im gleichen System). Geht leider nicht, die "adminCredentials" fallen mir auf die Füße.
Ich habe aus dem produktiven FHEM (gleicher Softwarestand) aus der fhem.cfg die DB- und DbRep-Definitionen in das neue Umfeld kopiert.
Der Zugriff auf die DB als solches ist lt. Log auch möglich.
Jetzt wollte ich im DbRep-Device folgendes ausführen:

Code Auswählen Erweitern

set <name> index recreate_Report_Idx
Dabei bekomme ich die Fehlermeldung: "errortext Can't use admin credentials for database access, see logfile"
Ich habe aber gar keine adminCredentials definiert. Müsste dann doch eigentlich auch so gehen oder? Und falls ich sie dennoch benötige: wie ist denn die Syntax in der Eingabe? Vor allem, wenn das aktuelle Passwort leer ist? Auch "useAdminCredentials" benutze ich nicht.

LG
Holger

[DS_Starter]

Hi Holger,

das ist sicherlich ein Fehler. Mach bitte noch ein verbose 4 log. Ich schaue es mir nachher an, bin noch unterwegs.

Grüsse,
Heiko

[Omega]

Hallo Heiko,

verbose 4 habe ich im DbRep-Device gesetzt, als Ausgabe nach

Code Auswählen Erweitern

set rep.myFHEMdb index recreate_Report_Idx kommen nur die folgenden Zeilen im Log:

Code Auswählen Erweitern


2020.03.10 19:43:27 3: DbRep rep.myFHEMdb - ################################################################
2020.03.10 19:43:27 3: DbRep rep.myFHEMdb - ###                    New Index operation                   ###
2020.03.10 19:43:27 3: DbRep rep.myFHEMdb - ################################################################
2020.03.10 19:43:27 3: DbRep rep.myFHEMdb - Command: index recreate_Report_Idx
2020.03.10 19:43:27 2: DbRep rep.myFHEMdb - ERROR - adminCredentials not set. Use "set rep.myFHEMdb adminCredentials" first.
2020.03.10 19:43:27 2: DbRep rep.myFHEMdb - ERROR - admin credentials are needed for database access, but can't use it


Zur Vervollständigung noch ein list vom DbRep-Device:

Code Auswählen Erweitern

Internals:
   DATABASE   myFHEMdb
   DEF        myFHEMdb
   FUUID      5e67dee9-f33f-e100-c079-e1841c04d7584c99
   FVERSION   93_DbRep.pm:v8.32.2-s21329/2020-03-01
   LASTCMD    index recreate_Report_Idx
   MODEL      Client
   NAME       rep.myFHEMdb
   NOTIFYDEV  global,rep.myFHEMdb
   NR         17
   NTFY_ORDER 50-rep.myFHEMdb
   ROLE       Client
   STATE      error
   TYPE       DbRep
   UTF8       1
   HELPER:
     DBLOGDEVICE myFHEMdb
     GRANTS     DELETE,INSERT,USAGE,UPDATE,SELECT
     IDRETRIES  3
     MINTS      2020-03-10 17:03:15
     PACKAGE    main
     VERSION    8.32.2
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      512
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   Helper:
     DBLOG:
       background_processing_time:
         myFHEMdb:
           TIME       1583865617.58589
           VALUE      0.0040
       errortext:
         myFHEMdb:
           TIME       1583865807.34897
           VALUE      Can't use admin credentials for database access, see logfile !
       index_state:
         myFHEMdb:
           TIME       1583865617.58589
           VALUE      Index Report_Idx doesn't exist. Please create the index by "set rep.myFHEMdb index recreate_Report_Idx" command !
       sql_processing_time:
         myFHEMdb:
           TIME       1583865617.58589
           VALUE      0.0023
       state:
         myFHEMdb:
           TIME       1583865807.35082
           VALUE      error
   OLDREADINGS:
   READINGS:
     2020-03-10 19:43:27   errortext       Can't use admin credentials for database access, see logfile !
     2020-03-10 19:43:27   state           error
Attributes:
   allowDeletion 1
   device     KG.Flur.Thermostat,KG.Werkzeug.Thermostat
   expimpfile /opt/fhem/backup/exp_myFHEMdb.sql
   icon       time_note
   reading    measured-temp
   room       Technik->DbLog
   showproctime 1
   timestamp_begin 2019-11-01 00:00:00
   timestamp_end 2019-11-01 10:44:00
   verbose    4

LG
Holger

[DS_Starter]

Hallo Holger,

jetzt hab ich es wieder ... dem DB-User fehlen die Rechte gemäß commandref:

Der verwendete Datenbank-Nutzer benötigt das ALTER, CREATE und INDEX Privileg.

Da die nicht vorhanden sind versucht das Modul den Admin-User zu verwenden der auch nicht gesetzt ist.
Du müstest also entweder dem verwendeten DB-User diese Rechte zuweisen oder einen Admin-User mit Passwort setzen:

Code Auswählen Erweitern


set <name> adminCredentials <User> <Passwort>


Einen Admin-User ohne Passwort habe ich bis jetzt aus Security Gründen nicht vorgesehen.
Aber ich werde die Log-Ausschriften etwas abändern damit man gleich darauf kommt.

LG,
Heiko

[Omega]

Hallo Heiko,

danke, jetzt habe ich es auch verstanden und korrekt umsetzen können (weiteren User angelegt mit entsprechenden Rechten und Passwort und den dann eingetragen).
Prompt hat alles funktioniert.

Danke noch einmal und einen schönen Abend.

LG
Holger

[DS_Starter]

Danke, dir auch.
Die Dbrep Version mit ein paar aussagefähigeren Logeinträgen für diesen Fall checke ich noch ein und ist morgen früh im Update.

LG,
Heiko

[ch.eick]

Hallo,
ich habe da ein SQL Problem mit einer MariaDB / MSQL DB und finde den SQL Fehler nicht.

Code Auswählen Erweitern


MySQL [fhem]> SELECT TIMESTAMP,DEVICE,READING,VALUE,LAG(VALUE) OVER w AS 'DELTA' FROM history WINDOW w AS (ORDER BY TIMESTAMP) where DEVICE = 'PV_Anlage_1' AND TIMESTAMP > "2020-03-14_00:00:00";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'w AS 'DELTA' FROM history WINDOW w AS (ORDER BY TIMESTAMP) where DEVICE = 'PV_Anla' at line 1


Hier waere die mysql Doku https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag
mit diesem Beispiel

Code Auswählen Erweitern


mysql> SELECT
         t, val,
         LAG(val)        OVER w AS 'lag',
         LEAD(val)       OVER w AS 'lead',
         val - LAG(val)  OVER w AS 'lag diff',
         val - LEAD(val) OVER w AS 'lead diff'
       FROM series
       WINDOW w AS (ORDER BY t);
+----------+------+------+------+----------+-----------+
| t        | val  | lag  | lead | lag diff | lead diff |
+----------+------+------+------+----------+-----------+
| 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
| 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
| 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
| 15:00:00 |  145 |  132 |  140 |       13 |         5 |
| 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
| 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
| 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
+----------+------+------+------+----------+-----------+


[DS_Starter]

Ich muß vorausschicken dass ich die Windows function mit over nicht kenne bzw. noch nicht benutzt habe. Hatte noch keinen Anwendungsfall bei dem ich es gebraucht hätte.

Aber ich würde erstmal schauen ob deine DB Version so etwas überhaupt kann. Bei MariaDB hat man es erst ab Version 10.2 implementiert soweit ich weiß.
Die Syntax ist hier beschrieben: https://mariadb.com/kb/en/window-functions-overview/

Setzt du MariaDB oder MySQL ein und in welcher Version ?

Grüße,
Heiko

[DS_Starter]

Dieses Statement läuft bei mir:

Code Auswählen Erweitern


SELECT TIMESTAMP,DEVICE,READING,VALUE,LAG(VALUE) OVER (ORDER BY TIMESTAMP) AS DELTA FROM history where DEVICE = "MySTP_5000" AND TIMESTAMP > "2020-03-14 00:00:00";


Version 10.3.21-MariaDB