Repository navigation
Cannot insert INSERT INTO ts_number #304
Description
Ich schreibe per javascript Temperaturdaten alle 5 Minuten in in einen Datenpunkt. Dieser Datenpunkt wir über den SQL Adapter in eine postgresql Datenbank geschrieben. Die Einstellungen für das Logging ist dem ersten Screenshot zu entnehmen. Ab un zu kommt es im Logfile zu folgender Fehlermeldung:
2023-04-10 07:01:53.188 - error: sql.0 (32475) Cannot insert INSERT INTO ts_number (id, ts, val, ack, _from, q) VALUES (4, 1681102502871, 54.7, true, 2, 0),(4, 1681102802872, 54.7, true, 2, 0),(4, 1681102502853, 54.7, true, 3, 0),(4, 1681102802872, 54.9, true, 3, 0);: error: doppelter Schlüsselwert verletzt Unique-Constraint »ts_number_pkey« (id: T_SpeicherUnten)
Diese Fehlermeldung tritt auf, da zwei Datensätze mit identischem Primary Key, bestehend aus id und ts geschrieben werden sollen, jedoch aus unterschiedlichen Quellen. Auch in den Verlaufsdaten des Datenpunktes (Screenshot 2) sind die gleichen Daten aus dem Datenpunk doppelt vorhanden, einmal mit der Quelle sql.0 adapter und einmal mit Quelle javascript.0 adapter. In der Regel haben die Daten aus den beiden Quellen unterschiedliche ts und somit können diese ohne Fehler in der SQL Datenbank gespeichert werden.
Ist es möglich die Daten durch geeignete Parameterwahl nur einer ausgewählten Quelle in der Datenbank zu speichern? Gibt es andere Möglichkeiten, den Fehler oben zu vermeiden?
Screenshots & Logfiles
Versions:
- Adapter version: v2.2.0
- JS-Controller version: 4.0.24
- Node version: v16.19.0
- Operating system: rasbian bullseye
Activity
Apollon77 commentedon Apr 13, 2023
ja muss man anschauen und fixen/aussortieren. Die interessante Frage an sich ist warum es bei dir passiert?
Itchy-TM commentedon May 6, 2023
Ich habe einen ähnlichen Fehler
Cannot insert INSERT INTO
iob.ts_number (id, ts, val, ack, _from, q) VALUES (516, 1683334519950, 880, 1, 7, 0);: Error: Duplicate entry '516-1683334519950' for key 'PRIMARY' (id: 0_userdata.0.Verbrauch.Allgemein.Harvest.ErzeugungPV+BWR)Aus irgendeinem Grund verknüpft der die Werte von id und ts und versucht das als einen Wert zu übergeben?
rapunzel72 commentedon May 6, 2023
Aus der Datenpunkt id und dem Timestamp ts wird der Primary key für die SQL datenbank gebildet. Da es zwei Quellen für den Datenpunkt gibt - SQL und Javascript, die mit dem gleichen ts versehen werden, kommt es zu der obigen Fehlermeldung. Da es eine Abhängigkeit zwischen den Quellen geben muss, zuerst tritt der javascript Datenpunkt auf und dann der SQL Datenpunkt, daß diese unterschiedliche ts bekommen. Scheinbar ist der Ablauf so schnell, daß beide den gleichen ts bekommen!
Apollon77 commentedon May 6, 2023
@Itchy-TM Hier wäre das Script interessant und ggf auch die Datenpunkt Einstellungen
Erwin40 commentedon Sep 26, 2024
Ich habe übrigens selbiges Problem.
Ich versuche vorher zwar zu löschen, aber das greift leider nicht immer.
GitTomahawk commentedon Nov 1, 2024
Ich würde generell "INSERT IGNORE" für inserts bevorzugen.
Dann wird zwar ein Warning geloggt, aber zulässige Queries werden trotzdem ausgeführt.
Ich habe aktuell dass Problem dasss das Converterscript history->sql fast nur mit Fehlermeldungen beschäftigt ist.
sebilm commentedon Dec 22, 2024
Ich bekomme den Fehler immer mehrfach (für mehrere Datenpunkte) beim Start des Adapters.
GitTomahawk commentedon Dec 23, 2024
Ich habe sämtliche Fehler die durch doppelte ID verursacht werden bei mir (mit IGNORE) beheben können: Pull Request #407
Das löst zwar nicht die Ursache, weshalb in manchen fällen Duplikate zum Speichern an den Adapter übergeben werden - durch IGNORE stellt es allerdings für die DB kein Problem mehr dar.
guitardoc commentedon Mar 31, 2025
Ich habe diesen Fehler auch und bin etwas ratlos was ich machen kann - was bedeutet das mit IGNORE? Wo muss/kann/soll ich da was einstellen?
GitTomahawk commentedon Apr 2, 2025
Sieh dir mal die Änderungen an die ich in Pull Request #407 eingestellt habe.
Sind nur ein paar Zeilen, die du in der mysql.js ändern musst.
Das IGNORE überspringt einfach nur das Hinzufügen eines Wertes wenn es für diesen Sensor zu diesem Timestamp schon einen Eintrag gibt.
GermanBluefox commentedon Oct 3, 2026
Fixed, and now covered by a test that runs against a real MariaDB.
@rapunzel72's diagnosis was exactly right: the same datapoint is logged from two sources, they land on the same millisecond, and both rows end up in one INSERT.
(id, ts)is the primary key, so the whole batch aborts.The adapter now suppresses that conflict:
ON DUPLICATE KEY UPDATE id=idON CONFLICT DO NOTHINGON CONFLICT DO NOTHINGinit()creates a plain non-unique index there, not a primary key, so no conflict can ariseRunning the batch from this report against MariaDB 10.11.14 stores 3 of the 4 rows without error, the first value for a timestamp winning. Removing the guard from the same statement reproduces the original failure exactly:
ER_DUP_ENTRY: Duplicate entry '4-1681102802872' for key 'PRIMARY'. Both are now tests (#574), together with a case asserting that a genuinely broken statement still raisesER_BAD_FIELD_ERROR.@GitTomahawk — that last case is why the guard is
ON DUPLICATE KEY UPDATE id=idrather than theINSERT IGNOREyou proposed in #407.id=idis a no-op assignment, so it suppresses the uniqueness conflict and nothing else;INSERT IGNOREwould also turn truncation and conversion errors into silent warnings, so a genuinely bad value would be dropped without anyone noticing. Your analysis of the cause was correct, and the fix that landed is the narrower form of it. Thank you for chasing it down.@Apollon77 asked back in 2023 why it happens at all: two writers for one state with no coordination between them, so nothing stops them from producing the same timestamp. That is not something the adapter can prevent — it can only avoid losing the rest of the batch over it, which it now does.
Note that #294 is a different bug despite the comment there — two statements in one query, a syntax error rather than a key violation. Also fixed, closed separately.
Closing.
Merge pull request #574 from ioBroker/fix/duplicate-key-guard-tests
Lock in the duplicate (id, ts) guard (ioBroker#304)