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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
