sqlite-3.40.0/test/crash.test

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# The focus of this file is testing the ability of the database to
# uses its rollback journal to recover intact (no database corruption)
# from a power failure during the middle of a COMMIT.  The special test
# module "crashtest" compiled with the special "os_test.c" backend is used.
# The os_test.c simulates the kind of file corruption that can occur
# when writes are happening at the moment of power loss.
#
# The special crash-test module with its os_test.c backend only works
# on Unix.
#
# $Id: crash.test,v 1.8 2004/08/21 17:54:46 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# set repeats 100
set repeats 10

# This proc execs a seperate process that crashes midway through executing
# the SQL script $sql on database test.db.
#
# The crash occurs during a sync() of file $crashfile. When the crash
# occurs a random subset of all unsynced writes made by the process are
# written into the files on disk. Argument $crashdelay indicates the
# number of file syncs to wait before crashing.
#
# The return value is a list of two elements. The first element is a
# boolean, indicating whether or not the process actually crashed or
# reported some other error. The second element in the returned list is the
# error message. This is "child process exited abnormally" if the crash
# occured.
proc crashsql {crashdelay crashfile sql} {
  set cfile [file join [pwd] $crashfile]

  set f [open crash.tcl w]
  puts $f "sqlite3_crashparams $crashdelay $cfile"
  puts $f "sqlite3 db test.db"
  puts $f "db eval {pragma cache_size = 10}"
  puts $f "db eval {"
  puts $f   "$sql"
  puts $f "}"
  close $f

  set r [catch {
    exec [file join . crashtest] crash.tcl >@stdout
  } msg]
  lappend r $msg
}

# The following procedure computes a "signature" for table "abc".  If
# abc changes in any way, the signature should change.
proc signature {} {
  return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]
}
proc signature2 {} {
  return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc2}]
}

#--------------------------------------------------------------------------
# Simple crash test:
#
# crash-1.1: Create a database with a table with two rows.
# crash-1.2: Run a 'DELETE FROM abc WHERE a = 1' that crashes during
#            the first journal-sync.
# crash-1.3: Ensure the database is in the same state as after crash-1.1.
# crash-1.4: Run a 'DELETE FROM abc WHERE a = 1' that crashes during
#            the first database-sync.
# crash-1.5: Ensure the database is in the same state as after crash-1.1.
# crash-1.6: Run a 'DELETE FROM abc WHERE a = 1' that crashes during
#            the second journal-sync.
# crash-1.7: Ensure the database is in the same state as after crash-1.1.
#
# Tests 1.8 through 1.11 test for crashes on the third journal sync and
# second database sync.  Neither of these is required in such a small test
# case, so these tests are just to verify that the test infrastructure
# operates as expected.
#
do_test crash-1.1 {
  execsql {
    CREATE TABLE abc(a, b, c);
    INSERT INTO abc VALUES(1, 2, 3);
    INSERT INTO abc VALUES(4, 5, 6);
  }
  set ::sig [signature]
  expr 0
} {0}
do_test crash-1.2 {
  crashsql 1 test.db-journal {
    DELETE FROM abc WHERE a = 1;
  }
} {1 {child process exited abnormally}}
do_test crash-1.3 {
  signature
} $::sig
do_test crash-1.4 {
  crashsql 1 test.db {
    DELETE FROM abc WHERE a = 1;
  }
} {1 {child process exited abnormally}}
do_test crash-1.5 {
  signature
} $::sig
do_test crash-1.6 {
  crashsql 2 test.db-journal {
    DELETE FROM abc WHERE a = 1;
  }
} {1 {child process exited abnormally}}
do_test crash-1.7 {
  catchsql {
    SELECT * FROM abc;
  }
} {0 {1 2 3 4 5 6}}

do_test crash-1.8 {
  crashsql 3 test.db-journal {
    DELETE FROM abc WHERE a = 1;
  }
} {0 {}}
do_test crash-1.9 {
  catchsql {
    SELECT * FROM abc;
  }
} {0 {4 5 6}}
do_test crash-1.10 {
  crashsql 2 test.db {
    DELETE FROM abc WHERE a = 4;
  }
} {0 {}}
do_test crash-1.11 {
  catchsql {
    SELECT * FROM abc;
  }
} {0 {}}

#--------------------------------------------------------------------------
# The following tests test recovery when both the database file and the the
# journal file contain corrupt data. This can happen after pages are
# written to the database file before a transaction is committed due to
# cache-pressure.
#
# crash-2.1: Insert 18 pages of data into the database.
# crash-2.2: Check the database file size looks ok.
# crash-2.3: Delete 15 or so pages (with a 10 page page-cache), then crash.
# crash-2.4: Ensure the database is in the same state as after crash-2.1.
#
# Test cases crash-2.5 and crash-2.6 check that the database is OK if the
# crash occurs during the main database file sync. But this isn't really
# different from the crash-1.* cases.
#
do_test crash-2.1 {
  execsql { BEGIN }
  for {set n 0} {$n < 1000} {incr n} {
    execsql "INSERT INTO abc VALUES($n, [expr 2*$n], [expr 3*$n])"
  }
  execsql { COMMIT }
  set ::sig [signature]
  execsql { SELECT sum(a), sum(b), sum(c) from abc }
} {499500.0 999000.0 1498500.0}
do_test crash-2.2 {
  expr [file size test.db] / 1024
} {19}
do_test crash-2.3 {
  crashsql 2 test.db-journal {
    DELETE FROM abc WHERE a < 800;
  }
} {1 {child process exited abnormally}}
do_test crash-2.4 {
  signature
} $sig
do_test crash-2.5 {
  crashsql 1 test.db {
    DELETE FROM abc WHERE a<800;
  }
} {1 {child process exited abnormally}}
do_test crash-2.6 {
  signature
} $sig

