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

DS_Starter · 13 März 2024, 21:30:27

Hallo Thowe,

Zitat- in autoForward und cmd darf kein Device-Name mit . enthalten sein
- im cmd mit '....' dürfen keine Anführungszeichen bei der where-clause "" stehen. Mit qq{ ... where device = 'name' ...} funktioniert es, auch mit dem §Platzhalter§.

hmm, ich habe diese Dinge mit folgendem Hash getestet:

Code Auswählen

{
  1  => { 
          timestamp_begin => "2024-03-01 12:00:00",
          autoForward     => '{ ".*ResultRow.*" => "Dum.Rep.All" }',
          cmd             => 'sqlCmd select count(*) from history where device = "SMA_Energymeter" and TIMESTAMP > "§timestamp_begin§"'
        },
}

Funktioniert einwandfrei und die generierten Readings werden per autoforeward auch verteilt:

Code Auswählen

2024.03.13 21:24:36.976 4: DbRep Rep.CPU - multiCmd index >1< start
2024.03.13 21:24:37.119 4: DbRep Rep.CPU - SQL online formatted: select count(*)
from history
where device = "SMA_Energymeter"
  and TIMESTAMP > "§timestamp_begin§";
2024.03.13 21:24:37.136 4: DbRep Rep.CPU - -------- New selection --------- 
2024.03.13 21:24:37.138 4: DbRep Rep.CPU - Command: sqlCmd select count(*) from history where device = "SMA_Energymeter" and TIMESTAMP > "§timestamp_begin§";
2024.03.13 21:24:37.140 4: DbRep Rep.CPU - FullDay option: 0
2024.03.13 21:24:37.141 4: DbRep Rep.CPU - Timestamp begin human readable: 2024-03-01 12:00:00
2024.03.13 21:24:37.142 4: DbRep Rep.CPU - Timestamp end human readable: 2024-03-13 21:24:37
2024.03.13 21:24:37.227 4: DbRep Rep.CPU - Database Model: MYSQL
2024.03.13 21:24:37.230 4: DbRep Rep.CPU - Database connect - user: fhemtest, UTF-8 option set: yes
2024.03.13 21:24:37.235 4: DbRep Rep.CPU - Communication between Client and Server will be compressed
2024.03.13 21:24:37.236 4: DbRep Rep.CPU - SQL execute: SHOW VARIABLES LIKE 'collation_database'
2024.03.13 21:24:37.238 4: DbRep Rep.CPU - Database Character set is >utf8mb4_bin<
2024.03.13 21:24:37.239 4: DbRep Rep.CPU - simple do statement: set names "utf8mb4" collate "utf8mb4_bin"
2024.03.13 21:24:37.240 4: DbRep Rep.CPU - SQL execute: select count(*) from history where device = "SMA_Energymeter" and TIMESTAMP > "'2024-03-01 12:00:00'";
2024.03.13 21:24:40.519 4: DbRep Rep.CPU - SQL result: 3893576
2024.03.13 21:24:40.524 4: Rep.CPU - Forward reading "SqlResultRow_1" to "Dum.Rep.All:SqlResultRow_1" 
2024.03.13 21:24:40.537 4: Rep.CPU - Forward reading "SqlResultRow_2" to "Dum.Rep.All:SqlResultRow_2"

Aktuellste DbRep Version 93_DbRep.pm:v8.53.5-s28638/2024-03-11.

DS_Starter · 13 März 2024, 21:38:54

Auch diese Variante:

Code Auswählen

{
  1  => { 
          timestamp_begin => '2024-03-01 12:00:00',
          autoForward     => '{ ".*ResultRow.*" => "Dum.Rep.All" }',
          cmd             => 'sqlCmd select count(*) from history where device = "Dum.Energy" and TIMESTAMP > §timestamp_begin§'
        },
}

läuft einwandfrei:

Code Auswählen

2024.03.13 21:36:18.466 4: DbRep Rep.CPU - multiCmd index >1< start
2024.03.13 21:36:18.595 4: DbRep Rep.CPU - SQL online formatted: select count(*)
from history
where device = "Dum.Energy"
  and TIMESTAMP > §timestamp_begin§;
