Zurücksetzen einzelner Datenpunkte im SQL-Adapter
-
@metaxa was du mit der 83 meinst weis ich nicht - es sind 83 Datenpunkte ohne Fahrschein.
Das dein Export 1,2GB groß ist heißt auch nicht viel - die SQL Datei ist eine Textdatei welche die Anweisungen zum Import enthält. Die ist dadurch schon mal größer als die eigentlichen Daten.
Allerdings sehe ich da Datenpunkte vom Typ String - das könnten Platzfresser sein wenn diese viele lange Einträge haben.javascript.0 2023-02-02 23:33:12.902 warn script.js.010_Testen.SQL_Datenpunkte_löschen: DB id: 102 (type: 1) -> number: 0, string: 100, bool: 0 | ioBroker id: 'device-reminder.0.Dishwasher.messageDP' javascript.0 2023-02-02 23:33:12.804 warn script.js.010_Testen.SQL_Datenpunkte_löschen: DB id: 100 (type: 0) -> number: 100, string: 0, bool: 0 | ioBroker id: 'sonoff.0.Kueche-LED-Power-32.ENERGY_Yesterday'So, die beiden Datenpunkte dort nehme ich als Beispiel.
Du musst auf die Shell / in die Bash / per puTTY auf den ioBroker.Dort musst du die Eingabeaufforderung von MySQL starten:
mysql -uandreas -p ioBroker-uandreas= Benutzernameandreas
-p= du willst ein Passwort eingeben
ioBroker= Name der Datenbank mit der du dich verbinden willst.
Er fragt nach dem Passwort, danach hast du einen Prompt:Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 64731 Server version: 10.6.11-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [iobroker]>Bei mir steht MariaDB, bei dir eventuell auch oder MySQL, die funktionieren beide gleich.
[iobroker]heisst das die Datenbank ausgewählt ist.Kleiner Test:
MariaDB [iobroker]> show tables; +--------------------+ | Tables_in_iobroker | +--------------------+ | datapoints | | sources | | ts_bool | | ts_counter | | ts_number | | ts_string | +--------------------+ 6 rows in set (0,001 sec)Das sind die Tabellen in denen die Daten liegen. Aus denen können wir die Daten mit den obigen Informationen aus dem Log löschen:
javascript.0 2023-02-02 23:33:12.902 warn script.js.010_Testen.SQL_Datenpunkte_löschen: DB id: 102 (type: 1) -> number: 0, string: 100, bool: 0 | ioBroker id: 'device-reminder.0.Dishwasher.messageDP'device-reminder.0.Dishwasher.messageDPhat in der Datenbank die id 102 (DB id: 102 (type: 1))
Und Daten in derts_stringTabelle (number: 0, string: 100, bool: 0)kurze Kontrolle:
MariaDB [iobroker]> select * from datapoints where id=102; +-----+----------------------------------------+------+ | id | name | type | +-----+----------------------------------------+------+ | 102 | device-reminder.0.Dishwasher.messageDP | 1 | +-----+----------------------------------------+------+ 1 row in set (0,001 sec)Ok, stimmt, also löschen der Werte aus
ts_stringund ausdatapoints:delete from ts_bool where id=102; delete from datapoints where id=102;Beim ersten Befehl sollte in der Zusammenfassung stehen wie viele Datensätze er gelöscht hat,
beim 2. Befehl sollte es immer 1 (einer) sein weil es ja nur einen Eintrag gibt.Der erste Befehl ist es der dein System in die Knie zwingen kann wenn er dort viel löschen muss.
Dazu komme ich weiter unten noch einmal.javascript.0 2023-02-02 23:33:12.804 warn script.js.010_Testen.SQL_Datenpunkte_löschen: DB id: 100 (type: 0) -> number: 100, string: 0, bool: 0 | ioBroker id: 'sonoff.0.Kueche-LED-Power-32.ENERGY_Yesterday'sonoff.0.Kueche-LED-Power-32.ENERGY_Yesterdayhat in der Datenbank die id 100 (DB id: 100 (type: 01))
Und Daten in derts_numberTabelle (number: 100, string: 0, bool: 0)Beim löschen eben die andere Tabelle:
delete from ts_number where id=100; delete from datapoints where id=100;und bei
boolwäre es ebents_bool
Wenn du mit allen fertig bist kannst du perexitdie Konsole wieder verlassen.So, nun zu dem "in die Knie gehen".
Variante 1 ist den ioBroker vorher zu stoppensudo systemctl stop iobrokerDann das löschen machen. Wenn es länger dauert, dauert es eben länger - Abwarten bis der Prompt wiederkommt. Das wird meist recht flott gehen - aber du wirst da ein oder mehrere Monster haben wo es dauern wird.
Variante 2 ist das löschen zu limitieren:
delete from ts_number where id=100 limit 5000;würde nur die ersten 5.000 Datensätze löschen. Dabei wird das System / ein oder mehrere CPU-Kerne kurz auf Anschlag gehen und der Prompt kommt dann wieder. Als Ergebnis sollte da so etwas stehen:
5000 row in set (4,321 sec)Ist die Zahl kleiner als dein Limit oder sogar 0 hast du alles gelöscht in der Tabelle. Ansonsten musst du das wiederholen.
Ob 5000 oder 50 so der 100.000 die richtige Zahl für dein System ist kann ich dir nicht sagen. Vermutlich kannst du bei
ts_numbereher höher greifen, beits_stringerst einmal niedriger.
Meine Geräte die einmal die Minute liefern mit 6 Monate Aufzeichnung haben 42.000 Datensätze.
Einer der Temperaturfühler hat alle 5 Sekunden gemeldet ... 280.000
der Shelly 3EM liefert alle 3 Sekunden und hat bei mir 2.673.105 Datensätze - pro Phase.Beim Abfragen des letzteren ging bei mir die CPU für einen Moment auf 100%. (aber nicht auf allen Kernen)
Wie viele es sind könntest du mit dem Befehl hier testen, der ging schon schneller:MariaDB [iobroker]> select COUNT(*) from ts_number where id=58; +----------+ | COUNT(*) | +----------+ | 2673128 | +----------+ 1 row in set (3,609 sec)Halt 4 Sekunden vollgas. Wenn ich die Abfrage wie im Skript mache (
select COUNT(*) from ts_number where id=58;) dann sind es schon ca. 11 Sekunden, also 3 mal so "schlimm" -
@bananajoe sagte in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
Wahnsinn der und vor allem dein Aufwand!
Ich hatte nach einer Lösung direkt auf meiner Syno im Web-UI gesucht. iO für paar Stunden lahm zu legen macht Stress zu Hause.
1 MyAdmin öffnen und die gewünschte DB auswählen
2 Rechts den Reiter SQL wählen
3 Hier kommt dann deine Arbeit nach deinem Skript voll zum Tragen!
@bananajoe sagte in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
delete from ts_number where id=100; delete from datapoints where id=100;4 Trockentraining
5 Ergebnis
6 Wenn das Ergebnis plausibel erscheint, anstelle simulieren, rechts mit "OK" löschen.
-
Erst noch mal vielen Dank für dein Engagement!
Ich hab das Zählen bzw. Ermitteln optimiert, ich hoffe es ist OK, wenn ich in dem Script Änderungen mache. Ich lade jetzt zum Zählen nicht mehr alle Records, sondern nutze count(*) zum Zählen. Dann muss ioBroker intern keine riesigen Dictionaries aufbauen. Bei mir dauert das Ermitteln so nur noch ein paar Sekunden und es scheint zu stimmen.
// SQL_Delete_Dead_Objects // V0.5 // // Changelog: // ---------- // 10.08.2019 V0.1 Scrounger erste Version welche nicht mehr existierende Datenpunkte anzeigt https://forum.iobroker.net/post/289821 // 03.05.2022 V0.2 andi2055 erweitert das die Daten auch aus der Datenbank gelöscht werden https://forum.iobroker.net/post/798291 // 01.02.2023 V0.3 BananaJoe umgestellt das nun Datenpunkte ohne SQL-Protokollierung gefunden werden https://forum.iobroker.net/post/939101 // 02.02.2023 V0.4 BananaJoe SQL-Instanz Konfigurierbar / Datenbankname konfigurierbar // 03.02.2023 V0.5 Gaspode Speicherverbrauch beim Zählen optimiert durch Verwendung von count(*) // Einstellungen: // Hier das löschen einschalten! false = nur anzeigen / true = anzeigen und wirklich löschen const activate_delete = false; // Name der SQL-Instanz, normalerweiser sql.0 const sql_instance = 'sql.0'; // Name der SQL-Datenbank - Groß und Kleinschreibung beachten! const sql_dbname = 'iobroker'; // Funktion zum Suchen und Löschen async function wartung() { try { // Alle Datenpunkte holen bei welchen die SQL-Protokollierung aktiv ist: let datapointsActiveArray = new Array(); let datapointsActiceStrings = ""; await sendTo(sql_instance, 'getEnabledDPs', {}, function (result) { Object.entries(result).forEach((entry) => { const [key, value] = entry; //console.log(`${key}`); datapointsActiceStrings = datapointsActiceStrings + `${key}` + ";"; }); //console.log(datapointsActiceStrings); datapointsActiveArray = datapointsActiceStrings.split(';'); }); // alle Datenpunkte aus Db holen let datapoints = await getQueryResult(`SELECT name, id, type FROM ${sql_dbname}.datapoints`); if (datapoints) { let count = 0; let sum = 0; // Datenpunkte durchlaufen for (const datapoint of datapoints) { // prüfen ob kein Objekt in ioBroker existiert // if (!getObject(datapoint.name)) { // prüfen ob SQL-Protokollierung nicht aktiv ist if (!datapointsActiveArray.includes(datapoint.name)) { count++; // Daten des Datenpunktes in Tabelle 'ts_bool' zählen let countBool = 0; let booleanTableItems = await getQueryResult(`SELECT count(*) as cnt FROM ${sql_dbname}.ts_bool WHERE id = ${datapoint.id}`); if (booleanTableItems && booleanTableItems[0].cnt > 0) { countBool = booleanTableItems[0].cnt; if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.ts_bool WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } } // Daten des Datenpunktes in Tabelle 'ts_number' zählen let countNumber = 0; let numberTableItems = await getQueryResult(`SELECT count(*) as cnt FROM ${sql_dbname}.ts_number WHERE id = ${datapoint.id}`); if (numberTableItems && numberTableItems[0].cnt > 0) { countNumber = numberTableItems[0].cnt; if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.ts_number WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } } // Daten des Datenpunktes in Tabelle 'ts_string' zählen let countString = 0; let stringTableItems = await getQueryResult(`SELECT count(*) as cnt FROM ${sql_dbname}.ts_string WHERE id = ${datapoint.id}`); if (stringTableItems && stringTableItems[0].cnt > 0) { countString = stringTableItems[0].cnt; if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.ts_string WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } } if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.datapoints WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } console.warn(`DB id: ${datapoint.id} (type: ${datapoint.type}) -> number: ${countNumber}, string: ${countString}, bool: ${countBool} | ioBroker id: '${datapoint.name}'`); sum = sum + countBool + countNumber + countString; } } console.warn(`${count} Objects found, that not exist anymore in ioBroker, sum of items in tables: ${sum.toLocaleString().replace(/,/g, ".")}`); } } catch (err) { console.error(`[wartung] error: ${err.message}`); console.error(`[wartung] stack: ${err.stack}`); } } async function getQueryResult(query) { return new Promise((resolve, reject) => { sendTo(sql_instance, 'query', query, function (result) { if (!result.error) { resolve(result.result); } else { resolve(null); } }); }); } wartung();Jetzt wäre es noch toll, wenn man einbeziehen könnte, ob für ein State ein Alias vergeben wurde.
-
@gaspode Cool!
Das Trockentraining dauert bei mir ca. 3 Minuten. Löschen traue ich mich nicht wirklich aus dem iO heraus.@BananaJoe & @Gaspode
Wäre es möglich am Ende des Trockendurchlaufs eine Liste auszugeben, z.B.:delete from ts_number where id=10; delete from datapoints where id=10; delete from ts_number where id=11; delete from datapoints where id=11; delete from ts_string where id=14; delete from datapoints where id=14; delete from ts_bool where id=17; delete from datapoints where id=17;LG, mxa
-
@metaxa said in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
Das Trockentraining dauert bei mir ca. 3 Minuten. Löschen traue ich mich nicht wirklich aus dem iO heraus.
Das Löschen wird durch meine Änderung auch nicht beeinflusst.
-
@gaspode Cool. Hatte ich auch schon dran gedacht, war mir dann aber zeitlich schon zu spät.
Wir so von mir übernommen -
@Gaspode
Wie meinst du das?
Deine coole Änderung bezieht sich nur auf den Trockendurchlauf? -
@metaxa sagte in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
@Gaspode
Wie meinst du das?
Deine coole Änderung bezieht sich nur auf den Trockendurchlauf?Das Problem beim löschen bleibt, nur das ermitteln der Anzahl der zu löschenden Werte ist optimiert worden
-
@metaxa said in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
Deine coole Änderung bezieht sich nur auf den Trockendurchlauf?
@bananajoe said in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
Das Problem beim löschen bleibt, nur das ermitteln der Anzahl der zu löschenden Werte ist optimiert worden
Exakt.
-
@bananajoe
ah ok! Umsomehr wäre dann am Ende des TRockendurchlaufs eine kopierbare Liste für mich der Hit! Die könnte ich dann hier
einfügen und ausführen. Aus Sicherheitsgründen kleinere Stücke.Ist aber händisch mit dem io-Log auch kein großartiges Problem, bin schon mitten drinnen.
Bei manchen Datenpunkten mit vielen Einträgen in der DB muss ich beim Löschen limitieren:
delete from ts_number where id=156 limit 500000; delete from datapoints where id=156;DANKE für Eure Arbeit und Überlegungen!
-
Hallo,
noch zwei Ergänzungen:
- Wenn ein Alias für einen Datenpunkt vergeben ist, wird das berücksichtigt
- Am Ende eines Trockenlaufs werden SQL Delete Statements zum manuellen Löschen ausgegeben
Dabei habe ich noch einen kleinen Fehler am Anfang korrigiert: sendTo kann nicht mit await verwendet werden, dafür gibt es inzwischen die Funktion sendToAsync. Da diese leider im Moment noch einen Fehler hat, hab ich dafür eine Ersatzfunktion eingefügt. Ein GitHub Issue dafür gibt es schon und der Fehler wird in der nächsten Version des Javascript Adapters korrigiert sein.
In der Praxis hat das Verwenden von sentTo wohl keine Probleme gemacht, da der erste SQL Befehl lange genug dauerte, sodass die Liste rechtzeitig gefüllt werden konnte.// SQL_Delete_Dead_Objects // V0.5 // // Changelog: // ---------- // 10.08.2019 V0.1 Scrounger erste Version welche nicht mehr existierende Datenpunkte anzeigt https://forum.iobroker.net/post/289821 // 03.05.2022 V0.2 andi2055 erweitert das die Daten auch aus der Datenbank gelöscht werden https://forum.iobroker.net/post/798291 // 01.02.2023 V0.3 BananaJoe umgestellt das nun Datenpunkte ohne SQL-Protokollierung gefunden werden https://forum.iobroker.net/post/939101 // 02.02.2023 V0.4 BananaJoe SQL-Instanz Konfigurierbar / Datenbankname konfigurierbar // 03.02.2023 V0.5 Gaspode Speicherverbrauch beim Zählen optimiert durch Verwendung von count(*) // 03.02.2023 V0.5 Gaspode Wenn für einen Datenpunkt ein Alias vergeben ist, wird das // anstelle des Datenpunktnamens verwendet. // 03.02.2023 V0.5 Gaspode Am Ende eines Trockenlaufs werden SQL Befehle zum manuellen Löschen // ausgegeben. // Einstellungen: // Hier das löschen einschalten! false = nur anzeigen / true = anzeigen und wirklich löschen const activate_delete = false; // Name der SQL-Instanz, normalerweiser sql.0 const sql_instance = 'sql.0'; // Name der SQL-Datenbank - Groß und Kleinschreibung beachten! const sql_dbname = 'iobroker'; // Ersatzfunktion, weil sendToAsync im Scripting-Adapter derzeit (6.1.4) buggy ist: async function mySendToAsync(_adapter, cmd, msg) { return new Promise((resolve, reject) => { sendTo(_adapter, cmd, msg, res => { if (!res || res.error) { reject(res ? res.error : new Error('Unknown error')); } else { resolve(res); } }); }); } // Funktion zum Suchen und Löschen async function wartung() { try { let sqlDeleteStatements = new Array(); // Alle Datenpunkte holen bei welchen die SQL-Protokollierung aktiv ist: let datapointsActiveArray = new Array(); const result = await mySendToAsync(sql_instance, 'getEnabledDPs', {}); for (const i in result) { datapointsActiveArray.push(result[i].aliasId.length == 0 ? i : result[i].aliasId); } // alle Datenpunkte aus Db holen let datapoints = await getQueryResult(`SELECT name, id, type FROM ${sql_dbname}.datapoints`); if (datapoints) { let count = 0; let sum = 0; // Datenpunkte durchlaufen for (const datapoint of datapoints) { // prüfen ob kein Objekt in ioBroker existiert // if (!getObject(datapoint.name)) { // prüfen ob SQL-Protokollierung nicht aktiv ist if (!datapointsActiveArray.includes(datapoint.name)) { count++; // Daten des Datenpunktes in Tabelle 'ts_bool' zählen let countBool = 0; let booleanTableItems = await getQueryResult(`SELECT count(*) as cnt FROM ${sql_dbname}.ts_bool WHERE id = ${datapoint.id}`); if (booleanTableItems && booleanTableItems[0].cnt > 0) { countBool = booleanTableItems[0].cnt; if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.ts_bool WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } else { sqlDeleteStatements.push(`DELETE FROM ${sql_dbname}.ts_bool WHERE id = ${datapoint.id};`); } } // Daten des Datenpunktes in Tabelle 'ts_number' zählen let countNumber = 0; let numberTableItems = await getQueryResult(`SELECT count(*) as cnt FROM ${sql_dbname}.ts_number WHERE id = ${datapoint.id}`); if (numberTableItems && numberTableItems[0].cnt > 0) { countNumber = numberTableItems[0].cnt; if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.ts_number WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } else { sqlDeleteStatements.push(`DELETE FROM ${sql_dbname}.ts_number WHERE id = ${datapoint.id};`); } } // Daten des Datenpunktes in Tabelle 'ts_string' zählen let countString = 0; let stringTableItems = await getQueryResult(`SELECT count(*) as cnt FROM ${sql_dbname}.ts_string WHERE id = ${datapoint.id}`); if (stringTableItems && stringTableItems[0].cnt > 0) { countString = stringTableItems[0].cnt; if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.ts_string WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } else { sqlDeleteStatements.push(`DELETE FROM ${sql_dbname}.ts_string WHERE id = ${datapoint.id};`); } } if (activate_delete) { sendTo(sql_instance, 'query', `DELETE FROM ${sql_dbname}.datapoints WHERE id = ${datapoint.id}`, function (result) { if (result.error) { console.error(result.error); } else { // show result console.log('Rows: ' + JSON.stringify(result.result)); } }); } else { sqlDeleteStatements.push(`DELETE FROM ${sql_dbname}.datapoints WHERE id = ${datapoint.id};`); } console.warn(`DB id: ${datapoint.id} (type: ${datapoint.type}) -> number: ${countNumber}, string: ${countString}, bool: ${countBool} | ioBroker id: '${datapoint.name}'`); sum = sum + countBool + countNumber + countString; } } console.warn(`${count} Objects found, that not exist anymore in ioBroker, sum of items in tables: ${sum.toLocaleString().replace(/,/g, ".")}`); if (!activate_delete) { console.log('Löschen war bei diesem Durchlauf nicht aktiviert. Du kannst die Daten mit den folgenden Kommandos manuell in SQL löschen:'); sqlDeleteStatements.forEach(element => console.log(element)); } } } catch (err) { console.error(`[wartung] error: ${err.message}`); console.error(`[wartung] stack: ${err.stack}`); } } async function getQueryResult(query) { return new Promise((resolve, reject) => { sendTo(sql_instance, 'query', query, function (result) { if (!result.error) { resolve(result.result); } else { resolve(null); } }); }); } wartung(); -
Mega!!!!!!!
javascript.0 (2778652) script.js.010_Testen.SQL_3_Datenpunkte_löschen: Löschen war bei diesem Durchlauf nicht aktiviert. Du kannst die Daten mit den folgenden Kommandos manuell in SQL löschen:
Herzlichen Dank!
-
@metaxa said in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
Herzlichen Dank!
Gerne. Und den Tippfehler hab ich auch gleich korrigiert (munuell -> manuell).
-
lief durch - hat gefunden und gelöscht - keine fehler -
-
@liv-in-sky said in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
lief durch - hat gefunden und gelöscht - keine fehler -
Freut mich.
Evtl. könnte man etwas in der Form ja als "Purge" oder so in den SQL Adapter einbauen. Dafür müsste natürlich das Lastproblem beim Löschen gelöst sein und man müsste es gut für andere Datenbank-Typen testen.
-
@gaspode denke, da wäre apollon77 der ansprechpartner
-
@liv-in-sky said in Zurücksetzen einzelner Datenpunkte im SQL-Adapter:
@gaspode denke, da wäre apollon77 der ansprechpartner
Ja denke ich auch. Ich will ihn nur nicht damit belasten, solange das nicht etwas konkreter ist.
-
https://github.com/ioBroker/ioBroker.sql/issues/247
Ihr könnt ja nochmal genau beschreiben was wir möchten (nicht einzeln löschen) und "voten"...
Danke für die Updates!PS: auch mal prüfen ob das hilft/mittlerweile funktioniert (bei mir zeitlich aktuell schwierig)
https://github.com/ioBroker/ioBroker.sql/issues/207 -
Hi zusammen, mal ne Frage.
Gibt es diese Möglichkeit auch für influxdb?Gruß Johnny
-
@gaspode Ich habe gerade bei mir 28 Shelly 1PM umorganisiert und dadurch jede Menge alter, verwaister SQL-Aufzeichnungen gehabt.
Und das Skript hat prima funktioniert - einfach noch mal Danke an dieser Stelle!
