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.