Queries working for themselves but not when inside TRANSACTION?

Queries and Resources
1

Hi guys, I have this strange issue where my queries are working if I run them for themselves but they don't work when put inside START TRANSACTION and COMMIT

This is my query:

START TRANSACTION;

INSERT INTO kunden (kundennr, art, firma, vorname, nachname, strasse_nr, plz, ort, email, telefon, ust_id, hrb_gericht, hrb_nr)
VALUES (
  {{ listbox1.selectedItem.kundennr }}, {{ select1.value }}, {{ textInput2.value }}, {{ textInput3.value }}, {{ textInput4.value }}, {{ textInput5.value }}, {{ textInput6.value }}, {{ textInput7.value }}, {{ email1.value }}, {{ phoneNumber1.value }}, {{ textInput8.value }}, {{ textInput9.value }}, {{ textInput10.value }})
ON DUPLICATE KEY UPDATE art = {{ select1.value }}, firma = {{ textInput2.value }}, vorname = {{ textInput3.value }}, nachname = {{ textInput4.value }}, strasse_nr = {{ textInput5.value }}, plz = {{ textInput6.value }}, ort = {{ textInput7.value }}, email = {{ email1.value }}, telefon = {{ phoneNumber1.value }}, ust_id = {{ textInput8.value }}, hrb_gericht = {{ textInput9.value }}, hrb_nr = {{ textInput10.value }};

SET @kunden_id = LAST_INSERT_ID();

INSERT INTO fahrzeuge (fahrzeugnr, fin, marke, modell, klasse, karosserieversion)
VALUES ({{ listbox2.selectedItem.fahrzeugnr }}, {{ textInput12.value }}, {{ select2.value }}, {{ textInput13.value }}, {{ select3.value }}, {{ select6.value }})
ON DUPLICATE KEY UPDATE fin = {{ textInput12.value }}, marke = {{ select2.value }}, modell = {{ textInput13.value }}, klasse = {{ select3.value }}, karosserieversion = {{ select6.value }};

SET @fahrzeuge_id = LAST_INSERT_ID();

INSERT INTO transaktionen (transaktionsart, handelsart, transaktionsdatum, kundennr_fid, fahrzeugnr_fid, personalnr_fid, liefertag, netto_kaufpreis, besteuerung, kennzeichen, erstzulassung, kilometerstand, schlusselnr)
VALUES ({{ segmentedControl1.value }}, {{ segmentedControl3.value }}, {{ date2.value }}, @kunden_id, @fahrzeuge_id,{{ select5.value }}, {{ date3.value }}, {{ numberInput3.value }}, {{ segmentedControl2.value }}, {{ textInput14.value }}, {{ date4.value }}, {{ numberInput6.value }}, {{ numberInput7.value }});

COMMIT;

SQL statement:

START TRANSACTION;

INSERT INTO kunden (kundennr, art, firma, vorname, nachname, strasse_nr, plz, ort, email, telefon, ust_id, hrb_gericht, hrb_nr)
VALUES (
  36, Verbraucher, , Max, Muster, Straße, PLZ, Ort, test@test.de, +49123891239012, , , )
ON DUPLICATE KEY UPDATE art = Verbraucher, firma = , vorname = Max, nachname = Muster, strasse_nr = Straße, plz = PLZ, ort = Ort, email = test@test.de, telefon = +49123891239012, ust_id = , hrb_gericht = , hrb_nr = ;

SET @kunden_id = LAST_INSERT_ID();

INSERT INTO fahrzeuge (fahrzeugnr, fin, marke, modell, klasse, karosserieversion)
VALUES (1132, ABC12345678901234, Ford, Modell, A1, A)
ON DUPLICATE KEY UPDATE fin = ABC12345678901234, marke = Ford, modell = Modell, klasse = A1, karosserieversion = A;

SET @fahrzeuge_id = LAST_INSERT_ID();

INSERT INTO transaktionen (transaktionsart, handelsart, transaktionsdatum, kundennr_fid, fahrzeugnr_fid, personalnr_fid, liefertag, netto_kaufpreis, besteuerung, kennzeichen, erstzulassung, kilometerstand, schlusselnr)
VALUES (Einkauf, Eigenfahrzeug, 2024-01-10, @kunden_id, @fahrzeuge_id,1, 2024-01-10, 12000, Differenzbesteuert, ABC, 2024-01-10, 123, 12);

COMMIT;

When running it gives me this error: Incorrect integer value: 'Verbraucher' for column 'kundennr' at row 1

The error only occurs if it tries to update an entry.

I can't seem to find what the issue is. Does anyone have an idea? Am I stupid?

2

Ok, after refreshing the site the error changed to Cannot add or update a child row: a foreign key constraint fails and the issue seems to be SET @kunden_id = LAST_INSERT_ID(); and SET @fahrzeuge_id = LAST_INSERT_ID();.

Any idea what I am doing wrong?

3

Hi @Rupur, the error we are getting after refreshing the page is coming from trying to insert a record into the child table, but the foreign key in the parent table is not found. The first place to check would be the "kunden" table. Does it have an "AUTO_INCREMENT" primary key column? If it doesn't, "LAST_INSERT_ID()" may not be evaluating to the id of the "kunde/kundin" we just inserted. Could you share how your queries look when you are running them outside of the transaction block? Does `SET @kunden_id = LAST_INSERT_ID();` run then with no issues?