SQLite

View Ticket
Login
Ticket Hash: 314cc133e5ada126f1b7efdda5f61cdfbf0e4927
Title: Trigger on normal table causes the database disk image to become malformed
Status: Fixed Type: Code_Defect
Severity: Important Priority: High
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-12-29 22:09:31
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-12-29 17:14:19: (text/x-fossil-wiki)
The following test case unexpectedly results in an "database disk image is malformed" error.

<pre>
PRAGMA recursive_triggers = true;
CREATE TABLE t0(c0 UNIQUE ON CONFLICT REPLACE, c1, c2);
CREATE INDEX i0 ON t0(c2);
INSERT INTO t0(c0) VALUES (0);
CREATE TRIGGER tr0 DELETE ON t0 BEGIN
	UPDATE t0 SET c2 = c0;
END;
INSERT INTO t0(c0, c2) VALUES(4, 0), (9, 0);
UPDATE t0 SET c0 = 0;
SELECT * FROM t0 WHERE x'' > t0.c2 GROUP BY c1; -- database disk image is malformed
</pre>

drh added on 2019-12-29 22:09:31: (text/x-fossil-wiki)
See also tickets [c1e19e12046d23fe] and [a8a4847a2d96f5de].

When the REPLACE conflict resolution algorithm runs, the DELETE trigger fires
and then deletes content out from under the row that is being updated.  This
greatly confuses the UPDATE processing, resulting in an extra row in an index,
which qualifies as a "corrupt database".  (The corruption can be fixed by
running REINDEX, but it is still corruption.)

The proposed solution is to prevent any changes to the table being updated
while running triggers as part of REPLACE conflict resolution in an UPDATE.

This problem seems to apply to UPDATEs only.  It does not appear (as far as
I can see) to affect INSERTs.  The reason is that an UPDATE is typically
a two-step operation:

   1.   Read the old values for the row being updated.
   2.   Write the new values for the row.

DELETE triggers that fire as a result of conflict resolution will happen in
between those two steps, and thus have the potential to change things out
from under the update operation.  With an INSERT, there is only one step
(specifically step 2 above) and so the DELETE triggers cannot change things
that have already been read, and hence have less opportunity to do harm.