Re: [sqlite] Safe to use SQLite over a sketchy network?

[Rowan Worth]

On Thu, 26 Sep 2019 at 13:01, Jens Alfke  wrote:

>
> > On Sep 24, 2019, at 3:48 PM, Keith Medcalf  wrote:
> >
> > There are not, to my knowledge, any client/server database systems that
> will work properly if the database resides on a network filesystem (meaning
> remote multi-access).  The "client" is remote from the "server" because the
> "client" and "server" use some sort of IPC mechanism (of which a network is
> an example) so that the "client" can send commands to and receive responses
> from the "server".
>
> Well, obviously. “Client/server” means databases like MySQL or Oracle. No
> one would run those with the server using a networked file system.
>

Haha, you'd be surprised!

https://blogs.msdn.microsoft.com/varund/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive/

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Gary R. Schmidt]

On 26/09/2019 15:30, Keith Medcalf wrote:

-Original Message-
From: sqlite-users  On
Behalf Of Gary R. Schmidt
Sent: Wednesday, 25 September, 2019 23:13
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Safe to use SQLite over a sketchy network?

On 26/09/2019 15:00, Jens Alfke wrote:



On Sep 24, 2019, at 3:48 PM, Keith Medcalf 

wrote:


There are not, to my knowledge, any client/server database systems

that will work properly if the database resides on a network filesystem
(meaning remote multi-access).  The "client" is remote from the "server"
because the "client" and "server" use some sort of IPC mechanism (of
which a network is an example) so that the "client" can send commands to
and receive responses from the "server".


Well, obviously. “Client/server” means databases like MySQL or Oracle.

No one would run those with the server using a networked file system.



I might have dreamt it, but NetAPP had an add-on for Oracle, quite
probably still do, that enhanced performance and behaviour when used for
storage.

Not sure if it was applied to the server or the NAS, possibly both, but
it was an Oracle-recommended solution for large storage requirements.


[Top-posting fixed.]
>
> That is remote block storage, not a remote filesystem.  Remote block
> storage just has a "longer wire" going to the block storage.  iSCSI,
> for example, will let you put your block storage anywhere, even in
> orbit.  The filesystem, however, still resides on the local computer.
>
No, it was over NFS, and while it could be used to give Oracle a "raw 
partition," it was intended to be used as a file-system that could be 
managed by the NAS, and allowed for the files to accessed by more than 
one Oracle server instance.


This was before we got Oracle RAC, I should mention.

Cheers,
GaryB-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Keith Medcalf]

That is remote block storage, not a remote filesystem.  Remote block storage 
just has a "longer wire" going to the block storage.  iSCSI, for example, will 
let you put your block storage anywhere, even in orbit.  The filesystem, 
however, still resides on the local computer.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Gary R. Schmidt
>Sent: Wednesday, 25 September, 2019 23:13
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Safe to use SQLite over a sketchy network?
>
>On 26/09/2019 15:00, Jens Alfke wrote:
>>
>>> On Sep 24, 2019, at 3:48 PM, Keith Medcalf 
>wrote:
>>>
>>> There are not, to my knowledge, any client/server database systems
>that will work properly if the database resides on a network filesystem
>(meaning remote multi-access).  The "client" is remote from the "server"
>because the "client" and "server" use some sort of IPC mechanism (of
>which a network is an example) so that the "client" can send commands to
>and receive responses from the "server".
>>
>> Well, obviously. “Client/server” means databases like MySQL or Oracle.
>No one would run those with the server using a networked file system.
>>
>I might have dreamt it, but NetAPP had an add-on for Oracle, quite
>probably still do, that enhanced performance and behaviour when used for
>storage.
>
>Not sure if it was applied to the server or the NAS, possibly both, but
>it was an Oracle-recommended solution for large storage requirements.
>
>   Cheers,
>   GaryB-)
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Gary R. Schmidt]

On 26/09/2019 15:00, Jens Alfke wrote:



On Sep 24, 2019, at 3:48 PM, Keith Medcalf  wrote:

