Multiple processes, same database: "Exception: attempt to write a readonly database Unable to fetch row"

FiddleWiddle

Hi,

I am new here and need some help with a database exception that I get when two processes acces the same sqlite database simultaneously. Thie first process is doing fine, but the second somehow does not get write access and throws exceptions on every attempt to write something.
Now I wonder if this is impossible in general or what I am doing wrong in particular.

Some more information that might be relevant:

Platform is Windows
Qt for Python
The second process is started from a different machine and accesses the database via network. Read and write access for the directory is given
I don't have any query or database objects as members, all of them exist only in their scope and I call finish() on all queries once they are not needed any longer.
I set db.setConnectOptions('QSQLITE_BUSY_TIMEOUT=3000') but this didn't do anything I guess.

Any help or best practices that I missed are very much appreciated!

Thanks in advance!

SGaist

Hi and welcome to devnet,

Please read the When To Use page of sqlite's documentation.

Your use case is one that should be avoided.

Kent-Dorfman

SQLlite would not be my choice for a concurrent access networked database...just sayin. I prefer PostgreSQL.

JonB

@FiddleWiddle
Further too the above two comments. FWIW, I came across https://charlesleifer.com/blog/sqlite-small-fast-reliable-choose-any-three-/

When would SQLite not be a good choice?
Multiple servers communicating with the database over the network, or situations in which you plan to run your database on a separate server.
The main consideration there, I think, is when you have multiple web-servers and need to connect to your database over the network. While you can use SQLite over an NFS mount, for instance, the SQLite documentation seems to indicate that this may be buggy.

So cross-machine access over a network seems to be potentially "flaky"....

FiddleWiddle

Thanks for all your answers!
I read https://www.sqlite.org/faq.html#q5 and was under the assumption that this might still be doable, considering that my application very rarely writes to the database. And it appears that my second client always gets the exception mentioned above. As if the first client would lock the database forever without performing any writes. Is that possible?
But the point @JonB mentioned seems to critical indeed.
In that case considering that the application already exists would you recommend another database that handles this situation well or would you adjust the application to be a client/server one with only the server having the SQLite database locally? Or anything else?

JonB

@FiddleWiddle

would you recommend another database that handles this situation well

If you are saying you are willing to move off SQLite as the RDBMS, why invent your own architecture trying to talk to a server application which in turn does SQLite when, say, MySQL or MariaDB are available and already work client/server and are supported from Qt? Or, I know nothing about it other than it's "object-relational" (and multi-user), but there is @Kent-Dorfman's recommendation of PostgreSQL, and that too is supported from Qt.

FiddleWiddle

I am willing to do anything that fixes my problem :)
I have no experience with those other databases that is why I ask. Please correct me if I am wrong, but I imagine the biggest task when using the new database is converting the existing data to the new structure and maybe fix some SQL syntax. On the other hand when switching to client server, I would introduce a new layer for the database access (using maybe Pyro for RPC, but also no experience) and leave the database itself untouched.
I am currently reading some PostgreSQL documentation to get some more insights there.

Edit: Oh and one addtional question: All other database systems require additional software to be installed, correct?

JonB

@FiddleWiddle
As I said I don't know about PostgreSQL, I'm imagining that (although it does SQL) being "object-relational" the point of using it might be to change what you do a bit to take advantage or work with it.

OTOH, if you are looking for the least work from where you are now, my thought would be that SQLite -> MySQL (or possibly MariaDB) might be the simplest. Googling for sqlite move/migrate to mysql or similar seems a good start, hits like:

https://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql
https://github.com/weewx/weewx/wiki/Transfer-from-sqlite-to-MySQL

might be of use to you.

All other database systems require additional software to be installed, correct?

Yep, 'fraid so.

SGaist

An SQL database system shall support the SQL standard. Some may have other features but that is another story.

Unless you are doing something highly SQLite specific, your Qt code will only change in the connections parameters.

Yes you will have to install additional software and do some administration setup stuff.

FiddleWiddle

Thanks a lot everyone!
I will close this topic as it seems my question is fully answered.