SQL.Adpter: Insert-Befehle innerhalb von 40ms

nettekov

Hallo zusammen,

ich glaube ich habe was gefunden.

Das Problem bei mir, das manch ein Insert Befehl von SQL-Server als Syntax-Error angemahnt wird, ist, wenn mehrere Insert-Befehle
in einer Transaktion gepackt und weggeschickt werden.
Schickt man jeden INSERT-Befehl in einer separaten Transaktion, dann werden Diese dann auch vom SQL-Server akzeptiert, auch wenn ich die Datensätze auf 8 erhöhe.
Wenn zwei oder mehrere Insert-Anweisung in einer Transaktion über das node-Module 'mysql' geschickt werden, dann wird anscheinend (und das ganz vorsichtig gesagt) das Protokoll nicht so eingestellt, das es für mehrere SQL-Statements nicht geeignet ist.(Dies ist aber nur eine Mutmaßung).
Schickt man die selben angemahnten SQL-Sequenzen unverändert über eine anderen SQL-Client, dann wird die komplette SQL-Sequenz auch in einer Transaktion an den SQL-Server geschickt und Diese werden dann akzeptiert.
Bei myphpadmin wird die SQl-Sequenz teilweise gesplittet in mehreren Transaktionen,
Bei Heidi-SQL werden Diese generell in einzelne Transaktionen gesplittet.

Aber nochmal, das habe ich erstmal nur bei mir festgestellt.

ich würde jetzt versuchen, hierfür eine JS-Funktion zu schreiben, damit das Datenloggen bei mir wieder lauffähig wird.
Kann sein , das ich da ggf. Euch nochmal was fragen muss.

Gruss nettekov

Hier die Protokolle:
Angemahntes SQL-Sequenz im IOBroker-Log:

Cannot insert INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (5, 1676135872618, 53.32),(5, 1676138387812, 53),(5, 1676138387812, 53),(5, 1676138670902, 52.95);INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (5, 1676138387812, 53, 1, 2, 0),(5, 1676138670902, 52.95, 1, 2, 0);: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (5, 1676...' at line 1 (id: LUFTFEUCHTE@William_Martin)

Inhalt von SQL_TEST2.js (kopiertes angemahntes SQL-Sequenz vom IOBroker-Protokoll):

ar mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '172.27.110.30',
  user     : 'iobroker',
  password : '*********',
  database : 'iobroker',
  port     : 3307
});


connection.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = " INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (5, 1676135872618, 53.32),(5, 1676138387812, 53),(5, 1676138387812, 53),(5, 1676138670902, 52.95);INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (5, 1676138387812, 53, 1, 2, 0),(5, 1676138670902, 52.95, 1, 2, 0);";
  connection.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

Ergebnis von node SQL_TEST2.js--> Transaktion wird erwartender Weise angemahnt