2024.03.13 21:36:18.614 4: DbRep Rep.CPU - -------- New selection --------- 
2024.03.13 21:36:18.614 4: DbRep Rep.CPU - Command: sqlCmd select count(*) from history where device = "Dum.Energy" and TIMESTAMP > §timestamp_begin§;
2024.03.13 21:36:18.615 4: DbRep Rep.CPU - FullDay option: 0
2024.03.13 21:36:18.616 4: DbRep Rep.CPU - Timestamp begin human readable: 2024-03-01 12:00:00
2024.03.13 21:36:18.616 4: DbRep Rep.CPU - Timestamp end human readable: 2024-03-13 21:36:18
2024.03.13 21:36:18.688 4: DbRep Rep.CPU - Database Model: MYSQL
2024.03.13 21:36:18.690 4: DbRep Rep.CPU - Database connect - user: fhemtest, UTF-8 option set: yes
2024.03.13 21:36:18.693 4: DbRep Rep.CPU - Communication between Client and Server will be compressed
2024.03.13 21:36:18.693 4: DbRep Rep.CPU - SQL execute: SHOW VARIABLES LIKE 'collation_database'
2024.03.13 21:36:18.695 4: DbRep Rep.CPU - Database Character set is >utf8mb4_bin<
2024.03.13 21:36:18.696 4: DbRep Rep.CPU - simple do statement: set names "utf8mb4" collate "utf8mb4_bin"
2024.03.13 21:36:18.696 4: DbRep Rep.CPU - SQL execute: select count(*) from history where device = "Dum.Energy" and TIMESTAMP > '2024-03-01 12:00:00';
2024.03.13 21:36:20.686 4: DbRep Rep.CPU - SQL result: 123887
2024.03.13 21:36:20.691 4: Rep.CPU - Forward reading "SqlResultRow_1" to "Dum.Rep.All:SqlResultRow_1" 
2024.03.13 21:36:20.700 4: Rep.CPU - Forward reading "SqlResultRow_2" to "Dum.Rep.All:SqlResultRow_2"

DS_Starter · 13 März 2024, 21:48:09

Und jetzt auch noch diese Variante:

Code Auswählen

{
  1  => { 
          timestamp_begin => '2024-03-01 12:00:00',
          autoForward     => '{ ".*" => "Dum.Rep.All" }',
          cmd             => 'sqlCmd select count(*) from history where device = "Dum.Energy" and TIMESTAMP > §timestamp_begin§'
        },
}

Problemlos ...

Code Auswählen

2024.03.13 21:46:54.695 4: DbRep Rep.CPU - multiCmd index >1< start
2024.03.13 21:46:54.841 4: DbRep Rep.CPU - SQL online formatted: select count(*)
from history
where device = "Dum.Energy"
  and TIMESTAMP > §timestamp_begin§;
2024.03.13 21:46:54.855 4: Rep.CPU - Forward reading "state" to "Dum.Rep.All:state" 
2024.03.13 21:46:54.868 4: DbRep Rep.CPU - -------- New selection --------- 
2024.03.13 21:46:54.869 4: DbRep Rep.CPU - Command: sqlCmd select count(*) from history where device = "Dum.Energy" and TIMESTAMP > §timestamp_begin§;
2024.03.13 21:46:54.870 4: DbRep Rep.CPU - FullDay option: 0
2024.03.13 21:46:54.871 4: DbRep Rep.CPU - Timestamp begin human readable: 2024-03-01 12:00:00
2024.03.13 21:46:54.872 4: DbRep Rep.CPU - Timestamp end human readable: 2024-03-13 21:46:54
2024.03.13 21:46:54.950 4: DbRep Rep.CPU - Database Model: MYSQL
2024.03.13 21:46:54.952 4: DbRep Rep.CPU - Database connect - user: fhemtest, UTF-8 option set: yes
2024.03.13 21:46:54.957 4: DbRep Rep.CPU - Communication between Client and Server will be compressed
2024.03.13 21:46:54.958 4: DbRep Rep.CPU - SQL execute: SHOW VARIABLES LIKE 'collation_database'
2024.03.13 21:46:54.960 4: DbRep Rep.CPU - Database Character set is >utf8mb4_bin<
2024.03.13 21:46:54.962 4: DbRep Rep.CPU - simple do statement: set names "utf8mb4" collate "utf8mb4_bin"
2024.03.13 21:46:54.963 4: DbRep Rep.CPU - SQL execute: select count(*) from history where device = "Dum.Energy" and TIMESTAMP > '2024-03-01 12:00:00';
2024.03.13 21:46:58.547 4: DbRep Rep.CPU - SQL result: 123948
2024.03.13 21:46:58.550 4: Rep.CPU - Forward reading "sqlCmd" to "Dum.Rep.All:sqlCmd" 
2024.03.13 21:46:58.560 4: Rep.CPU - Forward reading "sqlResultNumRows" to "Dum.Rep.All:sqlResultNumRows" 
2024.03.13 21:46:58.572 4: Rep.CPU - Forward reading "SqlResultRow_1" to "Dum.Rep.All:SqlResultRow_1" 
2024.03.13 21:46:58.584 4: Rep.CPU - Forward reading "SqlResultRow_2" to "Dum.Rep.All:SqlResultRow_2" 
2024.03.13 21:46:58.634 4: Rep.CPU - Forward reading "state" to "Dum.Rep.All:state"