#--------------------------------------------------------------------------
# The crash-3.* test cases are essentially the same test as test case
# crash-2.*, but with a more complicated data set.
#
# The test is repeated a few times with different seeds for the random
# number generator in the crashing executable. Because there is no way to
# seed the random number generator directly, some SQL is added to the test
# case to 'use up' a different quantity random numbers before the test SQL
# is executed.
#

# Make sure the file is much bigger than the pager-cache (10 pages). This
# ensures that cache-spills happen regularly.
do_test crash-3.0 {
  execsql {
    INSERT INTO abc SELECT * FROM abc;
    INSERT INTO abc SELECT * FROM abc;
    INSERT INTO abc SELECT * FROM abc;
    INSERT INTO abc SELECT * FROM abc;
    INSERT INTO abc SELECT * FROM abc;
  }
  expr [file size test.db] / 1024
} {554}
for {set i 1} {$i < $repeats} {incr i} {
  set sig [signature]
  do_test crash-3.$i.1 {
     crashsql [expr $i%5 + 1] test.db-journal "
       BEGIN;
       SELECT random() FROM abc LIMIT $i;
       INSERT INTO abc VALUES(randstr(10,10), 0, 0);
       DELETE FROM abc WHERE random()%10!=0;
       COMMIT;
     "
  } {1 {child process exited abnormally}}
  do_test crash-3.$i.2 {
    signature
  } $sig
}

#--------------------------------------------------------------------------
# The following test cases - crash-4.* - test the correct recovery of the
# database when a crash occurs during a multi-file transaction.
#
# crash-4.1.*: Test recovery when crash occurs during sync() of the
#              main database journal file.
# crash-4.2.*: Test recovery when crash occurs during sync() of an
#              attached database journal file.
# crash-4.3.*: Test recovery when crash occurs during sync() of the master
#              journal file.
#
do_test crash-4.0 {
  file delete -force test2.db
  file delete -force test2.db-journal
  execsql {
    ATTACH 'test2.db' AS aux;
    PRAGMA aux.default_cache_size = 10;
    CREATE TABLE aux.abc2 AS SELECT 2*a as a, 2*b as b, 2*c as c FROM abc;
  }
  expr [file size test2.db] / 1024
} {559}

for {set i 1} {$i<$repeats} {incr i} {
  set sig [signature]
  set sig2 [signature2]
  do_test crash-4.1.$i.1 {
     set c [crashsql $i test.db-journal "
       ATTACH 'test2.db' AS aux;
       BEGIN;
       SELECT random() FROM abc LIMIT $i;
       INSERT INTO abc VALUES(randstr(10,10), 0, 0);
       DELETE FROM abc WHERE random()%10!=0;
       INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
       DELETE FROM abc2 WHERE random()%10!=0;
       COMMIT;
     "]
     set c
  } {1 {child process exited abnormally}}
  do_test crash-4.1.$i.2 {
    signature
  } $sig
  do_test crash-4.1.$i.3 {
    signature2
  } $sig2
}
set i 0
set i 55
while {[incr i]} {
  set sig [signature]
  set sig2 [signature2]
  set ::fin 0
  do_test crash-4.2.$i.1 {
     set c [crashsql $i test2.db-journal "
       ATTACH 'test2.db' AS aux;
       BEGIN;
       SELECT random() FROM abc LIMIT $i;
       INSERT INTO abc VALUES(randstr(10,10), 0, 0);
       DELETE FROM abc WHERE random()%10!=0;
       INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
       DELETE FROM abc2 WHERE random()%10!=0;
       COMMIT;
     "]
     if { $c == {0 {}} } {
       set ::fin 1
       set c {1 {child process exited abnormally}}
     }
     set c
  } {1 {child process exited abnormally}}
  if { $::fin } break
  do_test crash-4.2.$i.2 {
    signature
  } $sig
  do_test crash-4.2.$i.3 {
    signature2
  } $sig2
}
for {set i 1} {$i < 5} {incr i} {
  set sig [signature]
  set sig2 [signature2]
  do_test crash-4.3.$i.1 {
     crashsql 1 test.db-mj* "
       ATTACH 'test2.db' AS aux;
       BEGIN;
       SELECT random() FROM abc LIMIT $i;
       INSERT INTO abc VALUES(randstr(10,10), 0, 0);
       DELETE FROM abc WHERE random()%10!=0;
       INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
       DELETE FROM abc2 WHERE random()%10!=0;
       COMMIT;
     "
  } {1 {child process exited abnormally}}
  do_test crash-4.3.$i.2 {
    signature
  } $sig
  do_test crash-4.3.$i.3 {
    signature2
  } $sig2
}