|
2019-05-07
| ||
| 20:06 | • Fixed ticket [e63cbcfd33]: Database table/index contents mismatch (corruption) caused by FK processing plus 3 other changes (artifact: c199eb6b37 user: drh) | |
| 20:06 | Generate all records for INSERT or UPDATE prior to running foreign key constraint checks, since the FK checks might modify the datatype of registers used to generate the records. Fix for ticket [e63cbcfd3378afe6980d6]. (check-in: 3c75605b46 user: drh tags: trunk) | |
| 19:13 | On an INSERT or UPDATE, generate the new table record prior to running foreign key checks, in case the foreign key checks changes datatypes on the registers holding column values. Proposed fix for ticket [e63cbcfd3378afe6980d626]. (check-in: 3f1c805164 user: drh tags: tkt-e63cbcfd) | |
| 19:13 | • Ticket [e63cbcfd33] Database table/index contents mismatch (corruption) caused by FK processing status still Open with 6 other changes (artifact: 55c1efff56 user: dan) | |
| 19:09 | • New ticket [e63cbcfd33]. (artifact: 6f81b88f45 user: dan) | |
| Ticket Hash: | e63cbcfd3378afe6980d6264e514b8db9117f2f8 | ||
| Title: | Database table/index contents mismatch (corruption) caused by FK processing | ||
| Status: | Fixed | Type: | Code_Defect |
| Severity: | Critical | Priority: | Immediate |
| Subsystem: | Unknown | Resolution: | Fixed |
| Last Modified: |
2019-05-07 20:06:59 6.18 years ago |
Created: |
2019-05-07 19:09:47 6.18 years ago |
| Version Found In: | |||
| User Comments: | ||||
|
dan added on 2019-05-07 19:09:47:
The following script ends up with the table b-tree for table "parent" containing the integer value 123, and the index b-tree for the PRIMARY KEY of "parent" containing text value "123". A corrupt database.
PRAGMA foreign_keys = true;
CREATE TABLE parent(
p TEXT PRIMARY KEY
);
CREATE TABLE child(
c INTEGER UNIQUE,
FOREIGN KEY(c) REFERENCES parent(p) DEFERRABLE INITIALLY DEFERRED
);
BEGIN;
INSERT INTO child VALUES(123);
INSERT INTO parent VALUES('123');
COMMIT;
PRAGMA integrity_check;
The problem is that right before inserting the record into the table b-tree for "parent", the equivalent of "SELECT count(*) FROM child WHERE c='123'" is run to check for foreign key constraint violation resolutions. When integer affinity is applied to the '123', it is also applied to the value that will be inserted into table "parent". dan added on 2019-05-07 19:13:02: This is a long-standing bug. Has been present since before 3.8.0 (2013). | ||||