bug report 441967839

aufrank

Hello!

I had trouble upgrading my database when the second sync preview. I never tried the first sync preview. I was successfully using zotero before I installed the sync preview, adding items as recently as a couple of days ago. The error that I received during the upgrade indicated that my database is corrupted.

I'm on a PPC mac, OSX 10.4.11, 2 G RAM. I'm running Firefox 3.0.1. Don't know if this is relevant (I'm guessing you use a bundled sqlite3 rather than the system one?), but I have sqlite3 version 3.6.1 installed via MacPorts. sqlite3 functions properly for me when I use it at the command line.

I'm happy to report the results of any debugging steps you advise me to take, including opening zotero.sqlite locally and doing any queries you suggest.

I do have my library archived. It's 561M tarred and gzipped.

Thanks for any advice,
/au

dstillman

Your database appears to be corrupted, probably from earlier use with Firefox 2, which had database instability problems. You should be able to use the DB Repair Tool to fix it.

aufrank

Thanks for the advice. I used the tool you linked to and got back a DB of 33 megs, which is about the same size as the original.

However, I've now allowed the upgrade process to run for ~6 hours, and the disk is still thrashing. The progress bar is no longer moving, and the beach ball is spinning whenever I bring Firefox to the front. I'm stuck on the dialog box that says "Please wait for the upgrade process to finish...".

The processor load is not high, and the disk is not filling up (or at least not very quickly), but the disk is continuously writing about ~60kb/sec and reading ~160kb/sec. I show ~1.15 gigs of inactive RAM, ~550 of active RAM, and ~150 of free RAM.

My library contains ~250 pdf files, and as I said it's 33 megs in size. When I attach a pdf file, I almost always choose to store a copy rather than to link to the file. Should I expect the upgrade to take more than 6 hours to complete, or has something gone wrong? If, for example, the full text search DB is being rebuilt from scratch during the upgrade, I could maybe see that taking quite a while and causing this kind of disk churn.

I'm quitting Firefox for now, and will restore the DB from a backup. I'm happy to take any debugging steps you suggest. If this process really will take more than 6 hours, I'll restart it and let it run over the weekend I guess.

Thanks for any help,
/au

aufrank

Just a quick followup:

I restarted it with a fresh copy of my zotero/ folder (and again, with the corrected zotero.sqlite file). This time I followed the instructions to get debugging info in a terminal. I'm observing the same behavior (progress bar stops moving,beach ball is spinning, disk is churning). The debug info indicates that it's performing a REINDEX on zotero.sqlite. Any way of estimating how long it would be reasonable to expect this step to last?

Thanks,
/au

dstillman

That seems extraordinarily excessive for a 33MB database. We'll have to look into the REINDEX performance, but, as a test, you might want to try a REINDEX on a test copy of your database from the command-line client to see how long it takes. You're most likely correct that it's the full-text index being rebuilt, which suggests that you could overcome this by clearing the index from the Zotero prefs before the upgrade, but I would be quite surprised if a command-line REINDEX took that long.

Just out of curiosity, can you also run this command (before the REINDEX) on the test DB:

SELECT COUNT(*) FROM fulltextItemWords;

(For what it's worth, we'll likely be switching to SQLite's built-in full-text search system in the future, which should be much more efficient than the current approach.)

aufrank

SELECT COUNT(*) FROM fulltextItemWords;
672049

The REINDEX finished overnight. No idea how long it ended up taking, I went to sleep. The preferences show that I have 485 indexed documents. The only reason this seems odd to me is the following:

$ find storage/ -regex ".*\.pdf$" | wc -l
241

But I guess lots of web page snapshots are being indexed as well.

Do you still want me to run the command line REINDEX?

Thanks,
/au

dstillman

Yes, please try from the command line—if possible, using a copy of your database from before the upgrade. You might want to add a 'date' command before and after so you can leave it unattended.

aufrank

On DB before upgrade (the time will be a little bit long, as I walked away for a minute):

$ time sqlite3 zotero.sqlite
SQLite version 3.6.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> REINDEX;
SQL error: indexed columns are not unique
sqlite> .quit

real 2m30.203s
user 0m2.376s
sys 0m0.734s


On version that has been upgraded:

$ time sqlite3 zotero.sqlite
SQLite version 3.6.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> REINDEX;
sqlite> .quit

real 1m52.338s
user 0m36.381s
sys 0m12.772s

That's surprising to me, given how long the upgrade process took... but I think I did what I think I did ;)