/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/btree.c.

2857
2858
2859
2860
2861
2862
2863

























2864
2865
2866
2867
2868
2869
2870
....
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956


2957
2958
2959
2960
2961
2962
2963
    BTREE_AUTOVACUUM_INCR
  );
  sqlite3BtreeLeave(p);
  return rc;
#endif
}



























/*
** Get a reference to pPage1 of the database file.  This will
** also acquire a readlock on that file.
**
** SQLITE_OK is returned on success.  If the file is not a
** well-formed database file, then SQLITE_CORRUPT is returned.
................................................................................
    */
    if( page1[19]==2 && (pBt->btsFlags & BTS_NO_WAL)==0 ){
      int isOpen = 0;
      rc = sqlite3PagerOpenWal(pBt->pPager, &isOpen);
      if( rc!=SQLITE_OK ){
        goto page1_init_failed;
      }else{
#if SQLITE_DEFAULT_SYNCHRONOUS!=SQLITE_DEFAULT_WAL_SYNCHRONOUS
        sqlite3 *db;
        Db *pDb;
        if( (db=pBt->db)!=0 && (pDb=db->aDb)!=0 ){
          while( pDb->pBt==0 || pDb->pBt->pBt!=pBt ){ pDb++; }
          if( pDb->bSyncSet==0
           && pDb->safety_level==SQLITE_DEFAULT_SYNCHRONOUS+1
          ){
            pDb->safety_level = SQLITE_DEFAULT_WAL_SYNCHRONOUS+1;
            sqlite3PagerSetFlags(pBt->pPager,
               pDb->safety_level | (db->flags & PAGER_FLAGS_MASK));
          }
        }
#endif
        if( isOpen==0 ){
          releasePage(pPage1);
          return SQLITE_OK;
        }
      }
      rc = SQLITE_NOTADB;


    }
#endif

    /* EVIDENCE-OF: R-15465-20813 The maximum and minimum embedded payload
    ** fractions and the leaf payload fraction values must be 64, 32, and 32.
    **
    ** The original design allowed these amounts to vary, but as of







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







<
<
<
<
<
<
<
<
|
<
<
<
<
<






>
>







2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
....
2955
2956
2957
2958
2959
2960
2961








2962





2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
    BTREE_AUTOVACUUM_INCR
  );
  sqlite3BtreeLeave(p);
  return rc;
#endif
}

/*
** If the user has not set the safety-level for this database connection
** using "PRAGMA synchronous", and if the safety-level is not already
** set to the value passed to this function as the second parameter,
** set it so.
*/
#if SQLITE_DEFAULT_SYNCHRONOUS!=SQLITE_DEFAULT_WAL_SYNCHRONOUS
static void setDefaultSyncFlag(BtShared *pBt, u8 safety_level){
  sqlite3 *db;
  Db *pDb;
  if( (db=pBt->db)!=0 && (pDb=db->aDb)!=0 ){
    while( pDb->pBt==0 || pDb->pBt->pBt!=pBt ){ pDb++; }
    if( pDb->bSyncSet==0 
     && pDb->safety_level!=safety_level 
     && pDb!=&db->aDb[1] 
    ){
      pDb->safety_level = safety_level;
      sqlite3PagerSetFlags(pBt->pPager,
          pDb->safety_level | (db->flags & PAGER_FLAGS_MASK));
    }
  }
}
#else
# define setDefaultSyncFlag(pBt,safety_level)
#endif

/*
** Get a reference to pPage1 of the database file.  This will
** also acquire a readlock on that file.
**
** SQLITE_OK is returned on success.  If the file is not a
** well-formed database file, then SQLITE_CORRUPT is returned.
................................................................................
    */
    if( page1[19]==2 && (pBt->btsFlags & BTS_NO_WAL)==0 ){
      int isOpen = 0;
      rc = sqlite3PagerOpenWal(pBt->pPager, &isOpen);
      if( rc!=SQLITE_OK ){
        goto page1_init_failed;
      }else{








        setDefaultSyncFlag(pBt, SQLITE_DEFAULT_WAL_SYNCHRONOUS+1);





        if( isOpen==0 ){
          releasePage(pPage1);
          return SQLITE_OK;
        }
      }
      rc = SQLITE_NOTADB;
    }else{
      setDefaultSyncFlag(pBt, SQLITE_DEFAULT_SYNCHRONOUS+1);
    }