Thowe · 14 März 2024, 00:00:06

Hallo Heiko,
Danke für die Verifizierungen, die mir nicht gelingen wollen.
Im DEF-Editor steht bei mir

Code Auswählen

+*00:01:00 {
   my $cmdhash = 
   "{
      1   => {    timestamp_end   => 'previous_day_end',
              device          => 'Stromkosten.HH_kWh',
                        reading         => 'state',
                        autoForward     => '{ ".*SqlResultRow_2.*" => "Stromkosten.HH_kWh" }',
            cmd              => 'sqlCmd select value from history where device = "Stromkosten.HH_kWh" and timestamp <= "§timestamp_end§" order by timestamp desc limit 1'
             }
    }"; 
    fhem ("set Rep_test_dev multiCmd $cmdhash");
}

und erhalte ich als Fehlermeldung

Code Auswählen

Can't find string terminator "'" anywhere before EOF at (eval 35383) line 8.

Entferne ich im o.g. Hash die Anführungsstriche im SQL-Platzhalter "§timestamp_end§", erhalte ich als Fehlermeldung

Code Auswählen

Unrecognized character \xC2; marked by <-- HERE after estamp <= <-- HERE near column 382 at (eval 35754) line

Wie können zwei FHEM-Installationen so unterschiedliche Eigenschaften haben?
Viele Grüße,
Thowe

DS_Starter · 14 März 2024, 00:08:34

Einen Fehler sehe ich.
Entferne erstmal die Anführungsstriche die den Hash (eigentlich ist eine Hashreferenz) umfassen:

my $cmdhash =
"{
....
}"

Und dann update auf die aktuellste Version falls noch nicht geschehen.

Als weiteren Test führe den Hash direkt im DbRep multiCmd aus. Einfach dort reinkopieren:

Code Auswählen

{
  1 => { timestamp_end   => 'previous_day_end',
         device          => 'Stromkosten.HH_kWh',
         reading         => 'state',
         autoForward     => '{ ".*SqlResultRow_2.*" => "Stromkosten.HH_kWh" }',
         cmd              => 'sqlCmd select value from history where device = "Stromkosten_HH.kWh" and timestamp <= "§timestamp_end§" order by timestamp desc limit 1'
       }
}

Thowe · 14 März 2024, 12:49:56

Hallo Heiko,
Danke für die Unterstützung!
Die Problemlösung: Mit dem Umweg über eine String-Variable müssen die Anführungszeichen in der Hash-Struktur escaped werden. Beispiel:

Code Auswählen

+*00:01:00 {
   my $cmdhash = 
   "{
      1   => {    timestamp_end   => 'previous_day_end',
                  device          => 'Stromkosten.HH_kWh',
                 reading         => 'state',
                autoForward     => '{\".*SqlResultRow_2.*\" => \"Stromkosten.HH_kWh => state\"}',
                 cmd             => 'sqlCmd select value from history where device = \"Stromkosten.HH_kWh\" and timestamp <= §timestamp_end§ order by timestamp desc limit 1'
             },
    }"; 
    fhem ("set Rep_test_dev multiCmd $cmdhash");
}

Zur Sicherheit noch die Fragen: Gewährleistet der Ablauf, dass bei einem autoForward das Device den neuen Wert hat, bevor die nächste DbRep-Anweisung im multiCmd ausgeführt wird? Gilt gleiches auch für executeAfterProc?
Viele Grüße,
Thowe

Thowe · 15 März 2024, 07:46:09

Hallo Heiko,
nur ein Gedanke:
Der hash-definierte Ablauf kann als zusammenhängender gerichteter Graph aufgefasst werden. Knoten sind Hash-Elemente 1,2,...,n. Mit den Kanten kann bestimmt werden, über welche Knoten der Weg läuft. Soll ein Teilgraph übersprungen werden, haben wir eine Splitsituation. Zur Entscheidung, welcher Weg genommen wird, muss eine Bedingung geprüft werden. Im einfachsten Fall ist der Weg eine ,,Knotenumgehung" zum nächsten Knoten. Falls der einzelne Knoten Träger einer Kantenbedingung wäre, würden von einem Startknoten mehrere Wege kaskadiert entlang der Knotennummern zu prüfen sein.
Viele Grüße,
Thowe

