Nummer bei jedem Insert um 1 erhöhen - Fehler tritt auf

markus129

Benutzer
Beiträge
11
Hallo zusammen,

ich habe in meiner MariaDB eine Kundentabelle in der automatisch bei jedem INSERT die Kundennummer einfach um 1 erhöht werden soll. Bisher hatte ich das einfach über ein AUTO_INCREMENT gelöst. Jetzt habe ich aber das Problem, dass ich die DB minütlich mit dem CRM-System synchronisiere und bei jedem Sync das AUTO_INCREMENT aus mir nicht ersichtlichen Gründen um 1 erhöht wird, wodurch die Kundennummer unerwünschterweise mit jeder Stunde um 60 höher werden, auch wenn kein Kunde hinzugefügt wurde. Das ist natürlich suboptimal. Daher wollte ich die Nummer selbst hochzählen. Aber alle Varianten, die ich eingesetzt habe, haben Fehler geworfen.

Info dazu: Ich verwende MariaDB in einem Docker Container (1:10.10.3+maria~ubu2204) und programmiere mit TypeScript. Das Ganze läuft dann unter nodeJS 18 und verwendet wird das mariadb npm package 3.1.0. Die SQL-Statements sind Prepared Statements.

1) INSERT INTO customers ( ... )
SELECT MAX(customer_id) + 1, ... FROM customers
ON DUPLICATE KEY UPDATE ...;
führt zu einem HY000 "This command is not supported in the prepared statement protocol yet" Error.

2) INSERT INTO customers ( ... )
VALUES ((SELECT MAX(customer_id) + 1 FROM customers), ... )
ON DUPLICATE KEY UPDATE ...;
löst einen "Table 'contacts' is specified twice, both as a target for 'INSERT' and as a separate source for data" Fehler aus.

3) Wie schon beschrieben funktioniert die AUTO_INCREMENT Variante zwar, zählt aber auch hoch, wenn gar kein INSERT stattfindet.

Habt ihr noch Ideen, was man noch machen könnte?

Vielen Dank schonmal im Voraus!
Markus
 
Werbung:
Wenn Du fast überall Punkte hinschreibst statt das vollständige Statement, kann man dazu kaum etwas sagen.
Normalerweise erzeugt man ID Werte mit einem Autoincrement Generator und nicht mit dieser Max Konstruktion. Was in der Konstellation on duplicate key update für einen Sinn machen soll, kann ich mir nicht vorstellen. Welcher Unterschied zwischen 1) und 2) besteht ist auch unklar, das Beispiel scheint inkonsistent zu sein, kann man dank der vielen Punkte aber auch nicht sagen.

Du solltest vielleicht besser Dein Syncmechanismus reparieren.
Und lückenhafte Kundennummern sind eigentlich ziemlich egal.
 
@dabadepdu
Ich wollte die relativ langen Statements mit den ... abkürzen. Es sind durch die Prepared Statements sowieso vor allem '?', die dort zu finden sind. Das "ON DUPLICATE KEY UPDATE" ist deswegen vorhanden, weil beim Sync sowohl neue, als auch modifizierte Datensätze aus dem CRM kommen. So konnte ich mit einem Statement sowohl die neuen einfügen als auch die nur modifizierten updaten, ohne neue Datensätze zu erzeugen. Aber du hast wahrscheinlich Recht, dass ich eher den Sync-Mechanismus anders gestalten sollte als die komplizierte MAX(id) + 1 Struktur zu bauen.
 
wenn da neue kommen, ist das ein INSERT. Und das erhöht die Sequence (bzw. das, was MySQL da hat, richtige Sequencen hat es ja nicht ...)
Du meinst, das Insert löst auf jeden Fall eine Erhöhung des Auto_Increments aus (wie auch immer das im Hintergrund funktioniert), auch wenn kein wirklich neuer Eintrag entsteht? Die Lösung wäre also das Aufteilen dieses Statements in ein Insert und ein Update Statement? Wäre zwar mehr Aufwand im Code und würde auf die Performance gehen, aber könnte funktionieren…
 
Nicht jeder Insert muß ja funktionieren, aber Sequencen werden dennoch höher gezählt:

Code:
postgres=# create table markus129(id int generated always as identity primary key, data int, check (data < 10));
CREATE TABLE
postgres=# insert into markus129 (data) values (12);
ERROR:  new row for relation "markus129" violates check constraint "markus129_data_check"
DETAIL:  Failing row contains (1, 12).
postgres=# insert into markus129 (data) values (2);
INSERT 0 1
postgres=# select * from markus129;
 id | data 
----+------
  2 |    2
(1 row)

postgres=#
 
Nicht jeder Insert muß ja funktionieren, aber Sequencen werden dennoch höher gezählt:

Code:
postgres=# create table markus129(id int generated always as identity primary key, data int, check (data < 10));
CREATE TABLE
postgres=# insert into markus129 (data) values (12);
ERROR:  new row for relation "markus129" violates check constraint "markus129_data_check"
DETAIL:  Failing row contains (1, 12).
postgres=# insert into markus129 (data) values (2);
INSERT 0 1
postgres=# select * from markus129;
 id | data
----+------
  2 |    2