There are not, to my knowledge, any client/server database systems that will work properly if the database resides on a network filesystem 
(meaning remote multi-access).  The "client" is remote from the "server" because the "client" and 
"server" use some sort of IPC mechanism (of which a network is an example) so that the "client" can send commands to 
and receive responses from the "server".


Well, obviously. “Client/server” means databases like MySQL or Oracle. No one 
would run those with the server using a networked file system.

I might have dreamt it, but NetAPP had an add-on for Oracle, quite 
probably still do, that enhanced performance and behaviour when used for 
storage.


Not sure if it was applied to the server or the NAS, possibly both, but 
it was an Oracle-recommended solution for large storage requirements.


Cheers,
GaryB-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Jens Alfke]

> On Sep 24, 2019, at 3:48 PM, Keith Medcalf  wrote:
> 
> There are not, to my knowledge, any client/server database systems that will 
> work properly if the database resides on a network filesystem (meaning remote 
> multi-access).  The "client" is remote from the "server" because the "client" 
> and "server" use some sort of IPC mechanism (of which a network is an 
> example) so that the "client" can send commands to and receive responses from 
> the "server".

Well, obviously. “Client/server” means databases like MySQL or Oracle. No one 
would run those with the server using a networked file system.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Gary R. Schmidt]

On 25/09/2019 15:36, Rowan Worth wrote:

On Wed, 25 Sep 2019 at 12:58, Simon Slavin  wrote:


When I first learned the SQLite had problems with Network File Systems I
read a ton of stuff to learn why there doesn't seem to be a Network File
Systems that implements locking properly. 

Still, I wonder why someone working on a Linux network file system, or
APFS, or ZFS, hasn't done it.



I'm not sure what your definition of "locking properly" is or when your
research was done, but POSIX advisory locks¹ work just fine on linux over
nfs (since at least v3) and lustre.

¹ That's the F_SETLK/F_GETLK/F_SETLKW commands via the fcntl() syscall,
which is also sqlite's default locking mechanism under UNIX.

I don't see it as that much of a problem, I've been locking 
database-type files over NFS/RFS/DECNET since the 1980s, and SMB since 
the 1990s.


Now, there have been a *lot* of crappy implementations of NFS out there, 
probably the crappiest currently in use is the Linux version, but it is 
better than it used to be (I wonder if sharing a file system still 
causes the entire NFS server to re-start), and let's not mention the 
reasoning behind, "Why should we drop back to NFSv3 if the NFSv4 
initiation fails?"


Although I have had to convince a few people of the right /way/ to take 
out a lock...


Cheers,
GaryB-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Rowan Worth]

On Wed, 25 Sep 2019 at 12:58, Simon Slavin  wrote:

> When I first learned the SQLite had problems with Network File Systems I
> read a ton of stuff to learn why there doesn't seem to be a Network File
> Systems that implements locking properly. 
>
> Still, I wonder why someone working on a Linux network file system, or
> APFS, or ZFS, hasn't done it.
>

I'm not sure what your definition of "locking properly" is or when your
research was done, but POSIX advisory locks¹ work just fine on linux over
nfs (since at least v3) and lustre.

¹ That's the F_SETLK/F_GETLK/F_SETLKW commands via the fcntl() syscall,
which is also sqlite's default locking mechanism under UNIX.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Simon Slavin]

When I first learned the SQLite had problems with Network File Systems I read a 
ton of stuff to learn why there doesn't seem to be a Network File Systems that 
implements locking properly.  I ended up with …

A) It slows access a lot.  Even with clever hashing to check for collisions it 
takes time to figure out whether your range is already locked.

B) Different use-cases have different preferences for retry-and-timeout times.  
It's one more thing for admins to configure and many admins don't get it right.

C) It's hard to debug.  There are numerous different orders in which different 
clients can lock and unlock ranges.  You have to run a random simulator to try 
them all.  The logic to deal with them properly is not as simple as you'd 
think.  Consider, for example, ranges which are not identical but do overlap.

D) It's mostly a waste of time.  Most client software doesn't care how to deal 
with a BUSY status and either crashes – which annoys the admin and user – or 
retries immediately – which makes the management CPU hot.  After all, most 
client software just wants to read a whole file or write a whole file.  And if 
two people save the same word processing document at nearly the same time, 
who's to say who was first ?

Still, I wonder why someone working on a Linux network file system, or APFS, or 
ZFS, hasn't done it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Rowan Worth]

On Wed, 25 Sep 2019 at 05:14, Randall Smith  wrote:

> I have an application where remote users will be connecting to a SQLite DB
> over a network connection that seems to be somewhat sketchy (I can't
> characterize it well; I'm hearing this second-hand).
>
> My question is: Do the commit-or-rollback semantics of SQLite transactions
> work if the connection to the file system is weird?  For example, do I get
> a rollback if the connection is lost mid-transaction or something?


SQLite's transaction protocol provides durability in the face of a crash or
severed connection - a partially committed transaction will be rolled back
as you suspected. Whether that comes into play also depends on the
behaviour on the filesystem though; eg. following a "lost connection" some
file servers will still consider the file locked by the disconnected client
until they hear back from said client or a timeout expires. I've seen some
configurations where this seems to be retained indefinitely, locking all
clients out of the DB until the server is restarted.

Also SQLite relies on synchronous i/o operations to provide consistency and
durability, and if the filesystem skimps on these (to try and provide
better performance or whatever) you will likely end up with a corrupt DB.
These synchronous ops tend to become the limiting factor in DB performance
- a write transaction in this environment is quite expensive and you won't
get anywhere close to the write throughput that you would from a
traditional RDMS.

  Or, is the underlying assumption with transactions that the connection
> between SQLite code and file system is 100% reliable?
>

As long as the filesystem provides a consistent view to each client of
events surrounding locks and cache invalidation despite the sketchy network
then you ought to get consistent data coming from sqlite. As others have
said sqlite is very much at the mercy of the filesystem's locking
semantics, and if that is not robust in the face of a sketchy network then
it probably will not be a usable solution.

Source: hundreds of production sqlite DBs on NFS over many years. We have
had a few instances of DB corruption as well as some phantom lock scenarios
so you do have to plan for those, but by and large things work well. That
said we have a reliable network layer, and the write-concurrency caveat is
a real concern.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Keith Medcalf]

On Tuesday, 24 September, 2019 16:30, Jens Alfke  wrote:

>There will be times when a program using SQLite finds itself running over
>a networked filesystem, but no one should deliberately write SQLite-based
>code intending to use a networked filesystem. For that you want a
>client/server database.

There are not, to my knowledge, any client/server database systems that will 
work properly if the database resides on a network filesystem (meaning remote 
multi-access).  The "client" is remote from the "server" because the "client" 
and "server" use some sort of IPC mechanism (of which a network is an example) 
so that the "client" can send commands to and receive responses from the 
"server".  The database files must still reside on the "server"'s local 
filesystem.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Jen Pollock]

On Tue, Sep 24, 2019 at 03:30:00PM -0700, Jens Alfke wrote:
> There will be times when a program using SQLite finds itself running
> over a networked filesystem, but no one should deliberately write
> SQLite-based code intending to use a networked filesystem. For that
> you want a client/server database.

SQLite on a networked filesystem works basically fine if the SQLite
database will only ever be opened read-only. But that's admittedly an
edge case.

Jen Pollock

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Jens Alfke]

> On Sep 24, 2019, at 3:22 PM, Jose Isaias Cabrera  wrote:
> 
> Even on a great network, you can have problems, so when you say "sketchy", 
> then definitely there will be problems.

And even with a perfect network and perfect networked filesystem, it's still 
possible to get denial-of-service behavior where one client begins a 
transaction, takes out a lock on the file, and then for one reason or another 
never ends the transaction. Maybe the client hangs, or drops into a debugger, 
or is waiting for user input in the middle of a transaction, or the host it's 
on loses its network connection and it takes a long time for the server to time 
out its connection and clean up after it.

Either way, you get a lengthy period where no other client can write to the 
database — it'll lock up, or fail with SQLITE_BUSY errors, or whatever.

There will be times when a program using SQLite finds itself running over a 
networked filesystem, but no one should deliberately write SQLite-based code 
intending to use a networked filesystem. For that you want a client/server 
database.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Jose Isaias Cabrera]

Randall Smith, on Tuesday, September 24, 2019 05:14 PM, wrote...​
> ​
> I have an application where remote users will be connecting to a SQLite DB 
> over a network​
> connection that seems to be somewhat sketchy (I can't characterize it well; 
> I'm hearing​
> this second-hand).​
> ​
> My question is: Do the commit-or-rollback semantics of SQLite transactions 
> work if the​
> connection to the file system is weird?  For example, do I get a rollback if 
> the​
> connection is lost mid-transaction or something?  Or, is the underlying 
> assumption with​
> transactions that the connection between SQLite code and file system is 100% 
> reliable?​
> ​
> I realize this is a bit vague, but if anyone has any experience or insight I 
> would appreciate it.​
​
Even on a great network, you can have problems, so when you say "sketchy", then 
definitely there will be problems. You must make sure that only one writer is 
writing to the DB, though many can read, only one writer.  If you do a search 
for sqlite network usage, you may get some good advice.  Just thinking 
out-loud.  Thanks.​
​
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Jens Alfke]

> On Sep 24, 2019, at 2:14 PM, Randall Smith  wrote:
> 
> My question is: Do the commit-or-rollback semantics of SQLite transactions 
> work if the connection to the file system is weird?

What filesystem is it — SMB, NFS, AFP, …?

I'm not an expert, but in general I think the database file should be OK if the 
connection is dropped, as long as the filesystem has some kind of 'fsync' 
equivalent. The bigger problem is file locking:

> file locking logic is buggy in many network filesystem implementations (on 
> both Unix and Windows). If file locking does not work correctly, two or more 
> clients might try to modify the same part of the same database at the same 
> time, resulting in corruption. Because this problem results from bugs in the 
> underlying filesystem implementation, there is nothing SQLite can do to 
> prevent it.
> 
> A good rule of thumb is to avoid using SQLite in situations where the same 
> database will be accessed directly (without an intervening application 
> server) and simultaneously from many computers over a network.
(from https://www.sqlite.org/whentouse.html 
 )

From what I've heard, this is especially problematic with NFS.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe to use SQLite over a sketchy network?

[Simon Slavin]

On 24 Sep 2019, at 10:14pm, Randall Smith  wrote:

> I have an application where remote users will be connecting to a SQLite DB 
> over a network connection that seems to be somewhat sketchy (I can't 
> characterize it well; I'm hearing this second-hand).
> 
> My question is: Do the commit-or-rollback semantics of SQLite transactions 
> work if the connection to the file system is weird?  For example, do I get a 
> rollback if the connection is lost mid-transaction or something?  Or, is the 
> underlying assumption with transactions that the connection between SQLite 
> code and file system is 100% reliable?

I can't answer your question as stated, but here are a few points which may get 
you further to a conclusion.

SQLite does not support network connections.  It has no networking code.  You 
can't talk to a SQLite database over a network connection.  What you can do is 
write your own frontend and backend software.  In which case, the network 
connection is in your software, not SQLite.

SQLite is written to talk to a database stored in local storage to the 
processor it's running on.  In other words, it talks local file system, not 
network file system.  If your SQLite code is running on computer X, then the 
database should be stored in a filesystem mounted /locally/ on computer X, not 
across a network.  So USB connections are okay, a RAID array mounted over 
FireWire/Thunderbolt is okay, but anything involving IP addresses is not okay.

It might actually work.  You might luck into a network file system that 
correctly supports locking.  But the SQLite developers cannot recommend any.  
This falls under section 2.1 of



which you might find interesting.

In terms of transactions and rollback, SQLite is bulletproof in recovery from 
hardware disconnection (unplugging a local drive) or powerloss of the computer 
it's running on.  When SQLite reopens the database it figures out that a 
transaction was not completed and rolls it all back [1].  However, as with 
normal operation described above, this can be relied on only if the connection 
correctly supports locking.

[1] Of course this works only if the database drive has not suffered file 
system corruption.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users