Kann den MySQL inzwischen die Tabellenstruktur in einer Transaction ändern? (inklusive Möglichkeit Rollback)? Bisher habe ich damit des öfteren Probleme, vor allem bei Schemamigrationen kann das einige ernste Probleme verursachen.
Oder in einer Transaktion die foreign key checks korrekt handhaben? Bisher ist dies mein Stand (aus der MySQL Doku): Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.
Kann den MySQL inzwischen die Tabellenstruktur in einer Transaction ändern? (inklusive Möglichkeit Rollback)?
Bisher habe ich damit des öfteren Probleme, vor allem bei Schemamigrationen kann das einige ernste Probleme verursachen.
Oder in einer Transaktion die foreign key checks korrekt handhaben?
Bisher ist dies mein Stand (aus der MySQL Doku):
Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.
SET FOREIGN_KEY_CHECKS=0
Das ist keine saubere (und immer sinnvolle) Lösung, zumal DB-spezifisch (was nicht immer gewünscht ist).