SQLite

Ticket Change Details
Login
Overview

Artifact ID: a6e343197ba40f3920400825c14d8cc84bb6dcdf
Ticket: 6bfb98dfc0c2fee464aa23707ba50be946b49f1c
False report of database corruption on an INSERT
User & Date: drh 2013-03-27 02:54:09
Changes

  1. foundin changed to: "3.7.16"
  2. icomment:
    The final INSERT in the script below reports that the database
    is corrupt (SQLITE_CORRUPT) and aborts.  Subsequent analysis shows
    no database corruption.
    
    <blockquote><verbatim>
    PRAGMA page_size=512;
    CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
    INSERT INTO t1 VALUES(1,randomblob(400));
    INSERT INTO t1 VALUES(2,randomblob(400));
    INSERT INTO t1 SELECT x+2, randomblob(400) FROM t1;
    INSERT INTO t1 SELECT x+4, randomblob(400) FROM t1;
    INSERT INTO t1 SELECT x+8, randomblob(400) FROM t1;
    INSERT INTO t1 SELECT x+16, randomblob(400) FROM t1;
    INSERT INTO t1 SELECT x+32, randomblob(400) FROM t1;
    INSERT INTO t1 SELECT x+64, randomblob(400) FROM t1 WHERE x<10;
    CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.x=74 BEGIN
      DELETE FROM t1;
      INSERT INTO t1 VALUES(75, randomblob(400));
      INSERT INTO t1 VALUES(76, randomblob(400));
    END;
    INSERT INTO t1 VALUES(74, randomblob(400));
    </verbatim></blockquote>
    
    The problem occurs because the INSERT operation leaves its cursor open, though
    marked CURSOR_INVALID, while holding a reference to a second-level page of the
    btree.  When the trigger fires it deletes content out from under this cursor,
    which is normally OK since the cursor is invalid.  However, during the second
    INSERT inside the trigger, when a page is removed from the freelist to hold
    the new content, that page is checked to make sure it is unreferenced.  This
    check fails since the invalid INSERT cursor is still holding a reference to
    the page.  That reference makes it appear as if the page was on the freelist
    and in a btree at the same time and so an SQLITE_CORRUPT error is thrown.
    However, the page is not really on the btree since the cursor that is 
    referencing it is marked CURSOR_INVALID.
    
    This problem has existing in the code since late 2009.  It is very difficult
    to hit, however (which helps to explain why it has gone undetected until now).
    The problem is very sensitive to the specific constants used in the script
    shown above.
    
  3. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Severe"
  6. status changed to: "Open"
  7. title changed to: "False report of database corruption on an INSERT"
  8. type changed to: "Code_Defect"