Neues Database Plugin

[Msinn]

Irgendwann könnte man vielleicht auch noch den Handler auswählbar machen.

Auch das geht bereits. Du musst dazu nut rechts auf den Button mit der Hand klicken.

[lexxmm]

Moin...neues Problem/Verhalten nach meinem Update: sämtliche 'avg' plots (simples {{ plot.period('', 'Heizung.ise.Aussentemperatur', 'avg', '3d') }}) führen zu folgendem Fehler:

Can not execute query: SELECT MIN(time), ROUND(AVG(val_num * (COALESCE(duration * (time >= %(time_start)s) * (time + duration <= %(time_end)s), 0) + COALESCE(duration / duration * (time + duration - %(time_start)s) * (time < %(time_start)s) * (time + duration >= %(time_start)s), 0) + COALESCE(COALESCE(duration, %(inow)s - time) / COALESCE(duration, %(inow)s - time) * (%(time_end)s - time) * (time + COALESCE(duration, %(inow)s - time) >= %(time_end)s), 0))) / AVG((COALESCE(duration * (time >= %(time_start)s) * (time + duration <= %(time_end)s), 0) + COALESCE(duration / duration * (time + duration - %(time_start)s) * (time < %(time_start)s) * (time + duration >= %(time_start)s), 0) + COALESCE(COALESCE(duration, %(inow)s - time) / COALESCE(duration, %(inow)s - time) * (%(time_end)s - time) * (time + COALESCE(duration, %(inow)s - time) >= %(time_end)s), 0))), 4) FROM log WHERE item_id = %(id)s AND time >= (SELECT COALESCE(MAX(time), 0) FROM log WHERE item_id = %(id)s AND time < %(time_start)s) AND time <= %(time_end)s AND time + COALESCE(duration, %(inow)s - time) > (SELECT COALESCE(MAX(time), 0) FROM log WHERE item_id = %(id)s AND time < %(time_start)s) GROUP BY ROUND(time / %(step)s) ORDER BY time ASC (args OrderedDict([('time_start', 1697131399796), ('time_end', 1697390599797), ('inow', 1697390599797), ('id', 446), ('step', 2592000)])): (1055, "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'smarthome_db.log.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
2023-10-15  19:23:19 ERROR    plugins.database    mysqldb@: Database: Error for query SELECT MIN(time), ROUND(AVG(val_num * (COALESCE(duration * (time >= 1697131399796) * (time + duration <= 1697390599797), 0) + COALESCE(duration / duration * (time + duration - 1697131399796) * (time < 1697131399796) * (time + duration >= 1697131399796), 0) + COALESCE(COALESCE(duration, 1697390599797 - time) / COALESCE(duration, 1697390599797 - time) * (1697390599797 - time) * (time + COALESCE(duration, 1697390599797 - time) >= 1697390599797), 0))) / AVG((COALESCE(duration * (time >= 1697131399796) * (time + duration <= 1697390599797), 0) + COALESCE(duration / duration * (time + duration - 1697131399796) * (time < 1697131399796) * (time + duration >= 1697131399796), 0) + COALESCE(COALESCE(duration, 1697390599797 - time) / COALESCE(duration, 1697390599797 - time) * (1697390599797 - time) * (time + COALESCE(duration, 1697390599797 - time) >= 1697390599797), 0))), 4) FROM log WHERE item_id = 446 AND time >= (SELECT COALESCE(MAX(time), 0) FROM log WHERE item_id = 446 AND time < 1697131399796) AND time <= 1697390599797 AND time + COALESCE(duration, 1697390599797 - time) > (SELECT COALESCE(MAX(time), 0) FROM log WHERE item_id = 446 AND time < 1697131399796) GROUP BY ROUND(time / 2592000) ORDER BY time ASC: (1055, "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'smarthome_db.log.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
2023-10-15  19:23:19 ERROR    modules.websocket.sv Problem fetching series for Heizung.ise.Aussentemperatur: (1055, "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'smarthome_db.log.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by") - Wrong sqlite/database plugin?

​

Da ich hier im Forum dazu nix gefunden habe, gehe ich von eigenem "Verschulden" aus Jemand eine Idee zur Ursache?

edit: ich nutze mysql

[lexxmm]

keine?

[wvhn]

Eine Idee (ohne eigene Erfahrung mit mySQL):
wenn man die Fehlermeldung im Netz recherchiert gibt es einen Hinweis auf den sql_mode "only_full_group_by", der defaultmäßig aktiviert ist. Du könntest versuchen, ihn zu deaktivieren. Dann akzeptiert mySQL auch nicht-aggregierte Spalten in den GROUP BY und ORDER BY (https://dev.mysql.com/doc/refman/5.7...-handling.html).


Gruß
Wolfram

[lexxmm]

Vielen Dank. Antwort leider zu spät gelesen. War ein guter Tipp. Ich hatte mir zwischenzeitlich von ChatGPT helfen lassen Kann ich nur empfehlen...für alle die es interessiert:

Der Fehler, den Sie sehen, bezieht sich auf die sql_mode Einstellung in MySQL, die only_full_group_by enthält. Diese Einstellung erzwingt einen strengeren SQL-Standard, bei dem Spalten, die nicht in einer Aggregatfunktion (GROUP BY) verwendet werden, Teil der GROUP BY Klausel sein müssen, wenn sie anderweitig referenziert werden, wie z.B. in der ORDER BY Klausel.

Um diesen Fehler zu beheben, haben Sie zwei Hauptoptionen: Ändern Sie die SQL-Abfrage, um mit only_full_group_by kompatibel zu sein:

Sie könnten die time Spalte in Ihrer GROUP BY Klausel aufnehmen, da sie in der ORDER BY Klausel referenziert wird. Seien Sie jedoch vorsichtig mit diesem Ansatz, da das Hinzufügen von zusätzlichen Spalten zu GROUP BY die von Ihrer Abfrage zurückgegebenen Ergebnisse ändern kann.

Ändern Sie diesen Teil:

GROUP BY ROUND(time / %(step)s) ORDER BY time ASC


Zu:

GROUP BY ROUND(time / %(step)s), time ORDER BY time ASC





Ändern Sie die MySQL-Einstellungen, um das only_full_group_by zu entfernen:

Wenn Sie administrativen Zugriff auf die MySQL-Instanz haben und sicher sind, dass diese Änderung andere Teile Ihres Systems nicht negativ beeinflusst, können Sie den sql_mode anpassen, um only_full_group_by zu entfernen.

Um es für die aktuelle Sitzung zu ändern:

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));



Für eine dauerhafte Änderung müssen Sie die my.cnf (oder my.ini unter Windows) Datei im Abschnitt [mysqld] aktualisieren und ONLY_FULL_GROUP_BY aus dem sql_mode entfernen. Nach dem Ändern müssen Sie den MySQL-Dienst neu starten.

[wvhn]

wow.

lexxmm Kannst Du den ChatGPT-Prompt dazu noch angeben? Ich habe gerade mal verschiedene Prompts ausprobiert, die nicht so ein umfassendes Ergebnis liefern, dafür aber tief in einzelne Details gehen bis hin zu einer komplett neu geschriebenen SQL-Abfrage.

Gruß
Wolfram

[lexxmm]

ganz simpel:

ich habe folgenden Fehler: SELECT MIN(time), ROUND(AVG(val_num * (COALESCE(duration * (time >= %(time_start)s) * (time + duration <= %(time_end)s), 0) + COALESCE(duration / duration * (time + duration - %(time_start)s) * (time < %(time_start)s) * (time + duration >= %(time_start)s), 0) + COALESCE(COALESCE(duration, %(inow)s - time) / COALESCE(duration, %(inow)s - time) * (%(time_end)s - time) * (time + COALESCE(duration, %(inow)s - time) >= %(time_end)s), 0))) / AVG((COALESCE(duration * (time >= %(time_start)s) * (time + duration <= %(time_end)s), 0) + COALESCE(duration / duration * (time + duration - %(time_start)s) * (time < %(time_start)s) * (time + duration >= %(time_start)s), 0) + COALESCE(COALESCE(duration, %(inow)s - time) / COALESCE(duration, %(inow)s - time) * (%(time_end)s - time) * (time + COALESCE(duration, %(inow)s - time) >= %(time_end)s), 0))), 4) FROM log WHERE item_id = %(id)s AND time >= (SELECT COALESCE(MAX(time), 0) FROM log WHERE item_id = %(id)s AND time < %(time_start)s) AND time <= %(time_end)s AND time + COALESCE(duration, %(inow)s - time) > (SELECT COALESCE(MAX(time), 0) FROM log WHERE item_id = %(id)s AND time < %(time_start)s) GROUP BY ROUND(time / %(step)s) ORDER BY time ASC (args OrderedDict([('time_start', 1697131399796), ('time_end', 1697390599797), ('inow', 1697390599797), ('id', 446), ('step', 2592000)])): (1055, "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'smarthome_db.log.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

hab nicht mal eine Frage gestellt ABER das ist ChatGPT 4 nicht 3.5

[Cannon]

Ich kriege die Probleme mit der blockierten Datenbank nicht so recht gelöst, auch nicht nach Monaten:

2023-12-04  18:41:41 WARNING  plugins.database    Problem dumping env.core.memory: database is locked
2023-12-04  18:41:46 WARNING  plugins.database    Problem dumping env.core.threads: database is locked
2023-12-04  18:41:51 WARNING  plugins.database    Problem dumping env.core.scheduler.worker_threads: database is locked
2023-12-04  18:41:56 WARNING  plugins.database    Problem dumping env.core.scheduler.idle_threads: database is locked
2023-12-04  18:42:01 WARNING  plugins.database    Problem dumping env.core.scheduler.active_threads: database is locked
2023-12-04  18:42:06 WARNING  plugins.database    Problem dumping env.system.load: database is locked
2023-12-04  18:42:11 WARNING  plugins.database    Problem dumping env.system.memory.used: database is locked
2023-12-04  18:42:16 WARNING  plugins.database    Problem dumping env.system.memory.percent: database is locked
2023-12-04  18:42:21 WARNING  plugins.database    Problem dumping env.system.swap: database is locked
2023-12-04  18:42:26 WARNING  plugins.database    Problem dumping env.system.diskusagepercent: database is locked
2023-12-04  18:42:31 WARNING  plugins.database    Problem dumping Zentral.Energie.Verbrauch.Wirkleistung_L1_L2_L3: database is locked
2023-12-04  18:42:36 WARNING  plugins.database    Problem dumping Zentral.Energie.Verbrauch.Wirkleistung_L1: database is locked
2023-12-04  18:43:45 WARNING  plugins.database    Problem dumping Zentral.Energie.Verbrauch.Wirkleistung_L1_L2_L3: database is locked
2023-12-04  18:43:50 WARNING  plugins.database    Problem dumping Zentral.Energie.Verbrauch.Wirkleistung_L1: database is locked
2023-12-04  18:44:02 ERROR    lib.db              Can not execute query: SELECT time, val_num as value FROM log WHERE item_id = ? AND time BETWEEN ? AND ? ORDER BY time ASC (args [56, 1672531200000, 1695340800000]): database is locked
2023-12-04  18:44:02 ERROR    plugins.db_addon    Error for query 'SELECT time, val_num as value FROM log WHERE item_id = 56 AND time BETWEEN 1672531200000 AND 1695340800000 ORDER BY time ASC': database is locked
2023-12-04  18:44:02 ERROR    plugins.db_addon    Error occurred during _query_item. Aborting...
2023-12-04  18:44:02 WARNING  plugins.db_addon    no valid data from database query received during _prepare_value_list. Aborting...​

Bin ich der Einzige mit dem Problem?

Wenn ich mit fuser auf die DB teste und dann mit ps mir den Prozess anzeigen lasse ist das nur SmartHomeNG. Nichts anderes. Auch das pausieren des DatabaseAddons führt nicht zur Lösung. Allerdings scheint das deaktiveren das DatabaseAddons das Problem zu lösen. Ich erhalte zwar mal eine Meldung:

2023-12-05  00:46:02 NOTICE   plugins.database    Skipping dump, since an other database operation running! Data is buffered and dumped later.
2023-12-05  00:46:37 NOTICE   plugins.database    Dumping buffered data from skipped dump(s).​

Aber die scheint nicht dramatisch zu sein, denke ich. Es wird halt nur später geschrieben.

Nach meinem jetztigen Kenntnisstand wäre der Fehler im DataBaseAddon zu suchen, richtig?

Ich nutzte übrigens die letzte aktuelle master-Version von SmartHomeNG nebst Plugins.

[aschwith]

Hallo zusammen,

gibt es hier SQL Experten im Forum, die bei der Erweiterung des database plugins unterstützen könnten?

Ich würde gerne das database plugin um eine neue DB Abfrage "delta" erweitern. Also als Beispiel:

value = item.db('delta','52w','now')

Die Option "delta" soll dann aus der Datenbank das Delta zwischen den Werten t_start und t_end berechnen. Also delta = DB_Wert(t_end) - DB_Wert(t_start). In dem Beispiel oben als das Delta aus aktuellem Wert und dem Wert von vor einem Jahr.

Für den Sonderfall von monoton steigenden Werten ginge es theroetisch jetzt schon mit

delta = item.db('max','52w','now') - item.db('min','52w','now')

Ich suche hier eine allgemeingültige Lösung.

[Sisamiwe]

Ich suche hier eine allgemeingültige Lösung.

Schau dir mal das dbaddon Plugin an. Da habe ich genau für solche Auswertungen erstellt. Es greift nur lesend auf die DB zu und kann u.a. das Delta (dort genannt Verbrauch) errmitteln.

[aschwith]

Hi Sisamiwe, danke für den Hinweis. Das dbaddon Plugin habe ich auf dem Schirm, ist auch ein sehr schönes und mächtiges Plugin. Ich will allerdings explizit die nativen database plugin Funktionen erweitern: Gerade für den Usecase, in Logiken zum Beispiel zum Ende des Jahres ein paar statistische Werte aus der DB zu ziehen. Ich brauche keine regelmäßigen Deltaberechnungen.
Perspektivisch würde ich auch gerne die Funktion "differentiate" des plugins weiter debuggen. Diese ist so aktuell nicht nutzbar. Die Option "integrate" funktioniert sehr gut. Analog habe ich auf dem Zettel, auch das Differenzieren, z.B. um aus Energiemessungen Leistungen zu ermitteln und plotten können.

Von daher der Aufruf an smarthomeNG user mit SQL Erfahrungen....

Viele Grüße

[Sisamiwe]

aschwith
Ein bisschen SQL kann ich.
Schlussendlich baut das Plugin nur ein SQL Statement zusammen. Nutzt du mariadb oder sqlite?
Ich habe "offline" also ohne shng erstmal das SQL Statement entwickelt und dann ins Plugin bzw Python übertragen.

Wegen der Belastung der Datenbank könnte man im Plugin den Wert zum jeweiligen Zeitpunkt ermitteln und dann die Differenz im Plugin berechnen. So läuft das auch im dbaddon Plugin.

Da ist aber auch allerhand Fehlerhandling notwendig. (es gibt zum Zeitpunkt keinen Wert und man muss den nächsten suchen oder es gibt überhaupt keinen Wert, weil es da noch keine Werte in der DB gab,...)

[aschwith]

Ich nutze mariadb. Dein Hinweis mit Fehlerhandling ist völlig richtig. Fürs Erste würde mir ausreichen, wenn die Funktion "none" zurückgibt, falls es kein Delta berechnet werden kann.

Kennst Du bei SQL die Abfrage für ein Differenzieren? Ich hatte mich mal im database plugin in der Funktion

def _single(self, func, start, end='now', item=None):

an der function "differentiate" versucht, bin aber an dem SQL Aufruf "LAG" gescheitert. Dieser scheint nicht von allen DBs unterstützt zu werden. Kennst Du dich da aus?

[aldaris]

Guten Tag, leider muss ich eine Frage zum Database Plugin stellen. Ich kriege es nicht hin:
Ich habe eine Item mit Datenbank, das regelmäßig Einträge generiert. In einem zweiten Item möchte ich stündlich die Veränderung der letzten Stunde darstellen. Das zweite Item hat crontab und eval auf das erste Item. Bisher war mein Basisitem monoton steigend, eval war

sh.A.db('max', '1h') - sh.A.db('min', '1h')

Klappte.

Neue Anforderung, das Basisitem kann auch fallen, dann liefert das oben keine sinnvollen Ergebnisse mehr. Daher habe ich es mit diff probiert:

sh.A.db('diff', '1h')

Das liefert aber leider auch rein positive Ergebnisse. In meinem Beispiel, wo die Datenwerte alle negativ sind und monoton fallen (-2, -3, -12, -12, -34,..) bekomme ich trotzdem positive Ergebnisse (-2 zu -34 hätte ich gerne -32). Ist das absichtlich so und/oder wie kann ich das sonst lösen?

Danke für jeden Hinweis und frohe Festtage
Aldaris

[ooUrmeloo]

Guten Tag, leider muss ich eine Frage zum Database Plugin stellen. Ich kriege es nicht hin:
Ich habe eine Item mit Datenbank, das regelmäßig Einträge generiert. In einem zweiten Item möchte ich stündlich die Veränderung der letzten Stunde darstellen. Das zweite Item hat crontab und eval auf das erste Item. Bisher war mein Basisitem monoton steigend, eval war

sh.A.db('max', '1h') - sh.A.db('min', '1h')

Klappte.

Neue Anforderung, das Basisitem kann auch fallen, dann liefert das oben keine sinnvollen Ergebnisse mehr. Daher habe ich es mit diff probiert:

sh.A.db('diff', '1h')

Das liefert aber leider auch rein positive Ergebnisse. In meinem Beispiel, wo die Datenwerte alle negativ sind und monoton fallen (-2, -3, -12, -12, -34,..) bekomme ich trotzdem positive Ergebnisse (-2 zu -34 hätte ich gerne -32). Ist das absichtlich so und/oder wie kann ich das sonst lösen?

Danke für jeden Hinweis und frohe Festtage
Aldaris

Was willst Du denn ausrechnen? Das Delta zwischen jetzt und vor einer Stunde? Oder das Delta max in der letzten Stunde minus min in der letzten Stunde?
M.E. macht das 'diff' genau, was es soll:

### sh.item.db(function, start, end='now')
und
* `diff`: return the differences between values

Damit: Start=1h - End=now --> -2 --34 = 32

Ich benutze 'diff' nicht, aber evtl. mal anders herum probieren:

sh.A.db('diff', 'now', '1h')

oder

sh.A.db('raw', 'now') - sh.A.db('raw', '1h')

Nur so eine Idee ...