[sqlite] How to make SQLite run safely on CIFS mounted file system?
Hi, I have mounted a CIFS share like this (cat /proc/mounts)://192.168.0.1/test2 /var/spool/storage/Share cifs rw,relatime,unc=\\192.168.0.1\test2,username=sune,uid=123,forceuid,gid=123,forcegid,addr=192.168.0.1,file_mode=0770,dir_mode=0770,nobrl,rsize=16384,wsize=114688 0 0 My CIFS options ( -o) are: username=sune,password=test,nounix,noserverino,uid=123,gid=123,nobrl,file_mode=0770,dir_mode=0770,port=1 1)I have a database file called data.db on the CIFS mounted share. I log on to sqlite3 from the client side and inserts a row into a table. I and this works fine. 2)I then log on to the server side and remove the data.db file. 3)Again back on the client side I run an ls to make sure the file is really gone, and it is. 4)I run a similar insert as in step 1 and sqlite does not complain(!). I have tried to add directio to my CIFS mount options but the behavior remains the same. If I run the tests on a filesystem other than CIFS, I get the expected:Error: disk I/O error What can I do to make SQLite run safely on CIFS? /Sune ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
2011/10/18 Sune Ahlgren : > What can I do to make SQLite run safely on CIFS? > /Sune Do not use SQLite on shared device. Use client/server database or client/server front-end of SQLite. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren wrote: > What can I do to make SQLite run safely on CIFS? > Nothing. Even MS Access cannot (or could not way back when i used it) be safely used on SMB/CIFS storage. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal wrote: > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren > wrote: >> What can I do to make SQLite run safely on CIFS? > > Nothing. Even MS Access cannot (or could not way back when i used it) be > safely used on SMB/CIFS storage. Can you elaborate as to why? Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the wall: > On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal wrote: > > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren > > wrote: > >> What can I do to make SQLite run safely on CIFS? > > > > Nothing. Even MS Access cannot (or could not way back when i used it) be > > safely used on SMB/CIFS storage. > > Can you elaborate as to why? http://sqlite.org/faq.html#q5 In short: buggy filesystem code that doesn't support distributed locks correctly. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 9:13 PM, Nico Williams wrote: > > Nothing. Even MS Access cannot (or could not way back when i used it) be > > safely used on SMB/CIFS storage. > > Can you elaborate as to why? > > i unfortunately can't, except to say that file locking on networked filesystems has, historically speaking, always been problematic. Communicating the locks between separate machines, race conditions, unclean network connection errors, blah blah blah. That goes for all applications, not just databases. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 9:23 PM, Stephan Beal wrote: > race conditions, unclean network connection errors, blah blah blah. That > goes for all applications, not just databases. > > And not just for CIFS, but NFS and other networked filesystems as well. i'm not personally aware of any (except maybe specialized clustered environments) where file locking is "reliably well-behaved," but if there's one out there someone else on this list certainly knows about it (and will hopefully correct me). -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On 19 Oct 2011, at 8:26pm, Stephan Beal wrote: > On Wed, Oct 19, 2011 at 9:23 PM, Stephan Beal wrote: > >> race conditions, unclean network connection errors, blah blah blah. That >> goes for all applications, not just databases. > > And not just for CIFS, but NFS and other networked filesystems as well. i'm > not personally aware of any (except maybe specialized clustered > environments) where file locking is "reliably well-behaved," but if there's > one out there someone else on this list certainly knows about it (and will > hopefully correct me). There are bugs in the AFS implementation too. Slightly different ones in different versions. Think is, it's possible to do it right. It's just that doing it right means every operation takes two or three times as long as the slightly buggy implementations we have now. And people prefer fast-but-buggy. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 9:40 PM, Simon Slavin wrote: > Think is, it's possible to do it right. It's just that doing it right > means every operation takes two or three times as long as the slightly buggy > implementations we have now. And people prefer fast-but-buggy. > A slight elaboration: Simone does not mean to imply that locking over networked filesystems is necessarily fast per se, but that "doing it right" would be slower than "fast but buggy." i once had a relatively simple app whose runtime increased 100x+ over NFS when locking was enabled, even though it only made a few lock/unlock calls. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 2:23 PM, Jay A. Kreibich wrote: > On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the wall: >> On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal wrote: >> > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren >> > wrote: >> >> What can I do to make SQLite run safely on CIFS? >> > >> > Nothing. Even MS Access cannot (or could not way back when i used it) be >> > safely used on SMB/CIFS storage. >> >> Can you elaborate as to why? > > http://sqlite.org/faq.html#q5 > > > In short: buggy filesystem code that doesn't support distributed > locks correctly. That explains why concurrent access over NFS or CIFS is dangerous. It doesn't explain that it's not possible to use SQLite3 over NFS or CIFS. Also, regarding NFS, it would be safe to use if SQLite3 were to use whole-file byte range locks. NFS makes concurrent access to byte ranges that are not locked unsafe. Of course, safely making changes to how SQLite3 locks files may be difficult now... CIFS does support byte range locking, and I suspect that the same rule I mentioned above regarding NFS applies. Plus, as a last resort there's CIFS open deny modes that SQLite3 could use to prevent concurrency. In response to other comments regarding other filesystems, I'll point out that on Lustre, because Lustre is fully POSIX-compliant regarding write visibility, SQLite3 should work fine as-is. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 03:24:35PM -0500, Nico Williams scratched on the wall: > On Wed, Oct 19, 2011 at 2:23 PM, Jay A. Kreibich wrote: > > On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the > > wall: > >> On Wed, Oct 19, 2011 at 4:00 AM, Stephan Beal > >> wrote: > >> > On Tue, Oct 18, 2011 at 12:37 PM, Sune Ahlgren > >> > wrote: > >> >> What can I do to make SQLite run safely on CIFS? > >> > > >> > Nothing. Even MS Access cannot (or could not way back when i used it) be > >> > safely used on SMB/CIFS storage. > >> > >> Can you elaborate as to why? > > > > ??http://sqlite.org/faq.html#q5 > > > > > > ??In short: buggy filesystem code that doesn't support distributed > > ??locks correctly. > > That explains why concurrent access over NFS or CIFS is dangerous. For most applications using a database, if there is a moderately high possible loss of data (i.e. "dangerous"), that's the same thing as "not possible." "Not a very good idea," at minimum. > It doesn't explain that it's not possible to use SQLite3 over NFS or > CIFS. As with concurrent access, it is *possible*, just dangerous. Which is "not possible" for most applications. Even if concurrency isn't an issue in your specific case, locks and synchronization tend to go hand in hand. Correct SQLite transactions depend on the ability to verify data has been physically written to storage. Most network file systems don't do this correctly. Heck... a lot of physical hard drives don't do this correctly. See: http://sqlite.org/lockingv3.html#how_to_corrupt http://sqlite.org/atomiccommit.html#sect_9_0 > Also, regarding NFS, it would be safe to use if SQLite3 were to use > whole-file byte range locks. NFS makes concurrent access to byte > ranges that are not locked unsafe. Of course, safely making changes > to how SQLite3 locks files may be difficult now... > > CIFS does support byte range locking, and I suspect that the same rule > I mentioned above regarding NFS applies. Plus, as a last resort > there's CIFS open deny modes that SQLite3 could use to prevent > concurrency. Just because the standards include such features, you seem to be under the impression that every client and every server running on top of any filesystem actually implement said features correctly, and that every combination of client and server interacts and operates correctly. Needless to say, it just ain't so. Not by a long-shot. As for modifying SQLite, the locking mechanism is actually somewhat modular, and I believe SQLite already includes the ability to some types of range locks. That still doesn't do you any good if the filesystem doesn't answer the APIs correctly. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 19/10/11 13:24, Nico Williams wrote: > That explains why concurrent access over NFS or CIFS is dangerous. It > doesn't explain that it's not possible to use SQLite3 over NFS or > CIFS. The simple answer is that they don't provide *exactly* the same behaviour and semantics when using the same system calls accessing files remotely versus locally. This is typically due to bugs in their implementations in code paths that aren't often used. > Also, regarding NFS, it would be safe to use if SQLite3 were to use > whole-file byte range locks. NFS has other issues. For example NFS lock daemons had a tendency to crash when dealing with offsets in the 2GB range. Samba has code to translate locks from the crash sensitive range to elsewhere. > Of course, safely making changes to how SQLite3 locks files may be > difficult now... It is impossible since a different version of SQLite could also be accessing the file on the client or the server. > CIFS does support byte range locking, and I suspect that the same rule > I mentioned above regarding NFS applies. CIFS locking is worlds apart from NFS locking with many differences. One of the hardest parts of writing a CIFS server for Unix is mapping into the limited locking on Unix (which is substantially similar to NFS locking). > Plus, as a last resort there's CIFS open deny modes that SQLite3 could > use to prevent concurrency. The UNIX VFS has something similar already implemented and could be ported to the Windows VFS. See section 2: http://www.sqlite.org/vfs.html > In response to other comments regarding other filesystems, I'll point > out that on Lustre, because Lustre is fully POSIX-compliant regarding > write visibility, SQLite3 should work fine as-is. The problem isn't necessarily that the filesystems work as documented, but rather that the sequence of operations that SQLite does is somewhat unusual compared to normal programs and hence hits various obscure bugs as has been repeatedly found by user experience. There is both client and server side code where those bugs could lie. It is virtually impossible to prove the absence of bugs, so if you want to risk your data then go ahead, but expect an "I told you so" if you end up with corruption. And since you are already using networking there are numerous database products that already work that way and won't have the issues. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6fS+gACgkQmOOfHg372QQENACeJrNUpnFgSIvLlgNrRICOF6Oc ajgAnRoBSwRk0uKQ/0D3i+zAmU2sEqnZ =yIct -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On Wed, Oct 19, 2011 at 4:16 PM, Jay A. Kreibich wrote: > On Wed, Oct 19, 2011 at 03:24:35PM -0500, Nico Williams scratched on the wall: >> Also, regarding NFS, it would be safe to use if SQLite3 were to use >> whole-file byte range locks. NFS makes concurrent access to byte >> ranges that are not locked unsafe. Of course, safely making changes >> to how SQLite3 locks files may be difficult now... >> >> CIFS does support byte range locking, and I suspect that the same rule >> I mentioned above regarding NFS applies. Plus, as a last resort >> there's CIFS open deny modes that SQLite3 could use to prevent >> concurrency. > > Just because the standards include such features, you seem to be > under the impression that every client and every server running on > top of any filesystem actually implement said features correctly, > and that every combination of client and server interacts and > operates correctly. I assume that? I'm aware that the Linux NFS client did not support distributed byte range locking prior to kernerl 2.6.12. That doesn't mean that SQLite3 couldn't have an option to do locking in a way that would work with NFS -- it just means that it'd be unsafe to use SQLite3 on NFS on older Linux distros with any concurrency, that the application would need additional locking (like, e.g., Firefox does or used to). > As for modifying SQLite, the locking mechanism is actually somewhat > modular, and I believe SQLite already includes the ability to some > types of range locks. That still doesn't do you any good if the > filesystem doesn't answer the APIs correctly. True, but one would have to make sure that older releases of SQLite3 don't open a DB that uses a lock protocol that they don't support. That can be done by, e.g., having a different magic, but I think we'd all want the new thing to NOT be the default. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?
On 19 Oct 2011, at 11:20pm, Nico Williams wrote: > On Wed, Oct 19, 2011 at 4:16 PM, Jay A. Kreibich wrote: >> Just because the standards include such features, you seem to be >> under the impression that every client and every server running on >> top of any filesystem actually implement said features correctly, >> and that every combination of client and server interacts and >> operates correctly. > > I assume that? I'm aware that the Linux NFS client did not support > distributed byte range locking prior to kernerl 2.6.12. That doesn't > mean that SQLite3 couldn't have an option to do locking in a way that > would work with NFS -- it just means that it'd be unsafe to use > SQLite3 on NFS on older Linux distros with any concurrency, that the > application would need additional locking (like, e.g., Firefox does or > used to). I started off with that impression but it turns out that pretty much all implementations of everything have bugs in the locking code. Forget 'older Linux distros', we're talking distros yet to be issued. If you actually demand proper implementation of range-locking you're out of luck. Fixing the bugs slows them down to the point everyone would complain about how stuff has slowed down. I spent some time on this before concluding that it would be simpler to write a server/client overlay for SQLite, and that would yield faster results than getting all the locking code working properly for any of the big well-known network file systems. People who know what I work with know I'm a huge Apple fan and when I give up on getting AFS working right you know there's something seriously wrong with it. A server/client architecture has the additional advantage of platform interoperability, and that you can use PRAGMAs to turn off some of the features of SQLite that slow things down. You just turn file sharing off and assume that your server has sole access to the file. >> As for modifying SQLite, the locking mechanism is actually somewhat >> modular, and I believe SQLite already includes the ability to some >> types of range locks. That still doesn't do you any good if the >> filesystem doesn't answer the APIs correctly. > > True, but one would have to make sure that older releases of SQLite3 > don't open a DB that uses a lock protocol that they don't support. > That can be done by, e.g., having a different magic, but I think we'd > all want the new thing to NOT be the default. Putting SQLite aside I wrote a program which generated a list of commands to test the network APIs: random ranges to lock, things to write to the file, ranges to unlock, things to read from the file. Couldn't get it to work on modern implementations of anything: SMB, NFS, AFP. Always found situations where range locks were ignored by another client, or unlocks apparently got executed without having any effect. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users