DS_Starter · 15 März 2024, 08:25:28

Morgen Thowe,

ZitatDie Problemlösung: Mit dem Umweg über eine String-Variable müssen die Anführungszeichen in der Hash-Struktur escaped werden.

Würdest du die äußeren Anführungszeichen ( my $cmdhash = "{ .... }" ) weglassen, bräuchtest du das escapen nicht.

Zitatnur ein Gedanke:
Der hash-definierte Ablauf kann als zusammenhängender gerichteter Graph aufgefasst werden. Knoten sind Hash-Elemente 1,2,...,n.

Ja, diesen Gedanken würde ich verfolgen.
Es ist zu beachten dass ein ausgeführter Knoten aus dem Hash entfernt wird (geschieht auch jetzt). Damit ist gewährleistet sich keine Endlosschleifen zu erstellen.
Ich versuche mich an einem ersten Testentwurf sobald ich dazu komme.

Grüße,
Heiko

Thowe · 15 März 2024, 12:27:45

Hallo Heiko,
ich hatte mit weggelassenen Quotes getestet, muss wohl noch einen Fehler gemacht haben.
Zur Sicherheit noch die Fragen: Gewährleistet der Ablauf, dass bei einem autoForward das Device den neuen Wert hat, bevor die nächste DbRep-Anweisung im multiCmd ausgeführt wird? Gilt gleiches auch für executeAfterProc?
Viele Grüße,
Thowe

flummy1978 · 15 März 2024, 12:38:09

Hallöchen,

irgendwie bin ich aktuell mit der Änderung nach der Umstellung und einer nahezu leeren DB noch nicht so ganz glücklich.... (liegt aber zu 99,99999999999999% an mir)

Sperre ich die DB mit "reopen ...." werde ich alle 15 Min mit

Code Auswählen

DbRep SQL_DBrep_Device - command message after averageValue: >Reopen executed.<im Log zugespamt. Das Verbose für das Device auf <2 möchte ich nicht unbedingt setzen, weil ich sonst solche "Fehler" (und wie den folgenden) nicht erkennen würde. Ja es läuft, aber ich würde es gern ohne Fehler haben und nicht nur "läuft irgedwie"

Nachdem ich das reopen wieder rausgenommen habe und das Device lediglich mit

Code Auswählen

set SQL_DBrep_Device multiCmd {...}läuft, kommt ständig (unregelmäßig) die Meldung:

Code Auswählen

2024.03.15 12:08:53.831 2: DbRep SQL_DBrep_Device - ERROR - DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at ./FHEM/93_DbRep.pm line 11932.

Hast Du nen Tipp für mich, wie ich mich der Fehlerursache nähern kann? Für das Grundverständnis:

In meinem Aufbau ist es so, dass einige Strom, (verbrauch), Temperatur, Helligkeit Infos usw. geloggt. Die allermeisten davon bleiben nur für 3-5 Tage in der Masse der Daten der Rest wird gelöscht. Alle 15 min wird ein Schnitt von einigen Daten erstellt, die dann für länger beibehalten werden. Ich gehe davon aus, dass die o.g. Fehlermeldung zu 99% von dem 15 min at kommt, das mit multiCmd arbeitet und alle Befehle nacheinander probiert.(Wenn ich oben die Sperre mit Reopen einbaue, kommt nämlich die zweite Meldung deutlich seltener aber eben auch ab und zu mal)

VG
Andreas

DS_Starter · 15 März 2024, 13:14:16

@Thowe,
bin noch eine Antwort schuldig

Zitatgewährleistet der Ablauf, dass bei einem autoForward das Device den neuen Wert hat, bevor die nächste DbRep-Anweisung im multiCmd ausgeführt wird? Gilt gleiches auch für executeAfterProc?

Ja das ist gewährleistet sofern alles richtig eingestellt ist.

@flummy1978,

ZitatSperre ich die DB mit "reopen ...." werde ich alle 15 Min mit

Code Auswählen
DbRep SQL_DBrep_Device - command message after averageValue: >Reopen executed.<
im Log zugespamt. Das Verbose für das Device auf <2 möchte ich nicht unbedingt setzen, weil ich sonst solche "Fehler" (und wie den folgenden) nicht erkennen würde. Ja es läuft, aber ich würde es gern ohne Fehler haben und nicht nur "läuft irgedwie"

