Restored metadata.db and original metadata.db different sizes?

[ilovejedd]

Did something stupid with my library and instead of restoring metadata.db from my primary backup, I accidentally overwrote it with the bad metadata.db. I did a database restore (from opf, which thankfully hadn't updated yet).

The restored metadata.db is just 4MB while the original metadata.db was 20MB. A quick spot check seems to show the books and metadata are correct albeit by no means was the verification exhaustive. What could cause the disparity in file size and what information might be missing from the restored database?

[BetterRed]

Relational databases accumulate dross over time, mainly due to deletions and replacements, so they need to be optimised occasionally - aka as 'vacuuming' Ψ²

By rebuilding it you have eliminated the dross. FYI - the Check database function does a 'vacuum' as its first step.

BR

[theducks]

Originally Posted by BetterRed

Relational databases accumulate dross over time, mainly due to deletions and replacements, so they need to be optimised occasionally - aka as 'vacuuming' Ψ²

By rebuilding it you have eliminated the dross. FYI - the Check database function does a 'vacuum' as its first step.

BR

I agree with all you said. But...

not the 4 times bigger part.
Everything (in the defining tables) would have to have been redefined, many times to get that huge.

I DO recommend 'quarterly' housekeeping (Calibre Maintenance) on any Library with frequent Metadata defining changes. FWIW in a RDB you define an Author: J.D. Robb. You assign that defined author INDEX (not visible to a normal user) to a book entry. , The same is true for all column types except Comments. That is a BLOB.

[ilovejedd]

I run calibredb check_library everytime I backup so the original metadata.db has already been vacuumed (from 27MB to 20MB, iirc).

I actually created a copy of the library and put the bad metadata.db. There were just some tags changed between bad and original, the metadata.db sizes are really close. Vacuuming that gave me just ~100KB reduction.

I really think something else was lost with the restore.

[jhowell]

Some plugins store library-specific data in the database.

[BetterRed]

@theducks - I overlooked the dimensions of the issue. But I have done a 'vacuum' on a database that reduced its size by 90%, admittedly that was a high volume TP application for an investment bank using Oracle on a Vaxen cluster. My predecessor was too scared to do any housekeeping of that sort, so it had been churning away 365*24*7 for several years :lol:

Originally Posted by jhowell

Some plugins store library-specific data in the database.

I have a vague recollection of a plugin that shovelled the entire text of books into a comments like custom column so it could do fast searches. It may not have been a 'known' plugin (i.e. with an entry in the Index) but one that someone asked me to test.

If you remove a plugin, then it won't remove its library specific data from metadata.db (or its global data from the config plugins directory). The Job Spy plugin has a tool to remove data left behind in library databases by defunct plugins.

BR

[kovidgoyal]

There are many thing in the db that a restore will not restore. Plugin data, book specific conversion settings, etc.

A restore is basically designed to restore only your book metadata, as that is the most precious thing. A restore should really be a last resort thing to do if you dont have backups.

And you can often recover a bad sqlite db by dumping it to sql and using the sql to re-create it, you might need to manually edit the sql a little to fix errors.

[ilovejedd]

Originally Posted by kovidgoyal

There are many thing in the db that a restore will not restore. Plugin data, book specific conversion settings, etc.

A restore is basically designed to restore only your book metadata, as that is the most precious thing. A restore should really be a last resort thing to do if you dont have backups.

And you can often recover a bad sqlite db by dumping it to sql and using the sql to re-create it, you might need to manually edit the sql a little to fix errors.

Thanks! Looks like the conversion settings (for 4,000+ books) was the culprit. I always do bulk conversions so I never realized that per book conversion settings were getting stored in metadata.db. I'll need to make a habit of deleting those via bulk metadata edit. I do versioned backups of metadata.db (sometimes up to 5 times in one day) so that should save quite a bit of space.

Yep, the metadata is the important part. The database wasn't actually bad or corrupted at all. I just did something stupid and accidentally changed tags that I didn't mean to change and restoring the old tags was priority. Anyway, I actually do have copies of the library in several flash drives that I forgot in the office. I just didn't want to wait until Monday to test out some things so I tried out the Restore database function and I'm happy to report it worked wonderfully. I'll nuke this library and restore from backup but in the meantime, I was able to test a bunch of regex stuff for search and replace, and MCS as well as templates for save to disk.