On Fri, Jan 30, 2015 at 8:07 AM, Mario M. Westphal <m...@mwlabs.de> wrote:

> When a user encounters the problem he/she restores the last working
> backup. I have a few users who faced this problem more than once. Here I
> always assumed some hardware glirch, a shaky USB connection, disk trouble,
> network problems (if the database is held on a Windows server or NAS),
> buggy SAMBA implementations or similar. Often, when asked, users
> ‘remembered’ a power failure, or some other problems with the disk or
> network. Case closed.

*Client/Server model*
It hasn't been mentioned by you yet, but if your software is acting in a
client/server model, ensure that your server is accessing the file LOCALLY
and not at a remote point.  Ensure that you're using the internal SQLite
threading tools and checking every single result for every single call in
the server software.  Do not ever let a remote client directly access to
the database file.

*NAS - Network Attached Storage*

If multiple users are accessing the file that lives on a different
computer, it is remote storage, which means NAS.  Any computer with any
share available on a network, that machine *IS* to be considered a NAS to a
remote machine.  Drobo, FTP, Windows, Unix/Linux,  CIFS/NFS/etc - Whatever
the protocol used, if what you're accessing isn't local to the computer, it
is a NAS.  Windows, Linux, and "Other" network protocols, be it 'buggy'
SAMBA or a Windows file share, it doesn't matter.  *ALL* are prone to
making SQLite have issues.  A single user using a single remote source
should be OK (But I wouldn't trust it), but the SECOND you start throwing
multiple connections at a remote file, you're begging, pleading, and even
offering your first born child to the computer Gods asking for data
problems.  The problem is NOT with Windows, and the problem isn't going to
show up in your event logs anywhere, but with the file sharing protocol
itself at the remote side, and even THAT machine won't make note of bad
file accesses or when a file is accessed.  The remote system isn't properly
releasing the necessary lock information to your computer, which is where
the problem is happening.

Directly from https://www.sqlite.org/howtocorrupt.html

2.0 File locking problemsSQLite uses file locks on the database file, and
on the write-ahead log or WAL file, to coordinate access between concurrent
processes. Without coordination, two threads or processes might try to make
incompatible changes to a database file at the same time, resulting in
database corruption.

2.1 Filesystems with broken or missing lock implementations

SQLite depends on the underlying filesystem to do locking as the
documentation says it will. But some filesystems contain bugs in their
locking logic such that the locks do not always behave as advertised. *This
is especially true of network filesystems and NFS in particular.* If SQLite
is used on a filesystem where the locking primitives contain bugs, and if
two or more threads or processes try to access the same database at the
same time, then database corruption might result.

{Highlighted by me}

Write your software to detect where the file is being loaded from.  If your
software is written for Windows, it is SIMPLISTIC to find out what kind of
drive you're accessing a file from, and it is even MORE simplistic to find
out if you're accessing a file via a UNC (\\system\share) by just looking
at what the full file path your program is loading the file from.  I've
never coded anything under a 'Nix system except for scripts, but there
should be a way to find out if the path you're accessing is remote by
looking at /etc/fstab (or equiv) and track from there.  The moment your
software sees a that it is accessing something OTHER than a file "local to
the computer, be it HDD/SDD/USB", warn the user of possible data
corruption, log that the attempt was made, and go from there on whatever
path you want to proceed.  Proceed with systems running as usual, or, deny
access to the file, or close out of the application entirely.

Confirm, with ABSOLUTE CERTAINTY, that database files that are being used
are on local storage devices and validate that if these files ARE being
accessed locally, THEN maybe start digging into different kinds of
corruption problems.  Removing a machine from your process is going to make
things MUCH easier to diagnose.
sqlite-users mailing list

Reply via email to