Database problem

[zin]

Calibre locked up while I was adding a book. I think there was a disk or file system issue.

After killing the locked up program, I restarted Calibre and the library came back with 0 books. It should be 1000+. The location is correct and the directory structure with all the actual books is there, but the database somehow got lost or cleared. The metadata.db file is about 100k.

Is there any method to restore the database?

If the answer is no, is there any easy way to re-add all my books? I didn't have much metadata entered but I did have cover pictures for most books.

Thanks,
Zin

[desertgrandma]

Welcome to MobileRead, Zin.

Hope your problem gets sorted out soon!

[DoctorOhh]

Originally Posted by zin

The metadata.db file is about 100k.

Is there any method to restore the database?

If you have a backup of the metadata.db file then exit/quit calibre and restore it and you should be fine.

Originally Posted by zin

If the answer is no, is there any easy way to re-add all my books? I didn't have much metadata entered but I did have cover pictures for most books.

If you have no backup of the metadata.db file create an empty library at a new location using the library icon on the top row of calibre.

Once this library is created click on the drop down arrow next to the Add Books icon (top left) select Adding books from directories, including sub directories (one book per directory, assumes every ebook file is the same book in a different format) now select the directory you previously used for your library and wait until it finishes adding those books to this library.

Since the much of the metadata was kept in the metadata.db file some of the metadata maybe lost, but the books and covers should all be added.

[zin]

Well, it almost worked.

Only about 2/3 of the books were added, none of the covers, and any books with multiple authors lost all but the first.

Any other ideas, or do I have a big job ahead of me?

Thanks in advance,
zin

[DoctorOhh]

Originally Posted by zin

Well, it almost worked.

Only about 2/3 of the books were added, none of the covers, and any books with multiple authors lost all but the first.

Any other ideas, or do I have a big job ahead of me?

The solution to this problem is to restore from backup.

It should have been more successful and if the covers are in the directories I thought it would work. Check Preferences - Import/export - Adding Books and ensure that Read metadata from file contents rather then file name is checked (see attached). If it wasn't checked, check it and start from the beginning.

If it was checked already, you may just have a lot of work ahead of you. Hopefully from here on out you will learn to backup your library / metadata.db file.

[Lady Fitzgerald]

I'm using an online backup service to backup my data files. If I were to lose my metadata, couldn't I just grab the latest library folder from the backup service and just paste in place of the corrupted one?

[DoctorOhh]

Originally Posted by Lady Fitzgerald

I'm using an online backup service to backup my data files. If I were to lose my metadata, couldn't I just grab the latest library folder from the backup service and just paste in place of the corrupted one?

Absolutely, As long as that backup includes the metadata.db file which is typically in the root of the library folder structure. If the structure of you library is fine just restoring the metadata.db file could be enough for it to all come together again.

[Lady Fitzgerald]

Cool! I'm covered without having to lift a finger. I had thought so but verification is always nice.

[chaley]

Note that books that have had an author or title change since the last backup will be 'lost' if you restore the database. Reason: author and title are used to construct the folder path for the book. When you change one of them, the folders are moved to the new names. Restoring a metadata.db with the old author/title metadata will look in the old (and now non-existent) folder. Result: books are gone.

This was the reason I decided not to do an automatic backup of metadata.db when you do a search/replace. If you were playing with authors or titles, restoring the old copy could make the problem much much worse. I didn't want to imply a promise we couldn't keep.

[DoctorOhh]

Originally Posted by chaley

Note that books that have had an author or title change since the last backup will be 'lost' if you restore the database. Reason: author and title are used to construct the folder path for the book. When you change one of them, the folders are moved to the new names. Restoring a metadata.db with the old author/title metadata will look in the old (and now non-existent) folder. Result: books are gone.

True, any changes since the last backup will be gone. That's the nature of the beast, hardly a revelation.

Originally Posted by chaley

This was the reason I decided not to do an automatic backup of metadata.db when you do a search/replace. If you were playing with authors or titles, restoring the old copy could make the problem much much worse. I didn't want to imply a promise we couldn't keep.