(1 row)

postgres=#
Okay. Vielen Dank. Mir war nicht bewusst, dass ein abgebrochenes insert auch das Auto_Increment erhöht. Ich dachte, das passiert nur bei einer erfolgreichen Transaktion.
 
Okay. Vielen Dank. Mir war nicht bewusst, dass ein abgebrochenes insert auch das Auto_Increment erhöht
Das meinte ich mit :
Du solltest vielleicht besser Dein Syncmechanismus reparieren.
Mir war nicht bewusst, dass Dir nicht bewusst war, wie das läuft.
Und nochmal für die Akten: Der DB sind die Lücken vollkommen egal, ebenso wie die Reihenfolge der Werte, ob es Zahlen oder Buchstaben sind, alles egal, es muss nur eindeutig sein für einen PK, nichts weiter. Es gibt keinen Schönheitspreis.
 
Hallo,

ich verstehe dein Problem gar nicht:

Code:
mysql> SELECT * FROM cust;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
+----+---------+--------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM cust_crm;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
+----+---------+--------+
8 rows in set (0.00 sec)

mysql>
mysql> insert into cust
    -> SELECT * from cust_crm cc
    -> ON DUPLICATE KEY UPDATE auftrag = cc.auftrag, status = cc.status;
Query OK, 0 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM cust;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
+----+---------+--------+
8 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO `cust_crm` (`auftrag`, `status`) VALUES (55, 'X');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> insert into cust
    -> SELECT * from cust_crm cc
    -> ON DUPLICATE KEY UPDATE auftrag = cc.auftrag, status = cc.status;
Query OK, 1 row affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM cust;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
|  9 |      55 | X      |
+----+---------+--------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM cust_crm;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
|  9 |      55 | X      |
+----+---------+--------+
9 rows in set (0.00 sec)

mysql>


funktioniert doch 100% und du benötigst kein PREPARED Statement.


Und: ob und wann ein AUTO_INCREMENT hoch zählt hängt von den Einstellungen ab.

Du kannst es z.B so einstellen das alle anderen Transaktionen bei einem INSERT mit AI warten
bis das INSERT fertig ist oder ein ROLLBACK gemacht hat. Dann bleibt AI im Fehlerfall unberührt.
 
Das meinte ich mit :

Mir war nicht bewusst, dass Dir nicht bewusst war, wie das läuft.
Und nochmal für die Akten: Der DB sind die Lücken vollkommen egal, ebenso wie die Reihenfolge der Werte, ob es Zahlen oder Buchstaben sind, alles egal, es muss nur eindeutig sein für einen PK, nichts weiter. Es gibt keinen Schönheitspreis.
Auch dir noch mal vielen Dank für die Infos. Ich hätte vielleicht auch noch dazu erwähnen sollen, dass es bei der genannten ID nicht um die geht, mit der ich im backend arbeite. Da verwende ich noch zusätzlich eine UUID. Die Customer ID ist bei uns eine Nummer, die der Vertrieb und die Rechnungslegung gerne haben wollen. Die brauchen die für eigene Zwecke. Da sind zu große Lücken zwischen den IDs nicht gerne gesehen. Abgesehen davon, dass sie davon recht schnell sehr lang werden.
 
Hallo,

ich verstehe dein Problem gar nicht:

Code:
mysql> SELECT * FROM cust;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
+----+---------+--------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM cust_crm;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
+----+---------+--------+
8 rows in set (0.00 sec)

mysql>
mysql> insert into cust
    -> SELECT * from cust_crm cc
    -> ON DUPLICATE KEY UPDATE auftrag = cc.auftrag, status = cc.status;
Query OK, 0 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM cust;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
+----+---------+--------+
8 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO `cust_crm` (`auftrag`, `status`) VALUES (55, 'X');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> insert into cust
    -> SELECT * from cust_crm cc
    -> ON DUPLICATE KEY UPDATE auftrag = cc.auftrag, status = cc.status;
Query OK, 1 row affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM cust;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
|  9 |      55 | X      |
+----+---------+--------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM cust_crm;
+----+---------+--------+
| id | auftrag | status |
+----+---------+--------+
|  1 |      22 | a      |
|  2 |      33 | b      |
|  3 |      44 | a      |
|  4 |      44 | b      |
|  5 |      66 | a      |
|  6 |      44 | c      |
|  7 |      55 | a      |
|  8 |      55 | c      |
|  9 |      55 | X      |
+----+---------+--------+
9 rows in set (0.00 sec)

mysql>


funktioniert doch 100% und du benötigst kein PREPARED Statement.


Und: ob und wann ein AUTO_INCREMENT hoch zählt hängt von den Einstellungen ab.

Du kannst es z.B so einstellen das alle anderen Transaktionen bei einem INSERT mit AI warten
bis das INSERT fertig ist oder ein ROLLBACK gemacht hat. Dann bleibt AI im Fehlerfall unberührt.
Das ist super interessant. Hast du zufällig auch eine Idee, wo ich die entsprechenden Einstellung in einer MariaDB finde? Sorry, ich bin wirklich kein Datenbankexperte und brauche da leider ein wenig Hilfe…
 
Werbung:
Zurück
Oben