Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

ALTER TABLE on a parent table deletes child rows through ON DELETE CASCADE

Aperta
  • Qualcosa non va?
#528 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
5/5
Tempo stimato
Più di una settimana
Idoneità per principianti
35/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
php, sqlite
Ambito
backend, databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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 parent deletes the child rows. MySQL fails with error 3730 (ER_FK_CANNOT_DROP_PARENT).
  • TRUNCATE TABLE parent deletes 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.

Lingua principale
PHP
Stelle
362
Fork
69
Merge medio
3g 11h
PR unite (30g)
12

Preparare l'ambiente

Apri in Codespaces

Avvia il container di sviluppo del progetto nel browser, con il tuo account GitHub.

  • Nessun Dockerfile né file Docker Compose
  • Nessun modello di pull request
  • Nessuna guida per i contributori

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di WordPress/sqlite-database-integration

Tutte le issue di WordPress/sqlite-database-integration

Issue simili

Altre issue su PHP

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.