sqlite3 unable to open network db file in readwrite mode

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3 unable to open network db file in readwrite mode

Lewis Balentine
*First, version numbers:*
My desktop is running: Linux Mint 19.1 with the MATE 1.20.1 user interface,
Kernel 4.15.0-52 Generic x86_64
The network machine is running: Linux Mint 18.3 with the MATE 1.18.0 user
interface, Kernel 4.4.0-141 Generic x86_64
SQLite Version 3.22.0
DB Browser for SQLite Version 3.10.1
Python 3.6.8

*Second, never assume:*
It may not be 'recommended' to access a sqlite database file on a network
share but that makes a lot of assumptions.

*Problem:*
The target sqlite3 database file is on a samba network share and mounted on
desktop with the following command:
sudo mount -o username=[myname],password=[mypw] -t cifs //[ipaddress]/kwpvr
/mnt/kwpvr
The file is marked readwrite for owner, group and others: -rw-rw-rw- 1
[myname] [myname] 40960 Jul 3 19:25 kwpvr3.db
The samba share is marked to allow create, delete and guest access
In Caja I can create, copy, delete, rename any file on that share.

When I open the file in DB Browser for SQLite I can browse the tables but I
can not change anything.
The error is: "attempt to write a readonly database"

In python I use the following line to open a sqlite connection:
sql3conn = sqlite3.connect("file:" + cfg["dbfile"] + "?mode=rw", uri=True)
That opens and works on a local file but not on the mounted file system.
I get the same error as above when trying to update a record.

So I fell back to the lowest common denominator: the SQLite3 command line
utility
Works fine locally but on the mounted file system I get the same error.
sqlite3 "file:/mnt/kwpvr/kwpvr3.db?mode=rw" "UPDATE Configuration SET
KeyValue='test' WHERE key='Active';"

*Question:*
Is this the intended behavior of sqlite3 or is this something unique to the
linux platform?
_______________________________________________
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: sqlite3 unable to open network db file in readwrite mode

Richard Hipp-3
On 7/3/19, Lewis Balentine <[hidden email]> wrote:
> When I open the file in DB Browser for SQLite I can browse the tables but I
> can not change anything.
> The error is: "attempt to write a readonly database"

Is the directory that contains the database file writable?  It needs
to be in order to write to the database file, so that SQLite can
create a rollback journal for transaction control.

--
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: sqlite3 unable to open network db file in readwrite mode

Kees Nuyt
In reply to this post by Lewis Balentine

On Wed, 3 Jul 2019 20:48:42 -0500, Lewis Balentine wrote:

> *First, version numbers:*
> My desktop is running: Linux Mint 19.1 with the MATE 1.20.1 user interface,
> Kernel 4.15.0-52 Generic x86_64
> The network machine is running: Linux Mint 18.3 with the MATE 1.18.0 user
> interface, Kernel 4.4.0-141 Generic x86_64
> SQLite Version 3.22.0
> DB Browser for SQLite Version 3.10.1
> Python 3.6.8
>
> *Second, never assume:*
> It may not be 'recommended' to access a sqlite database file on a network
> share but that makes a lot of assumptions.
>
> *Problem:*
> The target sqlite3 database file is on a samba network share and mounted on
> desktop with the following command:
> sudo mount -o username=[myname],password=[mypw] -t cifs //[ipaddress]/kwpvr
> /mnt/kwpvr
> The file is marked readwrite for owner, group and others: -rw-rw-rw- 1
> [myname] [myname] 40960 Jul 3 19:25 kwpvr3.db
> The samba share is marked to allow create, delete and guest access
> In Caja I can create, copy, delete, rename any file on that share.
>
> When I open the file in DB Browser for SQLite I can browse the tables but I
> can not change anything.
> The error is: "attempt to write a readonly database"
>
> In python I use the following line to open a sqlite connection:
> sql3conn = sqlite3.connect("file:" + cfg["dbfile"] + "?mode=rw", uri=True)
> That opens and works on a local file but not on the mounted file system.
> I get the same error as above when trying to update a record.
>
> So I fell back to the lowest common denominator: the SQLite3 command line
> utility
> Works fine locally but on the mounted file system I get the same error.
> sqlite3 "file:/mnt/kwpvr/kwpvr3.db?mode=rw" "UPDATE Configuration SET
> KeyValue='test' WHERE key='Active';"
>
> *Question:*
> Is this the intended behavior of sqlite3 or is this something unique to the
> linux platform?

Thanks for the detailed description.
My first thought: Is the directory writable?
It has to be so SQLite can create a journal file.
https://www.sqlite.org/atomiccommit.html#section_3_5

Apart from that, SQLite has to be able to create
files in its TMPDIR.
https://www.sqlite.org/tempfiles.html#temporary_file_storage_locations

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