/ Check-in [5c604479]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:If the user has not set it explicitly, set the "PRAGMA synchronous" setting to SQLITE_DEFAULT_SYNCHRONOUS when a database connection changes from wal to rollback journal mode.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 5c604479fda06714500959e121c719d1b6c8e54d1658eb9f560a4af95b7c5829
User & Date: dan 2017-03-16 18:55:21
Context
2017-03-17
03:21
Fix a problem in the enhanced PRAGMA integrity_check where it verifies CHECK constraints: Do not be confused by the reuse of the Table.pCheck field by VIEWs with named columns. Problem discovered by OSS-Fuzz. check-in: 019dd3d5 user: drh tags: trunk
2017-03-16
18:55
If the user has not set it explicitly, set the "PRAGMA synchronous" setting to SQLITE_DEFAULT_SYNCHRONOUS when a database connection changes from wal to rollback journal mode. check-in: 5c604479 user: dan tags: trunk
18:54
Fix a typo causing the build to fail if SQLITE_DEFAULT_SYNCHRONOUS==SQLITE_DEFAULT_WAL_SYNCHRONOUS. Closed-Leaf check-in: df39adea user: dan tags: default-synchronous-fix
14:28
Simplified OOM detection in the instr() SQL function. check-in: 6d85eb57 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  2857   2857       BTREE_AUTOVACUUM_INCR
  2858   2858     );
  2859   2859     sqlite3BtreeLeave(p);
  2860   2860     return rc;
  2861   2861   #endif
  2862   2862   }
  2863   2863   
         2864  +/*
         2865  +** If the user has not set the safety-level for this database connection
         2866  +** using "PRAGMA synchronous", and if the safety-level is not already
         2867  +** set to the value passed to this function as the second parameter,
         2868  +** set it so.
         2869  +*/
         2870  +#if SQLITE_DEFAULT_SYNCHRONOUS!=SQLITE_DEFAULT_WAL_SYNCHRONOUS
         2871  +static void setDefaultSyncFlag(BtShared *pBt, u8 safety_level){
         2872  +  sqlite3 *db;
         2873  +  Db *pDb;
         2874  +  if( (db=pBt->db)!=0 && (pDb=db->aDb)!=0 ){
         2875  +    while( pDb->pBt==0 || pDb->pBt->pBt!=pBt ){ pDb++; }
         2876  +    if( pDb->bSyncSet==0 
         2877  +     && pDb->safety_level!=safety_level 
         2878  +     && pDb!=&db->aDb[1] 
         2879  +    ){
         2880  +      pDb->safety_level = safety_level;
         2881  +      sqlite3PagerSetFlags(pBt->pPager,
         2882  +          pDb->safety_level | (db->flags & PAGER_FLAGS_MASK));
         2883  +    }
         2884  +  }
         2885  +}
         2886  +#else
         2887  +# define setDefaultSyncFlag(pBt,safety_level)
         2888  +#endif
  2864   2889   
  2865   2890   /*
  2866   2891   ** Get a reference to pPage1 of the database file.  This will
  2867   2892   ** also acquire a readlock on that file.
  2868   2893   **
  2869   2894   ** SQLITE_OK is returned on success.  If the file is not a
  2870   2895   ** well-formed database file, then SQLITE_CORRUPT is returned.
................................................................................
  2930   2955       */
  2931   2956       if( page1[19]==2 && (pBt->btsFlags & BTS_NO_WAL)==0 ){
  2932   2957         int isOpen = 0;
  2933   2958         rc = sqlite3PagerOpenWal(pBt->pPager, &isOpen);
  2934   2959         if( rc!=SQLITE_OK ){
  2935   2960           goto page1_init_failed;
  2936   2961         }else{
  2937         -#if SQLITE_DEFAULT_SYNCHRONOUS!=SQLITE_DEFAULT_WAL_SYNCHRONOUS
  2938         -        sqlite3 *db;
  2939         -        Db *pDb;
  2940         -        if( (db=pBt->db)!=0 && (pDb=db->aDb)!=0 ){
  2941         -          while( pDb->pBt==0 || pDb->pBt->pBt!=pBt ){ pDb++; }
  2942         -          if( pDb->bSyncSet==0
  2943         -           && pDb->safety_level==SQLITE_DEFAULT_SYNCHRONOUS+1
  2944         -          ){
  2945         -            pDb->safety_level = SQLITE_DEFAULT_WAL_SYNCHRONOUS+1;
  2946         -            sqlite3PagerSetFlags(pBt->pPager,
  2947         -               pDb->safety_level | (db->flags & PAGER_FLAGS_MASK));
  2948         -          }
  2949         -        }
  2950         -#endif
         2962  +        setDefaultSyncFlag(pBt, SQLITE_DEFAULT_WAL_SYNCHRONOUS+1);
  2951   2963           if( isOpen==0 ){
  2952   2964             releasePage(pPage1);
  2953   2965             return SQLITE_OK;
  2954   2966           }
  2955   2967         }
  2956   2968         rc = SQLITE_NOTADB;
         2969  +    }else{
         2970  +      setDefaultSyncFlag(pBt, SQLITE_DEFAULT_SYNCHRONOUS+1);
  2957   2971       }
  2958   2972   #endif
  2959   2973   
  2960   2974       /* EVIDENCE-OF: R-15465-20813 The maximum and minimum embedded payload
  2961   2975       ** fractions and the leaf payload fraction values must be 64, 32, and 32.
  2962   2976       **
  2963   2977       ** The original design allowed these amounts to vary, but as of

Changes to src/ctime.c.

    62     62   #endif
    63     63   #if SQLITE_DEFAULT_LOCKING_MODE
    64     64     "DEFAULT_LOCKING_MODE=" CTIMEOPT_VAL(SQLITE_DEFAULT_LOCKING_MODE),
    65     65   #endif
    66     66   #if defined(SQLITE_DEFAULT_MMAP_SIZE) && !defined(SQLITE_DEFAULT_MMAP_SIZE_xc)
    67     67     "DEFAULT_MMAP_SIZE=" CTIMEOPT_VAL(SQLITE_DEFAULT_MMAP_SIZE),
    68     68   #endif
           69  +#if SQLITE_DEFAULT_SYNCHRONOUS
           70  +  "DEFAULT_SYNCHRONOUS=" CTIMEOPT_VAL(SQLITE_DEFAULT_SYNCHRONOUS),
           71  +#endif
           72  +#if SQLITE_DEFAULT_WAL_SYNCHRONOUS
           73  +  "DEFAULT_WAL_SYNCHRONOUS=" CTIMEOPT_VAL(SQLITE_DEFAULT_WAL_SYNCHRONOUS),
           74  +#endif
    69     75   #if SQLITE_DIRECT_OVERFLOW_READ
    70     76     "DIRECT_OVERFLOW_READ",
    71     77   #endif
    72     78   #if SQLITE_DISABLE_DIRSYNC
    73     79     "DISABLE_DIRSYNC",
    74     80   #endif
    75     81   #if SQLITE_DISABLE_LFS

Changes to src/test_config.c.

   736    736     LINKVAR( MAX_PAGE_SIZE );
   737    737     LINKVAR( MAX_PAGE_COUNT );
   738    738     LINKVAR( MAX_LIKE_PATTERN_LENGTH );
   739    739     LINKVAR( MAX_TRIGGER_DEPTH );
   740    740     LINKVAR( DEFAULT_CACHE_SIZE );
   741    741     LINKVAR( DEFAULT_PAGE_SIZE );
   742    742     LINKVAR( DEFAULT_FILE_FORMAT );
          743  +  LINKVAR( DEFAULT_SYNCHRONOUS );
          744  +  LINKVAR( DEFAULT_WAL_SYNCHRONOUS );
   743    745     LINKVAR( MAX_ATTACHED );
   744    746     LINKVAR( MAX_DEFAULT_PAGE_SIZE );
   745    747     LINKVAR( MAX_WORKER_THREADS );
   746    748   
   747    749     {
   748    750       static const int cv_TEMP_STORE = SQLITE_TEMP_STORE;
   749    751       Tcl_LinkVar(interp, "TEMP_STORE", (char *)&(cv_TEMP_STORE),

Added test/sync2.test.

            1  +# 2017 March 16
            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  +# Specificly, it tests that "PRAGMA synchronous" appears to work.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix sync2
           19  +
           20  +#
           21  +# These tests are only applicable when pager pragma are
           22  +# enabled. Also, since every test uses an ATTACHed database, they
           23  +# are only run when ATTACH is enabled.
           24  +#
           25  +ifcapable !pager_pragmas||!attach {
           26  +  finish_test
           27  +  return
           28  +}
           29  +
           30  +proc execsql_sync {sql} {
           31  +  set s $::sqlite_sync_count
           32  +  set res [execsql $sql]
           33  +  concat [expr $::sqlite_sync_count-$s] $res
           34  +}
           35  +
           36  +proc do_execsql_sync_test {tn sql res} {
           37  +  uplevel [list do_test $tn [list execsql_sync $sql] [list {*}$res]]
           38  +}
           39  +
           40  +#-----------------------------------------------------------------------
           41  +# Tests for journal mode.
           42  +#
           43  +sqlite3 db test.db
           44  +do_execsql_test 1.0 {
           45  +  CREATE TABLE t1(a, b);
           46  +  INSERT INTO t1 VALUES(1, 2);
           47  +}
           48  +
           49  +do_execsql_sync_test 1.1 { INSERT INTO t1 VALUES(3, 4) } 4
           50  +
           51  +# synchronous=normal. So, 1 sync on the directory, 1 on the journal, 1 
           52  +# on the db file. 3 in total.
           53  +do_execsql_test      1.2.1 { PRAGMA main.synchronous = NORMAL }
           54  +do_execsql_test      1.2.2 { PRAGMA main.synchronous }     1
           55  +do_execsql_sync_test 1.2.3 { INSERT INTO t1 VALUES(5, 6) } 3
           56  +
           57  +# synchronous=off. No syncs.
           58  +do_execsql_test      1.3.1 { PRAGMA main.synchronous = OFF }
           59  +do_execsql_test      1.3.2 { PRAGMA main.synchronous }     0
           60  +do_execsql_sync_test 1.3.3 { INSERT INTO t1 VALUES(7, 8) } 0
           61  + 
           62  +# synchronous=full, journal_mode=delete. So, 1 sync on the directory,
           63  +# 2 on the journal, 1 on the db file. 4 in total.
           64  +do_execsql_test      1.4.1 { PRAGMA main.synchronous = FULL }
           65  +do_execsql_test      1.4.2 { PRAGMA main.synchronous }      2
           66  +do_execsql_sync_test 1.4.3 { INSERT INTO t1 VALUES(9, 10) } 4
           67  +
           68  +#-----------------------------------------------------------------------
           69  +# Tests for wal mode.
           70  +#
           71  +do_execsql_test      1.5 { PRAGMA journal_mode = wal } {wal}
           72  +
           73  +# sync=full, journal_mode=wal. One sync on the directory, two on the 
           74  +# wal file.
           75  +do_execsql_sync_test 1.6 { INSERT INTO t1 VALUES(11, 12) } 3
           76  +
           77  +# One sync on the wal file.
           78  +do_execsql_sync_test 1.7 { INSERT INTO t1 VALUES(13, 14) } 1
           79  +
           80  +# No syncs.
           81  +do_execsql_test      1.8.1 { PRAGMA main.synchronous = NORMAL }
           82  +do_execsql_test      1.8.2 { PRAGMA main.synchronous }          1
           83  +do_execsql_sync_test 1.8.3 { INSERT INTO t1 VALUES(15, 16) }    0
           84  +
           85  +# One sync on wal file, one on the db file.
           86  +do_execsql_sync_test 1.9   { PRAGMA wal_checkpoint }  {2 0 3 3}
           87  +
           88  +# No syncs.
           89  +do_execsql_test      1.10.1 { PRAGMA main.synchronous = OFF }
           90  +do_execsql_test      1.10.2 { PRAGMA main.synchronous }          0
           91  +do_execsql_sync_test 1.10.3 { INSERT INTO t1 VALUES(17, 18) }    0
           92  +
           93  +#-----------------------------------------------------------------------
           94  +# Tests for the compile time settings SQLITE_DEFAULT_SYNCHRONOUS and
           95  +# SQLITE_DEFAULT_WAL_SYNCHRONOUS. These tests only run if the former
           96  +# is set to "2" and the latter to "1". This is not the default, but
           97  +# it is currently the recommended configuration.
           98  +#
           99  +#   https://sqlite.org/compile.html#recommended_compile_time_options
          100  +#
          101  +if {$SQLITE_DEFAULT_SYNCHRONOUS==2 && $SQLITE_DEFAULT_WAL_SYNCHRONOUS==1} {
          102  +
          103  +  db close
          104  +  sqlite3 db test.db
          105  +
          106  +  # Wal mode, sync=normal. The first transaction does one sync on directory,
          107  +  # one on the wal file. The second does no syncs.
          108  +  do_execsql_sync_test 1.11.1 { INSERT INTO t1 VALUES(19, 20) } 2
          109  +  do_execsql_sync_test 1.11.2 { INSERT INTO t1 VALUES(21, 22) } 0
          110  +  do_execsql_test 1.11.3      { PRAGMA main.synchronous }       1
          111  +
          112  +  # One sync on wal file, one on the db file.
          113  +  do_execsql_sync_test 1.12   { PRAGMA wal_checkpoint }  {2 0 2 2}
          114  +
          115  +  # First transaction syncs the wal file once, the second not at all.
          116  +  # one on the wal file. The second does no syncs.
          117  +  do_execsql_sync_test 1.13.1 { INSERT INTO t1 VALUES(22, 23) } 1
          118  +  do_execsql_sync_test 1.13.2 { INSERT INTO t1 VALUES(24, 25) } 0
          119  +
          120  +  do_execsql_test 1.14 { PRAGMA journal_mode = delete } {delete}
          121  +  
          122  +  # Delete mode, sync=full. The first transaction does one sync on 
          123  +  # directory, two on the journal file, one on the db. The second does 
          124  +  # the same.
          125  +  do_execsql_sync_test 1.15.1 { INSERT INTO t1 VALUES(26, 27) } 4
          126  +  do_execsql_sync_test 1.15.2 { INSERT INTO t1 VALUES(28, 29) } 4
          127  +  do_execsql_test 1.15.3      { PRAGMA main.synchronous }       2
          128  +
          129  +  # Switch back to wal mode.
          130  +  do_execsql_test 1.16 { PRAGMA journal_mode = wal } {wal}
          131  +
          132  +  do_execsql_sync_test 1.17.1 { INSERT INTO t1 VALUES(30, 31) } 2
          133  +  do_execsql_sync_test 1.17.2 { INSERT INTO t1 VALUES(32, 33) } 0
          134  +  do_execsql_test 1.17.3      { PRAGMA main.synchronous }       1
          135  +
          136  +  # Now set synchronous=off, then switch back to delete mode. Check
          137  +  # that the db handle is still using synchronous=off.
          138  +  do_execsql_test 1.18.3      { PRAGMA main.synchronous=off }
          139  +  do_execsql_test 1.18 { PRAGMA journal_mode = delete } {delete}
          140  +
          141  +  do_execsql_sync_test 1.19.1 { INSERT INTO t1 VALUES(34, 35) } 0
          142  +  do_execsql_sync_test 1.19.2 { INSERT INTO t1 VALUES(36, 37) } 0
          143  +  do_execsql_test 1.19.3      { PRAGMA main.synchronous }       0
          144  +
          145  +  # Close and reopen the db. Back to synchronous=normal.
          146  +  db close
          147  +  sqlite3 db test.db
          148  +  do_execsql_sync_test 1.20.1 { INSERT INTO t1 VALUES(38, 39) } 4
          149  +  do_execsql_sync_test 1.20.2 { INSERT INTO t1 VALUES(40, 41) } 4
          150  +  do_execsql_test 1.20.3      { PRAGMA main.synchronous }       2
          151  +}
          152  +
          153  +finish_test