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

[klein0r]

Describe the bug

In some cases, the adapter generates a statement with two separate inserts. e.g.

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);

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)

See https://forum.iobroker.net/topic/62640/sql-adpter-insert-befehle-innerhalb-von-40ms

Versions:

• Adapter version: 1.16.1 and 2.2.0

[Erwin40]

Das ist der gleiche wie bei #304
Bitte mit entspr. Verweis schließen :-)

[GermanBluefox]

Fixed, and now covered by a test.

The adapter no longer builds one query holding two statements. insert() returns a list — one statement per table, chunked at 500 rows — and _insertValuesIntoDB() sends them one at a time over a single borrowed connection. Reproducing the batch from this report against the current builders gives two separate statements on all four dialects.

What was missing was anything keeping it that way: joining a list of queries into one is the obvious "cleanup" for whoever reads that code next, and only a comment stood in the way. test/testInsertStatements.js (#573) now asserts per dialect that a batch with both counter and value rows produces one statement per table, that none carries a second statement behind a semicolon, and that a 1200-row batch chunks into 500/500/200 without the chunks being glued back together. Verified by restoring the old behaviour: the mysql cases fail, and pass again once it is reverted.

One detail from your report that stays as it is: the duplicate row — (5, 1676138387812, 53) twice in ts_counter. That is inherent to the counter-reset path, which stores both the old and the new state, so the "new" value of one reset is the "old" value of the next. It is harmless: ts_counter has no primary key, getCounterDiff selects DISTINCT, and two equal consecutive values contribute nothing to the summed progression.

Note for anyone arriving from the comment below: #304 is a different bug — a primary key violation from two rows sharing (id, ts), not a syntax error from two statements in one query. It is also fixed, and covered separately in #574.

Closing.