Probleme beim Löschen einzelner Werte in MariaDB HA

Allgemein
1

Hallo liebe Community,
ich bin auf ein Problem gestoßen bei dem ich keine Lösung finde.
In meiner HA-Datenbank befinden sich fehlerhafte Werte (Null) die mir meine Graphen unschön aussehen lassen.

Gerne möchte ich diese Werte löschen. Ganz konkret geht es um einen Sensorwert mit dem Namen „sensor.jan_weight“. Leider überträgt meine Waage ab und zu einen Nullwert in HA.

Diese Werte würde ich gerne löschen.

Um mir die Werte anzeigen zu lassen habe ich folgende SQL-Abfrage ausgeführt:

SELECT states.* 
FROM states LEFT JOIN states_meta ON states.metadata_id=states_meta.metadata_id 
WHERE states_meta.entity_id = 'sensor.jan_weight' AND state < 50

Mit dieser Abfrage erhalte ich die Einträge mit dem Null Wert und alle Werte unter 50Kg.
Diese Werte sollen gelöscht warden. Dafür habe ich folgendes SQL-Script geschrieben. Der Wert “metadata_id” = 9922 entspricht dem Sensor “sensor.jan_weight“:

DELETE 
FROM states 
WHERE metadata_id = 9922 and state < 50

Damit erhalte ich folgende Fehlermedung:

SQL-Fehler (1451): Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`)) */

Ich habe soweit verstanden, dass das Problem mit verknüpften Tabellen zusammenhängt.
Die Informationen die ich bisher darüber in Google gefunden haben mir nicht weitergeholfen. Entweder verstehe ich die Lösung nicht oder die Lösung passt nicht zu meiner Anforderung nur einzelne Werte zu löschen.

Gibt es einen anderen Weg einzelne Sensor-Daten aus HA (idealerweise per Script) zu löschen?

2

Hallo JanNi,

auf die Gefahr hin, daß sich das Problem in der Zwischenzeit erledigt hat, trotzdem eine kurze Antwort, weil ich die gleiche Thematik auch gerade hatte:

Das Problem ist hier, daß innerhalb der Tabelle eine Zeile eine andere Zeile referenziert. (Die Zeile wird durch die “state_id” definiert, es existiert aber eine andere Zeile, die mittels der Spalte “old_state_id” auf die erstgenannte Zeile verweist.)

Du kannst also immer nur die Zeile mit der höchsten state_id löschen, da es keine Zeile geben kann, die darauf referenziert, und somit kein Constraint verletzt wird.

select max(state_id) from homeassistant.states s where s.metadata_id = [Deine metadata_id]

oder

select state_id from homeassistant.states s where s.metadata_id = [Deine metadata_id] order by s.state_id desc;

Für Deine Fragestellung würde ich den Constraint löschen

ALTER TABLE states DROP FOREIGN KEY states_ibfk_2;

…dann die fehlerhaften Zeilen löschen und die Zeile zuvor und danach wieder referenzieren (mit der oben beschriebenen Logik)

…und dann den Constraint wieder erstellen, der ist sicherlich nicht ohne Grund da:

ALTER TABLE homeassistant.states ADD CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id) ON DELETE RESTRICT;

Je nachdem welchen Wert Du bei purge_keep_days im Recorder eingestellt hast, fliegen die Werte früher oder später aus der states-Tabelle und wandern in die statistics bzw. statistics_short_term Tabelle(n). Achtung! diese Tabellen nutzen abweichende metadata_ids! (Vgl. Tabelle statistics_meta)

Hier existieren die Constraints nicht, daher kannst Du die Werte ohne Fehler löschen. Aber hier werden min-, max- und Durchschnittswerte gebildet, die durch Deine “Ausreißer” natürlich auch verfälscht werden. Die würde ich aus Bequemlichkeit entweder löschen oder den Vor- bzw. Folgewerten angleichen.

Viele Grüße