#endif

    /* EVIDENCE-OF: R-15465-20813 The maximum and minimum embedded payload
    ** fractions and the leaf payload fraction values must be 64, 32, and 32.
    **
    ** The original design allowed these amounts to vary, but as of

Changes to src/ctime.c.

62
63
64
65
66
67
68






69
70
71
72
73
74
75
#endif
#if SQLITE_DEFAULT_LOCKING_MODE
  "DEFAULT_LOCKING_MODE=" CTIMEOPT_VAL(SQLITE_DEFAULT_LOCKING_MODE),
#endif
#if defined(SQLITE_DEFAULT_MMAP_SIZE) && !defined(SQLITE_DEFAULT_MMAP_SIZE_xc)
  "DEFAULT_MMAP_SIZE=" CTIMEOPT_VAL(SQLITE_DEFAULT_MMAP_SIZE),
#endif






#if SQLITE_DIRECT_OVERFLOW_READ
  "DIRECT_OVERFLOW_READ",
#endif
#if SQLITE_DISABLE_DIRSYNC
  "DISABLE_DIRSYNC",
#endif
#if SQLITE_DISABLE_LFS







>
>
>
>
>
>







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

Changes to src/test_config.c.

736
737
738
739
740
741
742


743
744
745
746
747
748
749
  LINKVAR( MAX_PAGE_SIZE );
  LINKVAR( MAX_PAGE_COUNT );
  LINKVAR( MAX_LIKE_PATTERN_LENGTH );
  LINKVAR( MAX_TRIGGER_DEPTH );
  LINKVAR( DEFAULT_CACHE_SIZE );
  LINKVAR( DEFAULT_PAGE_SIZE );
  LINKVAR( DEFAULT_FILE_FORMAT );


  LINKVAR( MAX_ATTACHED );
  LINKVAR( MAX_DEFAULT_PAGE_SIZE );
  LINKVAR( MAX_WORKER_THREADS );

  {
    static const int cv_TEMP_STORE = SQLITE_TEMP_STORE;
    Tcl_LinkVar(interp, "TEMP_STORE", (char *)&(cv_TEMP_STORE),







>
>







736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
  LINKVAR( MAX_PAGE_SIZE );
  LINKVAR( MAX_PAGE_COUNT );
  LINKVAR( MAX_LIKE_PATTERN_LENGTH );
  LINKVAR( MAX_TRIGGER_DEPTH );
  LINKVAR( DEFAULT_CACHE_SIZE );
  LINKVAR( DEFAULT_PAGE_SIZE );
  LINKVAR( DEFAULT_FILE_FORMAT );
  LINKVAR( DEFAULT_SYNCHRONOUS );
  LINKVAR( DEFAULT_WAL_SYNCHRONOUS );
  LINKVAR( MAX_ATTACHED );
  LINKVAR( MAX_DEFAULT_PAGE_SIZE );
  LINKVAR( MAX_WORKER_THREADS );

  {
    static const int cv_TEMP_STORE = SQLITE_TEMP_STORE;
    Tcl_LinkVar(interp, "TEMP_STORE", (char *)&(cv_TEMP_STORE),

Added test/sync2.test.



















































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
# 2017 March 16
#
# 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.
#
# Specificly, it tests that "PRAGMA synchronous" appears to work.
#

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

#
# These tests are only applicable when pager pragma are
# enabled. Also, since every test uses an ATTACHed database, they
# are only run when ATTACH is enabled.
#
ifcapable !pager_pragmas||!attach {
  finish_test
  return
}

proc execsql_sync {sql} {
  set s $::sqlite_sync_count
  set res [execsql $sql]
  concat [expr $::sqlite_sync_count-$s] $res
}

proc do_execsql_sync_test {tn sql res} {
  uplevel [list do_test $tn [list execsql_sync $sql] [list {*}$res]]
}

#-----------------------------------------------------------------------
# Tests for journal mode.
#
sqlite3 db test.db
do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
}

do_execsql_sync_test 1.1 { INSERT INTO t1 VALUES(3, 4) } 4

# synchronous=normal. So, 1 sync on the directory, 1 on the journal, 1 
# on the db file. 3 in total.
do_execsql_test      1.2.1 { PRAGMA main.synchronous = NORMAL }
do_execsql_test      1.2.2 { PRAGMA main.synchronous }     1
do_execsql_sync_test 1.2.3 { INSERT INTO t1 VALUES(5, 6) } 3

# synchronous=off. No syncs.
do_execsql_test      1.3.1 { PRAGMA main.synchronous = OFF }
do_execsql_test      1.3.2 { PRAGMA main.synchronous }     0
do_execsql_sync_test 1.3.3 { INSERT INTO t1 VALUES(7, 8) } 0
 
# synchronous=full, journal_mode=delete. So, 1 sync on the directory,
# 2 on the journal, 1 on the db file. 4 in total.
do_execsql_test      1.4.1 { PRAGMA main.synchronous = FULL }
do_execsql_test      1.4.2 { PRAGMA main.synchronous }      2
do_execsql_sync_test 1.4.3 { INSERT INTO t1 VALUES(9, 10) } 4

#-----------------------------------------------------------------------
# Tests for wal mode.
#
do_execsql_test      1.5 { PRAGMA journal_mode = wal } {wal}

# sync=full, journal_mode=wal. One sync on the directory, two on the 
# wal file.
do_execsql_sync_test 1.6 { INSERT INTO t1 VALUES(11, 12) } 3

# One sync on the wal file.
do_execsql_sync_test 1.7 { INSERT INTO t1 VALUES(13, 14) } 1

# No syncs.
do_execsql_test      1.8.1 { PRAGMA main.synchronous = NORMAL }
do_execsql_test      1.8.2 { PRAGMA main.synchronous }          1
do_execsql_sync_test 1.8.3 { INSERT INTO t1 VALUES(15, 16) }    0

# One sync on wal file, one on the db file.
do_execsql_sync_test 1.9   { PRAGMA wal_checkpoint }  {2 0 3 3}

# No syncs.
do_execsql_test      1.10.1 { PRAGMA main.synchronous = OFF }
do_execsql_test      1.10.2 { PRAGMA main.synchronous }          0
do_execsql_sync_test 1.10.3 { INSERT INTO t1 VALUES(17, 18) }    0

#-----------------------------------------------------------------------
# Tests for the compile time settings SQLITE_DEFAULT_SYNCHRONOUS and
# SQLITE_DEFAULT_WAL_SYNCHRONOUS. These tests only run if the former
# is set to "2" and the latter to "1". This is not the default, but
# it is currently the recommended configuration.
#
#   https://sqlite.org/compile.html#recommended_compile_time_options
#
if {$SQLITE_DEFAULT_SYNCHRONOUS==2 && $SQLITE_DEFAULT_WAL_SYNCHRONOUS==1} {

  db close
  sqlite3 db test.db

  # Wal mode, sync=normal. The first transaction does one sync on directory,
  # one on the wal file. The second does no syncs.
  do_execsql_sync_test 1.11.1 { INSERT INTO t1 VALUES(19, 20) } 2
  do_execsql_sync_test 1.11.2 { INSERT INTO t1 VALUES(21, 22) } 0
  do_execsql_test 1.11.3      { PRAGMA main.synchronous }       1

  # One sync on wal file, one on the db file.
  do_execsql_sync_test 1.12   { PRAGMA wal_checkpoint }  {2 0 2 2}

  # First transaction syncs the wal file once, the second not at all.
  # one on the wal file. The second does no syncs.
  do_execsql_sync_test 1.13.1 { INSERT INTO t1 VALUES(22, 23) } 1
  do_execsql_sync_test 1.13.2 { INSERT INTO t1 VALUES(24, 25) } 0

  do_execsql_test 1.14 { PRAGMA journal_mode = delete } {delete}
  
  # Delete mode, sync=full. The first transaction does one sync on 
  # directory, two on the journal file, one on the db. The second does 
  # the same.
  do_execsql_sync_test 1.15.1 { INSERT INTO t1 VALUES(26, 27) } 4
  do_execsql_sync_test 1.15.2 { INSERT INTO t1 VALUES(28, 29) } 4
  do_execsql_test 1.15.3      { PRAGMA main.synchronous }       2

  # Switch back to wal mode.
  do_execsql_test 1.16 { PRAGMA journal_mode = wal } {wal}

  do_execsql_sync_test 1.17.1 { INSERT INTO t1 VALUES(30, 31) } 2
  do_execsql_sync_test 1.17.2 { INSERT INTO t1 VALUES(32, 33) } 0
  do_execsql_test 1.17.3      { PRAGMA main.synchronous }       1

  # Now set synchronous=off, then switch back to delete mode. Check
  # that the db handle is still using synchronous=off.
  do_execsql_test 1.18.3      { PRAGMA main.synchronous=off }
  do_execsql_test 1.18 { PRAGMA journal_mode = delete } {delete}

  do_execsql_sync_test 1.19.1 { INSERT INTO t1 VALUES(34, 35) } 0
  do_execsql_sync_test 1.19.2 { INSERT INTO t1 VALUES(36, 37) } 0
  do_execsql_test 1.19.3      { PRAGMA main.synchronous }       0

  # Close and reopen the db. Back to synchronous=normal.
  db close
  sqlite3 db test.db
  do_execsql_sync_test 1.20.1 { INSERT INTO t1 VALUES(38, 39) } 4
  do_execsql_sync_test 1.20.2 { INSERT INTO t1 VALUES(40, 41) } 4
  do_execsql_test 1.20.3      { PRAGMA main.synchronous }       2
}

finish_test