Zotero/SQlite database integrity problems - tips for Windows XP users

This post tells you how to fix some errors traceable to a 'database integrity' problem. Firefox3 is expected to makes things better. Till then, best to fix them. If you are a Windows XP user like me, this may help. It isn't comprehensive and I can't vouch for other platforms.

How will you know your database is corrupt?
===================================

Zotero can check its database, if you ask. Navigate to the Advanced pane of the Zotero preferences, under the gear menu in the Zotero toolbar. Press the button marked 'Check database integrity'. After 10-60 seconds (depending on size),an alert will appear. If it's OK it will say 'No errors were found in the database'. If not, it will say 'Errors were found!'

*Other discussions? Check out http://forums.zotero.org/discussion/2220 and http://forums.zotero.org/discussion/1114/

Where is the file that causes the problem?
================================

Zotero stores everything except your actual files and snapshots in a database called 'zotero.sqlite'. It keeps this in your profile directory (see below). This is what gets corrupted.

Where's your profile directory? In 'advanced preferences' dialogue (see above) press 'Show data directory'. This opens an explorer window at the right directory. It should contain a file called 'zotero.sqlite' and another marked 'zotero.sqlite.bak'. You'll need them both.

Fixing the database with the Zotero fix tool
=================================

The Zotero website at http://zotero.org/utils/dbfix lets you upload a faulty database file, and fixes it. I couldn't do this but I think that's a problem specific to my system. It looks as if it's pretty simple when it works. The instructions are on the page.

Watch out for:

*Exit Firefox first. Two bad things to fix while running: motorbikes and programmes.

*Make copies of zotero.sqlite and zotero.sqlite.bak (after you exit Firefox). Better still, backup the whole directory they live in. That way, if things go wrong, you can always get back to where you were.

How to find and download sqlite3.exe, the manual fix tool
===========================================

If you can't use the dbfix tool (or don't want to) you need a programme called sqlite3.exe from http://www.sqlite.org/download.html. Download sqlite-[version-number].zip under "Precompiled Binaries For Windows" and unzip it into a separate directory. The zip file currently (April 2008) contains one file, called sqlite3.exe. This is the programme you want.

You don't have to know how sqlite works but it helps. See http://www.sqlite.org/quickstart.html. Some things can confuse a Windows user – they certainly confused me. See below for pitfalls.

You have to 'run' sqlite3.exe from a DOS command window. To do this, click Windows 'start' and choose 'run'. Type 'cmd' and press enter. A DOS (usually black-on-white) window opens for you type in commands, like everyone did before mice were invented.

Watch out for:

*Sqlite3.exe isn't the installer, it's the actual programme. If you do try to start it, you can only enter sqlite commands. You don't have access to ordinary DOS commands like 'cd' to change directory. It's easier to start a DOS command window (see above).

*Windows doesn't 'know' about sqlite3.exe. The programme isn't registered, unlike most Windows programmes. So, if it doesn't reside in your current directory, Windows can't find it. You must either put a copy of sqlite3.exe in the same directory as your database file, or use full path names for files, including directories [which, if they contain spaces, will need to be quote-enclosed eg “My Library\zotero.sqlite.bak]. You can get clever and set up 'path' variables, but what are you trying to fix, your file or your reputation?

I copied sqlite3.exe into my Zotero directory, navigated there using 'cd' commands and did everything in one place. If you aren't sure what you are doing, copy both sqlite3.exe and zotero.sqlite.bak into a separate directory and work there.

*Run 'cmd', not the older 'command'. The latter is Dos 5 programmes using 8-character directories and can't handle Windows file names. 'Cmd' lets you navigate Windows directories.

Using sqlite to fix the file
===================

I'm supposing you have sqlite3.exe and zotero.sqlite.bak in the same directory, that you've got a DOS window open, and that you've navigated to the directory where you are going to do the business. You are going to tell sqlite3.exe to open zotero.sqlite.bak, 'dump' it (convert it to a set of SQL statements in ordinary text format) and then read the dump to make another database. '>' means the command prompt (non-windows users may write '%' or '$'). You enter:

> sqlite3 zotero.sqlite.bak

This should open the database and show you an SQLite prompt. This looks like:

sqlite>

