SQLite

View Ticket
Login
Ticket Hash: 34cd55d68e0e6e7c9a0711aab81a2ee3c354b4c0
Title: Database corruption following INSERT with a TRIGGER that does an affinity change
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-05-19 20:34:43
Version Found In: 3.8.10.1
User Comments:
drh added on 2015-05-19 19:08:17:

The following script generates a corrupt database file:

CREATE TABLE t1(ii INT);
CREATE TABLE t2(tt TEXT PRIMARY KEY, ss);
CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
  INSERT INTO t2(tt) VALUES(new.ii);
END;
CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
  UPDATE t2 SET ss = 4;
END;
INSERT INTO t1(ii) VALUES('1');
PRAGMA integrity_check;

This problem appears to have been introduced on 2014-08-23 (version 3.8.7) by check-in [25f2246be404f38b].


drh added on 2015-05-19 20:34:43:

In order for this bug to strike, one must do an INSERT into a table that has an index with a TEXT key, and two nested triggers on that table must convert the key value from TEXT to INTEGER and back to TEXT again.

Any index corruption that does occur because of this bug can be fixed by running REINDEX.