|Title:||Trigger on normal table causes the database disk image to become malformed|
|Last Modified:||2019-12-29 22:09:31|
|Version Found In:||3.30.0|
mrigger added on 2019-12-29 17:14:19:
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.