You are now using sqlite language, which processes SQL and some special 'sqlite' statements which all begin with a period ('.'). As long as this prompt is showing, you can't use DOS commands. To get out, type '.exit' (note the period at the beginning)

You are going to check everything is where it should be. You enter:

> .dump

This should display all your data in SQL. [If you have a lot of files it takes a while. You can interrupt using '^C' (control-C) though you come out at the DOS prompt so you'll have to re-enter '> sqlite3 zotero.sqlite.bak'].

Next, you're going to do the same thing except you'll send the output to a text file called 'zotero-dump.sql'. First you type:

> .out zotero-dump.sql

[If sqlite3.exe and your working files are in different directories, you'll need the full path as suggested in http://forums.zotero.org/discussion/2220, such as

sqlite>.out "C:\Documents and Settings\Alan.SAMSUNG\My Documents\My Library\zotero-dump".

Lot of traps for the unwary, for example, if the path has spaces in it then you need the double-quotes, but not otherwise]

This statement does not create the dump file. It redirects all output to this file from now on. So now, only type what it says below. Otherwise (as I found out), extra stuff gets pumped into the dump file, which confuses the hell out of sqlite when you try to read it back in. So now you just enter

> .dump
> .exit

The dump takes only a few seconds. You are now going to feed it back to sqlite, which will generate a cleaned-up database file. From the DOS prompt, enter

>sqlite3 zotero.repaired.sqlite 'PRAGMA auto_vacuum=1'

This creates a blank database in 'zotero.repaired.sqlite'. You'll get the sqlite prompt again, so type

sqlite>.exit

to get back to the DOS prompt. Now type:

>sqlite3 zotero.repaired.sqlite < zotero-dump.sql

This feeds back in the SQL instructions you just generated, and populates the database file. Now and finally, overwrite your existing 'zotero.sqlite' file with the ''zotero.repaired.sqlite' you just made, restart firefox and zotero, check the integrity and your collections, and carry on. Good time to back up your Zotero library.

Note: I couldn't get the 'PRAGMA auto_vacuum' to work with this syntax in Windows, which may be specific to my installation. I therefore edited the 'zotero-dump.sql' file so that the first line reads 'PRAGMA auto_vacuum=1;' This command (see http://www.sqlite.org/pragma.html) has to be executed before any tables are created.

Another way round (if you experience the same problem) is first create the database with

>sqlite3 zotero.repaired.sqlite

Then, while the sqlite prompt is still showing, enter

sqlite>PRAGMA auto_vacuum=1;

[don't forget the semi-colon]

Next, exit sqlite (.exit). Then enter

>sqlite3 zotero.repaired.sqlite < zotero-dump.sql

as before.
  • >>> http://www.sqlite.org/sqlite.html
    >>> http://www.sqlite.org/quickstart.html

    Upon trying the links quoted above I noticed that the period right after the ULR got added to the link destination, thus making it impossible to open the page directly.

    Simply removing the dot in the address line of your browser (or using my quotes above) helps.
  • Two additional comments:

    1) You write:

    >>> I couldn't get the 'PRAGMA auto_vacuum' to work with this syntax in Windows

    This also happened to me - however upon using double quotes "PRAGMA auto_vacuum" instead of single ones around the term it worked.


    2) While re-loading the SQL-dump into the newly created empty database the following error was shown:

    >>> SQL error near line 40822: column word is not unique

    I assume this was the culprit for my 'database integrity errors' in the first place and just mention this to make others aware that errors might (have to?) be shown during the re-import. (As obviously the database did have a corruption that needs to be fixed, hence the effort to do this - hopefully I'm not mistaken.)

    Thanks for the instruction - after doing this my database does not show any integrity errors any more (for now) - at first glance all items seem still to be there, too..

    Any idea what brings about the integrity problems (quite frequent with me) in the first place? Anything I could avoid to be doing?

    Thanks again
  • Any idea what brings about the integrity problems (quite frequent with me) in the first place?
    Instability of the storage layer in Firefox 2 with large operations (like those made by the fulltext indexer). These problems should go away in Firefox 3.
  • I have the SQLiteManager add-on for FF3. It has a built in integrity checker which says there are problems with the Zotero database (well, yes.) Is there a way to use this add-on to repir the database integrity? I didn't see a built-in tool
  • I updated to FireFox 3.0 and am still receiving errors when I check the database integrity. I've tried to upload my zotero folder for a database repair but unfortunately I can only compress the size to 150.0 MB -- way too large for the upload.

    Please could you tell me how to manually repair my database using a Mac computer?

    What are the long term concerns of having a database with errors?

    Is there any other way to fix my database?

    Thanks so much!
    -SR
  • I updated to FireFox 3.0 and am still receiving errors when I check the database integrity.
    Firefox 3 doesn't fix existing errors. You need to fix them, and after that you hopefully won't receive any more in Firefox 3.
    Please could you tell me how to manually repair my database using a Mac computer?
    It's the same basic process as above, except you have to use Terminal rather than the DOS command prompt.

    In your Home folder on OS X, create a new folder—let's call it "zotero-repair". Close Firefox and copy zotero.sqlite into there.

    Download the latest SQLite binary for OS X and move it into that folder. (You could use the built-in OS X version, but the newer version is probably better at dealing with errors.) It's gzipped, so you'll need to extract it first:

    1) Open Terminal in /Applications/Utilities.
    2) cd zotero-repair
    3) gunzip ./sqlite[tab]
    4) mv sqlite[tab] sqlite3

    [tab] means press the Tab key, which should auto-complete the filename. Otherwise you'll need to type it in exactly each time.

    After that, there are really only four steps, which you perform in the same Terminal window:

    5) ./sqlite3 zotero.sqlite .dump > dump.sql
    6) mv zotero.sqlite zotero.sqlite.old
    7) ./sqlite3 zotero.sqlite "PRAGMA auto_vacuum=1"
    8) ./sqlite3 zotero.sqlite < dump.sql

    You should then be able to copy that zotero.sqlite into your data directory, and Zotero shouldn't show any errors.
  • Dear Dan,

    I am running FF3 on Mac OS X Leopard. My integrity check failed. The uploader wouldn't upload my file, so I tried the sqlite3 process as above. However, it was giving me permission errors and would not recreate the zotero.sqlite file at the end of the above process. I don't know what is going on. Below is my process. I followed all directions. What is going wrong?

    Last login: Fri Aug 8 03:08:35 on ttys000
    You have mail.
    Shadys-MacBook-Pro:~ shadyfhakim$ cd zotero-repair
    Shadys-MacBook-Pro:zotero-repair shadyfhakim$ gunzip ./sqlite3-3.6.1-osx-x86.bin
    gunzip: ./sqlite3-3.6.1-osx-x86.bin: unknown suffix -- ignored
    Shadys-MacBook-Pro:zotero-repair shadyfhakim$ mv sqlite3-3.6.1-osx-x86.bin sqlite3
    Shadys-MacBook-Pro:zotero-repair shadyfhakim$ ./sqlite3 zotero.sqlite .dump > dump.sql
    -bash: ./sqlite3: Permission denied
    Shadys-MacBook-Pro:zotero-repair shadyfhakim$ mv zotero.sqlite zotero.sqlite.oldShadys-MacBook-Pro:zotero-repair shadyfhakim$ ./sqlite3 zotero.sqlite "PRAGMA auto_vacuum=1"
    -bash: ./sqlite3: Permission denied
    Shadys-MacBook-Pro:zotero-repair shadyfhakim$ ./sqlite3 zotero.sqlite < dump.sql-bash: ./sqlite3: Permission denied
    Shadys-MacBook-Pro:zotero-repair shadyfhakim$
  • It looks like OS X or Stuffit Expander expanded the file for you. After 'cd zotero-repair', run 'chmod 755 sqlite3'. (You can skip the mv step because the file is already renamed.)
  • edited August 11, 2008
    nevermind. i got the upload tool to work finally (I was out of the country before, and for some reason it wouldn't work). thanks
  • Hi,

    Any idea how to do this for Linux?

    yours always,

    Erik
  • Any idea how to do this for Linux?
    Same as the OS X instructions above.
  • If you have a problem with a locked sqlite database it is also worth to check

    http://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database

    and especially the NFS part, if you are using a network folder...

    Same problem in firefox and thunderbird if they crash in ubuntu
This discussion has been closed.