Thread: Libra How to fix a (minor?) database error?
View Single Post
Old 12-02-2021, 10:48 PM   #8
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 24,905
Karma: 47303824
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
Quote:
Originally Posted by Mister L View Post
Thanks, I tried doing it locally, but i still got the same error on the second command:
Spoiler:
sqlite> REINDEX sqlite_autoindex_content_1;
Error: stepping, UNIQUE constraint failed: content.ContentID (19)


When I tried opening the database in a GUI editor (SQLite Expert Personal) to delete the row 103877, possibly because I have no idea what I am doing, it made it worse:
Spoiler:

Résultat de l'exécution de la commande 'PRAGMA integrity_check' sur la base de données du dispositif Kobo:


*** in database main ***
On tree page 56787 cell 0: Rowid 103877 out of order
row 81794 missing from index content_bookid_index
row 81794 missing from index sqlite_autoindex_content_1
row 81795 missing from index content_bookid_index
wrong # of entries in index sqlite_autoindex_content_1

So I put back the original version from before I started tinkering with it and I am back to the original 1 error again.

I am not sure what I'm doing wrong, maybe this is too complicated for my (lack of) skill.
I think the main issue is the error in "sqlite_autoindex_content_1". The unique constraint error is because there are two rows in the "content" table with the same value in the "ContentID" column. That, of course, shouldn't happen. The content table contains the details of all the books on the device. There is one row for every book plus rows for each ToC entry in the books (with some complications). Removing the book that is causing the problem might fix it.

You should be able to find the book by running the SQL statement:

Code:
SELECT ContentId, COUNT(*)
FROM content
GROUP BY ContentId
HAVING COUNT(*) > 1
That will hopefully produce one result for the book causing the problem. The ContentId will tell us which book it is. It should start with "/mnt/onboard". If you are using calibre to sideload, then I'd expect it to look something like:

Code:
file:///mnt/onboard/Testing/Fussell, Paul/Great War and Modern Memory, The - Paul Fussell.kepub.epub
The title and author are in that and you can find it and delete it in the device. Or, find it in when the device is connected and delete it. If you do that, when you disconnect, the device should detect the missing book and remove it.

Quote:
Plan B: if I logout/login (effectively deleting the database and creating a new one, if I've understood correctly), is there a way to import from Calibre to the Kobo my annotations, collections, reading positions / dates, and statuses? These are all saved in Calibre using the Kobo Utilities plugin.
If you are saving the reading status, the can be restored to the device. Collections can be maintained through the driver. If you are doing this manually, then you can get the collections from the device and store them in calibre and then restore them later.

The annotations can be fetched using the Annotations plugin, but, they can't be restored. There isn't enough information stored to be able to do this. They are stored in the database and the table can be manually copied with a bit of work.
davidfor is offline   Reply With Quote