Calibre metadata.db contains errors... Calibre2opds cannot open file.

[anamardoll]

I'm having a spot of trouble.

I noticed a few days ago that Calibre2opds isn't able to run anymore. While testing through with the developer, it came out that he's not able to open my metadata.db file on his end.

Husband and I are programmers, and he has SQLiteExpert on his computer, so he snagged a copy of my metadata.db file and tried to open it. The following errors were thrown:

  2.6 Message : The following errors were encountered while refreshing schema:

Error refreshing schema for view "meta":_no such function:_sortconcat
Error refreshing schema for view "tag_browser_filtered_authors":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_custom_column_2":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_custom_column_3":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_publishers":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_ratings":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_series":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_tags":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_series":_no such function:_title_sort.

I'm on Calibre version 0.8.29. It looks like the database file starting throwing these errors about a month or two ago. Any help in resolving this would be welcome -- can I "start over" with a clean db file somehow? Thank you.

[itimpi]

There is a Library->Library Maintenance->Restore Database option that will rebuild a damaged Calibre database from the underlying files in the library.

[theducks]

did you run the Library Maintenance tool in Calibre (Library Icon arrow)?

[chaley]

Originally Posted by anamardoll

I'm having a spot of trouble.

I noticed a few days ago that Calibre2opds isn't able to run anymore. While testing through with the developer, it came out that he's not able to open my metadata.db file on his end.

Husband and I are programmers, and he has SQLiteExpert on his computer, so he snagged a copy of my metadata.db file and tried to open it. The following errors were thrown:

  2.6 Message : The following errors were encountered while refreshing schema:

Error refreshing schema for view "meta":_no such function:_sortconcat
Error refreshing schema for view "tag_browser_filtered_authors":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_custom_column_2":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_custom_column_3":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_publishers":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_ratings":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_series":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_filtered_tags":_no such function:_books_list_filter
Error refreshing schema for view "tag_browser_series":_no such function:_title_sort.

I'm on Calibre version 0.8.29. It looks like the database file starting throwing these errors about a month or two ago. Any help in resolving this would be welcome -- can I "start over" with a clean db file somehow? Thank you.

The calibre DB contains several views that make use of external python and C functions. Any operation that touches those views will fail with errors like the ones you see. The DB also has update and insert triggers that use functions, and any attempt to write the DB will toss errors.

The errors do not mean that the DB is corrupted. Can calibre open the DB (the library)? If so, then there is strong reason to believe that the DB is OK and the problem is somewhere else.

[anamardoll]

Originally Posted by itimpi

There is a Library->Library Maintenance->Restore Database option that will rebuild a damaged Calibre database from the underlying files in the library.

I tried that just now, but that didn't work -- Calibre2opds still crashes out when it tries to run.

(Additionally, WOW, that is a nuke option. Erased all my custom columns, so thank goodness I backed everything up first.)

Running a check database in Calibre now, but not hopeful.

---

As for the "oh, if Calibre works, the error must be elsewhere" post, if the database file can't be opened by Calibre2opds or SQLite, then there's a problem because that's relatively new.

[chaley]

Originally Posted by anamardoll

I tried that just now, but that didn't work -- Calibre2opds still crashes out when it tries to run.

(Additionally, WOW, that is a nuke option. Erased all my custom columns, so thank goodness I backed everything up first.)

Running a check database in Calibre now, but not hopeful.

Restore database restores the custom columns. If they are gone, then more than the database is messed up. The .opf files in the book directories are also corrupted.

As for the "oh, if Calibre works, the error must be elsewhere" post, if the database file can't be opened by Calibre2opds or SQLite, then there's a problem because that's relatively new.

I said "strongly suggests", not "must be".

Are you sure that SQLiteExpress does not try to open/load the views? If it does, then it will fail, because the functions are not available. SQLiteSpy (at least V1.8.12) does not open the views when it opens the DB.

[anamardoll]

Originally Posted by chaley

Restore database restores the custom columns. If they are gone, then more than the database is messed up. The .opf files in the book directories are also corrupted.I said "strongly suggests", not "must be".

Are you sure that SQLiteExpress does not try to open/load the views? If it does, then it will fail, because the functions are not available. SQLiteSpy (at least V1.8.12) does not open the views when it opens the DB.

Starting backward.

Husband says that yes, SQLiteExpert is trying to open/load the views. So... mystery solved, I guess. But the problem remains that Calibre2opds cannot open my metadata.db file and if neither the tool nor the developer can open it *and* it was working in December and I've changed nothing except update Calibre, then it seems like something is wrong.

Restore database retained my custom columns, but lost all the tagging data associated with them. So custom column "Source" was still there, but the actual data that X book was Source --> Amazon was gone. You say this means that the opf files are corrupt. Yay?

I have 1,000+ in my Calibre library. I hope there's a way to solve/fix this without having to re-input every single one. Because that would make me very sad indeed.

[anamardoll]

Added: I believe the Calibre2opds developer is using SQLiteSpy and he wasn't able to open my metadata.db file either.

[kovidgoyal]

You need to provide an actual error message that occurs when you try to open the database. The one you provided is meaningless as it is caused by trying to use functions that are not available.