Nach kurzer Überlegung halte ich es für angebracht die aus den execafter/before resultierenden Meldungen auf verbose 3 setze. So essntiell sind sie im Normalfall nicht und du kannst das DbRep mit verbose 2 betreiben ohne wichtige Meldungen zu verpassen.

Thowe · 15 März 2024, 20:35:36

Hallo Heiko,
das DbLogInclude beim autoForward in ein Dummy wird erst mit Verspätung ausgeführt (ich vermute: DbLog-Commit nach DbRep-Beendigung, s. reopen-Thema). Dadurch entfallen wohl auch Lösungen weg wie

Code Auswählen

executeAfterProc = {fhem("set Rep_dummy sqlCmd insert into history values (§timestamp_end§,'Dummydev','DUMMY','calculated','state','".(ReadingsVal("Dummydev","state",0)."','')")}

Siehst Du eine Möglichkeit, in einem Hash-Ablauf ein Device-Reading in die DB zu schreiben, das vom nächsten Ablaufschritt verarbeitet werden kann? Commit erzwingen mit executeBeforeAfter?
Viele Grüße,
Thowe

DS_Starter · 15 März 2024, 21:03:13

Hallo Thowe,

das geht so nicht weil ein "set Rep_dummy sqlCmd ..." nichblockierend asynchron arbeitet.
Wenn du sowas machen möchtest, musst du das blockierende "get ... sqlCmdBlocking ..." verwenden.
Lass dich durch das get nicht irritieren, ist nur damit der User nicht "zufällig" den falschen Setter benutzt.
Denk daran dass sqlCmdBlocking synchron arbeitet, die DB Zeit also dein FHEM beeinflusst.

betateilchen · 16 März 2024, 12:37:17

Hallo Heiko,

es gab vor einiger Zeit schonmal die Diskussion, dass beim Setzen von DbRep-Attributen der im Attribut angegeben perl Code im eval sofort ausgeführt wird. Damals war das Thema "Syntaxprüfung bei der Angabe von perl Code in Attributwerten".

Nun weiß ich nicht mehr, wie bzw. ob die Geschichte damals gelöst wurde.

Heute habe ich eine ganze Weile gebraucht, um herauszufinden, woher in meinem FHEM-Log eine Debug Meldung immer beim FHEM Start kommt, die mir die Uhrzeit ins Log schreibt. Am Ende bin darauf gekommen, dass das beim Setzen eines Attributwertes execute(After|Before)Proc passiert. Dort hatte ich "{Debug $hms}" eingetragen.

Code Auswählen

2024.03.16 10:11:33 0: Server shutdown
2024.03.16 10:11:37 1: stacktrace:
2024.03.16 10:11:37 1:     main::Debug                         called by ./FHEM/93_DbRep.pm (1616)
2024.03.16 10:11:37 1:     main::DbRep_Attr                    called by fhem.pl (3985)
2024.03.16 10:11:37 1:     main::CallFn                        called by fhem.pl (3205)
2024.03.16 10:11:37 1:     main::CommandAttr                   called by fhem.pl (1282)
2024.03.16 10:11:37 1: DEBUG>{Debug $hms}
2024.03.16 10:11:37 1: stacktrace:
2024.03.16 10:11:37 1:     main::Debug                         called by (eval 260) (1)
2024.03.16 10:11:37 1:     (eval)                              called by ./FHEM/93_DbRep.pm (1617)
2024.03.16 10:11:37 1:     main::DbRep_Attr                    called by fhem.pl (3985)
2024.03.16 10:11:37 1:     main::CallFn                        called by fhem.pl (3205)
2024.03.16 10:11:37 1:     main::CommandAttr                   called by fhem.pl (1282)
2024.03.16 10:11:37 1: DEBUG>10:11:37
2024.03.16 10:11:38 0: Featurelevel: 6.3

Ist das wirklich so gewollt?

DS_Starter · 16 März 2024, 12:49:07

Hallo Udo,

ZitatAm Ende bin darauf gekommen, dass das beim Setzen eines Attributwertes execute(After|Before)Proc passiert. Dort hatte ich "{Debug $hms}" eingetragen.

Du hast damals mit geholfen zu ergründen ob/wieso die Variable $hms in execute(After|Before)Proc durch einen User nicht verwendet werden konnte. Das war der Kontext.

Es ist natürlich ein berechtigter Hinweis/Einwand dass der Ausdruck bei Start von FHEM (respektive dem Setzen des Attr beim Start) nicht ausgeführt werden sollte.
Habe schon eine Idee die ich mir anschaue und eine Korrektur einbaue.

Danke für den Hinweis.

Grüße,
Heiko