MySQL Datenbank doppelte Einträge entfernen

[omega235]

Hallo,

meine SQL Datenbank war mitlerweile extrem Aufgebläht aus zwei Gründen:

1. Die SQL Einstellungen für Datenpunkte waren falsch konfiguriert (z.B. jede Änderung schreiben)

2. Der SQL-Adapter filter teilweise doppelte Einträge nicht zuverlässig raus, auch wenn eine Delta-Prüfung aktiviert ist (anderst kann ich mir viele Einträge in der DB nicht erklären.)

meine ts_number hatte mitlerweile ~ 60.000.000 Einträge und eine Filterung nur auf ts dauerte ohne zusätzliche indexe (default) dauerte rund 70 Sekunden.

Mit diesem Skript konnte ich die Datenmenge auf ~22.000.000 verringern ohne dass es für mich einen Datenverlust darstellt (kann bei anderen anderst sein)

dafür habe ich mir dieses Skript geschrieben das versucht die DB-Einträge etwas auszumisten

Vorraussetzung:
SQL-Adapter mit MySQL Datenbank

das ist die erste Version, bitte vorerst nur die ausführen, die wissen was sie tun und wie sie die Daten zurück bekommen falls was schief läuft
bitte sehr Vorsichtig sein hier werden Daten aus eurer Datenbank gelöscht.

Ich hoffe ich kann jemandem damit helfen

// Hier die Einstellungen vornehmen
// Version: Omega235_2020_12_14_0.1

//#############
// zum testen
//#############
let realDelete = false;

// ##############
// Welche Tabellen schon betrachtet werden. 
// Möglich :  [ 'ts_bool','ts_number', 'ts_string']
// ts_number am sinnvollsten, ts_string bzw ts_bool nur bedingt
// z.B. in ts_bool gibt es einen Taster-Datenpunkt der nur den Value 'true' kennen, also wird in die DB bei jedem Klick ein true eingefügt, dieses Skript löscht dann die TastenClicks aus der DB weil es keine Veränderung sieht
// z.B. in ts_string könnte es ein Sprachausgabe-Datenpunkt, der immer den aktuellen wert als Sprache ausgibt, wenn er nun zwei mal "ein Fenster is offen" ausgibt und der DP zwischenzeitlich nicht geleert wird, geht eine Info über die Sprachausgabe verloren
//############
let tablenames =['ts_number']// [ 'ts_bool','ts_number', 'ts_string']; //,


//###########
// Die Object-ID die angibt ob das Script läuft
// (wird verwendet um Skript anzuhalten wenn pause Button gedrückt wird)
//###########
let ObjectID_JS_Script_Enabled = 'javascript.1.scriptEnabled.Aufräumen_Überprüfen.DB_Clean_Duplicate'

// ###############
// Wenn zwei Datenpunkte mindestens so weit auseinander liegen werden sie nicht gelöscht, auch wenn sie gleich sind
// das kann notwendig sein, damit z.B. Flot jeden Tag/Stunde/... einen Wert sieht und diesen wert auch Anzeigt. Ansonsten würde wenn sich z.B. eine Temperatur nie ändert und das Skript alle Werte löscht, in Flot keine Linie mehr angezeigt.
// ############
let DuplicateEntryAllowedAfterSeconds  =23 * 60 * 60 // Nach 59 Minuten darf ein Doppelter State eingetragen sein


// Ende Einstellungen






let gesamt = 0;
let DuplicateEntryAllowedAfterMilliSeconds = DuplicateEntryAllowedAfterSeconds * 1000
await DB_Clean_Duplicate();




async function DB_Clean_Duplicate() {
    try {
        // alle Datenpunkte aus Db holen
        let all_Datapoints = await getQueryResultAsync(`SELECT * FROM iobroker.datapoints`);
        if (all_Datapoints) {
 
            // Datenpunkte durchlaufen
            for (var tableIndex in tablenames ) {
                for (const datapoint of all_Datapoints) {
                        if (!getState(ObjectID_JS_Script_Enabled).val) {
                            console.warn("Verarbeitung beendet durch Script gestoppt, bisher gelöscht/gefunden: " + gesamt);
                            return;
                        }
                       await AnalyseAndCleanOneDP(datapoint.id, datapoint.name, tablenames[tableIndex])

                }

            }
        }
    } catch (err) {
        console.error(`[DBClean] error: ${err.message}`);
        console.error(`[DBClean] stack: ${err.stack}`);
    }
    console.log(`FERTIG ${gesamt}`);

}

async function AnalyseAndCleanOneDP(DP_ID, DP_Name, tablename) {
    
    let historyEntrys = await getQueryResultAsync(`SELECT * FROM iobroker.${tablename} WHERE id = ${DP_ID} order by ts`);
    if (historyEntrys.length > 1)
    {
        var ts_to_delete = []
        console.debug(`${historyEntrys.length} history-Entrys werden überprüft: ${DP_Name}(${DP_ID}) ${tablename}`);
        let lastHistoryEntry = undefined;

        
        for (const oneHistoryEntry of historyEntrys)
        {

            if (lastHistoryEntry !== undefined && 
                lastHistoryEntry.val === oneHistoryEntry.val  &&
                lastHistoryEntry.ack === oneHistoryEntry.ack &&
                lastHistoryEntry._from === oneHistoryEntry._from &&
                (oneHistoryEntry.ts - lastHistoryEntry.ts)  < DuplicateEntryAllowedAfterMilliSeconds )
            {
                ts_to_delete.push(oneHistoryEntry.ts)
            }
            else 
            {
                lastHistoryEntry = oneHistoryEntry;
            }
        
        }
        if (ts_to_delete.length > 0){
            gesamt += ts_to_delete.length
            if (realDelete){
                console.log(`${ts_to_delete.length} werden nun gelöscht: ${DP_Name}(${DP_ID}) ${tablename}`);
                await getQueryResultAsync(`delete FROM iobroker.${tablename} WHERE id = ${DP_ID} and ts in (${ts_to_delete.join(',')})`);
                console.warn(`${ts_to_delete.length} wurden gelöscht: ${DP_Name}(${DP_ID}) ${tablename}`);

            }
            else
            {
                console.warn(`${ts_to_delete.length} sollten gelöscht werden: ${DP_Name}(${DP_ID}) ${tablename}`);
                console.debug(`delete FROM iobroker.${tablename} WHERE id = ${DP_ID} and ts in (${ts_to_delete.join(',')})`);

            }
        }

    
    }

}

 
async function getQueryResultAsync(query) {
    return new Promise((resolve, reject) => {
        sendTo('sql.0', 'query', query, function (result) {
            if (!result.error) {
                resolve(result.result);
            } else {
                
                console.error(result.error);
            }
        });
    });
}

[OliverIO]

@omega235
wie lange läuft den das Skript bei 20mio Datensätze?
Optimierter läuft wahrscheinlich delete mit subselect nach deinen kriterien direkt auf der mysql datenbank.
auf mysql kann man vor der aktion dann von den tabellen backup copys machen um diese bei Fehlern wieder zurückzusetzen.

[omega235]

@OliverIO

genau sagen kann ichs nich, da ich verschiedene Ansätze hatte, und dadurch schon viel gelöscht war.

Dieser lief sehr schnell. Für die letzten 1,5 Millionen Deletes waren es rund 20 minuten. (vorherige Läufe waren teils 6h beschäftigt und haben nur ne Million geschafft.)