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

ch.eick · 14 Februar 2022, 14:00:40

Zitat von: DS_Starter am 14 Februar 2022, 13:45:49
wie sieht denn das SQL aus ?


SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ;

               INSERT INTO history
                 (TIMESTAMP,DEVICE,READING,VALUE)
                  SELECT
                    TIMESTAMP,DEVICE,READING,VALUE
                  FROM (
                    SELECT
                      DATE_ADD(CURDATE(),INTERVAL t2.HOUR HOUR) AS TIMESTAMP,
                      t2.DEVICE,
                      @readingname                             AS READING,
                      cast(if(avg(t2.FACTOR) > 1.6, 1.6,
                              avg(t2.FACTOR) ) AS DECIMAL(2,1)) AS VALUE
                    FROM (
                      SELECT * FROM (
                        SELECT
                          t1.TIMESTAMP,
                          t1.HOUR,
                          t1.DEVICE,
                          t1.READING,
                          t1.VALUE,
                          if(@diff = 0,NULL, @temp:=cast((t1.VALUE-@diff) AS DECIMAL(6,2)))                                    AS DIFF,
                          if(((t1.VALUE+(-1*@temp))*@corr)=0,0, cast((t1.VALUE/(t1.VALUE+(-1*@temp))*@corr) AS DECIMAL(4,1))) AS FACTOR,
                          @diff:=t1.VALUE                                                                                        AS curr_V
                        FROM (
                          SELECT
                            h.TIMESTAMP,
                            date(h.TIMESTAMP) AS DATE,
                            hour(h.TIMESTAMP) AS HOUR,
                            h.DEVICE,
                            h.READING,
                            h.VALUE
                          FROM history AS h
                          WHERE h.DEVICE    =  @device
                            AND (h.READING  =  @reading1 OR h.READING = @reading2)
                            AND h.TIMESTAMP >= DATE_SUB(DATE(now()),INTERVAL @days DAY)
                            AND h.TIMESTAMP <= CURDATE()
                            AND MINUTE(h.TIMESTAMP) = 0
                            AND h.VALUE >= 0
                          GROUP BY DATE,HOUR,h.READING,h.DEVICE,h.TIMESTAMP
                         )t1
                       )tx
                        WHERE READING != @reading2
                          AND HOUR > 6
                     )t2
                      GROUP BY t2.HOUR,t2.DEVICE
                   )t3
                    WHERE
                      t3.VALUE != 0
                    ON DUPLICATE KEY UPDATE
                      VALUE=t3.VALUE;

DS_Starter · 14 Februar 2022, 14:09:03

... Monster

Habs bei mir getestet, ohne Befund:

Code Auswählen


