corrupt metadata.db files

[Naloomi]

Hello.

Recently my metadata.db files in the main calibre library got corrupted. This includes the backup. I say corrupted, but it's possible they got tampered with by someone having access to my computer. Either way, all the ebook files are there, but calibre does not recognize them in it's library. (Think walking into a library, shelves full, but the computer saying no books in the building).

I can 'add to library' but when I do that it creates duplicate folders and books. I don't want to move my books because they sync with an online backup (which also sync'd the metadata.db files, so I can't get them back from there either).

How do I rebuild the library without 'copying' the books and having thousands of duplicate files?

Naloomi

[BetterRed]

@Naloomi Disable online backup synching, copy what you have to local storage - eg. elsewhere on your disk, another computer, server, usb stick etc

What online backup service are you using ?

If its Google Drive then it might have done the 'tampering', see WARNING - Google Drive Incompatible with Calibre!


Otherwise, use Library Maintenance->Restore database - it will build a fresh metadata.db from the metadata.opf files in the book folders

Then run Library Maintenance->Check Library and resolve any anomalies in the second phase, hopefully there wont be any

When you're happy with the rebuild you could restart the online synch - although if it were me I'd trash what I had on the online service and start over.

Once you satisfied that all is OK, including your online backup & restore processes you can trash the local backup.

BR

[Adoby]

If you are lucky there are updated opf-files together with the book-files. These opf-files contains backup metadata that calibre can use to reconstruct the database.

There are two possible methods:

1. Use the library maintenance tools to restore the database, as suggested by BetterRed above.

2. Create a new empty library with the same structure as the corrupt one (there is an option to du just that) and add the books in the old library to the new library, from subfolders, with one book per folder. Calibre will then scan the old library and add all the books, using the metadata in the opf-files. It can take a while, if the library is large...

I would try 2 first, even if it is slower, because it creates a whole new library and leaves the old library as it is. Just in case there are more problems. But it means that you afterwards have to move the library to make the online backup work again.

Most likely the culprit was the online backup software. Perhaps you have had the backup software working while you had calibre running? That may create either a corrupt backup or a corrupt calibre library.

[theducks]

Originally Posted by Adoby

If you are lucky there are updated opf-files together with the book-files. These opf-files contains backup metadata that calibre can use to reconstruct the database.

There are two possible methods:

1. Use the library maintenance tools to restore the database, as suggested by BetterRed above.

2. Create a new empty library with the same structure as the corrupt one (there is an option to du just that) and add the books in the old library to the new library, from subfolders, with one book per folder. Calibre will then scan the old library and add all the books, using the metadata in the opf-files. It can take a while, if the library is large...

I would try 2 first, even if it is slower, because it creates a whole new library and leaves the old library as it is. Just in case there are more problems. But it means that you afterwards have to move the library to make the online backup work again.

Most likely the culprit was the online backup software. Perhaps you have had the backup software working while you had calibre running? That may create either a corrupt backup or a corrupt calibre library.

I am pretty sure #2 is not viable. If the DB is empty, it is probably now a Default structure. Now if the OP only had a default (no customizations) structure, they are good.

Rebuild is my first choice

[BetterRed]

Originally Posted by theducks

I am pretty sure #2 is not viable. If the DB is empty, it is probably now a Default structure. Now if the OP only had a default (no customizations) structure, they are good.

Rebuild is my first choice

Question re database restore (rebuild from OPFs) - what happens if something has 'corrupted' the book folder names by overwritting or stripping off the book id's e.g. Title (404).

Does the restore/rebuild fix up the folder names to tally with the book id in the opf and hence new database book table, or renumber everything or...?

BR

[eschwartz]

Originally Posted by BetterRed

Question re database restore (rebuild from OPFs) - what happens if something has 'corrupted' the book folder names by overwritting or stripping off the book id's e.g. Title (404).

Does the restore/rebuild fix up the folder names to tally with the book id in the opf and hence new database book table, or renumber everything or...?

BR

I believe it is just comparable to a streamlined way to

1. backup the library
2. wipe and create a new library in the current location
3. add the info save in the file metadata_db_prefs_backup.json
4. add all books from the backup, using the Add Books option #2
5. delete backup

In a virtual way of course.

So id #'s should be totally ignored, as the whole library really is being rebuilt from scratch.

EDIT: I'm wrong, shows what I get for assuming things.

[BetterRed]

Originally Posted by eschwartz

Original post changed use link to see it

I think you should make that post a response to mine - lest it confuse the OP

Does it really backup the library, the database yes - but RU sure it backs up the library, and if so where to - one of mine is fast approaching 800GB - not something I want copied willy nilly to some unknown location.

BR

