Manual database repair taking a long time (Zotero 6.0.26)

tshiunghan · August 2, 2023

I am having problems with my zotero.sqlite and zotero.sqlite.tmp. Though zotero.sqlite.bak is 178.6 MB, zotero.sqlite.tmp is 39.8 GB and zotero.sqlite is 8.5 TB (yes, terabytes).

Oddly, filelight says the file is only 181.2 MB (I'm using Manjaro KDE). But I tried backing up the whole data directory to a 1.5 TB hard drive, and got an insufficient space warning. And yet, my internal hard drive is only 250 GB, so I'm not sure what to believe.

Since the file is too large to upload to Zotero Database Repair Tool (https://www.zotero.org/utils/dbfix/), I followed the instructions here (https://www.zotero.org/utils/dbfix/manual) to repair the database.

After entering the command "sqlite3 zotero.sqlite .recover > dump.sql," the dump.sql file was created and hasn't been written to for over 30 minutes, but the cursor's still hanging. No command prompt, which means the operation isn't done yet, right? Makes sense, I would assume it would take a long time to read a 8.5 TB database, let alone repair it.

I tried to open dump.sql with the the DB browser (https://sqlitebrowser.org) but it's asking for a passphrase.

How should I proceed here? Would it be safe to enter the second terminal command (mv zotero.sqlite zotero.sqlite.old) without waiting for the first command to finish running?

dstillman · August 2, 2023

Those file sizes are definitely lies. I would give up on those and just swap in a copy of zotero.sqlite.bak. And if you've been syncing, Zotero will just download everything since that backup anyway (and would do so even with an empty database).

tshiunghan · August 3, 2023

The manual database repair completed overnight and I was able to execute the other two commands. I checked database integrity and there were no errors. After which, I deleted zotero.sqlite.old and zotero.sqlite.tmp.

I suppose it's safe to delete dump.sql as well?

Thanks for your help!

dstillman · August 3, 2023

I still wouldn’t use that version if you have a choice — it could easily have other problems that you’ll run into later. Again, if you were syncing, you don’t even really need to restore anything, but you could use zotero.sqlite.bak and save a few minutes of download time.

Yes, you can delete dump.sql and any .tmp files.

tshiunghan · August 3, 2023

Thanks for the suggestion. It was straightforward to repair the .bak file from a few days ago. After opening Zotero with the new sqlite file, all the entries (including annotations, notes and attachments) that I added after the last backup were automatically re-added. Good to go!