Option to open a database as read-only despite locked.
(1) By gordon on 2022-05-04 22:04:13 [link] [source]
Sometimes, I would like to browse the "places.sqlite" file of my web browser, but if the browser is running, I get "Error: database is locked".
One could copy the database file somewhere else, but that is less convenient.
I suggest SQLite to at least attempt to read the data while still warning the user that the database is locked.
(2) By Ben Harris (bjh21.me.uk) on 2022-05-08 15:12:09 in reply to 1 [link] [source]
You can do this using a URI filename:
sqlite3 'file:places.sqlite?mode=ro&nolock=1'
That opens the file places.sqlite in read-only mode with locking disabled. This isn't safe, in that changes to the database made by other corrections are likely to cause this connection to return incorrect results or crash. Read-only mode should at least mean that you don't corrupt the database in the process.
(3) By Simon Willison (simonw) on 2022-05-17 19:48:17 in reply to 2 [link] [source]
Thanks for this! Just added a --nolock feature to my Datasette application for browsing SQLite databases thanks to your tip, issue here: https://github.com/simonw/datasette/issues/1744
So now this works:
datasette ~/Library/Application\ Support/Google/Chrome/Default/History --nolock
(4) By liginity on 2025-02-21 05:47:47 in reply to 2 [link] [source]
I find that using the URI filename would not work, even when no one locks the sqlite file.
And removing the nolock param would work with unlocked file.
Could you tell me the reason?
$ sqlite3 'file:places.sqlite?mode=ro&nolock=1' ".tables"
Error: unable to open database file
$ sqlite3 'file:places.sqlite?mode=ro' ".tables"
moz_anno_attributes moz_keywords
moz_annos moz_meta
Information of software:
- sqlite3 3.40.1
- browser: Firefox 128.7.0esr
- os: debian 12
(5) By anonymous on 2025-05-14 16:49:24 in reply to 4 [link] [source]
Same issue here. I have no explanation:
sqlite3 'file:places.sqlite?mode=ro'
sqlite3 'file:places.sqlite?nolock=1'
works well,
But
sqlite3 'file:places.sqlite?mode=ro&nolock=1'
(6) By brickviking on 2025-05-15 08:38:06 in reply to 5 [source]
I had a similar result when I checked out the same-ish file on Fedora, so I'm suspecting it's something specific to Firefox's usage, as I don't have the same problem for a google-chrome-unstable database.
Here's the output I get from sqlite3 'file:places.sqlite?mode=ro&nolock=1':
trace.enabled_for("unix")
trace.xFullPathname("places.sqlite") -> SQLITE_OK, out="/home/user/.mozilla/firefox/yzx203r9.default-release/places.sqlite"
trace.xOpen(places.sqlite,flags=0x141) -> SQLITE_OK, outFlags=0x141
trace.xDeviceCharacteristics(places.sqlite) -> 0x00009000
trace.xFileControl(places.sqlite,MMAP_SIZE,0) -> SQLITE_OK, 0
trace.xDeviceCharacteristics(places.sqlite) -> 0x00009000
trace.xFileControl(places.sqlite,MMAP_SIZE,0) -> SQLITE_OK, 0
trace.xRead(places.sqlite,n=100,ofst=0) -> SQLITE_OK
trace.xFileControl(places.sqlite,BUSYHANDLER) -> SQLITE_NOTFOUND
trace.xFileControl(places.sqlite,MMAP_SIZE,0) -> SQLITE_OK, 0
trace.xFileControl(places.sqlite,PDB) -> SQLITE_NOTFOUND
trace.xRandomness(44)
trace.xFileControl(places.sqlite,PRAGMA,[database_list,]) -> SQLITE_NOTFOUND
trace.xAccess("/home/user/.mozilla/firefox/yzx203r9.default-release/places.sqlite-journal",0) -> SQLITE_OK, out=0
trace.xAccess("/home/user/.mozilla/firefox/yzx203r9.default-release/places.sqlite-wal",0) -> SQLITE_OK, out=0
trace.xFileSize(places.sqlite) -> SQLITE_OK, size=5242880
trace.xRead(places.sqlite,n=32768,ofst=0) -> SQLITE_OK
trace.xDeviceCharacteristics(places.sqlite) -> 0x00009000
trace.xGetLastError(0,zBuf) -> zBuf[] = "", rc = 2
Error: unable to open database file
trace.xFileControl(places.sqlite,HAS_MOVED) -> SQLITE_OK, 0
trace.xDeviceCharacteristics(places.sqlite) -> 0x00009000
trace.xClose(places.sqlite) -> SQLITE_OK
Cheers, brickviking
(Post 62)
(7) By anonymous on 2025-05-15 15:07:54 in reply to 6 [link] [source]
As for more context, I observe this error on totally different databases (out of the places.sqlite of web browsers).
It's hard to quantize, but I observed this behavior on ~95% of the db I use.
I am using python to connect to them, using the standard sqlite3 module > Connecting to the db is always fine (sqlite3#connect), but the Error: unable to open database file happens once executing a query on it.
(8) By brickviking on 2025-05-16 10:30:54 in reply to 7 [link] [source]
This might sound obvious, but is the problem also repeated when you use the sqlite3 binary as I did, whether compiled or deb package?
Also, do you have specific examples you could supply of small databases that exhibit this behaviour?
Regards, RTDoc brickviking
(Post 63)