SQLite

View Ticket
Login
Plaintext
2015-05-19
20:34 • Ticket [34cd55d6] Database corruption following INSERT with a TRIGGER that does an affinity change status still Fixed with 3 other changes (artifact: c195566c user: drh)
19:55 • Fixed ticket [34cd55d6]. (artifact: 4d04bea3 user: drh)
19:52
Ensure that when the VM applies TEXT affinity to a value it discards any existing REAL or INTEGER value. Fix for [34cd55d6]. Increase the version number to 3.8.10.2. (check-in: 40f67265 user: drh tags: branch-3.8.10)
19:44
Ensure that when the VM applies TEXT affinity to a value it discards any existing REAL or INTEGER value. Fix for [34cd55d6]. (check-in: f5d0ce80 user: dan tags: trunk)
19:08 • New ticket [34cd55d6] Database corruption following INSERT with a TRIGGER that does an affinity change. (artifact: 208465f3 user: drh)
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.