Need to repair/backup the zotero.sqllite

gelax

Hello everybody!
I use Kubuntu 7.04 (Feisty Fawn), Firefox 2.0.0.6 and Zotero 1.0.3

Something wrong with my zotero.sqllite. Kubuntu shows its size=462GB!
My partition has 90 GB and 7GB free. If i try to copy it to another partition linux tries to copy all 462 GB, however he has no chance:). Maybe EOF was lost or something similar. Zotero still works but sticks if i check database integrity.

I would remove the file and recreate database, but when i exported/imported it to/from Zotero RDF I lost all links to pdf files in my partition and I lost all collections.

Is it possible to to something with this fille or completely import the database?

Sincerely
Alex.

dstillman

Sounds like you might need to boot into single user mode (or use a Live CD, if you can't avoid mounting the partition the Zotero data directroy is on) and run fsck, though there's the potential it'll just delete/corrupt the file...

Is there a zotero.sqlite.bak file? What's the date/size?

I don't lose collections exporting to RDF and importing back in... Are you exporting using Export Library in the Actions (gear icon) menu?

As for file links, yes, Zotero copies linked files into the export directory rather than preserving them as filesystem paths.

gelax

>Is there a zotero.sqlite.bak file? What's the date/size?
Unfortunately it is around one month old

>I don't lose collections exporting to RDF and importing back in...
>Are you exporting using Export Library in the Actions (gear icon) menu?
Yes I do. Strange... Maybe it's because of corrupted database

gelax

I extracted the first part (1GB) of this file (just started to copy and interrupted it )
After some point in this file I see one long line of dots.
But Zotera doesn't want to work with this new one
And it works perfect with 467GB file.

dstillman

Well, you could try grabbing a hex editor, deleting from the dots onward, and saving as a new file in a different directory, though that seems highly unlikely to actually work. At the very least you could see how big the resultant file is. If it's manageable, you can zip it up and upload it to the DB Repair Tool and e-mail us the Report ID at support@zote....org, and we can take a look.

I'd strongly discourage you from writing to that directory/file much more without running fsck.

gelax

>Well, you could try grabbing a hex editor, deleting from the dots onward, and >saving as a new file in a different directory, though that seems highly unlikely to >actually work.
i tried to do similar. last time I used split command to extract first 7 GB, but with this file zotero didn't work.
I know a little bit sql. Is there a way to connect using mysql and backup everything, including file paths and then restore it from this backup to a new database. Maybe you have an appropriate script for this?

dstillman

i tried to do similar. last time I used split command to extract first 7 GB, but with this file zotero didn't work.

Well, yes, but I'm suggesting removing all the "dots", not just splitting the file at an arbitrary point. But, again, it probably wouldn't make a difference.

I know a little bit sql. Is there a way to connect using mysql and backup everything, including file paths and then restore it from this backup to a new database.

Zotero uses SQLite, not MySQL, so you'd need the SQLite command-line client, available from your package manager or sqlite.org

Maybe you have an appropriate script for this?

We do; it's the DB Repair Tool, but you'd have to do what I suggested above and remove the extraneous data to get the file small enough to upload. But you can do the same steps manually:

sqlite3 zotero.sqlite [on the original huge file]

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

% .dump [This should show you an SQL dump of your database with all your data. If not, the remaining steps aren't going to work.]
% .out ~/zotero-dump.sql [file to save dump to; in your case, this should be in another directory, since your current directory is compromised]
% .dump
% .exit

Then back on the command line, change to the directory to which you saved the dump. You might want to open the zotero-dump.sql file in a text editor and make sure all your data is there.

If so:

sqlite3 zotero.repaired.sqlite 'PRAGMA auto_vacuum=1'
sqlite3 zotero.repaired.sqlite < ~/zotero-dump.sql

You should then have a repaired database. You can then reboot to a live CD or single-user mode and run fsck, and in case it destroys the huge DB file, you'll have a clean backup (which you might want to use anyway).

Let me know if any of that's not clear or not working.

gelax

Thanks!! I did it!!

Here my comments, if somebody else needs.

1) sqllite3 worked both with the large file and with the first part of file,
so probably I just could split it and submit to you.

2) I was a bit confused with '%' in front of each statement, is it command prompt?
So i used
.dump [It showed me everything until the COMMIT statement]
.out ../Zotero_Backup/zotero-dump.sql [It created a new empty! file]
[strange, but .out ~/zotero-dump.sql could not create any file]
.dump [Copied data into the file]
.exit

3) Than i changed to this directory, executed
sqlite3 zotero.repaired.sqlite 'PRAGMA auto_vacuum=1'
sqlite3 zotero.repaired.sqlite < ~/zotero-dump.sql

renamed the file and gave it to Zotero.

Thank you very much for support! You did a great product
I like it very much

gelax

My zotero.lqlite grew up 400Gb again.
Now I fixed it fast, no problem

Just to let you know a possible reason. I use virtual PC and sometimes
run both zotero on Linux and on Windows using the same database.
Maybe it crashes everything.

dstillman

I use virtual PC and sometimes
run both zotero on Linux and on Windows using the same database.

At the same time, or one at a time?

Having Zotero open and using the same database file on both OSes simultaneously would be a very quick way to corrupt your database.