[Naloomi]

Library is large, close to 6400 e-books (I am at 6271 post problem).

I use Box, although due to the way it works I may end up having to switch to Dropbox (box does a popup for downloads that my Kindle doesn't accept, so getting books to my Kindle Keyboard 'on the go' is not possible without logging into my computer remotely and using 'send to kindle'. I hear that Dropbox does not do things this way, but have yet to test.)

I have completed step 1 that BetterRed suggests (use Library Maintenance->Restore database - it will build a fresh metadata.db from the metadata.opf files in the book folders). It has resulted in some 'errors', but looking closely at them they are all the same error, just with many different books.

Traceback (most recent call last):
File "site-packages\calibre\db\restore.py", line 253, in restore_books
File "site-packages\calibre\db\cache.py", line 57, in call_func_with_lock
File "site-packages\calibre\db\cache.py", line 1779, in restore_book
File "site-packages\calibre\db\cache.py", line 1383, in create_book_entry
File "site-packages\calibre\db\backend.py", line 287, in execute
File "c:\cygwin\home\kovid\sw\build\apsw-3.7.14.1-r1\src\cursor.c", line 231, in resetcursor
ConstraintError: ConstraintError: PRIMARY KEY must be unique

I've copied the 'list' to clipboard and saved it, and I figure I'll have to go through the multitude of books one by one, but do I have to? And at what point should I move to Step 2 that BetterRed suggests?

EDIT #1 - TONS of errors, but TBH, many/most of these errors were probably there to begin with.

EDIT #2 - The PRIMAY KEY is the # at the end of the folder name, isn't it? That's what I'm seeing. Duplicate folders, 'same book' although they're in different folders.

Example: Bob Builds Roads (666)
Bob Builds THE Roads (666)

Same book (made up), same 'KEY', but different folders thus the errors, right?

Naloomi

[eschwartz]

Originally Posted by BetterRed

I think you should make that post a response to mine - lest it confuse the OP

Does it really backup the library, the database yes - but RU sure it backs up the library, and if so where to - one of mine is fast approaching 800GB - not something I want copied willy nilly to some unknown location.

BR

I am thinking of this as more of an analogy, under the hood I am sure calibre just keeps track of all the files, perhaps I should rephrase this as "comparable to a streamlined way" Will add quote,

[BetterRed]

Originally Posted by Naloomi

Library is large, close to 6400 e-books (I am at 6271 post problem).

I use Box, although due to the way it works I may end up having to switch to Dropbox (box does a popup for downloads that my Kindle doesn't accept, so getting books to my Kindle Keyboard 'on the go' is not possible without logging into my computer remotely and using 'send to kindle'. I hear that Dropbox does not do things this way, but have yet to test.)

I have completed step 1 that BetterRed suggests (use Library Maintenance->Restore database - it will build a fresh metadata.db from the metadata.opf files in the book folders). It has resulted in some 'errors', but looking closely at them they are all the same error, just with many different books.



I've copied the 'list' to clipboard and saved it, and I figure I'll have to go through the multitude of books one by one, but do I have to? And at what point should I move to Step 2 that BetterRed suggests?

Naloomi

@Naloomi - can you confirm that those errors are coming from the "Restore database" task.

And how many is a 'multitude' - are you saying that it was OK up to 6271.

That error is 'unusual' as its the sort of thing that theoretically can't happen, I advise to wait for Kovid to respond - you could him a give him a nudge via a PM referring him to your thread.

BR

[Naloomi]

Originally Posted by BetterRed

@Naloomi - can you confirm that those errors are coming from the "Restore database" task.

And how many is a 'multitude' - are you saying that it was OK up to 6271.
BR

Oh Yes. I can confirm that they happened during the "Restore Database" task. The restore finished, and then it allowed me to copy the list to clipboard. At a guess, I'd say that the 'multitude' is several hundred different books. I've counted up to 100 so far, and I'm not 25% of the way through the list of books with the same error.

As I mentioned in Edit #2, it seems to be because the PRIMARY KEY. If I'm right, the PRIMARY KEY in the database is that # that follows book title. And a spot check has confirmed that each time that I have checked the books with the error they have a 'duplicate' folder with the same # after the folder name.

I just did a search (8279, the Ruth Plumly Thompson book, is Yankee in Oz) for the book referenced with # 8279. I found it, as a PDF, in a different directory (Unknown Author). When I open the directory, that folder has the Tag # of 8279. Lets confirm this further.

L. Frank Baum\The Scarecrow of Oz (8296) (This one has the error).
Calibre Library\Unknown\Oz 9. The Scarecrow of Oz (8296)

Eloise Jarvis McGraw\Merry Go Round In Oz (8277) (Has the error)
Calibre Library\Unknown\Oz 40. Merry Go Round In Oz (8277)

Ugh. So it appears I have duplicates that were never found in my 'find duplicate' purge months ago.

BetterRed, thank you for all your help so far on this.

Naloomi

[BetterRed]

Originally Posted by eschwartz

original post changed clink link above

@eschwartz - I think it uses the old book numbers - just restored my test library (35 books), the book folders appear to have the original numbers appended

E:\Calibre Libraries\Test\Unknown\Here is a bear (122)\metadata.opf
E:\Calibre Libraries\Test\Unknown\A Tale of Forbidden Love (114)\metadata.opf
E:\Calibre Libraries\Test\Tony Marx\What Happened to Obama 2 (30)\metadata.opf
E:\Calibre Libraries\Test\Tony Marx\Unknown (121)\metadata.opf
E:\Calibre Libraries\Test\tester\test (93)\metadata.opf
E:\Calibre Libraries\Test\Rubbish\Test PDF (120)\metadata.opf
E:\Calibre Libraries\Test\Maya Angelou\His Day Is Done_ A Nelson Mandela T (131)\metadata.opf
E:\Calibre Libraries\Test\Marx, Tony\Wither the Tea Party (132)\metadata.opf
E:\Calibre Libraries\Test\Lauren St John\Fire Storm (95)\metadata.opf
E:\Calibre Libraries\Test\IMF\Greece_ First and Second Reviews (12)\metadata.opf
E:\Calibre Libraries\Test\GLENDA JACKSON\Unknown (72)\metadata.opf

The OP's most recent post sort of confirms that

BR

[kovidgoyal]

The restore process is designed to restore from corruption of metadata.db, not the rest of the files/folders. Therefore, it requires the rest of the files folders to be in an uncorrupted state to work.

If both metadata.db and the rest of the files/folders are corrupted, your best bet is to use the add books from subfolders, one per folder technique with a new library.

[BetterRed]

Originally Posted by Naloomi

Oh Yes. I can confirm that they happened during the "Restore Database" task. The restore finished, and then it allowed me to copy the list to clipboard. At a guess, I'd say that the 'multitude' is several hundred different books. I've counted up to 100 so far, and I'm not 25% of the way through the list of books with the same error.

As I mentioned in Edit #2, it seems to be because the PRIMARY KEY. If I'm right, the PRIMARY KEY in the database is that # that follows book title. And a spot check has confirmed that each time that I have checked the books with the error they have a 'duplicate' folder with the same # after the folder name.

I just did a search (8279, the Ruth Plumly Thompson book, is Yankee in Oz) for the book referenced with # 8279. I found it, as a PDF, in a different directory (Unknown Author). When I open the directory, that folder has the Tag # of 8279. Lets confirm this further.

L. Frank Baum\The Scarecrow of Oz (8296) (This one has the error).
Calibre Library\Unknown\Oz 9. The Scarecrow of Oz (8296)

Eloise Jarvis McGraw\Merry Go Round In Oz (8277) (Has the error)
Calibre Library\Unknown\Oz 40. Merry Go Round In Oz (8277)

Ugh. So it appears I have duplicates that were never found in my 'find duplicate' purge months ago.

BetterRed, thank you for all your help so far on this.

Naloomi

@Naloomi - This is what I'd do

Move the \Calibre Library\Unknown\ folder out of the \Calibre Library\ folder with a file manager (Windows Explorer, Finder, Nautilus, whatever) and then rerun the database restore.

If you still get errors then run the Library Maintenance->Check Library function, the second phase will tell you of discrepancies, you can save the results to the clipboard. That should get you a place where you can resolve the discrepancies and hopefully end up with a clean library.

If it looks beyond repair - then you'll have to add all the books into a fresh library as suggested by Adoby & Kovid.

-------

What operating system are you using, and how long have you been using calibre ?

This is Windows only - I have a memory cell telling me that one could get a duplicate book number on a folder if you changed the author or title of a book whilst you had a format file open in a program that locked the file. The programs I know of that lock the files are Word for DOC, RTF, DOCX etc files, also XChange & Nitro for PDF files. But Kovid fixed that some time ago - early last year or maybe 2012. Does that ring any bells? I stress this is a Windows issue, Linux and OS/X don't provide any file locking mechanism.

Once you get the library back together I suggest you run the Check Library function now and again - I do it weekly, doesn't take long, just ran it on my 7482 'book' media library in < 2mins. I don't have a super fast computer.

BR

[BetterRed]

Originally Posted by eschwartz

EDIT: I'm wrong, shows what I get for assuming things.

before swimming check for lurking crocs and cloaked sharks.