Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?

[Simon Slavin]

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

Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?

[Nico Williams]

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?

[Roger Binns]

-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?

[Jay A. Kreibich]

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?

[Nico Williams]

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?

[Stephan Beal]

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?

[Simon Slavin]

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?

[Stephan Beal]

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?

[Stephan Beal]

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?

[Jay A. Kreibich]

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?

[Nico Williams]

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?

[Stephan Beal]

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?

[Kit]

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

[sqlite] How to make SQLite run safely on CIFS mounted file system?

[Sune Ahlgren]

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