Network share & disk I/O error

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

Network share & disk I/O error

Robert M. Münch
Hi,

Context: Users of our app can define a working directory where sqlite files are stored. This can be a network share. The files are only used by one user at the time.

We now saw two crash reports with „Database disk I/O error“ when a transaction was closed with „END“. Is this a known problem when having sqlite files on a network share?

I read about the locking quirks on network share but related this to cases where a file could be accessed by several users at the same time and an application trusts the operating system to handle the locking correct. I don’t understand it that way that using sqlite files from a network share is per se impossible.

Any insights on this?

Viele Grüsse.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

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

signature.asc (537 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Network share & disk I/O error

Richard Hipp-3
On 7/12/18, Robert M. Münch <[hidden email]> wrote:
>
> We now saw two crash reports with „Database disk I/O error“ when a
> transaction was closed with „END“. Is this a known problem when having
> sqlite files on a network share?
>

When network filesystems do not follow the usual semantics of a
filesystem, it can cause problems.

That said, Firefox and Chrome have for many years stored lots of stuff
in SQLite database files in the users home directory, which is often a
network share, and they have not reported any problems.  On the other
hand, they (or at least Firefox, I not sure about Chrome) run with
PRAGMA locking_mode=EXCLUSIVE [1] which tends to bypass most of the
network filesystem bugs, at the expense of restricting database access
to a single process at a time.
Is your system able to live with that restriction and use PRAGMA
locking_mode=EXCLUSIVE?

[1] https://www.sqlite.org/pragma.html#pragma_locking_mode

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Network share & disk I/O error

Stephen Chrzanowski
In reply to this post by Robert M. Münch
With Richards comment, the only additional thing I'd weigh in on is what
your remote file server is doing with the physical file.  Virus scanners
can interfere with the usual operation of your program, and can cause
certain things to happen.  Because the OS you're running your software on
is told that the file is written to successfully, it doesn't necessarily
mean that the remote server has actually performed the work.  There are a
lot of new assumptions that are thrown into the mix when dealing with
remote files.

Windows machines are notorious for not allowing things to happen to files
(IE: Delete/erase/rename/etc) while something external has hold of them,
which is a pain when doing file maintenance on larger systems with lots of
remote fingers on said file.  Linux is more liberal with how it handles
file access in that you can do more intentionally damaging things to files
(Such as delete/erase/rename/etc) but the problem with that approach is
that the client can never be sure its got the most recent data available
because the filesystem on the remote end has done something different with
the file compared to what your application is expecting, even with single
user single connection access.


On Thu, Jul 12, 2018 at 12:59 PM, Robert M. Münch <
[hidden email]> wrote:

> Hi,
>
> Context: Users of our app can define a working directory where sqlite
> files are stored. This can be a network share. The files are only used by
> one user at the time.
>
> We now saw two crash reports with „Database disk I/O error“ when a
> transaction was closed with „END“. Is this a known problem when having
> sqlite files on a network share?
>
> I read about the locking quirks on network share but related this to cases
> where a file could be accessed by several users at the same time and an
> application trusts the operating system to handle the locking correct. I
> don’t understand it that way that using sqlite files from a network share
> is per se impossible.
>
> Any insights on this?
>
> Viele Grüsse.
>
> --
>
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
>
> Saphirion AG
> smarter | better | faster
>
> http://www.saphirion.com
> http://www.nlpp.ch
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
_______________________________________________
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: Network share & disk I/O error

Robert M. Münch
In reply to this post by Richard Hipp-3
On 12 Jul 2018, at 19:11, Richard Hipp wrote:

> When network filesystems do not follow the usual semantics of a
> filesystem, it can cause problems.
>
> That said, Firefox and Chrome have for many years stored lots of stuff
> in SQLite database files in the users home directory, which is often a
> network share, and they have not reported any problems.

Hi, ok, that’s good news. So it’s definitely possible to get working.

> On the other hand, they (or at least Firefox, I not sure about Chrome) run with
> PRAGMA locking_mode=EXCLUSIVE [1] which tends to bypass most of the
> network filesystem bugs, at the expense of restricting database access
> to a single process at a time.

Ok, we can try that.

> Is your system able to live with that restriction and use PRAGMA
> locking_mode=EXCLUSIVE?

Yes, that shouldn’t be a problem. Thanks.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

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

signature.asc (537 bytes) Download Attachment