Calibre database corrupted

[Reader4106]

For years, our extremely large Calibre database (8.7.0), which contains many additional data fields, has been running on an external LaCie hard drive.
Now, our MacBook (14.7.6 Sonoma) crashed while I was working due to a power outage.
Since then, Calibre hasn't been able to open.
Even multiple recovery attempts, as well as restarting the computer and opening Calibre in Safe Mode, did not resolve the problem.

The following is the error message:

calibre, version 8.7.0
Corrupted database: The library database (/Volumes/eBooks_LaCie/Calibre_2023_08_12) appears to be corrupted. Do you want Calibre to attempt to restore it automatically? The restore may not be successful. If you select No, Calibre will create a new, empty library.

Traceback (most recent call last):
File "calibre/gui2/main.py", line 323, in initialize_db
File "calibre/db/legacy.py", line 197, in __init__
File "calibre/db/cache.py", line 190, in __init__
File "calibre/db/cache.py", line 474, in initialize_fts
File "calibre/db/cache.py", line 485, in start_fts_pool
File "calibre/db/cache.py", line 85, in call_func_with_lock
File "calibre/db/cache.py", line 597, in queue_next_fts_job
File "calibre/db/cache.py", line 500, in _update_fts_indexing_numbers
File "calibre/db/fts/connect.py", line 70, in number_dirtied
File "calibre/db/fts/connect.py", line 59, in get_connection
File "calibre/db/fts/connect.py", line 42, in initialize
File "calibre/db/backend.py", line 433, in execute
File "src/cursor.c", line 240, in resetcursor
apsw.CorruptError: CorruptError: database disk image is malformed


Is there still hope for rescue?

(Translated from German using Google Translate)

[theducks]

Is this drive a direct connected drive (eg. USB).
Calibre does not support shared, Network or cloud drives. Delays lead to corruption.

[Reader4106]

Yes, I think so. It is a thunderbolt device connected by an USB-adapter.
The disk still works properly.

[theducks]

Originally Posted by Reader4106

Yes, I think so. It is a thunderbolt device connected by an USB-adapter.
The disk still works properly.

Back it (the Library folder) up and try the Calibre recovery. This uses the metadata.opf in each folder to recover all the calibre GUI metadata. (Using Add books, only uses what is IN the book)

The only reason I can figure is the computer or disk powered down abnormally.

[Reader4106]

Back it (the Library folder) up and try the Calibre recovery.

-Does this mean that the entire library folder is to be duplicated?

try the Calibre recovery.

-How can Calibre recovery be started?

The only reason I can figure is the computer or disk powered down abnormally.

That's exactly what I suspect.

Traceback (most recent call last):
File "calibre/gui2/main.py", line 323, in initialize_db
File "calibre/db/legacy.py", line 197, in __init__
File "calibre/db/cache.py", line 190, in __init__
File "calibre/db/cache.py", line 474, in initialize_fts
File "calibre/db/cache.py", line 485, in start_fts_pool
File "calibre/db/cache.py", line 85, in call_func_with_lock
File "calibre/db/cache.py", line 597, in queue_next_fts_job
File "calibre/db/cache.py", line 500, in _update_fts_indexing_numbers
File "calibre/db/fts/connect.py", line 70, in number_dirtied
File "calibre/db/fts/connect.py", line 59, in get_connection
File "calibre/db/fts/connect.py", line 42, in initialize
File "calibre/db/backend.py", line 433, in execute
File "src/cursor.c", line 240, in resetcursor
apsw.CorruptError: CorruptError: database disk image is malformed

This error message isn't very long.
With this, along with the necessary background knowledge and suitable tools, could one repair the database "by hand"?

And for a better understanding:
Does the data stored in the respective file (/Volumes/eBooks_LaCie/Calibre_2023_08_12/Author/Book/metadata.opf) contain all the data present in the respective book record? Or only the data stored in the book metadata?

[DNSB]

Originally Posted by Reader4106

-Does this mean that the entire library folder is to be duplicated?

Yes. In case you can not recover the database due to multiple failures, you should be able to recover most of your books by creating a new library and then using the Add Books from folders and sub-folders option.

Originally Posted by Reader4106

-How can Calibre recovery be started?

With no device connected, click on the Library icon => Library Maintenance => Restore database.

Originally Posted by Reader4106

This error message isn't very long.
With this, along with the necessary background knowledge and suitable tools, could one repair the database "by hand"?

Using calibre's built-in tool is a lot faster and safer than trying to do the task by hand even if you have much experience with SQLite.

Originally Posted by Reader4106

And for a better understanding:
Does the data stored in the respective file (/Volumes/eBooks_LaCie/Calibre_2023_08_12/Author/Book/metadata.opf) contain all the data present in the respective book record? Or only the data stored in the book metadata?

The metadata.opf files in each book folder will allow recovering the database. I am not absolutely certain about some of the odder custom columns but think they are covered.

You can also run the database recovery from a command line which may help in some cases where the GUI is having issues. In a terminal session, the command line below should work but again do a backup of the library before using it. You can also run calibredb alone to see more of it's options.

/Applications/calibre.app/Contents/MacOS/calibredb restore_database

[theducks]

The restore process uses metadata_db_prefs_backup.json
That has all the structure of metadata.db.
So any custom column should be created as it was (includes 'from other columns'), the the restore of the entries happens.