Database corruption

wqmul
Hi

About a week ago Zotero on my main computer reported a database corruption.

I suspect it's because I foolishly tried to save something to Zotero using a bookmarklet on a browser on another device.

Anyway, I managed to sync my online library to a different computer for backup. So at least I have my database, in the state it was in about a week ago, online and on this different computer.

The corrupted database file is too big to upload to the online repair tool.

I tried the manual instructions from https://forums.zotero.org/discussion/2607/

I couldn't get the 'PRAGMA auto_vacuum' to work and had to create the repaired.sqlite database first. But even after that I kept getting errors like:
Error: near line 746547: UNIQUE constraint failed: fulltextWords.word
Error: near line 746548: UNIQUE constraint failed: fulltextWords.word

My question is, what's the best way to fix this please?

If I copy over the database file from the different computer that I'd synced up, to overwrite the corrupt database on my main computer, would that work? (although that use a different storage location for Zotero - on my main computer I use an external drive where it's worked fine for years so I don't think that caused the issue - but are the paths etc in the database file relative?)

Or should I just uninstall Zotero, reinstall, and then sync to get back what I had a week ago, and go on from there?

Thanks for any suggestions.
  • dstillman
    I suspect it's because I foolishly tried to save something to Zotero using a bookmarklet on a browser on another device.
    No, that wouldn't have anything to do with this. Why would that be foolish?

    If you're storing your data directory on an external drive, the corruption is most likely from unmounting the drive while Zotero was open.
    If I copy over the database file from the different computer that I'd synced up, to overwrite the corrupt database on my main computer, would that work?
    You can copy zotero.sqlite from the other computer, or you can just delete zotero.sqlite on this computer and sync to pull down the data from the online library. Don't uninstall anything.
    on my main computer I use an external drive where it's worked fine for years so I don't think that caused the issue - but are the paths etc in the database file relative?
    Everything in the data directory is relative, so you don't have to worry about stored files. If you use linked files, you'd want to make sure that you had set the Linked Attachment Base Directory properly on the other computer and on this one so that the paths are stored relative to that directory.
  • wqmul
    Thank you very much Dan for your fast reply.

    I had never tried saving from a different device using a bookmarklet before (no Zotero installed, just logged in to Zotero on the browser), and I thought maybe that was why it had corrupted. It seems it was coincidental bad timing then, if you don't think that was the cause, and it's helpful to that you confirmed this is safe to do.

    But I never unmount the external drive while Zotero is open, not intentionally anyway. My PC froze while Zotero was open/trying to sync and I had to power off holding down the power button, could that have been the cause of the corruption?

    Thanks for the suggestion to delete zotero.sqlite from this computer then sync. Just to be sure, that's close Zotero, delete zotero.sqlite from this computer, open Zotero and sync? And it will open OK even with that file deleted?

    And with respect to linked files, if the folder names are different on each computer (different usernames), would it still work please if I try copying from the other computer?

    Again, thank you very much for your help.
  • dstillman
    edited June 26, 2020
    My PC froze while Zotero was open/trying to sync and I had to power off holding down the power button, could that have been the cause of the corruption?
    Yes, that's entirely possible.
    Just to be sure, that's close Zotero, delete zotero.sqlite from this computer, open Zotero and sync? And it will open OK even with that file deleted?
    Yes. Zotero will create an empty database at startup and warn you that you're syncing to an empty database. You can just say yes, and it will pull down the data from your online library. Your files already exist locally and will just remain where they are.
    And with respect to linked files, if the folder names are different on each computer (different usernames), would it still work please if I try copying from the other computer?
    First, to be clear, you don't need to copy anything other than the zotero.sqlite file. You'll already have all the attachment files you had before on this computer, since only the database itself (zotero.sqlite) was corrupted.

    But files aren't really relevant here. You're getting the same data, with the same references to files, whether you copy the database or use sync.
  • wqmul
    Thanks Dan, I'm trying the sync now. Decided against trying to copy across the other zotero.sqlite file, just in case.

    There was no warning about empty database that I could see, although it was certainly very empty when I opened it after deleting that database file! I just checked that my login details were still there and then clicked the Sync button.

    It's doing it now, fingers crossed...
  • wqmul
    Hi Dan, just to report that it seems to have worked, thanks very much for all your help!
Sign In or Register to comment.