Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

EXTRA DURABLE build setting to improve robustness against corruption #736

Closed
brodycj opened this issue Jan 4, 2018 · 4 comments
Closed

Comments

@brodycj
Copy link
Contributor

brodycj commented Jan 4, 2018

As discussed in http://sqlite.1065341.n5.nabble.com/Is-WAL-mode-more-robust-against-corruption-td99624.html:

From https://www.sqlite.org/pragma.html#pragma_synchronous the default PRAGMA synchronous setting is FULL, wonder if it should be EXTRA instead.

Also waiting for more feedback if anyone else on the sqlite-users mailing list can find any possible cause for database corruption in this project. A couple possible causes for corruption on iOS that were recently fixed:

UPDATE: it is desired to use compile-time option to enable this setting by default, details in comment below

ALSO WANTED: use recent SQLite version (3.22.0) to eliminate possibility of corruption due to outdated SQLite version

@brodycj brodycj changed the title Improve robustness against corruption? Extra durable setting to improve robustness against corruption (??) Jan 12, 2018
@brodycj brodycj changed the title Extra durable setting to improve robustness against corruption (??) Extra durable build option to improve robustness against corruption (??) Jan 12, 2018
@brodycj brodycj changed the title Extra durable build option to improve robustness against corruption (??) EXTRA DURABLE build setting to improve robustness against corruption Jan 12, 2018
@brodycj
Copy link
Contributor Author

brodycj commented Jan 12, 2018

From https://www.sqlite.org/pragma.html#pragma_synchronous the default PRAGMA synchronous setting is FULL, wonder if it should be EXTRA instead.

Probably better to use SQLITE_DEFAULT_SYNCHRONOUS=3 setting ref:

NOTE that it looks like SQLITE_EXTRA_DURABLE is no longer supported ref: https://www.sqlite.org/compile.html#extra_durable (TBD information in https://www.sqlite.org/pragma.html#pragma_synchronous seems to be in conflict)

ADDITIONAL EXPLANATION: As I said after the response I got to http://sqlite.1065341.n5.nabble.com/Is-WAL-mode-more-robust-against-corruption-td99624.html I would expect that a mobile app may be terminated at any time and would not expect advanced notification to be 100% reliable, especially in a hybrid mobile stack, and should be robust against crashes including possible OS crashes due to bugs, battery issues, etc. This is why I am thinking it would be safest to use the compile-time equivalent to PRAGMA synchronous=3 (EXTRA).

brodycj pushed a commit to brodycj/Android-sqlite-ext-native-driver that referenced this issue Mar 22, 2018
brodycj pushed a commit to cbforks/Cordova-sqlite-storage-dependencies-dev that referenced this issue Mar 22, 2018
(cordova-sqlite-storage-dependencies 1.2.0)

using -DSQLITE_DEFAULT_SYNCHRONOUS=3 compile-time setting

ref: storesafe/cordova-sqlite-storage#736
brodycj pushed a commit to cbforks/Cordova-sqlite-storage-dependencies-dev that referenced this issue Mar 22, 2018
(cordova-sqlite-storage-dependencies 1.2.0)

using -DSQLITE_DEFAULT_SYNCHRONOUS=3 compile-time setting

ref: storesafe/cordova-sqlite-storage#736
brodycj pushed a commit to cbforks/Cordova-sqlite-storage-dependencies-dev that referenced this issue Mar 22, 2018
(cordova-sqlite-storage-dependencies 1.2.0)

using -DSQLITE_DEFAULT_SYNCHRONOUS=3 compile-time setting

additional doc fix to cover use of sqlite3.[hc] for macOS

ref: storesafe/cordova-sqlite-storage#736
brodycj pushed a commit to cbforks/Cordova-sqlite-storage-dependencies-dev that referenced this issue Mar 22, 2018
(cordova-sqlite-storage-dependencies 1.2.0)

using -DSQLITE_DEFAULT_SYNCHRONOUS=3 compile-time setting

additional doc fix to cover use of sqlite3.[hc] for macOS

ref: storesafe/cordova-sqlite-storage#736
brodycj pushed a commit to brodycj/cordova-sqlite-ext-deps that referenced this issue Mar 22, 2018
(cordova-sqlite-ext-deps 1.1.0)

using -DSQLITE_DEFAULT_SYNCHRONOUS=3 compile-time setting

also with -DSQLITE_THREADSAFE=1 for Android

