Re: [sqlite] Is WAL mode more robust against corruption?

[Chris Brody]

On Thu, Dec 28, 2017 at 3:59 PM, Simon Slavin  wrote:
> On 28 Dec 2017, at 8:10pm, Chris Brody  wrote:
>
>> [...]
>> But I wondered if WAL may be more robust against possible sqlite
>> corruption, in theory or in practice.

After sending I found a couple threads that *seem* to confirm that WAL
mode may be more reliable against possible corruption:
* http://sqlite.1065341.n5.nabble.com/WAL-mode-is-reliable-td41264.html
* https://marc.info/?l=sqlite-users&m=132052237302135&w=2

>> Any comments?
>
> WAL mode makes SQLite neither more nor less liable to corruption.  You should 
> not be seeing corruption in SQLite no matter what mode it’s in.

Thanks Simon for the response. I just found it today, unfortunately
directed to my spam folder. Makes sense, assuming there is nothing
wrong according to the "how to corrupt" checklist.

> If you want a PRAGMA to strengthen against corruption, try "PRAGMA 
> synchronous = FULL":
>
> 

I will guess you meant to try "PRAGMA synchronous = EXTRA" (seems to
be FULL by default). Maybe a good idea in a hybrid mobile application
environment.

> If you are seeing corruption, you might want to see this checklist.
>
> 

Right. The challenge for me is that my users are JavaScript
developers, often with very limited native platform experience.

> Given that you’re running on a phone/tablet, emphasis is on handling 
> suspension (backgrounding) and termination of the app properly.  There are 
> people here experienced with using SQLite on iPhone and Android who can 
> criticise your approach.
>
> If nothing obvious occurs to you, please post some details: which platform, 
> which circumstances, are you doing multi-thread or multi-process, do you 
> check result codes for your API calls, are you getting error results ?
>
> Simon.

In general I would expect that the app may be suspended or terminated
with no advance notice due to the hybrid environment they run in. I
think the Cordova/PhoneGap framework should give some form of
notification but I would not trust this mechanism to be 100% reliable.
I also want the apps to be robust against possible crashes, memory
issues for example. Right now I am thinking it would be safest to use
"PRAGMA synchronous = EXTRA" at the beginning, feedback would be
highly appreciated.

The hybrid JavaScript/native SQLite API component I maintain is
available in the following location:
https://github.com/litehelpers/Cordova-sqlite-storage

This component acts as a bridge between JavaScript and native code on
Android, iOS, and Windows. I am in the middle of some updates and the
documentation really needs cleanup at this point. Right now it is
using one background thread per database on Android, dynamic
background threads on iOS, no form of background threading on Windows.

I already fixed a couple possible causes of corruption on iOS as discussed in:
- https://github.com/litehelpers/Cordova-sqlite-storage/issues/703
- https://github.com/litehelpers/Cordova-sqlite-storage/issues/716

Reports of database corruption have been extremely rare in this
project but I just wanted to check how to make it as safe as possible.

Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is WAL mode more robust against corruption?

[Simon Slavin]

On 28 Dec 2017, at 8:10pm, Chris Brody  wrote:

> I am considering whether or not to recommend the WAL mode for users in the
> PhoneGap sqlite plugin that I maintain. The negative I see is the delays
> that may result at certain points from the need for database checkpoints.
> But I wondered if WAL may be more robust against possible sqlite
> corruption, in theory or in practice. Any comments?

WAL mode makes SQLite neither more nor less liable to corruption.  You should 
not be seeing corruption in SQLite no matter what mode it’s in.  If you want a 
PRAGMA to strengthen against corruption, try "PRAGMA synchronous = FULL":



If you are seeing corruption, you might want to see this checklist.



Given that you’re running on a phone/tablet, emphasis is on handling suspension 
(backgrounding) and termination of the app properly.  There are people here 
experienced with using SQLite on iPhone and Android who can criticise your 
approach.

If nothing obvious occurs to you, please post some details: which platform, 
which circumstances, are you doing multi-thread or multi-process, do you check 
result codes for your API calls, are you getting error results ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is WAL mode more robust against corruption?

[Chris Brody]

Hello,

I am considering whether or not to recommend the WAL mode for users in the
PhoneGap sqlite plugin that I maintain. The negative I see is the delays
that may result at certain points from the need for database checkpoints.
But I wondered if WAL may be more robust against possible sqlite
corruption, in theory or in practice. Any comments?

Thanks and happy 2018!

Chris

https://www.linkedin.com/in/chrisbrody/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users