Cannot empty Trash [Report ID: 531678683]

oce

There are many child items in my trash folder. I try to delete them all my Ctrl-A and then select delete. Try empty trash. No response. Then if I try delete it one by one, it kept having this error and requires me to quit and restart zotero.

Thanks for your help in following up.

dstillman

database disk image is malformed

Your database is corrupted, which generally happens from having your Zotero data directory in a cloud storage folder or network drive. If that's the case, you should move it back to the default location. You should never store the data for any database-backed program in cloud storage.

You can try to fix the damage with the DB Repair Tool, or, if this was in cloud storage or have a local backup, you can restore from an earlier version of zotero.sqlite. If that doesn't work, and your data is all online, you can just delete the zotero.sqlite file (or move it somewhere temporarily for backup) with Zotero closed and then reopen Zotero and sync to pull down your library.

oce

Thanks dstillman for your quick solution!
As my database exceed db repair tool, i followed the instructions on https://forums.zotero.org/discussion/2607/

I got a zotero.repaired.sqlite but it is 0KB. My original size is 144,672KB... :( so it doesn't work. It has the following error messages:
Error: near line 207923: UNIQUE constraint failed: fulltextWords.wordID
Error: near line 207924: UNIQUE constraint failed: fulltextWords.wordID
.... (repeat for a few extra time)
Error: near line 210038: UNIQUE constraint failed: fulltextWords.word
Error: near line 210514: UNIQUE constraint failed: fulltextWords.word

I don't have a back up of the file because this trash problem was developed long time ago I believed. I just never attempt to empty trash before....

dstillman

Based on the location of the corruption, one thing you can try is clearing your full-text index from the Search pane of the Zotero preferences, though it may not work for the same reason. (If it does work, you can reindex your attachments after.)

Make a backup of your database before trying that.

If you're not successful with that, we might be able to increase the limit on the online repair tool.

oce

Thanks so much Dstillman, I can't clear the index, the following error shows up:

Error: Error(s) encountered during statement execution: database disk image is malformed [QUERY: DELETE FROM fulltextItemWords] [PARAMS: ] [ERROR: database disk image is malformed]

dstillman

I've increased the limit to 150 MB online. See if you can get yours to go through now.

oce

Thanks a lot. I have successfully repaired the database! And all trashes are emptied!

dstillman

OK, great.

jellyturtle

My database is 215 MB (grad school...) so I had to find a local workaround for the "UNIQUE constraint failed: fulltextWords.wordID" error. And I did!

When the .dump command encounters corruption (which I think in this case is caused by multiple rows with the same wordID, the integer primary key for the fulltextWords table - the result of the full-text index), it adds a ROLLBACK command. (It also apparently stops partway through, I only got 240 MB out in dump.sql). So when you run the dump.sql commands, it interprets all the commands, but then rolls back the zotero.sqlite database to a 0 byte file.

Instead of .dump, use .recover (introduced in sqlite 3.29.0). This does not insert a rollback command, and also gave me a bigger (444 MB) dump.sql. https://www.sqlite.org/cli.html#recover_data_from_a_corrupted_database

The commands are then:
sqlite3 zotero.sqlite .recover > dump.sql
mv zotero.sqlite zotero.sqlite.old
sqlite3 zotero.sqlite < dump.sql

This fixed the corrupted Zotero database. Now back to writing.

dstillman

@jellyturtle: Huh, I hadn't seen that new command. We'll update the repair tool and the instructions to use that instead of .dump. Thanks for letting us know.