dblog: unique index auf history tabelle möglich?

Begonnen von fhem024, 13 Januar 2025, 07:07:48

Vorheriges Thema - Nächstes Thema

fhem024

Hallo zusammen,

meine Datenschnittstelle liefert unter bestimmten Bedingungen doppelte Werte. Ein unique index wie dieser

create unique index history_idx1 on fhem.history (timestamp,event,device)

würde den Eintrag doppelter Werte verhindern.

Jetzt ist allerdings die Frage, wie dblog darauf reagiert, wenn beim Insert ein dupplicate entry Fehler auftritt. Wird der einfach ignoriert oder bricht dblog dann vollständig ab? Die Frage ist an der Stelle auch, ob der commit auf row level - Ebene stattfindet oder ob ganze Blöcke committed werden? Der doppelte Input-Datensatz kommt ursprünglich von mqtt und ist aller Wahrscheinlichkeit nach (ich habe es noch nicht verifiziert) Teil eines größeren JSONs.

Danke!

DS_Starter

DbLog kann mit gesetzem Primary Key umgehen.
Anzulegen wäre er mit:
  ALTER TABLE fhem.history PRIMARY KEY (`TIMESTAMP`, `DEVICE`, `READING`) USING BTREE;Da in der Tabelle vermutlich schon doppelte Datensätze vorhanden sind, wird ein Primary Key nicht so einfach anzulegen sein.
Um die doppelten Datensätze loszuwerden, kann man DbRep delDoublets probieren oder auch das im DbRep-Wiki beschriebene Standby Verfahren.

Sicher gibt es noch mehr Varianten. Du kannst ja dein gewähltes Verfahren für Nachnutzer dokumentieren.

LG

Proxmox+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

fhem024

Hallo @DS_Starter,

Danke für Deine Info, dass DbLog mit einem unique index (ich hoffe, dass der auch geht abgesehen vom private key) umgehen kann.

Wie Du korrekt vermutet hast, gibt es natürlich schon doppelte Einträge. Die habe ich wie folgt ermittelt (zuvor habe ich einen eindeutigen Index eingefügt):

alter table fhem.history add id serial primary key;

SELECT
    id,
    event,
    TIMESTAMP
FROM
    (   SELECT
            his1.id AS id,
            a.reading,
            a.timestamp,
            a.event,
            a.device
        FROM
            (   SELECT
                    TIMESTAMP,
                    reading,
                    event,
                    device
                FROM
                    (   SELECT
                            TIMESTAMP,
                            reading,
                            event,
                            device,
                            COUNT(reading) AS anzahl
                        FROM
                            fhem.history
                        GROUP BY
                            TIMESTAMP,
                            reading,
                            event,
                            device ) test
                WHERE
                    anzahl > 1 ) a
        JOIN
            fhem.history his1
        ON
            a.timestamp = his1.timestamp
        AND a.reading=his1.reading
        AND a.event = his1.event
        AND a.device=his1.device ) his2
GROUP BY
    reading,
    TIMESTAMP,
    event,
    device
ORDER BY
    TIMESTAMP

Das Ergebnis kann dann erst mal verifiziert werden.

Danach kann man die doppelten Einträge bereinigen, indem man auf die ids, die man oben gefunden hat, einen delete ausführt:

delete from fhem.history where id in (...)

Natürlich kann man den delete auch direkt an den obigen großen Select anbinden - dann muss man den manuellen Zwischenschritt mit dem in-Konstrukt nicht machen. Ich habe aber festgestellt, dass die Kombination in einem einzigen Aufruf sehr lange bis auch gar nicht durchlaufen kann (in akzeptabler Zeit), wenn die Anzahl der Einträge zu groß ist bzw. das System, auf dem die DB läuft, zu schwach ist.

Nach der Bereinigung habe ich den unique index angelegt:
create unique index history_idx1 on fhem.history (timestamp,event,device) USING BTREE;

Ich habe absichtlich die Spalte event und nicht reading genommen, weil event auch den Wert enthält.

DS_Starter

Ob es mit einem Unique Key funktioniert kann ich nicht sagen, ich prüfe derzeit auf Primary Key und es sind dementsprechende Insert Befehle eingebaut/modifiziert.

Bin auf deine Ergebnisse mit dem Unique gespannt.
Proxmox+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

fhem024

Zitat von: DS_Starter am 13 Januar 2025, 20:13:23Ob es mit einem Unique Key funktioniert kann ich nicht sagen,
Ich habe nun heute das erste mal wieder die Situation gehabt, wo es zu den doppelten Einträgen gekommen wäre, wenn der unique Index nicht entsprechend angelegt gewesen wäre. Hat keine Probleme gegeben - auch keine Einträge im Log (bei Verbose 3). Also alles gut.