1 # 2001 September 15

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.

    12 #

    13 # The focus of this file is testing the ability of the database to

    14 # uses its rollback journal to recover intact (no database corruption)

    15 # from a power failure during the middle of a COMMIT.  The OS interface

    16 # modules are overloaded using the modified I/O routines found in test6.c.  

    17 # These routines allow us to simulate the kind of file damage that 

    18 # occurs after a power failure.

    19 #

    20 # $Id: crash.test,v 1.27 2008/01/08 15:18:52 drh Exp $

    21 

    22 set testdir [file dirname $argv0]

    23 source $testdir/tester.tcl

    24 

    25 ifcapable !crashtest {

    26   finish_test

    27   return

    28 }

    29 

    30 set repeats 100

    31 #set repeats 10

    32 

    33 # The following procedure computes a "signature" for table "abc".  If

    34 # abc changes in any way, the signature should change.  

    35 proc signature {} {

    36   return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]

    37 }

    38 proc signature2 {} {

    39   return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc2}]

    40 }

    41 

    42 #--------------------------------------------------------------------------

    43 # Simple crash test:

    44 #

    45 # crash-1.1: Create a database with a table with two rows.

    46 # crash-1.2: Run a 'DELETE FROM abc WHERE a = 1' that crashes during

    47 #            the first journal-sync.

    48 # crash-1.3: Ensure the database is in the same state as after crash-1.1.

    49 # crash-1.4: Run a 'DELETE FROM abc WHERE a = 1' that crashes during

    50 #            the first database-sync.

    51 # crash-1.5: Ensure the database is in the same state as after crash-1.1.

    52 # crash-1.6: Run a 'DELETE FROM abc WHERE a = 1' that crashes during

    53 #            the second journal-sync.

    54 # crash-1.7: Ensure the database is in the same state as after crash-1.1.

    55 #

    56 # Tests 1.8 through 1.11 test for crashes on the third journal sync and

    57 # second database sync.  Neither of these is required in such a small test

    58 # case, so these tests are just to verify that the test infrastructure

    59 # operates as expected.

    60 #

    61 do_test crash-1.1 {

    62   execsql {

    63     CREATE TABLE abc(a, b, c);

    64     INSERT INTO abc VALUES(1, 2, 3);

    65     INSERT INTO abc VALUES(4, 5, 6);

    66   }

    67   set ::sig [signature]

    68   expr 0

    69 } {0}

    70 for {set i 0} {$i<10} {incr i} {

    71   set seed [expr {int(abs(rand()*10000))}]

    72   do_test crash-1.2.$i {

    73     crashsql -delay 1 -file test.db-journal -seed $seed {

    74       DELETE FROM abc WHERE a = 1;

    75     }

    76   } {1 {child process exited abnormally}}

    77   do_test crash-1.3.$i {

    78     signature

    79   } $::sig

    80 }

    81 do_test crash-1.4 {

    82   crashsql -delay 1 -file test.db {

    83     DELETE FROM abc WHERE a = 1;

    84   }

    85 } {1 {child process exited abnormally}}

    86 do_test crash-1.5 {

    87   signature

    88 } $::sig

    89 do_test crash-1.6 {

    90   crashsql -delay 2 -file test.db-journal {

    91     DELETE FROM abc WHERE a = 1;

    92   }

    93 } {1 {child process exited abnormally}}

    94 do_test crash-1.7 {

    95   catchsql {

    96     SELECT * FROM abc;

    97   }

    98 } {0 {1 2 3 4 5 6}}

    99 

   100 do_test crash-1.8 {

   101   crashsql -delay 3 -file test.db-journal {

   102     DELETE FROM abc WHERE a = 1;

   103   }

   104 } {0 {}}

   105 do_test crash-1.9 {

   106   catchsql {

   107     SELECT * FROM abc;

   108   }

   109 } {0 {4 5 6}}

   110 do_test crash-1.10 {

   111   crashsql -delay 2 -file test.db {

   112     DELETE FROM abc WHERE a = 4;

   113   }

   114 } {0 {}}

   115 do_test crash-1.11 {

   116   catchsql {

   117     SELECT * FROM abc;

   118   }

   119 } {0 {}}

   120 

   121 #--------------------------------------------------------------------------

   122 # The following tests test recovery when both the database file and the the

   123 # journal file contain corrupt data. This can happen after pages are

   124 # written to the database file before a transaction is committed due to

   125 # cache-pressure.

   126 #

   127 # crash-2.1: Insert 18 pages of data into the database.

   128 # crash-2.2: Check the database file size looks ok.

   129 # crash-2.3: Delete 15 or so pages (with a 10 page page-cache), then crash.

   130 # crash-2.4: Ensure the database is in the same state as after crash-2.1.

   131 #

   132 # Test cases crash-2.5 and crash-2.6 check that the database is OK if the 

   133 # crash occurs during the main database file sync. But this isn't really

   134 # different from the crash-1.* cases.

   135 #

   136 do_test crash-2.1 {

   137   execsql { BEGIN }

   138   for {set n 0} {$n < 1000} {incr n} {

   139     execsql "INSERT INTO abc VALUES($n, [expr 2*$n], [expr 3*$n])"

   140   }

   141   execsql { COMMIT }

   142   set ::sig [signature]

   143   execsql { SELECT sum(a), sum(b), sum(c) from abc }

   144 } {499500 999000 1498500}

   145 do_test crash-2.2 {

   146   expr ([file size test.db] / 1024)>16

   147 } {1}

   148 do_test crash-2.3 {

   149   crashsql -delay 2 -file test.db-journal {

   150     DELETE FROM abc WHERE a < 800;

   151   }

   152 } {1 {child process exited abnormally}}

   153 do_test crash-2.4 {

   154   signature

   155 } $sig

   156 do_test crash-2.5 {

   157   crashsql -delay 1 -file test.db {

   158     DELETE FROM abc WHERE a<800;

   159   }

   160 } {1 {child process exited abnormally}}

   161 do_test crash-2.6 {

   162   signature

   163 } $sig

   164 

   165 #--------------------------------------------------------------------------

   166 # The crash-3.* test cases are essentially the same test as test case

   167 # crash-2.*, but with a more complicated data set. 

   168 #

   169 # The test is repeated a few times with different seeds for the random

   170 # number generator in the crashing executable. Because there is no way to

   171 # seed the random number generator directly, some SQL is added to the test

   172 # case to 'use up' a different quantity random numbers before the test SQL

   173 # is executed.

   174 #

   175 

   176 # Make sure the file is much bigger than the pager-cache (10 pages). This

   177 # ensures that cache-spills happen regularly.

   178 do_test crash-3.0 {

   179   execsql {

   180     INSERT INTO abc SELECT * FROM abc;

   181     INSERT INTO abc SELECT * FROM abc;

   182     INSERT INTO abc SELECT * FROM abc;

   183     INSERT INTO abc SELECT * FROM abc;

   184     INSERT INTO abc SELECT * FROM abc;

   185   }

   186   expr ([file size test.db] / 1024) > 450

   187 } {1}

   188 for {set i 1} {$i < $repeats} {incr i} {

   189   set sig [signature]

   190   do_test crash-3.$i.1 {

   191      set seed [expr {int(abs(rand()*10000))}]

   192      crashsql -delay [expr $i%5 + 1] -file test.db-journal -seed $seed "

   193        BEGIN;

   194        SELECT random() FROM abc LIMIT $i;

   195        INSERT INTO abc VALUES(randstr(10,10), 0, 0);

   196        DELETE FROM abc WHERE random()%10!=0;

   197        COMMIT;

   198      "

   199   } {1 {child process exited abnormally}}

   200   do_test crash-3.$i.2 {

   201     signature

   202   } $sig

   203 } 

   204 

   205 #--------------------------------------------------------------------------

   206 # The following test cases - crash-4.* - test the correct recovery of the

   207 # database when a crash occurs during a multi-file transaction.

   208 #

   209 # crash-4.1.*: Test recovery when crash occurs during sync() of the 

   210 #              main database journal file.

   211 # crash-4.2.*: Test recovery when crash occurs during sync() of an 

   212 #              attached database journal file.

   213 # crash-4.3.*: Test recovery when crash occurs during sync() of the master

   214 #              journal file. 

   215 #

   216 ifcapable attach {

   217   do_test crash-4.0 {

   218     file delete -force test2.db

   219     file delete -force test2.db-journal

   220     execsql {

   221       ATTACH 'test2.db' AS aux;

   222       PRAGMA aux.default_cache_size = 10;

   223       CREATE TABLE aux.abc2 AS SELECT 2*a as a, 2*b as b, 2*c as c FROM abc;

   224     }

   225     expr ([file size test2.db] / 1024) > 450

   226   } {1}

   227   

   228   set fin 0

   229   for {set i 1} {$i<$repeats} {incr i} {

   230     set seed [expr {int(abs(rand()*10000))}]

   231     set sig [signature]

   232     set sig2 [signature2]

   233     do_test crash-4.1.$i.1 {

   234        set c [crashsql -delay $i -file test.db-journal -seed $::seed "

   235          ATTACH 'test2.db' AS aux;

   236          BEGIN;

   237          SELECT randstr($i,$i) FROM abc LIMIT $i;

   238          INSERT INTO abc VALUES(randstr(10,10), 0, 0);

   239          DELETE FROM abc WHERE random()%10!=0;

   240          INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);

   241          DELETE FROM abc2 WHERE random()%10!=0;

   242          COMMIT;

   243        "]

   244        if { $c == {0 {}} } {

   245          set ::fin 1

   246          set c {1 {child process exited abnormally}}

   247        }

   248        set c

   249     } {1 {child process exited abnormally}}

   250     if {$::fin} break

   251     do_test crash-4.1.$i.2 {

   252       signature

   253     } $sig

   254     do_test crash-4.1.$i.3 {

   255       signature2

   256     } $sig2

   257   } 

   258   set i 0

   259   set fin 0

   260   while {[incr i]} {

   261     set seed [expr {int(abs(rand()*10000))}]

   262     set sig [signature]

   263     set sig2 [signature2]

   264     set ::fin 0

   265     do_test crash-4.2.$i.1 {

   266        set c [crashsql -delay $i -file test2.db-journal -seed $::seed "

   267          ATTACH 'test2.db' AS aux;

   268          BEGIN;

   269          SELECT randstr($i,$i) FROM abc LIMIT $i;

   270          INSERT INTO abc VALUES(randstr(10,10), 0, 0);

   271          DELETE FROM abc WHERE random()%10!=0;

   272          INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);

   273          DELETE FROM abc2 WHERE random()%10!=0;

   274          COMMIT;

   275        "]

   276        if { $c == {0 {}} } {

   277          set ::fin 1

   278          set c {1 {child process exited abnormally}}

   279        }

   280        set c

   281     } {1 {child process exited abnormally}}

   282     if { $::fin } break

   283     do_test crash-4.2.$i.2 {

   284       signature

   285     } $sig

   286     do_test crash-4.2.$i.3 {

   287       signature2

   288     } $sig2

   289   } 

   290   for {set i 1} {$i < 5} {incr i} {

   291     set sig [signature]

   292     set sig2 [signature2]

   293     do_test crash-4.3.$i.1 {

   294        crashsql -delay 1 -file test.db-mj* "

   295          ATTACH 'test2.db' AS aux;

   296          BEGIN;

   297          SELECT random() FROM abc LIMIT $i;

   298          INSERT INTO abc VALUES(randstr(10,10), 0, 0);

   299          DELETE FROM abc WHERE random()%10!=0;

   300          INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);

   301          DELETE FROM abc2 WHERE random()%10!=0;

   302          COMMIT;

   303        "

   304     } {1 {child process exited abnormally}}

   305     do_test crash-4.3.$i.2 {

   306       signature

   307     } $sig

   308     do_test crash-4.3.$i.3 {

   309       signature2

   310     } $sig2

   311   }

   312 }

   313 

   314 #--------------------------------------------------------------------------

   315 # The following test cases - crash-5.* - exposes a bug that existed in the

   316 # sqlite3pager_movepage() API used by auto-vacuum databases.

   317 # database when a crash occurs during a multi-file transaction. See comments

   318 # in test crash-5.3 for details.

   319 #

   320 db close

   321 file delete -force test.db

   322 sqlite3 db test.db

   323 do_test crash-5.1 {

   324   execsql {

   325     CREATE TABLE abc(a, b, c);                          -- Root page 3

   326     INSERT INTO abc VALUES(randstr(1500,1500), 0, 0);   -- Overflow page 4

   327     INSERT INTO abc SELECT * FROM abc;

   328     INSERT INTO abc SELECT * FROM abc;

   329     INSERT INTO abc SELECT * FROM abc;

   330   }

   331 } {}

   332 do_test crash-5.2 {

   333   expr [file size test.db] / 1024

   334 } [expr [string match [execsql {pragma auto_vacuum}] 1] ? 11 : 10]

   335 set sig [signature]

   336 do_test crash-5.3 {

   337 # The SQL below is used to expose a bug that existed in

   338 # sqlite3pager_movepage() during development of the auto-vacuum feature. It

   339 # functions as follows:

   340 # 

   341 # 1: Begin a transaction.

   342 # 2: Put page 4 on the free-list (was the overflow page for the row deleted).

   343 # 3: Write data to page 4 (it becomes the overflow page for the row inserted).

   344 #    The old page 4 data has been written to the journal file, but the

   345 #    journal file has not been sync()hronized.

   346 # 4: Create a table, which calls sqlite3pager_movepage() to move page 4

   347 #    to the end of the database (page 12) to make room for the new root-page.

   348 # 5: Put pressure on the pager-cache. This results in page 4 being written

   349 #    to the database file to make space in the cache to load a new page. The

   350 #    bug was that page 4 was written to the database file before the journal

   351 #    is sync()hronized.

   352 # 6: Commit. A crash occurs during the sync of the journal file.

   353 #

   354 # End result: Before the bug was fixed, data has been written to page 4 of the

   355 # database file and the journal file does not contain trustworthy rollback

   356 # data for this page.

   357 #

   358   crashsql -delay 1 -file test.db-journal {

   359     BEGIN;                                             -- 1

   360     DELETE FROM abc WHERE oid = 1;                     -- 2

   361     INSERT INTO abc VALUES(randstr(1500,1500), 0, 0);  -- 3

   362     CREATE TABLE abc2(a, b, c);                        -- 4

   363     SELECT * FROM abc;                                 -- 5

   364     COMMIT;                                            -- 6

   365   }

   366 } {1 {child process exited abnormally}}

   367 integrity_check crash-5.4

   368 do_test crash-5.5 {

   369   signature

   370 } $sig

   371 

   372 #--------------------------------------------------------------------------

   373 # The following test cases - crash-6.* - test that a DROP TABLE operation

   374 # is correctly rolled back in the event of a crash while the database file

   375 # is being written. This is mainly to test that all pages are written to the

   376 # journal file before truncation in an auto-vacuum database.

   377 #

   378 do_test crash-6.1 {

   379   crashsql -delay 1 -file test.db {

   380     DROP TABLE abc;

   381   }

   382 } {1 {child process exited abnormally}}

   383 do_test crash-6.2 {

   384   signature

   385 } $sig

   386 

   387 #--------------------------------------------------------------------------

   388 # These test cases test the case where the master journal file name is 

   389 # corrupted slightly so that the corruption has to be detected by the

   390 # checksum.

   391 do_test crash-7.1 {

   392   crashsql -delay 1 -file test.db {

   393     ATTACH 'test2.db' AS aux;

   394     BEGIN;

   395     INSERT INTO abc VALUES(randstr(1500,1500), 0, 0);

   396     INSERT INTO abc2 VALUES(randstr(1500,1500), 0, 0);

   397     COMMIT;

   398   }

   399 

   400   # Change the checksum value for the master journal name.

   401   set f [open test.db-journal a]

   402   fconfigure $f -encoding binary

   403   seek $f [expr [file size test.db-journal] - 12]

   404   puts -nonewline $f "\00\00\00\00"

   405   close $f

   406 } {}

   407 do_test crash-7.2 {

   408   signature

   409 } $sig

   410 

   411 finish_test