2022.02.14 14:03:06.594 4: DbRep Report - -------- New selection --------- 
2022.02.14 14:03:06.595 4: DbRep Report - Command: sqlCmd SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ;   INSERT INTO history  (TIMESTAMP,DEVICE,READING,VALUE)  SELECT  TIMESTAMP,DEVICE,READING,VALUE  FROM (  SELECT  DATE_ADD(CURDATE(),INTERVAL t2.HOUR HOUR) AS TIMESTAMP,  t2.DEVICE,  @readingname AS READING,  cast(if(avg(t2.FACTOR) > 1.6, 1.6,  avg(t2.FACTOR) ) AS DECIMAL(2,1)) AS VALUE  FROM (  SELECT * FROM (  SELECT  t1.TIMESTAMP,  t1.HOUR,  t1.DEVICE,  t1.READING,  t1.VALUE,  if(@diff = 0,NULL, @temp:=cast((t1.VALUE-@diff) AS DECIMAL(6,2))) AS DIFF,  if(((t1.VALUE+(-1*@temp))*@corr)=0,0, cast((t1.VALUE/(t1.VALUE+(-1*@temp))*@corr) AS DECIMAL(4,1))) AS FACTOR,  @diff:=t1.VALUE AS curr_V  FROM (  SELECT  h.TIMESTAMP,  date(h.TIMESTAMP) AS DATE,  hour(h.TIMESTAMP) AS HOUR,  h.DEVICE,  h.READING,  h.VALUE  FROM history AS h  WHERE h.DEVICE = @device  AND (h.READING = @reading1 OR h.READING = @reading2)  AND h.TIMESTAMP >= DATE_SUB(DATE(now()),INTERVAL @days DAY)  AND h.TIMESTAMP <= CURDATE()  AND MINUTE(h.TIMESTAMP) = 0  AND h.VALUE >= 0  GROUP BY DATE,HOUR,h.READING,h.DEVICE,h.TIMESTAMP  )t1  )tx  WHERE READING != @reading2  AND HOUR > 6  )t2  GROUP BY t2.HOUR,t2.DEVICE  )t3  WHERE  t3.VALUE != 0  ON DUPLICATE KEY UPDATE  VALUE=t3.VALUE;
2022.02.14 14:03:06.596 4: DbRep Report - timeDiffToNow - year: , day: 2, hour: , min: , sec: 
2022.02.14 14:03:06.596 4: DbRep Report - startMonth: 1 endMonth: 1 lastleapyear: 0 baseYear: 2022 diffdaylight:0 isdaylight:0
2022.02.14 14:03:06.597 4: DbRep Report - FullDay option: 0
2022.02.14 14:03:06.597 4: DbRep Report - Time difference to current time for calculating Timestamp begin: 172801 sec
2022.02.14 14:03:06.597 4: DbRep Report - Timestamp begin human readable: 2022-02-12 14:03:05
2022.02.14 14:03:06.598 4: DbRep Report - Timestamp end human readable: 2022-02-14 14:03:06
2022.02.14 14:03:06.625 4: DbRep Report - adminCredentials successfully read from file
2022.02.14 14:03:06.627 4: DbRep Report - Database connect - user: root, UTF-8 option set: yes
2022.02.14 14:03:06.629 4: DbRep Report - Set SQL session variable: SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ;
2022.02.14 14:03:06.632 4: DbRep Report - SQL execute:    INSERT INTO history  (TIMESTAMP,DEVICE,READING,VALUE)  SELECT  TIMESTAMP,DEVICE,READING,VALUE  FROM (  SELECT  DATE_ADD(CURDATE(),INTERVAL t2.HOUR HOUR) AS TIMESTAMP,  t2.DEVICE,  @readingname AS READING,  cast(if(avg(t2.FACTOR) > 1.6, 1.6,  avg(t2.FACTOR) ) AS DECIMAL(2,1)) AS VALUE  FROM (  SELECT * FROM (  SELECT  t1.TIMESTAMP,  t1.HOUR,  t1.DEVICE,  t1.READING,  t1.VALUE,  if(@diff = 0,NULL, @temp:=cast((t1.VALUE-@diff) AS DECIMAL(6,2))) AS DIFF,  if(((t1.VALUE+(-1*@temp))*@corr)=0,0, cast((t1.VALUE/(t1.VALUE+(-1*@temp))*@corr) AS DECIMAL(4,1))) AS FACTOR,  @diff:=t1.VALUE AS curr_V  FROM (  SELECT  h.TIMESTAMP,  date(h.TIMESTAMP) AS DATE,  hour(h.TIMESTAMP) AS HOUR,  h.DEVICE,  h.READING,  h.VALUE  FROM history AS h  WHERE h.DEVICE = @device  AND (h.READING = @reading1 OR h.READING = @reading2)  AND h.TIMESTAMP >= DATE_SUB(DATE(now()),INTERVAL @days DAY)  AND h.TIMESTAMP <= CURDATE()  AND MINUTE(h.TIMESTAMP) = 0  AND h.VALUE >= 0  GROUP BY DATE,HOUR,h.READING,h.DEVICE,h.TIMESTAMP  )t1  )tx  WHERE READING != @reading2  AND HOUR > 6  )t2  GROUP BY t2.HOUR,t2.DEVICE  )t3  WHERE  t3.VALUE != 0  ON DUPLICATE KEY UPDATE  VALUE=t3.VALUE;
2022.02.14 14:03:06.642 4: DbRep Report - data autocommitted
2022.02.14 14:03:06.643 3: DbRep Report - Number of entries processed in db fhemtest: 0 by INSERT

ch.eick · 14 Februar 2022, 14:49:57

Zitat von: DS_Starter am 14 Februar 2022, 14:09:03
... Monster

Wer? Ich?

Ist ja mein reden, nur wo kommt die Meldung dann her?
Das läuft ja auch schon über ein Jahr so, abgesehen von der letzten Division durch Null, die ich dann abgefangen habe.

