Scan error: database is locked

[per...@gmail.com]

First, a thanks to Mark. I've been using sonospy happily for about 8 years.

However, I've just noticed that 6 months ago(!!), my daily update scans started failing, with no changes made to the .db files.

From the log file:

Database: /home/pi/LucyStorage/chickadee/sspy/sonospy/Classical2.db
Scanning: /home/pi/LucyStorage/clmusic
Scannumber: 2161
** EXITING on unhandled exception - See errors/ErrorDump-20231010-113731.txt
python ./gettags.py -d Classical2.db -v /home/pi/LucyStorage/clmusic

From said ErrorDump (edited):

<class 'sqlite3.OperationalError'>

Traceback (most recent call last):
  File "./gettags.py", line 2939, in <module>
    status = main()
  File "./gettags.py", line 2934, in main
    process_dir(path.decode(enc), options, database)
  File "./gettags.py", line 509, in process_dir
    db.commit()
OperationalError: database is locked

This is from today's attempt at a rescan after a clean reboot of my Pi, but it turns out its happening daily since this past February on both of my databases. /home/pi/Lucystorage/chickadee points to a directory mounted from my NAS, but the access permissions are correct, and sonospy is able to write its log files, so I doubt it's a simple permissions issue.

pycpoint operates normally using the (stale, unupdated) database. I no longer recall what if anything I may have been tinkering with elsewhere in my system.

Any idea where to start looking? I've added the verbose flag above to try and trace better, but it seems to choke before it does anything meaningful so the verbose log says nothing useful.

Thanks, Martin

[Mark Henkelis]

It sounds like something else was trying to update the DB file, and is holding it open (I'm assuming you have free space on the drive).

You could run:

    sudo fuser -v /home/pi/LucyStorage/chickadee/sspy/sonospy/Classical2.db

to see if anything has it open.

It's worth copying the file to a new name and running against that too.

Mark.

--
You received this message because you are subscribed to the Google Groups "Sonospy Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sonospy-deve...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/sonospy-devel/e5d9bcca-c9e2-46a7-acbd-aabbb0af4169n%40googlegroups.com.

[per...@gmail.com]

Thanks Mark; impressive you're still willing to answer questions on this after so many years.

Nothing else has it open, and trying a new name failed too. But it triggered a thought - I changed NASes about when the trouble started, and sqlite sometimes has lock problems on remote mounted drives (which is a necessity for running sonospy on a Pi, however). Following https://hakanu.net/python/2023/04/15/sqlite3-over-network-shared-drive-and-database-getting-locked-error/, I used DB Browser to change the .db to WAL journal mode, and Synchronous to normal (from full). I'm not up on the intricacies of SQLite, but was worth a try.

The good news is that my scans now run, but after one scan I now have a huge WAL file that persists after the scan process has terminated. I can get rid of it manually by running PRAGMA wal_checkpoint in DB Browser, but I haven't explored if changes are being preserved properly.

Does any of this ring any bells or trigger some experiences? If not, I'll dig deeper some other day and learn a bit more about WAL; right now I'm just parroting arcane mantras Google search found for me and sorta seem to work....

Martin

[Mark Henkelis]

When you say "trying a new name", do you mean you renamed it or copied it.

I'll have to refresh my memory on the code - I do remember someone else had a similar issue, it's possible that they sent me their DB to check, but they may have just started afresh from a new on and scanned again. DB browser can leave a lock on the DB, so make sure you exit it around changes.

To view this discussion on the web, visit https://groups.google.com/d/msgid/sonospy-devel/f37e52fb-a3a2-44af-aa7d-1a52d5417d30n%40googlegroups.com.

[per...@gmail.com]

I copied the Classical2.db file that was giving lock errors to Classical2dup.db and attempted a scan with the latter as destination (guaranteed not opened by anything else yet...). Same error.

It's possible DB Browser was open when I ran my first scan in WAL mode, the one that generated an anomalously large persistent .db-wal file. I then manually checkpointed that to make it go away. Since then I've run two scheduled daily scans, with no trouble; a small .db-wal file persists but not worrisome (and DB Browser is long closed).

Thanks for looking into this when you have a chance. Assuming my hypothesis of network shared sqlite lock issues, sorted by using .wal mode, checks out, perhaps a concrete help you could give is to suggest where in the code we could verify if the db is open in WAL mode, and if so add a checkpoint on exit to kill .db-wal files, which really don't need to persist. Setting WAL mode could still be manually done in the rare cases needed, just as I did. Just a thought.

Martin