ALTER TABLE on a parent table deletes child rows through ON DELETE CASCADE
Maintainer antworten meist innerhalb von 1 Tag
Dieses Issue hat noch niemand übernommen.
Bewertung
- Schwierigkeit
- 5/5
- Geschätzter Aufwand
- Über eine Woche
- Anfängerfreundlichkeit
- 35/100
Rechercherichtung
Start by reading the driver's ALTER TABLE handling and transaction wrapper, then inspect how they manage SQLite foreign key settings around table recreation. The reproduction shows child rows are deleted after adding a column; verify behavior with the provided SQL, including inside a transaction. The issue asks maintainers to decide whether to emulate MySQL's implicit DDL commits, so implementation scope and expected transaction behavior need agreement first.
Vom Indexierungsmodell aus dem Issue-Text verfasst.
Beschreibung
Reproduction
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id INT, pid INT, FOREIGN KEY (pid) REFERENCES parent (id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1), (2);
INSERT INTO child VALUES (10, 1), (20, 2);
ALTER TABLE parent ADD COLUMN name TEXT;
SELECT COUNT(*) FROM child;
-- 0 (MySQL: 2)
Reproduced on trunk (3.1.0) with PHP 8.3 and SQLite 3.45.1, with stringified fetches on, as in WordPress. It also happens when the ALTER TABLE runs inside START TRANSACTION ... COMMIT. With ON DELETE SET NULL, the child rows lose their references instead.
Cause
ALTER TABLE recreates the table: it creates a new table, copies the rows, drops the old table, and renames the new one. Before that, it runs PRAGMA foreign_keys = OFF, as the SQLite procedure requires. But the statement runs inside the driver's wrapper transaction, and PRAGMA foreign_keys is a no-op inside a transaction. Foreign keys stay on, so DROP TABLE performs an implicit DELETE of the old table, which fires the foreign key actions of the child tables.
Related
The same implicit DELETE affects two statements that MySQL rejects for a referenced parent table:
DROP TABLE parentdeletes the child rows. MySQL fails with error 3730 (ER_FK_CANNOT_DROP_PARENT).TRUNCATE TABLE parentdeletes the child rows. MySQL fails with error 1701 (ER_TRUNCATE_ILLEGAL_FK).
A related transaction difference: MySQL implicitly commits an open transaction before and after DDL statements, but the driver runs them inside the user transaction, so a ROLLBACK undoes them together with the earlier work:
START TRANSACTION;
INSERT INTO t VALUES (1);
CREATE TABLE u (id INT);
ROLLBACK;
-- Driver: t is empty, and u doesn't exist.
-- MySQL: t has the row, and u exists.
Fix direction
Foreign keys can only be disabled outside a transaction. Emulating the implicit commit for DDL would fix both problems: with no transaction open, the driver can disable foreign keys before the wrapper transaction begins and restore them after it ends, as the SQLite procedure intends. For DROP TABLE and TRUNCATE, the driver could reject a referenced parent table as MySQL does.
This changes transaction behavior, since DDL could no longer be rolled back, so I'd like your view on the approach first. I'm happy to contribute a PR in whichever direction you prefer.
Use of AI
This report was prepared with Claude Code.
- Vorherrschende Sprache
- PHP
- Sterne
- 362
- Forks
- 69
- Ø Merge
- 3 T. 11 Std.
- Gemergte PRs (30 T.)
- 12
Entwicklungsumgebung
Startet den Dev-Container des Projekts im Browser, mit Ihrem eigenen GitHub-Konto.
- Kein Dockerfile und keine Docker-Compose-Datei
- Keine Pull-Request-Vorlage
- Kein Beitragsleitfaden
Erste Schritte
- Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
- Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
- Forken Sie das Repository und arbeiten Sie in einem Branch.
- Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.
Mehr aus WordPress/sqlite-database-integration
-
MySQL-on-SQLite does not translate DATE arithmetic with INTERVAL HOUREvtl. vergeben @samuelmbabhazi hat das vor 25 Tagen übernommen. Offen
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 72/100
WordPress/sqlite-database-integration#454 · 3 Kommentare ·
Maintainer antworten meist innerhalb von 1 Tag
-
Schwierigkeit 4/5 3-5 Tage Anfängerfreundlichkeit 40/100
WordPress/sqlite-database-integration#531 ·
Maintainer antworten meist innerhalb von 1 Tag
-
Schwierigkeit 4/5 3-5 Tage Anfängerfreundlichkeit 48/100
WordPress/sqlite-database-integration#529 ·
Maintainer antworten meist innerhalb von 1 Tag
-
Schwierigkeit 4/5 1-2 Tage Anfängerfreundlichkeit 52/100
WordPress/sqlite-database-integration#527 ·
Maintainer antworten meist innerhalb von 1 Tag
-
Schwierigkeit 5/5 Über eine Woche Anfängerfreundlichkeit 35/100
WordPress/sqlite-database-integration#526 ·
Maintainer antworten meist innerhalb von 1 Tag
Alle Issues in WordPress/sqlite-database-integration
Ähnliche Issues
-
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 66/100
Automattic/blocks-engine#2866 ·
Maintainer antworten meist innerhalb von 1 Tag
-
enhancement good first issue help wanted
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 82/100
-
backend gamification P0
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 66/100
semanadeinformatica/website#461 ·
-
HttpRequestTrait fatals on WordPress 7.2: $blocked_http_requests clashes with WP_UnitTestCase_BaseOffen
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 72/100
-
area: docs eco: php good first issue help wanted lane: free-agent roadmap size: XS type: docs
Schwierigkeit 2/5 1-3 Stunden Anfängerfreundlichkeit 68/100
arminazimi/evergreen#453 ·
Maintainer antworten meist innerhalb von 1 Tag