SET autocommit and SET foreign_key_checks are accepted but have no effect #529

Reproduction

CREATE TABLE p (id INT PRIMARY KEY);
CREATE TABLE c (pid INT, FOREIGN KEY (pid) REFERENCES p (id));

SET foreign_key_checks = 0;
SELECT @@foreign_key_checks;
-- 0
INSERT INTO c VALUES (99);
-- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed
-- (MySQL: the row is inserted)

CREATE TABLE t (id INT PRIMARY KEY);

SET autocommit = 0;
SELECT @@autocommit;
-- 0
INSERT INTO t VALUES (1);
ROLLBACK;
SELECT id FROM t;
-- 1   (MySQL: no rows; with autocommit off, the INSERT opened a transaction)

Reproduced on trunk (3.1.0) with PHP 8.3 and SQLite 3.45.1.

Both statements are accepted and stored as session variables, so SELECT @@... reports the new value, but neither changes behavior. Before they are set, both variables read as NULL (MySQL: 1).

Impact

• foreign_key_checks: SQL dumps disable foreign key checks so that tables can be loaded in any order. mysqldump output sets FOREIGN_KEY_CHECKS=0 in its header. Here, importing such a dump fails at the first row of a child table that is loaded before its parent.
• autocommit: Code that turns autocommit off and relies on ROLLBACK to discard its changes has them committed instead. This one silently keeps data the application meant to discard.

Implementation notes

foreign_key_checks maps to PRAGMA foreign_keys, with one limit: SQLite ignores that pragma inside a transaction, so a change made inside a transaction can only take effect when it ends. The driver's wrapper transaction has the same limit, and #528 discusses running DDL outside it, which would also be the place to apply this setting.

autocommit = 0 could be emulated by opening a transaction before the next statement and keeping it open until COMMIT or ROLLBACK, as MySQL does.

Until then, would it be preferable to reject these values with an error rather than accept them silently? I'm happy to contribute a PR in whichever direction you prefer.

Use of AI

This report was prepared with Claude Code.