Re: [sqlite] Accesing database remotely

[Roger Binns]

I've also seen Google search results where "optimistic" or "opportunistic"
caching on Windows clients may cause data corruption, in the context of
other file-based database libraries.
Oplocks by themselves won't cause corruption.  The way oplocks work is that
the file server tells the client they are the only ones with the file
open, so the client can cache stuff, doesn't have to send all writes and
locking through immediately etc.  If another client opens the same file,
then that is held up and the first client is asked to relinquish the oplock.
The first client will then send uncommitted data, apply any locks etc
and then the second client will have the open succeed.
This scheme allows significant performance improvements in the situation
where only one machine has a file open, or many machines have a read-only
file open.
The default configuration of Samba and Windows servers is to give out 
oplocks.  Since the Windows CIFS server is part of the kernel, it
can hold up opens by any other process to maintain data integrity.
Samba can ensure that other Samba connections maintain data integrity,
but it can't do so for other UNIX processes.

The lesson is that you should be *very* careful if you have multiple
network protocols to the same file (eg CIFS/SMB and NFS) or are
mixing local and remote access to the same file.
There is also another failure mode which is the client having an
oplock, and telling the process using SQLite that all the data
is safely committed.  Then when the file is closed, or an oplock
break message is received being unable to actually send the data
back to the server, or not doing so in a timely enough manner.
Windows clients always ask for oplocks by default.  Generally you
can cause oplocks not to be held by opening the same file again
with different permissions (eg request read only).  Use ethereal
to verify if the oplock is broken.
Roger

Re: [sqlite] Accesing database remotely

[Ng Pheng Siong]

On Mon, Jan 31, 2005 at 05:09:56PM -0500, Stephen C. Gilardi wrote:
> You may get it to work, but using an sqlite database file on a network 
> file system has been reported to be troublesome.  

FWIW I wrote a test program that wrote to a database file on a network
drive concurrently from several PCs. All PCs ran Win98. I tested with the
network drive being a Win98 share and Samba on FreeBSD (it was a
dual-boot). It worked _for_me_. YMMV.

>  in a section entitled "How To 
> Corrupt Your Database Files":

I've also seen Google search results where "optimistic" or "opportunistic"
caching on Windows clients may cause data corruption, in the context of
other file-based database libraries.

Cheers.

-- 
Ng Pheng Siong <[EMAIL PROTECTED]> 

http://sandbox.rulemaker.net/ngps -+- M2Crypto, ZServerSSL for Zope, Blog
http://www.sqlcrypt.com -+- Database Engine with Transparent AES Encryption

Re: [sqlite] Accesing database remotely

[Stephen C. Gilardi]

You may get it to work, but using an sqlite database file on a network 
file system has been reported to be troublesome.  Here is a quote from 
 in a section entitled "How To 
Corrupt Your Database Files":


SQLite uses POSIX advisory locks to implement locking on Unix. On 
windows it uses the LockFile(), LockFileEx(), and UnlockFile() system 
calls. SQLite assumes that these system calls all work as advertised. 
If that is not the case, then database corruption can result. One 
should note that POSIX advisory locking is known to be buggy or even 
unimplemented on many NFS implementations (including recent versions of 
Mac OS X) and that there are reports of locking problems for network 
filesystems under windows. Your best defense is to not use SQLite for 
files on a network filesystem.


Reliable locking is necessary for sqlite to succeed in allowing more 
than one process to access the database contents without corruption.

There is more discussion of this in the mailing list archives and in 
the sqlite source files.

--Steve
On Jan 31, 2005, at 4:44 PM, Richard Boyd wrote:
Hi,
I’m writing a database that will be accessed remotely over Ethernet. I 
had started to write a protocol that would float on top of TCP/IP but 
after reading some posts here it seems that it would be overkill. Is 
it practical to access the database file remotely and run commands on 
it from the remote PC? Are there going to be problems with sharing 
conflicts with more than one application accessing the database 
simultaneously? I know from reading some Sqlite documentation that 
there is some protection built into Sqlite. Does it work automatically 
or is there something I’d have to do at the operating system level?

FYI: I’m using Linux for the computer where the database is stored and 
windows for the machine that will remotely access the database over 
Ethernet.

Thanks again,
Richard.
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005

[sqlite] Accesing database remotely

[Richard Boyd]

Hi,

 

I’m writing a database that will be accessed remotely
over Ethernet. I had started to write a protocol that would float on top of
TCP/IP but after reading some posts here it seems that it would be overkill. Is
it practical to access the database file remotely and run commands on it from
the remote PC? Are there going to be problems with sharing conflicts with more
than one application accessing the database simultaneously? I know from reading
some Sqlite documentation that there is some protection built into Sqlite. Does
it work automatically or is there something I’d have to do at the
operating system level?

 

FYI: I’m using Linux for the computer where the
database is stored and windows for the machine that will remotely access the
database over Ethernet.

 

Thanks again,

Richard.






No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005