ref: storesafe/cordova-sqlite-storage#736
brodycj pushed a commit to brodycj/Cordova-sqlite-storage-common-dev that referenced this issue Mar 22, 2018
Use SQLite 3.22.0 with SQLITE_DEFAULT_SYNCHRONOUS=3 (EXTRA DURABLE)
compile-time setting on all platforms (Android/iOS/macOS/Windows)

ref: storesafe#736
brodycj pushed a commit to brodycj/cordova-sqlite-ext that referenced this issue Mar 23, 2018
Use SQLite 3.22.0 with SQLITE_DEFAULT_SYNCHRONOUS=3 (EXTRA DURABLE)
compile-time setting on all platforms (Android/iOS/macOS/Windows),
using cordova-sqlite-ext-deps 1.1.0

also with SQLITE_THREADSAFE=1 compile-time setting for all platforms

ref: storesafe/cordova-sqlite-storage#736
@brodycj
Copy link
Contributor Author

brodycj commented Mar 23, 2018

Done in cordova-sqlite-storage & cordova-sqlite-ext, still needed in other plugin versions.

Also related: storesafe/cordova-sqlite-storage-help#34

brodycj pushed a commit to storesafe/android-sqlite-evcore-ndk-driver-free that referenced this issue Mar 26, 2018
- doc updates
- sqlite 3.22.0
- build flag updates

Includes SQLITE_DEFAULT_SYNCHRONOUS=3 (EXTRA DURABLE build setting)
ref: storesafe/cordova-sqlite-storage#736
brodycj pushed a commit to brodycj/cordova-sqlite-evcore-free-dependencies that referenced this issue Mar 27, 2018
(cordova-sqlite-evcore-free-dependencies 0.8.2)

includes -DSQLITE_DEFAULT_SYNCHRONOUS=3 (EXTRA DURABLE) compile-time
setting for Android ref: storesafe/cordova-sqlite-storage#736
brodycj pushed a commit to brodycj/cordova-sqlite-evcore-legacy-ext-common-free that referenced this issue Mar 27, 2018
Using cordova-sqlite-evcore-free-dependencies 0.8.2 with
SQLite 3.22.0 with SQLITE_DEFAULT_SYNCHRONOUS=3
(EXTRA DURABLE ref: storesafe/cordova-sqlite-storage#736)
and other build fixes
brodycj pushed a commit to brodycj/cordova-sqlite-evcore-legacy-ext-common-free that referenced this issue Mar 27, 2018
Using cordova-sqlite-evcore-free-dependencies 0.8.2 with
SQLite 3.22.0, with SQLITE_DEFAULT_SYNCHRONOUS=3
(EXTRA DURABLE ref: storesafe/cordova-sqlite-storage#736)
and other build fixes
brodycj pushed a commit to storesafe/cordova-sqlite-evcore-extbuild-free that referenced this issue Mar 27, 2018
SQLite 3.22.0 with SQLITE_DEFAULT_SYNCHRONOUS=3
(EXTRA DURABLE ref: storesafe/cordova-sqlite-storage#736)
and other build fixes
brodycj pushed a commit to brodycj/cordova-sqlite-ext that referenced this issue Apr 10, 2018
- Use cordova-sqlite-ext-deps 1.1.0 with SQLite 3.22.0,
  with Android sqlite-native-driver NDK build in JAR
  (along with other compile-time option updates)
  to resolve issue on cordova-android@7
  ref: storesafe/cordova-sqlite-storage#729
- SQLITE_DEFAULT_SYNCHRONOUS=3 (EXTRA DURABLE) compile-time setting on
  all platforms (Android/iOS/macOS/Windows)
  ref: storesafe/cordova-sqlite-storage#736
- plugin.xml use lib-file for Android sqlite-native-driver NDK build in
  JAR from cordova-sqlite-ext-deps (1.1.0) to resolve issue on
  cordova-android@7 (along with other compile-time option updates)
  ref: storesafe/cordova-sqlite-storage#729
- SQLITE_THREADSAFE=1 for iOS/macOS along with others (Android/Windows)
  ref: storesafe/cordova-sqlite-storage#754
- Enable FTS5 & JSON1 on all platforms
@brodycj
Copy link
Contributor Author

brodycj commented Apr 12, 2018

This setting is now enabled.

@brodycj
Copy link
Contributor Author

brodycj commented Dec 17, 2019

For the sake of clarity, the following build-time definition is used on all platforms: SQLITE_DEFAULT_SYNCHRONOUS=3

This is the equivalent to using the following PRAGMA setting: PRAGMA schema.synchronous = EXTRA

ref: https://www.sqlite.org/pragma.html#pragma_synchronous

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant