SQLite DB on external USB HD - is it safe?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite DB on external USB HD - is it safe?

Wolfgang Enzinger
Dear group members,

since it's wise to store SQLite databases on local HDs (as opposed to
network filesystems) in order to avoid corruption, I would like to have my
program check if this requirement is fulfilled. I'm on Windows so I use
GetDriveType() for testing the DB path. Now I noticed that this function
returns DRIVE_FIXED also for drives that are actually USB connected
external HD volumes. Anybody got any experiences if this is actually a safe
place for an SQLite-DB? If it's not, any ideas as to how one can
distinguish such drives from "real" HDs?

Any input appreciated.

Wolfgang

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB on external USB HD - is it safe?

Keith Medcalf

On Tuesday, 9 May, 2017 15:02, Wolfgang Enzinger <[hidden email]> enquired,

> since it's wise to store SQLite databases on local HDs (as opposed to
> network filesystems) in order to avoid corruption,

As long as you understand what a "network filesysem" is.  A "network filesystem" is a filesystem that is remote (run on a different computer).  The underlying storage device (and its location) is irrelevant.

SQLite databases need to be stored on "Local Filesystems", not on "Remote/Network Filesystems".  The locality of the underlying storage device is irrelevant.  It could be a "locally-located remote" spinning magnetic disk attached to your computer by a mere 6-inch SATA cable, or a "not so local remote" spinning magnetic disk attached to your computer via a fibre-optic cable spanning the earth (perhaps Fibre-channel, iSCSI, or even SATA over IP).  In either case the protocol used to attach the "remote disk" to the "local computer filesystem" is running a block oriented protocol and is not a "remote filesystem".  The filesystem is running on the self-same computer as is hosting the application using the database.

Conversely, storing an SQLite database on a "remote filesystem" provided by a raspberry pi running Windows that is attached to your computer via a 3-inch ethernet cable and a database on a remote filesystem residing on a Cray computer located on the other side of the planet via NFS is equally subject to corruption because in this case the filesystem resides on a "remote" computer (that is not the same computer on which the application accessing the database resides).

> I would like to have my
> program check if this requirement is fulfilled. I'm on Windows so I use
> GetDriveType() for testing the DB path. Now I noticed that this function
> returns DRIVE_FIXED also for drives that are actually USB connected
> external HD volumes.
> Anybody got any experiences if this is actually a
> safe place for an SQLite-DB?

Is the filesystem running on the same (local) computer running the application accessing the database?  If so, there is no problem.  This is true even for 1000-mile long USB cables where the filesystem is provided by the local computer and not a remote computer.

> If it's not, any ideas as to how one can
> distinguish such drives from "real" HDs?

You can store an SQLite database on a RAMDISK, an SSD, or any of the many thousand of other types of storage devices on which the "local computer" (the one running the application accessing the database) runs the filesystem.  The underlying block storage media is irrelevant.  Only the locality of the filesystem is important.

If Windows reports that the filesystem is "local" then it is OK.  If Windows reports that the filesystem is "remote", then it is ungood.  A "drive mapped to the local computer" is a remote filesystem, for example, and is ungood.  A local filesystem accessing a storage device via a really long cable is still a local filesystem and is good (even though the storage device itself may be located in a very remote part of Siberia, very far away from your computer in sunny Florida).





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB on external USB HD - is it safe?

Wolfgang Enzinger

OK, thanks for the detailed analysis! :-)

Am Tue, 09 May 2017 16:06:28 -0600 schrieb Keith Medcalf:

[...]

> If Windows reports that the filesystem is "local" then it is OK.  If
> Windows reports that the filesystem is "remote", then it is ungood.  A
> "drive mapped to the local computer" is a remote filesystem, for
> example, and is ungood.  A local filesystem accessing a storage device
> via a really long cable is still a local filesystem and is good (even
> though the storage device itself may be located in a very remote part of
> Siberia, very far away from your computer in sunny Florida).

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB on external USB HD - is it safe?

Bill Wade
In reply to this post by Wolfgang Enzinger
I'd say that "local file system" versus "remote file system" is really more
of a shorthand for the requirement that low-level operations such as locks
and reads behave the way that sqlite expects them to behave.



In particular, locks on remote file systems are notorious for poor behavior.



If I perform a sequence of operations like



- I'm running my database application

- I put my operating system to sleep

- I remove the "local" drive that has my database

- Mount it on another machine and do database operations

- Move it back to the original machine

- "wake up" the original machine



It seems entirely plausible that my database application had its locks
"violated", but doesn't get notified of that, so I would say you should do
a lot of research before deciding that is a safe sequence. I haven't tested
it, so I don't know.



That sequence becomes a lot more "plausible" as something that might
happen, when talking about media that is easily moved from
machine-to-machine, such as external USB drives.





-----Original Message-----
>> From: Keith Medcalf [mailto:[hidden email]]
>> Sent: Tuesday, May 9, 2017 17:06
>> To: SQLite mailing list <[hidden email]>
>> Subject: Re: [sqlite] SQLite DB on external USB HD - is it safe?

>> ...

>> If Windows reports that the filesystem is "local" then it is OK.

>> ...
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB on external USB HD - is it safe?

Simon Slavin-3

On 11 May 2017, at 4:12pm, Bill Wade <[hidden email]> wrote:

> I'd say that "local file system" versus "remote file system" is really more
> of a shorthand for the requirement that low-level operations such as locks
> and reads behave the way that sqlite expects them to behave.
>
>
>
> In particular, locks on remote file systems are notorious for poor behavior.

It’s the word 'remote' which is not clearly explained.  In the SQLite documentation, the word 'remote' means you’re accessing it using a networking API not a file system API.  It’s the network storage APIs which tend to do locking badly.

So if you’re talking to a drive using disk access commands it’s local, even if it’s a RAID array in a 19" rack connected using Thunderbolt.  But if you mounted the drive as "SMB:" it’s 'remote' even if it’s physically in your CPU’s box.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite DB on external USB HD - is it safe?

Tim Streater-3
On 11 May 2017, at 23:35, Simon Slavin <[hidden email]> wrote:

> It’s the word 'remote' which is not clearly explained.  In the SQLite
> documentation, the word 'remote' means you’re accessing it using a networking
> API not a file system API.  It’s the network storage APIs which tend to do
> locking badly.

Local: disk I/O is under the control of the computer you are working on.

Remote: disk I/O is under the control of another computer. Your computer makes requests to the other computer, that it perform I/O operations on behalf of your computer. But the other computer decides when and how to make them.



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users