Error: Unable to open the database file

[Dr. Hawkins]

On Wed, Apr 6, 2016 at 6:05 AM, Ray <ray at linkit.com> wrote:

> I thought of downloading it, updating it, and then putting it back on the
> server but that wouldn't work if two users simultaneously did so.  Since
> I'll have many users using the database simultaneously everything has to be
> done on the server.  I know only one user can write to an sqLite database
> at a time, but that only takes about 20 milliseconds if done on the server
> and the other writes get cued, something that wouldn't happen in the
> download/re-upload scenario.
>

You are going past what SQLite is meant to handle, and asking for trouble.

When SQLite writes, it changes a patch of disk (I couldn't tell you how
much).

The other users won't be queued up waiting to write; they'll be getting
failure to open.

You're either going to need a persistent middleware app running on the
server, or to follow the advice of the SQLite team:  use postgres for
something like this.

SQLite is wonderful, but it also knows it's limits.  I use it in-memory,
and as a convenient way to throw backup files.

And depending upon what you're doing, mySQL may not be an appropriate
choice.  In particular, it doesn't handle real transactions.

SQLite and postgres can handle

BEGIN TRANSACTION;

SELECT this from that;

UPDATE that WITH thisstuff;

UPDATE somethingElse WITH that

END TRANSACTION;


whereas mySQL would do this as separate SELECT and  two UPDATEs

If you need either all or none of them to happen (e.g., dependencies and
consistency), mySQL is not your choice.

postgres also means a single 20ms transaction for such things, while mySQL
would be three separate 20ms transactions.


-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462