root@server03:~/test$ node SQL_TEST2.js
Connected!
/home/root/test/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (5, 1676...' at line 1
    at Query.Sequence._packetToError (/home/root/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/root/test/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/root/test/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/root/test/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/root/test/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/root/test/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/root/test/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/root/test/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    --------------------
    at Protocol._enqueue (/home/root/test/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/home/root/test/node_modules/mysql/lib/Connection.js:198:25)
    at Handshake.<anonymous> (/home/root/test/SQL_TEST2.js:15:14)
    at Handshake.<anonymous> (/home/root/test/node_modules/mysql/lib/Connection.js:526:10)
    at Handshake._callback (/home/root/test/node_modules/mysql/lib/Connection.js:488:16)
    at Handshake.Sequence.end (/home/root/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
    at Handshake.Sequence.OkPacket (/home/root/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:92:8)
    at Protocol._parsePacket (/home/root/test/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/root/test/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/root/test/node_modules/mysql/lib/protocol/Parser.js:43:10) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (5, 1676...' at line 1",
  sqlState: '42000',
  index: 0,
  sql: ' INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (5, 1676135872618, 53.32),(5, 1676138387812, 53),(5, 1676138387812, 53),(5, 1676138670902, 52.95);INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (5, 1676138387812, 53, 1, 2, 0),(5, 1676138670902, 52.95, 1, 2, 0);'
}

Inhalt von SQL_TEST5.js (kopiertes angemahntes SQL-Sequenz wird in zwei Transaktionen aufgeteilt):

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '172.27.110.30',
  user     : 'iobroker',
  password : '***************',
  database : 'iobroker',
  port     : 3307
});


connection.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql1 = " INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (5, 1676135872618, 53.32),(5, 1676138387812, 53),(5, 1676138387812, 53),(5, 1676138670902, 52.95)";
  connection.query(sql1, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
    var sql2 = "INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (5, 1676138387812, 53, 1, 2, 0),(5, 1676138670902, 52.95, 1, 2, 0);";
  connection.query(sql2, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

Ergebnis von node SQL_TEST5.js--> Transaktion wird akzeptiert

root@server03:~/test$ vi SQL_TEST5.js
root@server03:~/test$ node SQL_TEST5.js
Connected!
1 record inserted
1 record inserted

Inhalt von SQL_TEST6.js (kopiertes angemahntes SQL-Sequenz wird in zwei Transaktionen aufgeteilt und jeder INSERT-Befehl wurden die Datensätze auf acht erhöht):

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '172.27.110.30',
  user     : 'iobroker',
  password : '*****************',
  database : 'iobroker',
  port     : 3307
});


connection.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql1 = " INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (1235, 1676135872618, 53.32),(1235, 1676138387812, 53),(1235, 1676138387812, 53),(1235, 1676138670902, 52.95),(1236, 1676135872618, 53.32),(1236, 1676138387812, 53),(1236, 1676138387812, 53),(1235, 1676138670902, 52.95);";
  connection.query(sql1, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
    var sql2 = "INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (1235, 1676138387812, 53, 1, 2, 0),(1235, 1676138670902, 52.95, 1, 2, 0),(1236, 1676138387812, 53, 1, 2, 0),(1236, 1676138670902, 52.95, 1, 2, 0),(1237, 1676138387812, 53, 1, 2, 0),(1237, 1676138670902, 52.95, 1, 2, 0),(1238, 1676138387812, 53, 1, 2, 0),(1238, 1676138670902, 52.95, 1, 2, 0);";
  connection.query(sql2, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

Ergebnis von node SQL_TEST6.js--> Transaktionen werden akzeptiert

root@server03:~/test$ vi SQL_TEST6.js
root@server03:~/test$ node SQL_TEST6.js
Connected!
1 record inserted
1 record inserted

Angemahntes SQL-Sequenz im IOBroker-Log:

	Cannot insert INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (47, 1676131245941, 8.04),(47, 1676133519887, 7.82);INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (47, 1676133519887, 7.82, 1, 2, 0),(47, 1676133530714, 7.87, 1, 2, 0);: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (47, 167...' at line 1 (id: TEMPERATUR@AussenHaustuer)

Ergebnis über anderen SQL-Client--> Transaktion wird akzeptiert

nettekov

@OliverIO
Hallo OliverIO,

In der main.js werden mit dieser Funktion die Datenlogs gesendet?

function _insertValueIntoDB(query, id, cb) {
    adapter.log.debug(query);

    borrowClientFromPool((err, client) => {
        if (err) {
            returnClientToPool(client);
            adapter.log.error(err);
            cb && cb(); // BF asked (2021.12.14): may be return here err?
        } else {
            client.execute(query, (err /* , rows, fields */) => {
                returnClientToPool(client);
                if (err) {
                    adapter.log.error(`Cannot insert ${query}: ${err} (id: ${id})`);
                } else {
                    checkRetention(id);
                }
                cb && cb(); // BF asked (2021.12.14): may be return here err?
            });
        }
    });
}

Gruss Nettekov

Marc Berg

@nettekov sagte in SQL.Adpter: Insert-Befehle innerhalb von 40ms:

SQL-Adapter mit zweiter Version v1.16.1:

IOBroker node:

So, wie es aussieht hast du es ja hier mit irgendwelchen Inkompatibilitäten zu tun. Darum verstehe ich nicht, warum nicht erstmal dieser Schiefstand der Adapter-Version sowie ein Update der Node Version angegangen wird?
An deiner Stelle würde ich den Adapter löschen und neu installieren sowie Node.js auf die empfohlene Version (16) bringen.

nettekov

@marc-berg Vielen dank für deine Antwort.

Weiß Du , wofür die V1.16.1 steht.
Bevor ich den SQL-Adapter , deinstalliere und wieder installiere, sollten wir erst wissen, wofür Diese Version steht.

nettekov

Hallo zusammen,

ich habe in die '_insertValueIntoDB' Funktion einfach mal ein Log-Befehl eingebaut, der bei jedem Daten-Loggen das Query anzeigt.

function _insertValueIntoDB(query, id, cb) {
    adapter.log.debug(query);

    borrowClientFromPool((err, client) => {
        if (err) {
            returnClientToPool(client);
            adapter.log.error(err);
            cb && cb(); // BF asked (2021.12.14): may be return here err?
        } else {
            client.execute(query, (err /* , rows, fields */) => {
                returnClientToPool(client);

                adapter.log.error(`Hallo Welt --> ${query}`); // Nettekov
                
               if (err) {
                    adapter.log.error(`Cannot insert ${query}: ${err} (id: ${id})`);
                } else {
                    checkRetention(id);
                }
                cb && cb(); // BF asked (2021.12.14): may be return here err?
            });
        }
    });
}

Wenn man jetzt im Protokollbereich von IOBroker nachschauen, sieht man , da die meine Datenlog' in der SQL-Transaktion nur ein SQl-Statement haben.
Wenn zwei SQL-Statements in einer Transaktion auftauchen, dann kommt auch der Syntax-Error.

Gruss nettekov

haus-automatisierung

@nettekov sagte in SQL.Adpter: Insert-Befehle innerhalb von 40ms:

Wenn zwei SQL-Statements in einer Transaktion auftauchen, dann kommt auch der Syntax-Error.

Eventuell kann @apollon77 was dazu sagen?

Marc Berg

@nettekov sagte in SQL.Adpter: Insert-Befehle innerhalb von 40ms:

@marc-berg Vielen dank für deine Antwort.

Weiß Du , wofür die V1.16.1 steht.
Bevor ich den SQL-Adapter , deinstalliere und wieder installiere, sollten wir erst wissen, wofür Diese Version steht.

Ich weiß es nicht, vielleicht es nur ein Anzeigefehler. Genauso gut kann ein Update unvollständig durchgelaufen sein. Ich finde es aber verdächtig, und eine Neuinstallation eines Adapters ist keine Raketentechnologie.

nettekov

Hallo zusammen,

ich versuche es mit diesen Code-Schnipsel, das Problem provisorisch zu umgehen. Dient nur zu Testzwecken.

                 adapter.log.error(`Hallo Welt --> ${query}`);

         var start=0;
        var ende=0;
        var schleife=true;
        var sub_query=query;
        do{
                start=query.indexOf("INSERT",ende);
                ende=query.indexOf(";INSERT",start);
                if (ende<0){
                        ende=query.length ;
                         schleife=false;
                }
                else{
                        ende++;
                        schleife=true;
                }
                sub_query=query.substring(start,ende);
                client.execute(sub_query, (err /* , rows, fields */) => {
                        if (err) {
                                adapter.log.error(`Cannot insert ${query}: ${err} (id: ${id})`);
                        } else {
                        checkRetention(id);
                        }
                        cb && cb(); // BF asked (2021.12.14): may be return here err?
                });


        }while(schleife);
        returnClientToPool(client);

(Achtung! Kopieren und Einsetzen auf eigene Gefahr! Dies ist nicht mit dem Entwickler abgesprochen und dient nur zur Testzwecken. Ich nehme keinerlei Gewähr!)

Momentan sieht es gut aus, es gibt keine Fehlermeldung mehr, wenn zwei INSERT in einer Transaktion stehen, bzw die Werte werden auch gespeichert.

Gruss nettekov

OliverIO

@nettekov sagte in SQL.Adpter: Insert-Befehle innerhalb von 40ms:

In der main.js werden mit dieser Funktion die Datenlogs gesendet?

sorry für die verspätung, aber ich glaube du hast es selbst schon herausgefunden.
ja, das ist die funktion, welche für die inserts zuständig ist.
mit client.execute wird der befehl ausgeführt und wenn ein fehler kommt, wird die query (so wie es der adapter erzeugt hat) ausgegeben + die db fehlermeldung + die betroffene id

apollon77

@haus-automatisierung Also Wenn da wirklich in einem SQL Statement vom Adapter zwei Queries gesendet werden zusammen dann muss ich schauen warum - weil ich entsinne mich das die SQL Lib das nicht kann .... aber da muss ich reinschauen. Github issue bitte anlegen

haus-automatisierung

@apollon77 Danke!

FYI https://github.com/ioBroker/ioBroker.sql/issues/294

klein0r created this issue in ioBroker/ioBroker.sql

open Adapter adds two insert statements to the same request (leads to an error) #294

nettekov

@apollon77
@haus-automatisierung
Hallo zusammen,

ich wollte mal ein Status nach 3 Tagen abgeben. Wenn ich irgendwie helfen kann, gebt mir bitte Bescheid.

ich bekommen ca 5-20 mal in der Stunde , SQL-Sequenzen mit mehrfach INSERT-Statements von den Aquara Sensoren.
Beispiel aus den Logs:

sql.0			
	15.02.2023 19:54	info	Hallo Welt -->(SUB)adapter.log.error INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (47, 1676485989164, 5.77),(47, 1676486828459, 5.26);INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (47, 1676486828459, 5.26, 1, 2, 0);
sql.0			
	15.02.2023 19:34	info	Hallo Welt -->(SUB)adapter.log.error INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (47, 1676484472310, 6.11),(47, 1676485989164, 5.77);INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (47, 1676485989164, 5.77, 1, 2, 0);
sql.0			
	15.02.2023 19:54	info	Hallo Welt -->(SUB)adapter.log.error INSERT INTO `iobroker`.ts_counter (id, ts, val) VALUES (50, 1676483202748, 19.83),(50, 1676486821479, 19.78),(50, 1676486821479, 19.78),(50, 1676487143779, 19.72);INSERT INTO `iobroker`.ts_number (id, ts, val, ack, _from, q) VALUES (50, 1676486821479, 19.78, 1, 2, 0),(50, 1676487143779, 19.72, 1, 2, 0);
..............

Durch den temporären, eingebauten Codeschnipsel in der '_InsertValueIntoDB' - Funktion werden alle eingehenden mehrfach INSERT-Statement in einzelne INSERT-Sequenz separiert und als einzelne Transaktionen verschickt.
Bis jetzt habe ich keine Fehlermeldung mehr erhalten, bzw. die Daten werden ohne Probleme gespeichert.

Wie gesagt, dies ist nur temporär bis das Problem gefixt ist.
Gruss nettekov

(Achtung! Kopieren und Einsetzen auf eigene Gefahr! Dies ist nicht mit dem Entwickler abgesprochen und dient nur zur Testzwecken. Ich nehme keinerlei Gewähr!)

function _insertValueIntoDB(query, id, cb) {
    adapter.log.debug(query);

    borrowClientFromPool((err, client) => {
        if (err) {
            returnClientToPool(client);
            adapter.log.error(err);
            cb && cb(); // BF asked (2021.12.14): may be return here err?
        } else {
//BEGIN********************************************
                 //adapter.log.info(`Hallo Welt --> ${query}`);

         var start=0;
        var ende=0;
        var schleife=true;
        var sub_query=query;
        do{
                start=query.indexOf("INSERT",ende);
                ende=query.indexOf(";INSERT",start);
                if (ende<0){
                        ende=query.length ;
                         schleife=false;
                }
                else{
                        ende++;
                        schleife=true;
                        adapter.log.info(`Hallo Welt -->(SUB)adapter.log.error ${query}`);

                }
                sub_query=query.substring(start,ende);
                client.execute(sub_query, (err /* , rows, fields */) => {
                        if (err) {
                                adapter.log.error(`Cannot insert ${query}: ${err} (id: ${id})`);
                        } else {
                        checkRetention(id);
                        }
                        cb && cb(); // BF asked (2021.12.14): may be return here err?
                });


        }while(schleife);
        returnClientToPool(client);
//ENDE******************************************************
/*
            client.execute(query, (err ) => {
                returnClientToPool(client);
                
                if (err) {
                    adapter.log.error(`Cannot insert ${query}: ${err} (id: ${id})`);
                } else {
                    checkRetention(id);
                }
                cb && cb(); // BF asked (2021.12.14): may be return here err?
            });*/
        }
    });
}

haus-automatisierung

@nettekov sagte in SQL.Adpter: Insert-Befehle innerhalb von 40ms:

Durch den temporären, eingebauten Codeschnipsel in der '_InsertValueIntoDB' - Funktion werden alle eingehenden mehrfach INSERT-Statement in einzelne INSERT-Sequenz separiert und als einzelne Transaktionen verschickt.

Die Frage ist ja eher, wie die da rein kommen.

OT: Schau dir mal die String-Funktion split an

nettekov

@haus-automatisierung said in SQL.Adpter: Insert-Befehle innerhalb von 40ms:

Die Frage ist ja eher, wie die da rein kommen.

Die kommen durch die Aqara Sensoren für Temperatur, Luftdruck und Luftfeuchtigkeit.

Aqara Smart Luftdruck Temperatur Feuchtigkeit Umwelt Aqara Sensor Arbeit Für Xiaomi Hause Android IOS APP Control Homekit

Ich hatte letztes Jahr im Januar 3 Stück gekauft und bis dato keine Probleme.
Im Januar zwei weitere implementiert. Erst in der Kombination diesen zwei gab es die Probleme, dann aber z. T. von allen fünf.
Von den anderen Zigbee-Teilnehmern, bzw. Datenpunkten ist alles ruhig.
Vielleicht haben wir hier ein Chargen Problem bei den Aqara.
Würde zumindest erklären, warum das Problem jetzt auftritt.

Ich habe noch einen Sensor übrig, denn kann ich gerne zuschicken.

Grüße nettekov

nettekov

@haus-automatisierung said in SQL.Adpter: Insert-Befehle innerhalb von 40ms:

OT: Schau dir mal die String-Funktion split an

Jaja, ich ziehe mir den gerne an.
Beim nächsten Flickschustern nehme ich die Zange statt nicht den Hammer.

apollon77

@nettekov @haus-automatisierung ALso wo er herkommt weiss ich ... und ist "schon ewig" so ... ich denke eher das das counter Feature nicht so oft genutzt wird (und nicht vom testing), sodass das einfach noch nie wirklich aufgefallen ist.

Sauber müsste man umbauen das die "insert" Methode in den DB-Function Files (mysql.js, mssql.js ...) keinen String sondern ein array zurückgibt und dann wie bei der abarbeitung von SQLFunct.init oder .destroy das von der Methode allScripts() gemacht wird weil die das array dann "kommandoweise" abarbeitet. Ich schaue mal wann ich neben Matter zeit finde das zu machen, aber ja dein workkaround tut auch erstmal für dich

NEWS

SQL.Adpter: Insert-Befehle innerhalb von 40ms

open Adapter adds two insert statements to the same request (leads to an error) #294

Support us

512
Online

31.9k
Users

80.1k
Topics

1.3m
Posts

NEWS

SQL.Adpter: Insert-Befehle innerhalb von 40ms

open Adapter adds two insert statements to the same request (leads to an error) #294

Support us

512Online

31.9kUsers

80.1kTopics

1.3mPosts

512
Online

31.9k
Users

80.1k
Topics

1.3m
Posts