DS_Starter · 14 Februar 2022, 14:54:02

Gute Frage ...
Aber die Stelle ./FHEM/93_DbRep.pm line 12969 deutet darauf hin dass du sqlCmdBlocking benutzt und nicht sqlCmd. Kann das sein ?

ch.eick · 14 Februar 2022, 15:12:45

Zitat von: DS_Starter am 14 Februar 2022, 14:54:02

Gute Frage ...
Aber die Stelle ./FHEM/93_DbRep.pm line 12969 deutet darauf hin dass du sqlCmdBlocking benutzt und nicht sqlCmd. Kann das sein ?

Ja, denn der weitere Programm Ablauf greift auf die frisch eingetragenen Daten zu.

DS_Starter · 14 Februar 2022, 15:22:04

Auch das hab ich gerade gestestet, auch ohne Befund. Hmm ...

ch.eick · 14 Februar 2022, 15:31:57

Zitat von: DS_Starter am 14 Februar 2022, 15:22:04
Auch das hab ich gerade gestestet, auch ohne Befund. Hmm ...

Hmm, wenn keine SQL Meldung kommt sehe ich auch nicht wo es harkt.
Eine Spalte (NULL) erzeuge ich zumindest nicht wissendlich.

DS_Starter · 14 Februar 2022, 15:40:22

Nö, kommt nichts. Hier nochmal das Log:

Code Auswählen


2022.02.14 15:21:12.949 4: DbRep Report - -------- New selection --------- 
2022.02.14 15:21:12.950 4: DbRep Report - Command: sqlCmdBlocking
2022.02.14 15:21:12.950 4: DbRep Report - SQL execute: SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ; INSERT INTO history (TIMESTAMP,DEVICE,READING,VALUE) SELECT TIMESTAMP,DEVICE,READING,VALUE FROM ( SELECT DATE_ADD(CURDATE(),INTERVAL t2.HOUR HOUR) AS TIMESTAMP, t2.DEVICE, @readingname AS READING, cast(if(avg(t2.FACTOR) > 1.6, 1.6, avg(t2.FACTOR) ) AS DECIMAL(2,1)) AS VALUE FROM ( SELECT * FROM ( SELECT t1.TIMESTAMP, t1.HOUR, t1.DEVICE, t1.READING, t1.VALUE, if(@diff = 0,NULL, @temp:=cast((t1.VALUE-@diff) AS DECIMAL(6,2))) AS DIFF, if(((t1.VALUE+(-1*@temp))*@corr)=0,0, cast((t1.VALUE/(t1.VALUE+(-1*@temp))*@corr) AS DECIMAL(4,1))) AS FACTOR, @diff:=t1.VALUE AS curr_V FROM ( SELECT h.TIMESTAMP, date(h.TIMESTAMP) AS DATE, hour(h.TIMESTAMP) AS HOUR, h.DEVICE, h.READING, h.VALUE FROM history AS h WHERE h.DEVICE = @device AND (h.READING = @reading1 OR h.READING = @reading2) AND h.TIMESTAMP >= DATE_SUB(DATE(now()),INTERVAL @days DAY) AND h.TIMESTAMP <= CURDATE() AND MINUTE(h.TIMESTAMP) = 0 AND h.VALUE >= 0 GROUP BY DATE,HOUR,h.READING,h.DEVICE,h.TIMESTAMP )t1 )tx WHERE READING != @reading2 AND HOUR > 6 )t2 GROUP BY t2.HOUR,t2.DEVICE )t3 WHERE t3.VALUE != 0 ON DUPLICATE KEY UPDATE VALUE=t3.VALUE;
2022.02.14 15:21:12.951 4: DbRep Report - Set SQL session variables: SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ;
2022.02.14 15:21:12.958 4: DbRep Report - Number of entries processed in db fhemtest: 0 by INSERT

Das ist kein SQL Fehler oder sonst etwas was angemeckert wird.
Aber vielleicht ist es das Insert "INSERT INTO history (TIMESTAMP,DEVICE,READING,VALUE)". Wenn z.B. das Feld TIMESTAMP evtl. leer gesetzt werden soll, käme m.M. nach so ein Fehler oder aber du willst einen Wert setzen in einem nicht definierten Feld, also z.B. "INSERT INTO history (TIMESTAMP,DEVICE,READING,VALUE) .... (2022-02-14 00:00:00,bla,blubb,40,irgendwas)"