If you want to test the validity of the sqlite database use the official sqlite3 command line client from sqlite.org

sqlite3 metadata.db .dump > dump.sql

You will see that the sqlite database is converted into SQL statements with no issues. calibre itself uses the official sqlite3 libraries.

[chaley]

Originally Posted by anamardoll

Husband says that yes, SQLiteExpert is trying to open/load the views. So... mystery solved, I guess. But the problem remains that Calibre2opds cannot open my metadata.db file and if neither the tool nor the developer can open it *and* it was working in December and I've changed nothing except update Calibre, then it seems like something is wrong.

My guess is that you added books or changed metadata. If calibre2opds has data sensitivities, these changes could prevent it from working.

If you upgraded calibre from a version older than 0.8.9, then calibre's DB will also have been upgraded. It might be necessary to upgrade calibre2opds.

Restore database retained my custom columns, but lost all the tagging data associated with them. So custom column "Source" was still there, but the actual data that X book was Source --> Amazon was gone. You say this means that the opf files are corrupt. Yay?

The values to restore for a book are in the OPF. If the values are not being restored, then that argues that the OPF is not correct. However, looking at the code it might be that I didn't handle enumerated column types (column with fixed set of values) correctly, especially if the enumeration changed after it was used. Is "Source" an enumerated column? If the problem I suspect exists, then during the restore you would have seen a message that "Source" has conflicting custom column definitions. Did you?

[EDIT] The restore code is correct after all. It obtains the list of permitted values from the latest OPF, so changes to the list are accounted for.

You can ensure that the column definitions and OPF contents are correct by using Library -> Library Maintenance -> Start backing up metadata ... This will rewrite all the OPFs. It can take a while, as calibre writes a book a second (or thereabouts).

Another point that follows on Kovid's suggestion: calibre's "Check Library" function dumps the database to SQL, recreates it, then restores it from the dumped SQL. This process will eliminate any structural corruption (unless the official SQLite is broken) and will also eliminate any data integrity corruption. Restore your backup, start calibre, and run check library. If that completes successfully, and if calibre can be stopped and restarted, then the probability of DB structural or data corruption approaches zero. Try calibre2opds. If it still cannot open the database, then one should look carefully at calibre2opds for the problem.

[DoctorOhh]

Originally Posted by anamardoll

I noticed a few days ago that Calibre2opds isn't able to run anymore. While testing through with the developer, it came out that he's not able to open my metadata.db file on his end.

I've read the entire thread and I may have missed it but I didn't see any place where you state that calibre is having trouble with the metadata.db.

I'm curious, when you open calibre does it work?

[itimpi]

Just for the record - I do not know of any issues with Calibre2Opds working with the calibre database at any version level.

[DoctorOhh]

Originally Posted by itimpi

Just for the record - I do not know of any issues with Calibre2Opds working with the calibre database at any version level.

That's what confused me about the wording of the problem.

[anamardoll]

I guess I'm not explaining myself very clearly, sorry: yes, Calibre works.* But Calibre2opds doesn't, it can't open the metadata.db file, I've been dealing with the Calibre2opds developer (thank you, by the way!) for over a week, and since he can't open my metadata file with his SQ tools, he can't diagnose the problem further.

And the only thing I've changed in the 8+ months of running Calibre2opds weekly is that I upgraded Calibre. So I've got a system that worked great and now doesn't, and I'm looking at the single point of change that I'm aware of as the possible introduction of the error.

(* Although for about the same amount of time I've had "error sending metadata to device" errors when using Calibre with my two Sony readers. I thought that was an unrelated problem, but it may not be.)

---

I did run a check library yesterday and it said everything was kosher except it didn't like the Calibre2opds catalog folders in the Calibre library directory. But they've always been there before with no problem.

I'll run the "Library -> Library Maintenance -> Start backing up metadata ..." now, thank you, since we're about to go get groceries and you say it takes awhile.

I'll run "sqlite3 metadata.db .dump > dump.sql" when we get back, thank you.

[chaley]

Originally Posted by anamardoll

But Calibre2opds doesn't, it can't open the metadata.db file, I've been dealing with the Calibre2opds developer (thank you, by the way!) for over a week, and since he can't open my metadata file with his SQ tools, he can't diagnose the problem further.

If you send me the metadata.db, I will take a look at it. If I can't open it, then I can probably determine why. If I can, then we have more information. Send it to calibre-bugs at charles dot haleys dot org.

Do note that with your database I will be able to see the metadata of the books in your library. If you consider that a privacy violation (which I can understand), don't send it to me.

And the only thing I've changed in the 8+ months of running Calibre2opds weekly is that I upgraded Calibre. So I've got a system that worked great and now doesn't, and I'm looking at the single point of change that I'm aware of as the possible introduction of the error.

Are you running windows -- perhaps windows update changed a system library? Perhaps you updated your java version? Perhaps bit rot? Very hard to know.

I'll run the "Library -> Library Maintenance -> Start backing up metadata ..." now, thank you, since we're about to go get groceries and you say it takes awhile.

Although running this is a good idea, I don't think that at this point you should rebuild your database. If calibre is causing the problem, then it will almost certainly still be there after the rebuild.