Database integrity error--can't seem to fix

saulthomas

I have a database integrity error that I can't seem to fix. I have been unable to upload my zotero.sqlite file (compressed to a 54MB .zip file) to the online Zotero Database Repair Tool. I've tried different browsers on different computers, but always get "An internal service error occurred. Please try again later."

I've also tried the advanced instructions on both linux as well as on a mac. After putting in the following command:
sqlite3 zotero.sqlite < dump.sql

I get the following results:
Error: near line 147408: column word is not unique
Error: near line 147521: column word is not unique
Error: near line 172004: column word is not unique

After the command has finished running, the command prompt comes back up. I am assuming the return of the command prompt means that the process was finished and the errors within the database were fixed, but perhaps that's not correct.

In any case, when I open up zotero and try and do another database integrity check, I again get result that the database has errors.

Does anyone have any ideas how to correct this? I am not sure how long these errors have been in place, or whether it is safe to use zotero in this state.

dstillman

Are you sure you're replacing the right file? A newly created file really shouldn't be corrupted. You can type "PRAGMA integrity_check;" from within the SQLite client test the database.

Also, where are you storing the database? On a local disk, or on a network share or something like Dropbox?

saulthomas

Fixed! The "PRAGMA integrity_check;" came out with "ok", so I looked over the instructions again. It turns out that when I did the manual integrity check the first time, I failed to run the long list of statements "to correct potential issues resulting from database corruption or bugs in previous versions of Zotero". I was hesitant to try that out before because I didn't understand how to "open your database in an SQLite client and run the following block of statements". (I'm not very familiar with unix or command line tools).

I just asked a unix-using friend of mine, and he told me to input the command as follows (after making sure I wasn't pasting in any line breaks):

sqlite3 zotero.sqlite '[statements removed by D.S. — see the official instructions for the complete set]'

I tried that and it must have corrected the problem, as now the database integrity check within zotero tells me there are no errors within the database.

As to where I'm storing the database, about 2 years ago I stored it in a dropbox folder, but now I store it on my local disk. I stopped using dropbox because it introduced "conflicts" when I would occasionally have two linked computers open at the same time. Maybe this recently discovered integrity error was a holdover from when I was using dropbox?

Thanks much for your help!

dstillman

Maybe this recently discovered integrity error was a holdover from when I was using dropbox?

That's quite possible.