ch.eick · 14 Februar 2022, 19:19:40

Zitat von: DS_Starter am 14 Februar 2022, 15:40:22
Nö, kommt nichts. Hier nochmal das Log:

Code Auswählen Erweitern
2022.02.14 15:21:12.949 4: DbRep Report - -------- New selection --------- 2022.02.14 15:21:12.950 4: DbRep Report - Command: sqlCmdBlocking 2022.02.14 15:21:12.950 4: DbRep Report - SQL execute: SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ; INSERT INTO history (TIMESTAMP,DEVICE,READING,VALUE) SELECT TIMESTAMP,DEVICE,READING,VALUE FROM ( SELECT DATE_ADD(CURDATE(),INTERVAL t2.HOUR HOUR) AS TIMESTAMP, t2.DEVICE, @readingname AS READING, cast(if(avg(t2.FACTOR) > 1.6, 1.6, avg(t2.FACTOR) ) AS DECIMAL(2,1)) AS VALUE FROM ( SELECT * FROM ( SELECT t1.TIMESTAMP, t1.HOUR, t1.DEVICE, t1.READING, t1.VALUE, if(@diff = 0,NULL, @temp:=cast((t1.VALUE-@diff) AS DECIMAL(6,2))) AS DIFF, if(((t1.VALUE+(-1*@temp))*@corr)=0,0, cast((t1.VALUE/(t1.VALUE+(-1*@temp))*@corr) AS DECIMAL(4,1))) AS FACTOR, @diff:=t1.VALUE AS curr_V FROM ( SELECT h.TIMESTAMP, date(h.TIMESTAMP) AS DATE, hour(h.TIMESTAMP) AS HOUR, h.DEVICE, h.READING, h.VALUE FROM history AS h WHERE h.DEVICE = @device AND (h.READING = @reading1 OR h.READING = @reading2) AND h.TIMESTAMP >= DATE_SUB(DATE(now()),INTERVAL @days DAY) AND h.TIMESTAMP <= CURDATE() AND MINUTE(h.TIMESTAMP) = 0 AND h.VALUE >= 0 GROUP BY DATE,HOUR,h.READING,h.DEVICE,h.TIMESTAMP )t1 )tx WHERE READING != @reading2 AND HOUR > 6 )t2 GROUP BY t2.HOUR,t2.DEVICE )t3 WHERE t3.VALUE != 0 ON DUPLICATE KEY UPDATE VALUE=t3.VALUE; 2022.02.14 15:21:12.951 4: DbRep Report - Set SQL session variables: SET @days:=30, @corr:=0.7, @diff:=0, @temp:=0, @device:='WR_1', @reading1:='SW_Total_DC_P_sumOfAllPVInputs', @reading2:='Solar_Calculation_fc0', @readingname:='Solar_Correction_Faktor_auto' ; 2022.02.14 15:21:12.958 4: DbRep Report - Number of entries processed in db fhemtest: 0 by INSERT

Das ist kein SQL Fehler oder sonst etwas was angemeckert wird.
Aber vielleicht ist es das Insert "INSERT INTO history (TIMESTAMP,DEVICE,READING,VALUE)". Wenn z.B. das Feld TIMESTAMP evtl. leer gesetzt werden soll, käme m.M. nach so ein Fehler oder aber du willst einen Wert setzen in einem nicht definierten Feld, also z.B. "INSERT INTO history (TIMESTAMP,DEVICE,READING,VALUE) .... (2022-02-14 00:00:00,bla,blubb,40,irgendwas)"

Die Tabelle, die ins INSERT geht ist absolut sauber

Code Auswählen


