Foreign Key Error in Transaction, obwohl vorhanden

[Son Goku]

Hallo

ich habe folgende 2 Tabellen:

Code: Select all

CREATE TABLE `parent` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `child` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `parentID` int(10) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `parentID` (`parentID`),
 CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Per Script werden jetzt beide Tabellen befüllt:

Code: Select all

$mysql->query("START TRANSACTION");

foreach($names as $name) {
  // Abfrage, ob ein Gewünschter Name "$name" in Tabelle parent vorhanden ist.
  
  // Wenn vorhanden ID in variable speichern.
  
  // Wenn nicht vorhanden, INSERT in Tabelle parent, zurückgegebene ID in Variable speichern.
  
  // INSERT in Tabelle child mit der eben gespeicherten ID.
}

// Wenn Fehler
$mysql->query("ROLLBACK");

// Wenn OK
$mysql->query("COMMIT");

Im ersten Schleifendurchlauf wird $name in der Tabelle parent gefunden und die ID ausgelesen, in allen anderen Fällen ist dieser nicht vorhanden und wird daher eingefügt. Die IDs hab ich zur Kontrolle ausgegeben und sind alle korrekt, aufsteigend. Es läuft kein anderes Script nebenher.

Beim INSERT in Tabelle child bekomme ich ab dem 2. Durchlauf aber immer folgende Meldung:

ErrNo: 1452
Error: Cannot add or update a child row: a foreign key constraint fails (`testdb`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `parent` (`id`))

Wenn ich unmittelbar nach dem INSERT ein SELECT mache, kann ich die Zeile mit der ID auch nicht finden, obwohl das innerhalb der Transaktion ja möglich sein sollte. Oder irre ich mich?

[ddm3ve]

Transaktionslevel richtig eingestellt?

https://dev.mysql.com/doc/refman/8.0/en ... evels.html

[Son Goku]

Steht auf READ COMMITTED.

Was wäre denn dann die richtige Einstellung?

Bei REPEATABLE READ hätte ich dann, wenn ich es richtig verstehe das gleiche Problem. Ich führe das SELECT aus, zu diesem Zeitpunkt wird ein Snapshot der DB erstellet und der von mir anschließend geschriebene Eintrag nicht gelesen werden können, weil er zu neu ist.

READ UNCOMMITTED würde auf den ersten Blick passen. Aber was, wenn ein anderes Script Daten während der Transaktion liest, diese als Committed annimmet, fortfährt und diese durch einen späteren Rollback wieder entfernt werden?

SERIALIZABLE werde ich nicht so ganz schlau draus. Autocommit ist bei mir aktiviert. Da es aber ne eigene Transaktion ist, werde ich wohl nicht auf die soeben geschriebene Zeile zugreifen können.

[ddm3ve]

Das habe ich bei mir auch geprüft.

Wie folgt die Tabllen angelegt:
test und test2 jeweils mit spalen test1,test2,test3

Primary Key und führende Tabelle ist test und spalte test1.
Foreign Key fleisst nach Tabelle test2 und spalte test1.

Code: Select all

CREATE TABLE `test` (
  `test1` int(10) NOT NULL,
  `test2` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `test3` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `test2` (
  `test1` int(10) NOT NULL,
  `test2` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `test3` varchar(10) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `test`
  ADD PRIMARY KEY (`test1`);

ALTER TABLE `test2`
  ADD KEY `IDX_PRIM_TEST` (`test1`);


ALTER TABLE `test2`
  ADD CONSTRAINT `IDX_PRIM_TEST` FOREIGN KEY (`test1`) REFERENCES `test` (`test1`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

Und hier funktioniert ds wie von dir erwartet. Offensichtlich ist da was am Contraint falsch.

Code: Select all

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (test1, test2, test3) values (10,10,10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 (test1, test2, test3) values (10,10,10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------+-------+-------+
| test1 | test2 | test3 |
+-------+-------+-------+
|    10 | 10    | 10    |
+-------+-------+-------+
1 row in set (0.00 sec)

mysql> select * from test1;
ERROR 1146 (42S02): Table 'test.test1' doesn't exist
mysql> select * from test2;
+-------+-------+-------+
| test1 | test2 | test3 |
+-------+-------+-------+
|    10 | 10    | 10    |
+-------+-------+-------+
1 row in set (0.00 sec)

[Son Goku]

Danke, bin gerade dabei das durchzutesten, wobei mir als erstes auffällt, dass du keinen auto_increment drin verwendest.
Im Notfall lege ich die Tabellen einfach mal neu an.

[ddm3ve]

Dürfte keine Rolle spielen.
Sprich auto_increment spielt zumindest für die Tabelle test keine Rolle.
Bei test2 wird es nicht funktionieren. Also hier muss der Fremdschlüssel einfliessen und kein auto_increment Wert.