metadata.db corrupted

[Philantrop]

I have shredded my metadata.db and it's gone.

I have, however, an sqlite3 dump of the whole database from before the problem occurred.
Thus, I can recreate metadata.db (using sqlite3 metadata.db < dumpfile) which I can verify by connecting to the db and selecting data from it which works fine.
Neither calibredb nor the GUI see any of my books, though.

Instead both claim to update the database and delete all the content while doing so:

$ calibredb list
Upgrading database to version 2...                                                   
Upgrading database to version 3...                                                   
Upgrading database to version 4...                                                   
idtitleauthors

I can't seem to find a way to stop them doing that. What am I missing and what can I do to recover my data - after all, it's all at my fingertips and yet still so far...

[kovidgoyal]

What happens if you run it with

calibre --with-library /path/to/folder/containing/recovered/metadata.db

[Philantrop]

If I recover the db to database_path (which is on NFS if it matters) and run calibre with --with-library, it starts empty and empties the recovered db again.

If I recover the db to an alternative path, calibre starts empty as well but, interestingly, the database doesn't get wiped.

[kovidgoyal]

Edit global.py

Change library_path to point to some folder on your local drive.
delete database_path
set migrated to True

Now copy your recovered metadata.db to the folder you specified earlier as well as all the actual ebook folders/files

Now run calibre

Note that library_path sets the location of the calibre library, not database_path, which is on there for legacy reasons

[Philantrop]

library_path now points to a local dir with the recovered metadata.db inside.
migrated = True (which was already the case, though)
I've removed database_path from global.py.
I've copied the recovered (and verified as containing my data) to library_path and the NFS dir in which my actual ebooks (as stored there by calibre) reside.

Then I started calibre:

$ calibre
link  hasn't been detected!
link  hasn't been detected!
Upgrading database to version 2...
Upgrading database to version 3...
Upgrading database to version 4...

(The "link" message has always been there.)

No books are shown in calibre.
library_path/metadata.db is wiped empty after quitting calibre. The other metadata.db in the ebook storage area (how do you call that?) is still fine.

The very same setup (with everything on NFS) has worked fine till the original metadata.db got killed (manually by myself by accident).

[kovidgoyal]

What happens with

calibre-debug -c "from calibre.library.database2 import LibraryDatabase2; db = LibraryDatabase2('/path/to/folder/containing/recovered/metadata.db');"

[Philantrop]

Originally Posted by kovidgoyal

What happens with

calibre-debug -c "from calibre.library.database2 import LibraryDatabase2; db = LibraryDatabase2('/path/to/folder/containing/recovered/metadata.db');"

Upgrading database to version {2,3,4}...

And the recovered metadata.db is emptied again.

[kovidgoyal]

that means your recovered metadata.db is also corrupted. What size is the db file? One thing to try is the following

Run

sqlite3 metadata.db

At the sqlite prompt

pragma user_version=4;

quit and then try the previous command again

[Philantrop]

First of all, Kovid, thank you very, very much. Your quick and effective help is greatly appreciated.

In the end, "pragma user_version=4;" did the job. Calibre is now working for me again, I have two independent file backups of metadata.db which is now stored in git as well - just to be sure.

Looks like sqlite3's ".dump" does not dump everything Calibre needs? Is there another way to create a "calibre-compatible" dump?

[kovidgoyal]

Glad it worked

Basically, the only thing it doesn't dump is user_version (possibly because that's not really a part of the database). At some point I have to create a backup/restore infrastructure for calibre, if only the day had 240 hours