+---------------------+--------+------------------------------+-------+
| TIMESTAMP           | DEVICE | READING                      | VALUE |
+---------------------+--------+------------------------------+-------+
| 2022-02-14 08:00:00 | WR_1   | Solar_Correction_Faktor_auto |   1.6 |
| 2022-02-14 09:00:00 | WR_1   | Solar_Correction_Faktor_auto |   1.6 |
| 2022-02-14 10:00:00 | WR_1   | Solar_Correction_Faktor_auto |   1.6 |
| 2022-02-14 11:00:00 | WR_1   | Solar_Correction_Faktor_auto |   1.2 |
| 2022-02-14 12:00:00 | WR_1   | Solar_Correction_Faktor_auto |   1.0 |
| 2022-02-14 13:00:00 | WR_1   | Solar_Correction_Faktor_auto |   0.9 |
| 2022-02-14 14:00:00 | WR_1   | Solar_Correction_Faktor_auto |   0.7 |
| 2022-02-14 15:00:00 | WR_1   | Solar_Correction_Faktor_auto |   0.6 |
| 2022-02-14 16:00:00 | WR_1   | Solar_Correction_Faktor_auto |   0.6 |
| 2022-02-14 17:00:00 | WR_1   | Solar_Correction_Faktor_auto |   0.2 |
+---------------------+--------+------------------------------+-------+

DS_Starter · 15 Februar 2022, 09:35:50

Schau mal, ich habe hier etwas dazu gefunden: https://stackoverflow.com/questions/19121106/out-of-range-value-for-column-null-at-row-1

Sieht mir ähnlich zu deinem Fall aus.

ch.eick · 15 Februar 2022, 09:42:49

Zitat von: DS_Starter am 15 Februar 2022, 09:35:50
Schau mal, ich habe hier etwas dazu gefunden: https://stackoverflow.com/questions/19121106/out-of-range-value-for-column-null-at-row-1

Sieht mir ähnlich zu deinem Fall aus.

Vielen Dank, da bin auch bereits dran.

ch.eick · 15 Februar 2022, 10:49:37

Hallo Heiko,
es scheint definitiv am INSERT zu liegen, aber warum verstehe ich noch nicht.
Zum Test habe ich stufenweise die SELECTs mit DbRep sqlCmdBlocking ausgeführt und mir die Ausgaben angeschaut.
Das lief alles Fehlerfrei und auch im FHEM Log kam keine Meldung.
Erst beim INSERT kommt die Meldung im DbRep.

Noch verstehe ich es einfach nicht.

DS_Starter · 15 Februar 2022, 10:53:18

Wir setzen in Perl/dem Modul natürlich noch die entsprechenden Perl Bibliotheken DBI/DBD für den Datenbankzugriff ein. Möglicherweise ergeben sich daraus bestimmte Abweichungen von einer Ausführung in einem normalen SQL-Client.

ch.eick · 15 Februar 2022, 18:35:36

Zitat von: DS_Starter am 15 Februar 2022, 10:53:18
Wir setzen in Perl/dem Modul natürlich noch die entsprechenden Perl Bibliotheken DBI/DBD für den Datenbankzugriff ein. Möglicherweise ergeben sich daraus bestimmte Abweichungen von einer Ausführung in einem normalen SQL-Client.

Ich bin dem ganzen nun auf der Spur, auch in der Datenbank kommt diese Meldung ;-(
Da ich auf der aktuellsten MySQL Version 8.0.28 bin scheint da etwas beim INSERT rein gekommen zu sein, was ich noch nicht anders umsetzen kann.
Mal schauen, ob es bei der nächsten Version wieder weg sein wird, mit Docker ist ein Update ja sehr einfach.

Wenn ich Zeit finde kann ich auch mal einen Downgrade der DB machen, mal schauen.

Adimarantis · 08 April 2022, 10:55:13

Ich bekomme bei "reduceLog" mit DBRep (und auch mit DBLog aber natürlich in einer anderen Zeile) immer folgende Fehlermeldung:

Code Auswählen

PERL WARNING: Use of uninitialized value $ku in multiplication (*) at ./FHEM/93_DbRep.pm line 9400.

Nach einem Blick in den Code vermute ich folgendes Problem:

Code Auswählen

my ($kd,$ku) = 1;
Die Intention ist wahrscheinlich dass beide Variablen auf 1 gesetzt werden. Das ist aber nicht der Fall - $ku bleibt leer.
Nach dem ersten $ku++ steht es dann auf 1, daher kommt die Warnung nur 1x pro Lauf.
Soweit ich sehe beinflusst das potentiell nur die Logausgabe und nicht die Funktion und kommt wahrscheinlich nur wenn global->stacktrace=1 gesetzt ist. Trotzdem wäre es schön wenn ich die Warnung loskriegen würde