I'm confused. How will restoring just the metadata.db make things much much worse? It will create blank folders with no books but it won't cause any folders to go poof, will it? Running a database integrity check at this point will point out any mismatches and you can manually search for the missing items or re-add those items via the metadata edit window.

The nature of the beast is that you don't restore from backup until there is a problem. Depending on how you back things up restoring the metadata.db file might be the route to minimized loss of metadata. My metadata.db file is backed-up on the fly every time it is changed. I can try the last 150 backups in order and never physically lose a file because of it.

Of course if restoring just the metadata.db file doesn't produce the results you like, restoring the entire library is still an option.

Maybe I'm missing something, the promise of a backup is that you can recover to the last point in time you actually backed up your files.

[Manichean]

Originally Posted by dwanthny

I'm confused. How will restoring just the metadata.db make things much much worse? It will create blank folders with no books but it won't cause any folders to go poof, will it? Running a database integrity check at this point will point out any mismatches and you can manually search for the missing items or re-add those items via the metadata edit window.

I think what he's trying to say is that if you overwrite your (assumedly broken) metadata.db with an older one, and in the meantime had changed some author names or titles, which Calibre would have mirrored in the folder structure, then the older, working metadata.db would point to nonexisting folders while, at the same time, you'd have nonreferenced folders in your library. Sorting out which is which might be decidedly non-trivial if you've changed a lot of things. Thus, making things even worse.

[chaley]

Originally Posted by dwanthny

I'm confused. How will restoring just the metadata.db make things much much worse? It will create blank folders with no books but it won't cause any folders to go poof, will it?

No, calibre won't create blank folders.

Assume that I change an author from Joe to John. Calibre will change the metadata in the db, then rename the folder 'Joe' to 'John'.

Now I restore the db from before. It thinks that the author is Joe, and that the folder is 'Joe'. Unfortunately, the folder 'Joe' does not exist, which is something that is not supposed to happen. In addition, the folder 'John' is sitting out in space, loved by no one.

Running a database integrity check at this point will point out any mismatches and you can manually search for the missing items or re-add those items via the metadata edit window.

Yes, you can recover this way.

If you are careful, before running db integrity you can manually change the authors back to what they were, and the floating-in-space folders will be found and used. This must be done before calibre notices that the folders are missing and changes the db.

The reason it is 'much much worse' is that restoring a db can have catastrophic unintended consequences. For example, assume a user did something to tags that s/he didn't like. Perhaps all of them were turned to the letter Z or some such, it doesn't matter. In the same operation, the user changed the authors of the selected books on the first screen (yes, this is possible). Noticing the bad tags, the user says 'Hey, it made a backup' and restores it. At this point the tags are made right but all the formats are are made gone. Ouch.

[theducks]

Originally Posted by Manichean

I think what he's trying to say is that if you overwrite your (assumedly broken) metadata.db with an older one, and in the meantime had changed some author names or titles, which Calibre would have mirrored in the folder structure, then the older, working metadata.db would point to nonexisting folders while, at the same time, you'd have nonreferenced folders in your library. Sorting out which is which might be decidedly non-trivial if you've changed a lot of things. Thus, making things even worse.

I notices browsing the Metadata.DB contents (yes I peeked ), that Authors also get a non-reused (renaming an author gets a new number) index number.
If Calibre were to use this number, similar to that used for numbering title file names, finding "orphans" should now become a rather trivial (Not in list) operation

[chaley]

Originally Posted by theducks

I notices browsing the Metadata.DB contents (yes I peeked ), that Authors also get a non-reused (renaming an author gets a new number) index number.

Unfortunately, when you use the rename_author function, the id is not changed.

[theducks]

Originally Posted by chaley

Unfortunately, when you use the rename_author function, the id is not changed.

I just changed a name (spelling) and a new index number was assigned according to my peek (using SQL-lite DB Browser)

but now that you mention it, the old one was NOT discarded. (so why are there gaps in the numbers?).