Neues Database Plugin

[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]

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]

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]

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.

[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.

[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

[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]

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]

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]

keine?

[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

[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.

[bmx]

Ja, stimmt leider. Aber das das bereits über die Admin Oberfläche ging war mir entfallen. Irgendwann könnte man vielleicht auch noch den Handler auswählbar machen.

[Msinn]

Den Loglevel für lib.db kannst Du übrigens auch in der Admin GUI anpassen.

Capto_Capture 2023-09-30_10-10-06_AM.jpg

Falls es den Logger bisher nicht gibt, kann er im selben Tab über den Button weiterer Logger angelegt werden.

[Msinn]

Wird aber nicht viel ergeben. Der Autor der lib.db hat keine DEBUG Ausgaben implementiert. Das höchste der Gefühle sind INFO Ausgaben.