6

I have a program which frequently crashes (Zotero), leaving its SQLite database locked and inaccessible for use by the restarted program unless I reboot (which I really want to avoid). So this does not work (using the generic name, db.sqlite in place of the actual file in my case, zotero.sqlite):

sqlite3 db.sqlite
sqlite> .backup main backup.sqlite
Error: database is locked

Based on an answer here, I tried:

echo ".dump" | sqlite3 db.sqlite | sqlite3 db.sqlite2
mv db.sqlite2 db.sqlite

db.sqlite2 is 0KB so this obviously did not work. Inspired by another thread, I also tried 

echo '.dump' | sqlite3 db.sqlite > db.dump
cat db.dump | sqlite3 db.sqlite2
mv db.sqlite2 db.sqlite

which leads to db.sqlite2 which is slightly smaller in filesize to db.sqlite, and Zotero (the file which requires the database) was not able to recognize its contents.

So this seems like a very brute force way of doing it, but worked:

cp -pv db.sqlite db.sqlite2
rm -f db.sqlite
mv db.sqlite2 db.sqlite

I wonder if there are any drawbacks to this solution and why the other methods are proposed before this one.

Community
  • 1
  • 1
hatmatrix
  • 42,883
  • 45
  • 137
  • 231
  • 2
    Are you sure the crashed process isn't still hanging around? – CL. May 07 '13 at 16:38
  • Well, `ps -aux | grep zotero` gives me `(zotero-bin)` which appears to be a zombie process, and its parent process is `1` so I can't kill it. This explains why I have to `rm -f` it? – hatmatrix May 07 '13 at 18:02
  • `init` automatically reaps zombies. What is the state (`STAT`) of that zotero process? – CL. May 07 '13 at 19:15
  • Thanks @CL, but can I invoke `init`? The stat of the `(zotero-bin)` is `?E`. – hatmatrix May 08 '13 at 00:40
  • `init` *is* pid 1. `E` is not documented; "exiting" or "error"? – CL. May 08 '13 at 11:21
  • Right, so `init` is not properly reaping this zombie. `E` is "The process is trying to exit." (I'm working on OSX so BSD-Unix... https://developer.apple.com/library/mac/#documentation/Darwin/Reference/Manpages/man1/ps.1.html) – hatmatrix May 08 '13 at 16:58
  • Real quick: http://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database http://www.sqlite.org/lockingv3.html http://www.sqlite.org/sharedcache.html http://www.sqlite.org/howtocorrupt.html http://www.sqlite.org/src/doc/trunk/src/os_unix.c However I am on the move at the moment, I'll try some things for you and write a real answer within a couple of hours. :) – Stolas May 27 '13 at 15:00

1 Answers1

2

It may be that for transaction control zotero uses table based locking to prevent database inconsistencies during concurrent access. When one user uses the database it locks the tables so only they can use it and prevent them seeing the database in an inconsistent state. However when it crashes it's not releasing these locks.

In a well setup database upon crashing the transaction should be rolled back (undone) and locks released to prevent data being left in an inconsistent state or the tables being locked and you having to manually release them.

I've read on the internet that storing the database on a Network File System can interfere with SQLites locking mechanism, by changing the database location to a none network location you should be able to avoid these problems unless it's somewhere else.

If it is somewhere else I would recommend making a copy of your database and using the zotero database repair tool to try and identify any faults in set up etc that could cause this and repair them: https://www.zotero.org/utils/dbfix/

I hope this helps.

user1646196
  • 589
  • 6
  • 28